Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- set @start := CAST((now() - interval 7 day) AS DATE);
- select
- A.date AS date,
- A.hour AS hours,
- IFNULL(G.Active_installs_2019,0) as Installs,
- A.signups AS SignUps,
- CAST((A.signups/IFNULL(G.Active_installs_2019,0)) AS DECIMAL(18,2)) as "SignUps/Installs",
- B.DocUploaded AS DocUpload,
- CAST((B.DocUploaded/A.signups) AS DECIMAL(18,2)) as "DocUpload/SignUps",
- B.DocApproved,
- CAST((B.DocApproved/B.DocUploaded) AS DECIMAL(18,2)) as "DocApproved/DocUpload",
- F.cardupload as PaymentApproved,
- CAST((F.cardupload/B.DocApproved) AS DECIMAL(18,2)) as "PaymentApproved/DocApproved",
- D.TotalMFTs,
- CAST((D.TotalMFTs/F.cardupload) AS DECIMAL(18,2)) as "MFTs/PaymentApproved",
- CAST((D.TotalMFTs/A.signups) AS DECIMAL(18,2)) as "MFTs/SignUps",
- B.MaleApproved,
- CAST((B.MaleApproved/B.DocApproved) AS DECIMAL(18,2)) as "Male%",
- B.FemaleApproved,
- CAST((B.FemaleApproved/B.DocApproved) AS DECIMAL(18,2)) as "Female%"
- from (select
- date(m.created_at + interval '8' hour) as date,
- hour(m.created_at + interval '8' hour) as hour,
- 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,2 ) A
- left join (select
- date(dl.created_at + interval '8' hour) as date,
- hour(dl.created_at + interval '8' hour) as hour,
- count(distinct case when dl.state not in ('noInput','null') then dl.member_id end) DocUploaded,
- count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
- count(distinct case when dl.state = 'reject' then dl.member_id end) DocRejected,
- count(distinct case when dl.gender = 'man' and dl.state = 'approved' then dl.member_id end) MaleApproved,
- count(distinct case when dl.gender = 'woman' and dl.state = 'approved' 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,2) B
- on (B.date = A.date and B.hour = A.hour)
- left join (select
- date(r.occupy_start_at + interval '8' hour) as date,
- hour(r.occupy_start_at + interval '8' hour) as hour,
- 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,2) C
- on (C.date = B.date and C.hour = B.hour)
- left join (
- select
- date(c.created_at + interval '8' hour) as date,
- hour(c.created_at + interval '8' hour) as hour,
- 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,2) D
- on (D.date = C.date and D.hour = C.hour)
- left join (
- select
- date(pm.created_at + interval '8' hour) as date,
- hour(pm.created_at + interval '8' hour) as hour,
- count(distinct pm.member_id) as cardupload
- from
- payment_methods as pm
- where pm.state = 'approved'
- and pm.created_at + interval '8' hour >= @start
- group by 1,2) F
- on (F.hour = A.hour and F.hour = A.hour)
- left join (
- select
- date(d.updated_at + interval '8' hour) as date,
- hour(d.updated_at + interval '8' hour) as hour,
- count(distinct case when d.state = 'active' then d.id end) as Active_installs_2019
- from devices d
- where d.created_at + interval '8' hour >= @start
- group by 1,2 ) G
- on (G.date = A.date and G.hour = A.hour)
- group by 1,2
- order by 1 desc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement