Installs to MFT - Shanyi - Rahil

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

select
A.Week,
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 "SU/I",
B.DocUploaded,
CAST((B.DocUploaded/A.signups) AS DECIMAL(18,2)) as "DU/SU",
B.DocApproved,
CAST((B.DocApproved/B.DocUploaded) AS DECIMAL(18,2)) as "DA/DU",
#F.cardupload as PaymentApproved,
#CAST((F.cardupload/B.DocApproved) AS DECIMAL(18,2)) as "PA/DA",
D.TotalMFTs,
CAST((D.TotalMFTs/B.DocApproved) AS DECIMAL(18,2)) as "MFT/DA",
CAST((D.TotalMFTs/A.signups) AS DECIMAL(18,2)) as "MFT/SU",
B.MaleApproved,
CAST((B.MaleApproved/B.DocApproved) AS DECIMAL(18,2)) as "MA",
B.FemaleApproved,
CAST((B.FemaleApproved/B.DocApproved) AS DECIMAL(18,2)) as "FA"

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

left join (select
WEEKOFYEAR(dl.created_at + interval '8' hour) as Week,
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) B
on B.Week = A.Week

left 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

left 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
left join (
select
weekofyear(pm.created_at + interval '8' hour) as week,
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) F
on F.week = A.week
left join (
select
weekofyear(d.updated_at + interval '8' hour) as week,
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 ) G
on G.week = A.week

group by 1
order by 1 desc;