Becca WIP - Applies, Redeems, MFT

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

select
E.*,
IFNULL(F.MFT,0) as MFT
from
(select
c.coupon_policy_id as cpid,
c.comment as couponname,
count(c.activated_at) as TotalApplied,
count(c.used_at) as totalredeems
from coupons c
where c.coupon_policy_id in (716,715,712,713,709,706,707,703,702,701,700,699,698,696,695,694,693)
and c.state = 'normal'
group by 1
order by 1 asc) E
left join
(select
B.cpid as cpid,
count(A.mid) as MFT
from
(select
ch.member_id as mid,
min(r.id) as rid
from charges ch left join reservations r on r.member_id = ch.member_id
where ch.created_at + interval '8' hour >= @startdate1
and ch.kind = 'subscriptionFee'
group by ch.member_id) A
join
(select
distinct c.member_id as mid,
c.reservation_id as rid,
c.coupon_policy_id as cpid,
c.comment
from coupons c
where c.coupon_policy_id in (716,715,712,713,709,706,707,703,702,701,700,699,698,696,695,694,693)
and c.used_at is not null
group by 1,2
order by 1 asc) B
on B.rid = A.rid
group by B.cpid) F
on E.cpid = F.cpid