Region , Zone - Wise Reservation Dump

############################### Region wise Reservation Dump


set @start := '2019-1-1';
select
A.city,A.region, A.month, A.number_of_cars, A.cumulative_cars, IFNULL(B.ActiveMembers,0) as MAU, IFNULL(C.totalmfts,0) as MFTs, IFNULL(D.nuse,0) as resv,
IFNULL(round(D.Dur,2),0) as duration, 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/A.number_of_cars,0) as Mau_percar,
IFNULL(C.totalmfts/B.ActiveMembers,0) as MFT_perMAU, IFNULL(round(((D.rental - D.discount)/A.cumulative_cars),2),0) as Netrev_PC_PD , IFNULL(D.nuse/A.number_of_cars,0) as resv_perCar,
IFNULL(D.nuse/B.ActiveMembers,0) as rentals_perMAU
from
#### Number of cars
(select
month(czl.log_date + interval '8' hour) as month, count(distinct czl.id) as cumulative_cars,count(distinct czl.car_id) as number_of_cars,
z.id as zid, z.name as name, z.city as city, z.region as region
from car_zone_logs czl
join cars ca on ca.id = czl.car_id
join car_classes cc on cc.id = ca.car_class_id
join zones z on z.id = czl.zone_id
Where czl.log_date >= @start
and czl.zone_id not in (2,3,101,383,378,712,818,786)
group by month,region) A
left join
############## Number of Active Members
(select
month(r.return_at + interval '8' hour) as month,
z.region as region,
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 members m
on m.id = r.member_id
left join zones z on z.id = r.start_zone_id
where r.return_at + interval '8' hour >= @start
and m.email not like '%socar.my%' and m.imaginary = 'normal'
group by 1,2
order by 1 asc) B
on A.month = B.month and A.region = B.region
left join
######### Number of MFTs
(select
A.month as month,
IFNULL(B.zid,"") as zid,
IFNULL(B.region,"") as region,
count(A.mid) as totalmfts
from
(select
distinct c.member_id as mid, Month(c.created_at + interval '8' hour) as month, WEEKOFYEAR(c.created_at + interval '8' hour) as Week
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.id as zid,z.region as region
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.zid is not null
group by A.month,region ) C
on A.month = C.month and A.region = C.region
left join
####### for Number of Hours
(select
month(r.return_at) as month,  z.region as region, count(r.id) as nuse,
sum(timestampdiff(minute, r.start_at, r.return_at)/60) as dur,
sum((timestampdiff(minute, r.start_at, r.end_at)/60)/24)*12 + if(mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)>=12,12,mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)) as adj_dur,
sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),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
where r.member_id=m.id
and r.start_zone_id = z.id
and r.way in ('round','d2d','oneway')
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,383,378,697,712,818)
group by month, region) D
on D.month = A.month and D.region = A.region
where A.month > month(now()) - 9
group by A.month, A.region
order by 3 desc;

############################### Zone wise Reservation Dump

set @start := '2019-1-1';
select
A.city,A.region, A.zid, A.name as zonename, A.month, A.number_of_cars, A.cumulative_cars, IFNULL(B.ActiveMembers,0) as MAU, IFNULL(C.totalmfts,0) as MFTs, IFNULL(D.nuse,0) as resv,
IFNULL(round(D.Dur,2),0) as duration, 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/A.number_of_cars,0) as Mau_percar,
IFNULL(C.totalmfts/B.ActiveMembers,0) as MFT_perMAU, IFNULL(round(((D.rental - D.discount)/A.cumulative_cars),2),0) as Netrev_PC_PD , IFNULL(D.nuse/A.number_of_cars,0) as resv_perCar,
IFNULL(D.nuse/B.ActiveMembers,0) as rentals_perMAU
from
#### Number of cars
(select
month(czl.log_date + interval '8' hour) as month, count(distinct czl.id) as cumulative_cars,count(distinct czl.car_id) as number_of_cars,
z.id as zid, z.name as name, z.city as city, z.region as region
from car_zone_logs czl
join cars ca on ca.id = czl.car_id
join car_classes cc on cc.id = ca.car_class_id
join zones z on z.id = czl.zone_id
Where czl.log_date >= @start
and czl.zone_id not in (2,3,101,383,378,712,818,786)
group by month,zid) A
left join
############## Number of Active Members
(select
month(r.return_at + interval '8' hour) as month,
r.start_zone_id as zid,
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 members m
on m.id = r.member_id
where r.return_at + interval '8' hour >= @start
and m.email not like '%socar.my%' and m.imaginary = 'normal'
group by 1,2
order by 1 asc) B
on A.month = B.month and A.zid = B.zid
left join
######### Number of MFTs
(select
A.month as month,
IFNULL(B.zid,"") as zid,
count(A.mid) as totalmfts
from
(select
distinct c.member_id as mid, Month(c.created_at + interval '8' hour) as month, WEEKOFYEAR(c.created_at + interval '8' hour) as Week
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.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
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,zid ) C
on A.month = C.month and A.zid = C.zid
left join
####### for Number of Hours
(select
month(r.return_at) as month,  r.start_zone_id as zid, count(r.id) as nuse,
sum(timestampdiff(minute, r.start_at, r.return_at)/60) as dur,
sum((timestampdiff(minute, r.start_at, r.end_at)/60)/24)*12 + if(mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)>=12,12,mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)) as adj_dur,
sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),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
where r.member_id=m.id
and r.start_zone_id = z.id
and r.way in ('round','d2d','oneway')
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,383,378,697,712,818)
group by month, zid) D
on D.month = A.month and D.zid = A.zid
where A.month > month(now()) - 9
group by A.month, A.zid
order by 5 desc