Advertisement
Guest User

Untitled

a guest
Feb 8th, 2016
46
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.83 KB | None | 0 0
  1.  
  2. --sample purchases to verify fields
  3. select
  4. a.athlete_id,
  5. date_trunc('d',a.timestamp) as purchase_date,
  6. a.platform_id,
  7. a.sku,
  8. b.from_trial,
  9. b.subscription_type,
  10. b.subscriber_type,
  11. b.platform
  12. from
  13. event_premium_purchase_success a
  14. left outer join
  15. subscribe_events b
  16. on
  17. a.athlete_id = b.athlete_id
  18. AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
  19. order by
  20. date_trunc('d',timestamp)
  21. limit 100;
  22.  
  23.  
  24. --sample failed purchases
  25. select
  26. a.athlete_id,
  27. date_trunc('d',a.timestamp) as purchase_date,
  28. a.platform_id,
  29. a.sku,
  30. max(case when b.athlete_id is not null then 1 else 0 end) as in_subscribe_events
  31. from
  32. event_premium_purchase_success a
  33. left outer join
  34. subscribe_events b
  35. on
  36. a.athlete_id = b.athlete_id
  37. AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
  38. group by
  39. 1,2,3,4
  40. having
  41. b.athlete_id is null
  42. order by
  43. date_trunc('d',timestamp);
  44.  
  45. select
  46. a.athlete_id,
  47. date_trunc('d',a.timestamp) as purchase_date,
  48. a.platform_id,
  49. a.sku,
  50. b.*
  51. from
  52. event_premium_purchase_success a
  53. left outer join
  54. subscribe_events b
  55. on
  56. a.athlete_id = b.athlete_id
  57. AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
  58. where
  59. a.athlete_id = 5741909
  60. order by
  61. a.timestamp;
  62.  
  63.  
  64. --aggregated purchase record success rates
  65. select
  66. purchase_date,
  67. platform_id,
  68. sku,
  69. sum(in_subscribe_events),
  70. count(*),
  71. sum(in_subscribe_events)*1.00/count(*) as avg_in_sub_events
  72. from
  73. (
  74. select
  75. a.athlete_id,
  76. date_trunc('d',a.timestamp) as purchase_date,
  77. a.platform_id,
  78. a.sku,
  79. max(case when b.athlete_id is not null then 1 else 0 end) as in_subscribe_events
  80. from
  81. event_premium_purchase_success a
  82. left outer join
  83. subscribe_events b
  84. on
  85. a.athlete_id = b.athlete_id
  86. AND date_diff('d',date_trunc('d',a.timestamp),b.date) between 0 and 1
  87. group by
  88. 1,2,3,4
  89. order by
  90. date_trunc('d',timestamp)
  91. )
  92. group by
  93. 1,2,3
  94. order by
  95. 1,2,3
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement