Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- set @startdate1 = '2019-5-1 00:00';
- select
- E.*,
- IFNULL(F.MFT,0) as MFT
- from
- (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
- join
- (select
- weekofyear(c.created_at + interval '8' hour) as week,
- 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.state = 'normal'
- group by 1,2
- order by 1 asc) E
- on E.cpid = F.cpid and E.week = F.week
- where E.week >= 26
- order by E.week desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement