Rahil Leon - MFT Increase Analyses

select

A.memberid,
A.day,
A.ridelength ,
A.ridelengthinminutes,
A.startingday,
A.startingtime,
B.regionname,
B.carname,
A.rid,
A.carid,
B.zoneid,
A.state,
A.amount,
C.mft_21,
C.TotalMFTs



from (select
distinct m.id as memberid,
sum(p.amount) as amount,
date(r.occupy_start_at + interval '8' hour) as day,
timestampdiff(minute,r.start_at, r.end_at)/60 as ridelength,
TIMESTAMPDIFF(minute,r.start_at + interval '8' hour,r.end_at + interval '8' hour) as ridelengthinminutes,
weekday(r.occupy_start_at + interval '8' hour) as startingday,
hour(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 outer join payments p
on p.reservation_id = r.id

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

group by m.id,r.id
order by amount desc) A

left outer join

(select
c.id as carid,
cc.car_name as carname,
z.region as regionname,
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 outer join

(Select
ch.member_id as memberid,
count(distinct case when ch.kind = 'subscriptionFee' and str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year then ch.member_id end) as mft_21,
count(distinct case when ch.kind = 'subscriptionFee' then ch.member_id end) as TotalMFTs

from charges ch

join members m
on m.id = ch.member_id

join reservations r
on r.id = ch.member_id

where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-3-1 00:00'
and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-5-1'

group by m.id

) C

on C.memberid = A.memberid

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

order by A.amount desc