Weekly KPI Index Sheet

#set @start := '2018-12-31 00:00';

select
A.Week,
A.signups,
B.DocUploaded,
B.DocApproved,
D.TotalMFTs,
C.ActiveMembers,
C.ridelength,
A.MFT,
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'
group by 1 ) 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'
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;