WIP Tracker - MFTs Redeems Applies - Version 2.0

set @startdate1 = '2019-5-1 00:00';

select
E.*,
IFNULL(F.MFT,0) as MFT
from
(select
weekofyear(c.updated_at + interval '8' hour) as week,
c.coupon_policy_id as cpid,
c.comment as couponname,
count(c.activated_at + interval 8 hour) as TotalApplied,
count(c.used_at + interval 8 hour) as totalredeems
from coupons c
where c.state = 'normal'
and (c.updated_at + interval '8' hour) >= @startdate1
group by 1,2
order by 1 desc) E
left join
(select
A.week as week,
B.cpid as cpid,
count(A.mid) as MFT
from
(Select P.mid as mid, Q.rid as rid , P.week as week from
(select
distinct 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'
group by ch.member_id) P
left join
(select distinct r.member_id as mid , min(r.id) as rid
from reservations r
where r.state in ('completed','inUse')
group by 1) Q
on P.mid = Q.mid
where Q.rid is not null) A
join
(select
distinct c.reservation_id as rid,
c.coupon_policy_id as cpid,
c.comment
from coupons c
where c.used_at is not null
group by 1,2
order by 1 asc) B
on B.rid = A.rid
group by B.cpid,A.week
order by A.week,B.cpid Desc) F
on E.cpid = F.cpid and E.week = F.week
where E.week >= 26
order by E.week desc