One way trips- Frequency - Hoon - Rahil

Select A.mid as memberid,
B.rid as rid,
B.way as way,
B.reservationdate as res_date,
Date(C.MFTDate) as MFTdate,
date(C.lastactivedate) as lastactivedate,
ROUND(D.rev,2) as gross_amount,
ROUND(IFNULL(E.coupon,0),1) as coupon_amount,
ROUND(IFNULL(D.rev-E.coupon,0),2) as Net_amount
from
(select
distinct r.member_id as mid
from reservations r
join members m
on m.id = r.member_id
where r.way  = 'oneway' and m.state = 'normal' and m.imaginary = 'normal' and r.end_zone_id in ('244','245')) A
join
(select
m.id as memberid,
r.id as rid,
r.way as way,
Date(r.start_at + interval '8' hour) as reservationdate
from  members m
left outer join reservations r on m.id = r.member_id
Where r.start_at != 0 and m.imaginary = 'normal' and r.state = 'completed'
group by m.id,r.id) B on B.memberid = A.mid
left join
(select
m.id as memberid,
TIMESTAMPDIFF(YEAR, str_to_date(m.birthday, '%Y%m%d'), Now()) AS age,
min(r.start_at) as MFTdate,
Max(r.start_at) as lastactivedate
from  members m
left outer join reservations r on m.id = r.member_id
join charges c on m.id = c.member_id
Where r.start_at != 0
and m.imaginary = 'normal'
and c.kind = 'subscriptionFee'
and r.state = 'completed'
group by m.id) C  on B.memberid = C.memberid
left join
(select sum(c.amount) as rev, c.reservation_id as rid
from charges c
where c.state = 'normal' and c.kind IN ('rent' , 'oneway', 'd2d', 'mileage')
group by c.reservation_id) as D ON B.rid = D.rid
left join
(select IFNULL(p.amount,0) as coupon, p.reservation_id as rid
from payments p
where p.state = 'normal' and p.paid_type = 'coupon') as E on E.rid = B.rid