Query for 1 way trips - Age, Duration, MFTs - Rahil

select
A.date,
A.mid memberid,
A.age age,
A.dur as duration,
A.rid reservationid,
sum(p.amount) net,
sum(A.gross) gross,
B.firstbooking as firstbookingid,
if(B.firstbooking = A.rid,"MFT","non-mft") as MFT_yes_no
from
(select
date(r.return_at + interval '8' hour) as date,
r.id as rid,
c.member_id as mid,
round((timestampdiff(hour, r.start_at, r.end_at)),2) as dur,
YEAR(r.start_at) - YEAR(m.birthday) as age,
sum(c.amount) as gross
from charges c
join reservations r on r.id = c.reservation_id
join members m on m.id = c.member_id
where r.way  = 'oneway'
and r.state = 'completed'
group by rid
order by rid  desc) A
left join
(select min(r.id) as firstbooking,
r.member_id as mid
from reservations r
where r.state = 'completed'
group by mid
order by mid  desc) B
on B.mid = A.mid
left join payments p  on p.reservation_id = A.rid
left join reservations r on r.id = A.rid
where p.paid_type = 'card'
and r.state = 'completed'
group by A.rid
order by A.mid desc