Hourly Trend Check - Rahil - Socorona

set @startdate1 = '2020-02-01';
set @startdate2 = '2020-04-31';
use socar_malaysia;
Select I.date as date, Q.cumulative_cars as cars, D.signups as signups, P.TotalMFTs as MFTs, I.Total_reservations as Total_reservations,   Round(I.net_rev,2) as Net_rev,
ROUND(I.Total_reservations/Q.cumulative_cars,2) as RESPCPD, ROUND(I.net_rev/Q.cumulative_cars,2) as RevPCPD,
Round(I.Dur/Q.cumulative_cars,2) as DURPCPD,  Round(I.net_rev/I.ad_sdur,2) as NRPH
from
( select date(A.return_date) as date, count(A.rid2) as Total_reservations, count(distinct A.mid) as Total_Unique_Members,
sum(A.dur) as dur, sum(A.ad_sdur) as ad_sdur,
IFNULL(Sum(E.charges),0) as gross_rev,
IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid, r.way as way,
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,
hour(r.return_at + interval '8' hour) as date
from reservations r left join members m on r.member_id = m.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and hour(r.return_at + interval 8 hour) <= hour(now() + interval 8 hour)
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, date(p.created_at + interval '8' hour) as date,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid
group by 1 ) I
left join
(select
date(czl.log_date + interval 8 hour ) as date,
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 >= @startdate1
and czl.log_date + interval 8 hour <= @startdate2
and z.id not in (2, 3, 101)
group by 1
order by 1,2 desc) Q
on Q.date = I.date
left join
(select
Date(m.created_at + interval '8' hour) as Date,
count(distinct m.id) as signups
from members m
left outer join reservations r on
r.member_id = m.id
where m.created_at + interval '8' hour >= @startdate1
and m.created_at + interval '8' hour <= @startdate2
and hour(m.created_at + interval 8 hour) <= hour(now() + interval 8 hour)
and m.imaginary = 'normal'
group by 1 ) D
on D.Date = I.Date
left join
(select
date(c.created_at + interval '8' hour) as date,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as TotalMFTs
from charges c join members m on m.id = c.member_id
where c.created_at + interval '8' hour >= @startdate1
and c.created_at + interval '8' hour <= @startdate2
and hour(c.created_at + interval '8' hour) <= hour(now() + interval 8 hour)
and m.imaginary = 'normal'
group by 1) P
on P.date = I.date
group by 1
order by 1 desc