Time, Rev, Coupon Rate - Rahil

select

A.rid,
A.memberid,
A.age,
A.couponname,
A.date,
A.ridelength ,
#A.ridelengthinminutes,
A.startingday,
A.couponactivationtime,
A.reservationstarttime,
A.startingtime,
B.regionname,
B.city,
B.carname,
#A.carid,
#B.zoneid,
A.state,
#E.rev,
IFNULL(F.rev2,0) as accidentrev,
round((IFNULL(c.rev,0) - IFNULL(p.coupon,0)),2) as net_rev,
round(IFNULL((IFNULL(p.coupon,0)/c.rev),0),2) as coupon_rate


from (select
distinct m.id as memberid,
(YEAR(r.occupy_start_at) - YEAR(m.birthday)) as age,
IFNULL(c.comment,0) as couponname,
date(r.occupy_start_at + interval '8' hour) as date,
timestampdiff(minute,CONVERT_TZ(r.start_at, '+00:00', '+8:00'), CONVERT_TZ(r.end_at, '+00:00', '+8:00'))/60 as ridelength,
TIMESTAMPDIFF(minute,CONVERT_TZ(r.start_at, '+00:00', '+8:00'), CONVERT_TZ(r.end_at, '+00:00', '+8:00')) as ridelengthinminutes,
weekday(r.occupy_start_at + interval '8' hour) as startingday,
IFNULL(timestamp(c.activated_at + interval '8' hour),0) as couponactivationtime,
timestamp(r.start_at + interval '8' hour) as reservationstarttime,
timestamp(r.occupy_start_at + interval '8' hour) as startingtime,
r.id as rid,
r.state as state,
r.car_id as carid

from members m

left outer join reservations r on m.id = r.member_id
LEFT JOIN coupons c ON c.reservation_id = r.id

where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-7-1'
and m.imaginary = 'normal'
and m.state = 'normal'
and r.state IN ('completed','inuse','reserved')

group by m.id,r.id
) A
left outer join

(select
c.id as carid,
cc.car_name as carname,
z.region as regionname,
z.city as city,
z.id as zoneid
from cars c

left outer join car_classes cc on cc.id = c.car_class_id
left outer join zones z on z.id = c.zone_id
left outer join car_zone_logs as cz on c.id = cz.car_id

where cz.car_state = 'Normal'
#and z.state = 'normal'

group by c.id) B

on A.carid = B.carid

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 c ON A.rid = c.rid

left join (
		select IFNULL(payments.amount,0) as coupon, payments.reservation_id as rid
        from payments
        where payments.state = 'normal' and payments.paid_type = 'coupon'
	) as p on p.rid = A.rid

left join (
		select sum(c.amount) as rev2, c.reservation_id as rid
        from charges c
        where c.state = 'normal' and c.kind IN ('penalty', 'accident')
        group by c.reservation_id
   ) as F ON A.rid = F.rid

group by A.memberid,B.carid,A.rid

order by net_rev desc