Becca Query - Referrals _ Edited by Rahil

set @start := '2018-12-31 00:00';
set @start2 := '2019-11-1 00:00';

select
A.Month as Month_2019,
B.TotalReferralSignups as referralsingups_2019,
D.TotalSignups as totalsignups_2019,
A.MFTs as ReferralMFTs_2019,
C.TotalMFTs as TotalMFT_2019,
CAST((A.MFTs/B.TotalReferralSignups)*100 AS DECIMAL(18,2)) as Pct_MFT_from_referrals_Conversion,
CAST((A.MFTs/C.TotalMFTs)*100 AS DECIMAL(18,2)) as Pct_MFT_to_totalMFTs,
CAST((C.TotalMFTs/D.TotalSignups)*100 AS DECIMAL(18,2)) as Pct_MFT_Contribution_totalsignups

from(
select
MONTH(c.created_at + interval '8' hour) as Month,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as MFTs
from charges c
join members m on m.id = c.member_id
join member_appendixes ma on ma.member_id = m.id and ma.recommender like '%@%'
where c.created_at + interval '8' hour > @start
and c.created_at + interval '8' hour < @start2
and m.imaginary = 'normal'
group by 1) A

join (
select
MONTH(m.created_at + interval '8' hour) as Month,
count(distinct m.id) as TotalReferralSignups
from members m
join member_appendixes ma
on ma.member_id = m.id and ma.recommender like '%@%'
where m.created_at + interval '8' hour > @start
and m.created_at + interval '8' hour < @start2
and m.imaginary = 'normal'
group by 1) B
on B.Month = A.Month

join (
select
MONTH(c.created_at + interval '8' hour) as Month,
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 c.created_at + interval '8' hour < @start2
and m.imaginary = 'normal'
group by 1) C
on C.Month = B.Month

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

;

set @start3 := '2017-12-31 00:00';
set @start4 := '2019-1-1 00:00';

select
A.Month as Month_2018,
B.TotalReferralSignups as referralsingups_2018,
D.TotalSignups as totalsignups_2018,
A.MFTs as ReferralMFTs_2018,
C.TotalMFTs as TotalMFT_2018,
CAST((A.MFTs/B.TotalReferralSignups)*100 AS DECIMAL(18,2)) as Pct_MFT_from_referrals_Conversion,
CAST((A.MFTs/C.TotalMFTs)*100 AS DECIMAL(18,2)) as Pct_MFT_to_totalMFTs,
CAST((C.TotalMFTs/D.TotalSignups)*100 AS DECIMAL(18,2)) as Pct_MFT_Contribution_totalsignups


from(
select
MONTH(c.created_at + interval '8' hour) as Month,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as MFTs
from charges c
join members m on m.id = c.member_id
join member_appendixes ma on ma.member_id = m.id and ma.recommender like '%@%'
where c.created_at + interval '8' hour > @start3
and c.created_at + interval '8' hour < @start4
and m.imaginary = 'normal'
group by 1) A

join (
select
MONTH(m.created_at + interval '8' hour) as Month,
count(distinct m.id) as TotalReferralSignups
from members m
join member_appendixes ma
on ma.member_id = m.id and ma.recommender like '%@%'
where m.created_at + interval '8' hour > @start3
and m.created_at + interval '8' hour < @start4
and m.imaginary = 'normal'
group by 1) B
on B.Month = A.Month

join (
select
MONTH(c.created_at + interval '8' hour) as Month,
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 > @start3
and c.created_at + interval '8' hour < @start4
and m.imaginary = 'normal'
group by 1) C
on C.Month = B.Month

join (
select
MONTH(m.created_at + interval '8' hour) as Month,
count(distinct m.id) as TotalSignups
from members m
where m.created_at + interval '8' hour > @start3
and m.created_at + interval '8' hour < @start4
and m.imaginary = 'normal'
group by 1) D
on D.month = B.Month