Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- set @start := '2019-07-1';
- select
- A.city, A.region as region, 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(D.nuse,0) as resv,
- IFNULL(round(D.Dur,2),0) as duration, 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(round(((D.nuse)/A.cumulative_cars),2),0) as Res_PC_PD ,IFNULL(round(((D.Dur)/A.cumulative_cars),2),0) as Dur_PC_PD ,IFNULL(round(((D.ad_sdur)/A.cumulative_cars),2),0) as adj_dur_PC_PD ,
- ROUND(IFNULL(B.ActiveMembers/A.number_of_cars,0),2) as Wau_percar,IFNULL(round(((D.rental - D.discount)/D.ad_sdur),2),0) as Netrev_P_Hour,
- ROUND(IFNULL(C.totalmfts/B.ActiveMembers,0),2) as MFT_perMAU, IFNULL(round(((D.rental - D.discount)/A.cumulative_cars),2),0) as Netrev_PC_PD , 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,
- z.city as city,
- z.region as region,
- year(czl.log_date + interval 8 hour ) as Year,
- 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,3
- order by 1,2 desc) 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 zones z on z.id = r.start_zone_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
- 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,
- 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.region as region,
- 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,region ) C
- on A.month = C.month and A.region = C.region
- left join
- ####### for Number of Hours
- (select
- month(r.return_at+interval 8 hour) as month, z.region as region, 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
- where r.member_id = m.id
- and r.start_zone_id = z.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)
- group by month , region) D
- on D.month = A.month and D.region = A.region
- group by A.month, A.region
- order by 3 asc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement