MFT Potential by car model - Rahil for Prashant

set @start := '2017-07-1';
select
A.car_name as car_name, A.month as month, A.year as year, A.number_of_cars, A.cumulative_cars, IFNULL(B.ActiveMembers,0) as WAU, IFNULL(C.totalmfts,0) as MFTs,
IFNULL(round(((C.totalmfts)/A.cumulative_cars),2),0) as MFT_per_Car ,
ROUND(IFNULL(B.ActiveMembers/A.number_of_cars,0),2) as Wau_percar,
ROUND(IFNULL(C.totalmfts/B.ActiveMembers,0),2) as MFT_perMAU
from
#### Number of cars
(select
month(czl.log_date + interval 8 hour ) as month,
year(czl.log_date + interval 8 hour ) as year,
cc.car_name as car_name,
z.region as region,
count(distinct czl.car_id) as number_of_cars ,  count(distinct czl.id) as cumulative_cars
from car_zone_logs czl
left join cars cr on cr.id = czl.car_id
left join car_classes cc on cc.id = cr.car_class_id
left join zones z on czl.zone_id = z.id
where czl.zone_state= 'normal'
and czl.car_state = 'normal'
and czl.log_date + interval 8 hour >= @start
and z.id not in (2, 3, 101)
group by 1,2,3
order by 1,2 desc) A
left join
############## Number of Active Members
(select
month(r.return_at + interval '8' hour) as month,
Year(r.return_at + interval '8' hour) as year,
cc.car_name as car_name,
count(distinct case when r.state NOT IN ('canceled', 'fail') then r.member_id end) as ActiveMembers,
count(distinct case when r.state NOT IN ('canceled', 'fail') then r.id end) as resv
from reservations r
join zones z on z.id = r.start_zone_id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_id
join members m
on m.id = r.member_id and m.imaginary = 'normal'
where r.return_at + interval '8' hour >= @start
group by 1,2,3
order by 1 asc) B
on A.month = B.month and A.car_name = B.car_name and B.year = A.year
left join
######### Number of MFTs
(select
A.month as month,
A.year as year,
B.car_name as car_name,
count(A.mid) as totalmfts
from
(select
distinct c.member_id as mid, Month(c.created_at + interval '8' hour) as month, Year(c.created_at + interval '8' hour) as year
from charges c
where c.kind = 'subscriptionFee' and c.created_at + interval '8' hour >= @start) A
left join
(select
distinct r.member_id as mid, min(r.id) as rid,cc.car_name as car_name,
z.id as zid
from reservations r
join members m on r.member_id = m.id
join zones z on r.start_zone_id = z.id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_id
where m.imaginary in ('sofam', 'normal') and r.member_id not in ('125', '127') and r.start_at + interval '8' hour >= @start
group by r.member_id) B
on A.mid = B.mid
where B.zid is not null
group by A.month, A.year , car_name ) C
on A.month = C.month and A.car_name = C.car_name and A.year = C.year
group by A.month, A.year , A.car_name
order by 2 asc , 3 asc