Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- set @start := '2018-1-15 00:00';
- select
- A.Week,
- A.signups,
- B.DocUploaded,
- B.DocApproved,
- D.TotalMFTs,
- A.MFT,
- C.ActiveMembers,
- C.Bookings,
- C.Charges,
- B.MaleApproved,
- B.FemaleApproved
- from (select
- WEEKOFYEAR(m.created_at + interval '8' hour) as Week,
- count(distinct m.id) as signups,
- count(distinct case when m.grade = 'paidMember' then m.id end) as PaidMembers,
- count(distinct case when r.state = 'completed' then m.id end ) as MFT
- 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') A
- join (select
- WEEKOFYEAR(dl.created_at + interval '8' hour) as Week,
- count(distinct dl.member_id) as 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') B
- on B.Week = A.Week
- join (select
- WEEKOFYEAR(r.created_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(G.amount) as Charges
- from reservations r
- left outer join (select
- ch.reservation_id as rid,
- sum(ch.amount) as amount
- from charges ch
- group by 1
- ) G on G.rid = r.id
- join members m
- on m.id = r.member_id
- where r.created_at + interval '8' hour >= @start
- and m.imaginary = 'normal') 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') D on
- D.Week = C.Week
- group by 1
- order by 1 asc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement