Coupon Info for Viny - Rahil

set @startdate1 = '2017-1-1 00:00';

select
E.*, IFNULL(B.MFTs,0) as MFTs
from
(select
    #LEFT(YEARWEEK(c.updated_at + interval '8' hour,3),4) AS year,
    #weekofyear(c.updated_at + interval '8' hour) as week,
    c.coupon_policy_id as cpid,
    c.comment as couponname,
    cp.enable_at as cp_enableat,
    cp.disable_at as cp_disableat,
    #c.promo_code_id as pcid,
    #pc.code as pc,
    count(c.activated_at + interval 8 hour) as TotalApplied,
    count(c.used_at + interval 8 hour) as totalredeems
from coupons c
left join promo_codes pc on c.coupon_policy_id = pc.coupon_policy_id
left join coupon_policies cp on c.coupon_policy_id = cp.id
where c.state = 'normal'
and date (c.updated_at + interval '8' hour) >= @startdate1
and c.coupon_policy_id in ('392')
group by 1) E
left join
(select A.cpid as cpid, A.mid as mid,
count(distinct case when A.cpid = '392' then A.mid end) as MFTs
 from
(select
distinct r.member_id as mid, min(r.id) as mrid, co.coupon_policy_id as cpid
from reservations r left join members m on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
where r.state in ('completed','inUse','reserved')
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1) A
where A.cpid in ('392')
group by 1) B
#on (E.year = B.year and E.week = B.week and E.cpid = B.cpid)
on E.cpid = B.cpid
WHERE E.cpid in ('392')
group by E.cpid
order by E.TotalApplied DESC