SHARE
TWEET

Becca WIP - Applies, Redeems, MFT

ahmedrahil786 Jul 15th, 2019 (edited) 97 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. set @startdate1 = '2019-5-1 00:00';
  2.  
  3. select
  4. E.*,
  5. IFNULL(F.MFT,0) as MFT
  6. from
  7. (select
  8. c.coupon_policy_id as cpid,
  9. c.comment as couponname,
  10. count(c.activated_at) as TotalApplied,
  11. count(c.used_at) as totalredeems
  12. from coupons c
  13. where c.coupon_policy_id in (716,715,712,713,709,706,707,703,702,701,700,699,698,696,695,694,693)
  14. and c.state = 'normal'
  15. group by 1
  16. order by 1 asc) E
  17. left join
  18. (select
  19. B.cpid as cpid,
  20. count(A.mid) as MFT
  21. from
  22. (select
  23. ch.member_id as mid,
  24. min(r.id) as rid
  25. from charges ch left join reservations r on r.member_id = ch.member_id
  26. where ch.created_at + interval '8' hour >= @startdate1
  27. and ch.kind = 'subscriptionFee'
  28. group by ch.member_id) A
  29. join
  30. (select
  31. distinct c.member_id as mid,
  32. c.reservation_id as rid,
  33. c.coupon_policy_id as cpid,
  34. c.comment
  35. from coupons c
  36. where c.coupon_policy_id in (716,715,712,713,709,706,707,703,702,701,700,699,698,696,695,694,693)
  37. and c.used_at is not null
  38. group by 1,2
  39. order by 1 asc) B
  40. on B.rid = A.rid
  41. group by B.cpid) F
  42. on E.cpid = F.cpid
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top