Source of MFTs - Trail

set @startdate1 = '2019-1-1 00:00';
select
A.week as week,
F.MFTs as total_MFTs,
count(B.mid) as MFT_coupons,
count(C.mid) as MFT_Referral_plus_Coupon,
E.MFTs as Referral_MFT,
(F.MFTs)-((count(B.mid)) + E.MFTs - (count(C.mid))) as No_coupon_Referral
from
(select
ch.member_id as mid,
weekofyear(ch.created_at + interval '8' hour) as week
from charges ch
where ch.created_at + interval '8' hour >= @startdate1
and ch.kind = 'subscriptionFee') A
join
(select
distinct c.member_id as mid,
min(r.id) as fb,
weekofyear(r.start_at + interval '8' hour) as week,
c.coupon_policy_id as cpid,
c.comment
from coupons c
join reservations r on c.reservation_id = r.id
where c.used_at is not null
and r.state = 'completed'
and r.start_at + interval 8 hour > @startdate1
group by 1
order by 1 asc) B
on B.mid = A.mid and B.week = A.week
left join
(select
distinct c.member_id as mid,
min(r.id) as fb,
weekofyear(r.start_at + interval '8' hour) as week,
c.coupon_policy_id as cpid,
c.comment
from coupons c
join member_appendixes ma on ma.member_id = c.member_id
join reservations r on c.reservation_id = r.id
where c.used_at is not null
and r.state = 'completed'
and r.start_at + interval 8 hour > @startdate1
and ma.recommender like '%@%'
group by 1
order by 1 asc) C
on C.mid = A.mid and C.week = A.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 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 > @startdate1
and m.imaginary = 'normal'
group by 1
order by 1 desc) E
on E.week = A.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 MFTs
from charges c
where c.created_at + interval '8' hour > @startdate1
group by 1) F
on F.week = A.week
group by A.week
order by A.week Desc