SHARE
TWEET

WIP by Week - Applies, Redeems and MFTs

ahmedrahil786 Jul 15th, 2019 (edited) 102 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. A.week as week,
  9. B.cpid as cpid,
  10. count(A.mid) as MFT
  11. from
  12. (Select P.mid as mid, Q.rid as rid , P.week as week from
  13. (select
  14. distinct ch.member_id as mid,
  15. weekofyear(ch.created_at + interval '8' hour) as week
  16. from charges ch
  17. where ch.created_at + interval '8' hour >= @startdate1
  18. and ch.kind = 'subscriptionFee'
  19. group by ch.member_id) P
  20. left join
  21. (select distinct r.member_id as mid , min(r.id) as rid
  22. from reservations r
  23. where r.state in ('completed','inUse')
  24. group by 1) Q
  25. on P.mid = Q.mid
  26. where Q.rid is not null) A
  27. join
  28. (select
  29. distinct c.reservation_id as rid,
  30. c.coupon_policy_id as cpid,
  31. c.comment
  32. from coupons c
  33. where c.used_at is not null
  34. group by 1,2
  35. order by 1 asc) B
  36. on B.rid = A.rid
  37. group by B.cpid,A.week
  38. order by A.week,B.cpid Desc) F
  39.  
  40. join
  41.  
  42. (select
  43. weekofyear(c.created_at + interval '8' hour) as week,
  44. c.coupon_policy_id as cpid,
  45. c.comment as couponname,
  46. count(c.activated_at) as TotalApplied,
  47. count(c.used_at) as totalredeems
  48. from coupons c
  49. where c.state = 'normal'
  50. group by 1,2
  51. order by 1 asc) E
  52. on E.cpid = F.cpid and E.week = F.week
  53. where E.week >= 26
  54. order by E.week desc
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