Coupon Tracking by policy ID for Shanyi - Rahil

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

select
E.*,
IFNULL(F.MFTs,0) as MFT
from
(select
weekofyear(c.updated_at + interval '8' hour) as week,c.promo_code_id as pcid,pc.code as pc,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, promo_codes pc
where c.state = 'normal'
and c.promo_code_id=pc.id
and c.coupon_policy_id = pc.coupon_policy_id
and (c.updated_at + interval '8' hour) >= @startdate1
#and c.promo_code_id in (641,642,643)
group by 1,2
order by 1 desc) E
left join
(Select L.week as week , L.pcid as pcid, Sum(L.mft) as MFTs from
(select A.week, A.rid2 as rid, IFNULL(A.pcid,0) as pcid, if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.comment,"") as coupon, co.promo_code_id as pcid,
weekofyear(r.start_at + interval '8' hour) as week,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
from reservations r
left join members m on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
left join promo_codes pc on co.coupon_policy_id = pc.coupon_policy_id
where r.state in ('completed','inUse')
and (r.start_at + interval '8' hour) >= @startdate1
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A) L
Group by 1,2) F
on E.pcid = F.pcid and E.week = F.week
where E.week >= 26
#AND E.pcid in ('641','642','643')
AND E.pc like 'FREE4%'
group by E.week
order by E.week desc, E.TotalApplied DESC