Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- set @start := '2018-12-31 00:00';
- select
- A.Week,
- A.signups,
- B.DocUploaded,
- B.DocApproved,
- D.TotalMFTs,
- C.ActiveMembers,
- C.Bookings as rentals,
- C.ridelength
- from (select
- WEEKOFYEAR(m.created_at + interval '8' hour) as Week,
- 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 >= @start
- and m.imaginary = 'normal'
- group by 1 ) A
- join (select
- WEEKOFYEAR(dl.created_at + interval '8' hour) as Week,
- count(distinct case when dl.state not like 'noInput' then dl.member_id end) DocUploaded,
- count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
- count(distinct case when dl.gender = 'man' then dl.member_id end) MaleApproved,
- count(distinct case when dl.gender = 'woman' then dl.member_id end) FemaleApproved
- from driver_licenses dl
- join members m
- on m.id = dl.member_id
- where dl.created_at + interval '8' hour >= @start
- and m.imaginary = 'normal'
- group by 1) B
- on B.Week = A.Week
- join (select
- WEEKOFYEAR(r.occupy_start_at + interval '8' hour) as Week,
- count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
- count(distinct case when r.state = 'completed' then r.id end) as Bookings,
- sum(timestampdiff(minute,CONVERT_TZ(r.start_at, '+00:00', '+8:00'), CONVERT_TZ(r.end_at, '+00:00', '+8:00'))/60) as ridelength,
- sum(G.amount) as Charges
- from reservations r
- left outer join (select
- ch.reservation_id as rid,
- sum(ch.amount) as amount
- from payments ch
- group by 1
- ) G on G.rid = r.id
- join members m
- on m.id = r.member_id
- where r.start_at + interval '8' hour >= @start
- and m.imaginary in ('normal')
- and r.state = 'completed'
- group by 1) C on
- C.Week = B.Week
- join (
- select
- WEEKOFYEAR(c.created_at + interval '8' hour) as Week,
- 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 >= @start
- and m.imaginary = 'normal'
- group by 1) D on
- D.Week = C.Week
- group by 1
- order by 1 asc;
- Select
- weekofyear(t1.date) as week,
- t1.date,
- t1.ncars,
- t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff
- from
- (select
- Date(date_sub(czl.log_date , interval 1 day)) as date,
- weekday(date_sub(czl.log_date , interval 1 day)) as weekday,
- count(distinct czl.car_id ) as ncars
- from car_zone_logs czl
- where czl.log_date + interval '8' hour >= @start
- and czl.car_state = 'normal'
- and czl.zone_state = 'normal'
- and czl.csa_state = 'normal'
- and weekday(date_sub(czl.log_date , interval 1 day)) = 6
- group by 1) t1
- left join
- (select
- Date(date_add(czl.log_date , interval 7 day)) as date,
- weekday(date_add(czl.log_date , interval 7 day)) as weekday,
- count(distinct czl.car_id ) as ncars
- from car_zone_logs czl
- where czl.log_date + interval '8' hour >= @start
- and czl.car_state = 'normal'
- and czl.zone_state = 'normal'
- and czl.csa_state = 'normal'
- and weekday(date_sub(czl.log_date , interval 1 day)) = 6
- group by 1) t2
- on t1.date = t2.date - 1
- order by t1.date ;
- set @start2 := '2019-01-01 00:00';
- select
- A.Week,
- A.signups,
- B.DocUploaded,
- B.DocApproved,
- D.TotalMFTs,
- C.ActiveMembers,
- C.Bookings as rentals,
- C.ridelength
- from (select
- month(m.created_at + interval '8' hour) as Week,
- 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 >= @start2
- and m.imaginary = 'normal'
- group by 1 ) A
- join (select
- month(dl.created_at + interval '8' hour) as Week,
- count(distinct case when dl.state not like 'noInput' then dl.member_id end) DocUploaded,
- count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
- count(distinct case when dl.gender = 'man' then dl.member_id end) MaleApproved,
- count(distinct case when dl.gender = 'woman' then dl.member_id end) FemaleApproved
- from driver_licenses dl
- join members m on m.id = dl.member_id
- where dl.created_at + interval '8' hour >= @start2
- and m.imaginary = 'normal'
- group by 1) B
- on B.Week = A.Week
- join (select
- month(r.occupy_start_at + interval '8' hour) as Week,
- count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
- count(distinct case when r.state = 'completed' then r.id end) as Bookings,
- sum(timestampdiff(minute,CONVERT_TZ(r.start_at, '+00:00', '+8:00'), CONVERT_TZ(r.end_at, '+00:00', '+8:00'))/60) as ridelength,
- sum(G.amount) as Charges
- from reservations r
- left outer join (select
- ch.reservation_id as rid,
- sum(ch.amount) as amount
- from payments ch
- group by 1
- ) G on G.rid = r.id
- join members m on m.id = r.member_id
- where r.start_at + interval '8' hour >= @start2
- and m.imaginary in ('normal')
- and r.state = 'completed'
- group by 1) C on
- C.Week = B.Week
- join (
- select
- month(c.created_at + interval '8' hour) as Week,
- 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 >= @start2
- and m.imaginary = 'normal'
- group by 1) D on
- D.Week = C.Week
- group by 1
- order by 1 asc;
- set @start := '2018-12-31 00:00';
- Select
- t1.date,
- t1.ncars,
- t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff_cars,
- t1.nzones,
- t1.nzones - COALESCE(t2.nzones, t1.nzones) AS diff_zones
- from
- (select
- Month(date_sub(czl.log_date , interval 1 day)) as date,
- LAST_DAY(date_sub(czl.log_date , interval 1 day)) as weekday,
- count(distinct czl.car_id ) as ncars,
- count(distinct czl.zone_id ) as nzones
- from car_zone_logs czl
- where czl.log_date + interval '8' hour >= @start
- and czl.car_state = 'normal'
- and czl.zone_state = 'normal'
- and czl.csa_state = 'normal'
- and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
- group by 1) t1
- left join
- (select
- Month(date_add(czl.log_date , interval 1 month)) as date,
- LAST_DAY(date_add(czl.log_date , interval 1 month)) as weekday,
- count(distinct czl.car_id ) as ncars,
- count(distinct czl.zone_id ) as nzones
- from car_zone_logs czl
- where czl.log_date + interval '8' hour >= @start
- and czl.car_state = 'normal'
- and czl.zone_state = 'normal'
- and czl.csa_state = 'normal'
- and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
- group by 1) t2
- on t1.date = t2.date
- order by t1.date;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement