MFT_21 - SIgnups and MFTs _ Rahil

set @start := '2019-1-1';

select
A.week as week,
D.TotalSignups,
E.TotalSignups_21,
A.TotalMFTs,
A.mft_21,
CAST((A.TotalMFTs/D.TotalSignups)*100 AS DECIMAL(18,2)) as Pct_MFT_21_from_signups,
CAST((A.mft_21/E.TotalSignups_21)*100 AS DECIMAL(18,2)) as Pct_MFT_21_from_signups_21


from (select
week(c.created_at + interval '8' hour) as Week,
month(c.created_at + interval '8' hour) as month,
DAY(c.created_at + interval '8' hour) as Day,
count(distinct case when c.kind = 'subscriptionFee' and str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year then c.member_id end) as mft_21,
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
left outer join member_appendixes ma
on ma.id = m.id
where c.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) A

join (
select
week(m.created_at + interval '8' hour) as week,
count(distinct m.id) as TotalSignups
from members m
where m.created_at + interval '8' hour > @start
and m.imaginary = 'normal'
group by 1) D
on D.week = A.week

left join (
select
week(m.created_at + interval '8' hour) as week,
count(distinct case when str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year then m.id end) as TotalSignups_21
from members m
where m.created_at + interval '8' hour > @start
and m.imaginary = 'normal'
and str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year
group by 1) E
on E.week = A.week

group by 1
order by 1 asc
;