MFTs Vs Total MFTs - Referral Dashboard

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

select
A.dateofbooking,
B.TotalReferralSignups,
A.MFTs as ReferralMFTs,
D.Signups,
C.TotalMFTs,
CAST((A.MFTs/B.TotalReferralSignups)*100 AS DECIMAL(18,2)) as PctConversion,
CAST((A.MFTs/C.TotalMFTs)*100 AS DECIMAL(18,2)) as PctMFTContribution


from(
select
dayofyear(c.created_at + interval '8' hour) as day,
date(c.created_at + interval '8' hour) as dateofbooking,
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 m.imaginary = 'normal'

group by 1) A



join (
select
dayofyear(m.created_at + interval '8' hour) as day,
date(m.created_at + interval '8' hour) as dateofbooking,
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.imaginary = 'normal'

group by 1) B
on B.day = A.day

join (
select
dayofyear(c.created_at + interval '8' hour) as day,
date(c.created_at + interval '8' hour) as dateofbooking,
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) C
on C.day = B.day

join (
select
dayofyear(m.created_at + interval '8' hour) as day,
date(m.created_at + interval '8' hour) as dateofbooking,
count(distinct m.id) as Signups
from members m
where m.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) D
on D.day = C.day