MFT/car, MAU/car , NR/Car - car model wise - Rahil

set @start = "2017-01-01 00:00:00";
select
A.month as month, A.year as year, A.active_zones, A.active_cars, A.cumulative_cars,
IFNULL(D.nuse,0) as resv, IFNULL(round(D.Dur,2),0) as dur, IFNULL(round(D.ad_sdur,2),0) as adj_dur, IFNULL(round(D.rental,2),0) as gross_rev, IFNULL(round(D.discount,2),0) as coupon, IFNULL(round((D.rental - D.discount),2),0) as Net_Rev,
IFNULL(B.ActiveMembers,0) as WAU, IFNULL(C.totalmfts,0) as MFTs,
#IFNULL(round(((D.nuse)/A.cumulative_cars),2),0) as Res_PCPD,IFNULL(round(((D.Dur)/A.cumulative_cars),2),0) as Dur_PCPD ,IFNULL(round(((D.ad_sdur)/A.cumulative_cars),2),0) as adj_dur_PCPD,
ROUND(IFNULL(B.ActiveMembers/A.cumulative_cars,0),2) as Wau_PCPD, ROUND(IFNULL(C.totalmfts/B.ActiveMembers,0),2) as MFT_rate,
IFNULL(round(((D.rental - D.discount)/B.ActiveMembers),2),0) as Netrev_perWAU,
 IFNULL(round(((D.rental - D.discount)/A.cumulative_cars),2),0) as Netrev_PCPD , ROUND(IFNULL(D.nuse/B.ActiveMembers,0),2) as resv_perWAU
from
#### Number of cars
(select
month(czl.log_date + interval 8 hour ) as month,
year(czl.log_date + interval 8 hour ) as Year,
count(distinct czl.zone_id) as active_zones,
count(distinct czl.car_id) as active_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)
and cc.id in (1)
group by 1,2
order by 2 asc , 1 asc) 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,
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 c on c.id = r.car_id
left join car_classes cc on cc.id = c.car_class_id
join members m
on m.id = r.member_id and m.imaginary = 'normal'
where r.return_at + interval '8' hour >= @start
and cc.id in (1)
group by 1,2
order by 2 asc , 1 asc) B
on A.month = B.month and A.year = B.year
left join
######### Number of MFTs
(select A.month as month,
    A.year as year,
    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, cc.id as ccid,
    min(r.id) as rid
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 c on c.id = r.car_id
left join car_classes cc on cc.id = c.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 and cc.id in (1)
group by r.member_id) B
on A.mid = B.mid
where B.ccid in (1)
group by 1,2
order by 2 asc , 1 asc ) C
on A.month = C.month and A.year = C.year
left join
####### for Number of Hours
(select month(r.return_at+interval 8 hour) as month,
year(r.return_at+interval 8 hour) as year,
count(r.id) as nuse,
    round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
    round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
    sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance')),0)) as rental,
    sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)) as discount
    #count(distinct m.id) as mau
from reservations r, members m , zones z , cars c , car_classes cc
where r.member_id = m.id
and r.start_zone_id = z.id
and r.car_id = c.id
and c.car_class_id = cc.id
and r.state = 'completed'
and m.imaginary in ('normal', 'sofam')
and r.return_at+interval 8 hour >= @start
and r.start_zone_id not in (2, 3, 101)
and cc.id in (1)
group by 1,2
order by 2 asc , 1 asc) D
on D.month = A.month and D.year = A.year
group by A.month, A.year
order by 2 asc , 1 asc