MFTs, MAUs. RESV by City - Rahil

set @startdate1 = '2017-01-01';
set @startdate2 = '2020-01-01';
use socar_malaysia;

Select A.year as year, A.month as month, A.city as city , A.duration as duration, A.resv as resv, A.mau as mau, B.cars as cars , C.totalmfts as totalmfts from
(select year(A.return_date) as year,  month(A.return_date) as month,  A.city as city, count(distinct A.mid) as mau, Sum(A.dur) as duration, Count(A.rid2) as resv  from
(select
distinct r.id as rid2, r.member_id as mid, z.city as city,
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,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
left join zones z on r.start_zone_id = z.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A
where A.city  not in ("-")
group by 1,2,3
order by 1,2 asc ) A
left join
(select year(czl.log_date + interval 8 hour ) as year, month(czl.log_date + interval 8 hour ) as month, z.city as city, count(distinct czl.car_id) as 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 z.id = czl.zone_id
where czl.zone_state = 'normal'
and czl.log_date + interval 8 hour >= @startdate1
and czl.log_date + interval 8 hour <= @startdate2
group by 1,2,3
order by 1,2 asc) B
on A.year = B.year and A.month = B.month and A.city = B.city

left join
(Select
Year(A.date) as year,
Month(A.date) as month,
count(A.mid) as totalmfts,
IFNULL(B.city,"") as City
from
(select
distinct c.member_id as mid,
date(c.created_at + interval '8' hour) as date
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,
z.city as city
from reservations r
join members m on r.member_id = m.id
join zones z on r.start_zone_id = z.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.city  not in ("-")
group by 1,2,4
order by 1,2 asc) C
on A.year = C.year and A.month = C.month and A.city = C.city