Advertisement
jacky_di

subs_voyagu_tasks

Nov 27th, 2022 (edited)
1,081
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 2.47 KB | None | 0 0
  1. --TASK 1
  2. with test as (
  3. SELECT
  4. concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date)) as date
  5. , license_name
  6. , count(license_id) as subscriptions
  7. FROM `test-project-369620.voyagu.subscriptions`
  8. where operation_type = 'purchase'
  9. group by
  10. concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date))
  11. , license_name
  12. )
  13. select *
  14. , sum(subscriptions) over(partition by license_name order by date rows between unbounded preceding and current row) as subscription_summary
  15. from test
  16. order by
  17. date
  18. , license_name
  19.  
  20.  
  21. --TASK 2
  22. Select
  23. first
  24. , (licenses)
  25. , round((SUM(CASE WHEN month_number = 0 THEN 1 ELSE 0 END) / licenses), 2) AS month_0
  26. , round((SUM(CASE WHEN month_number = 1 THEN 1 ELSE 0 END) / licenses), 2) AS month_1
  27. , round((SUM(CASE WHEN month_number = 2 THEN 1 ELSE 0 END) / licenses), 2) AS month_2
  28. , round((SUM(CASE WHEN month_number = 3 THEN 1 ELSE 0 END) / licenses), 2) AS month_3
  29. , round((SUM(CASE WHEN month_number = 4 THEN 1 ELSE 0 END) / licenses), 2) AS month_4
  30. , round((SUM(CASE WHEN month_number = 5 THEN 1 ELSE 0 END) / licenses), 2) AS month_5
  31. , round((SUM(CASE WHEN month_number = 6 THEN 1 ELSE 0 END) / licenses), 2) AS month_6
  32. from
  33. (select
  34. m.license_id
  35. , m.dates
  36. , n.first as first
  37. , DATE_DIFF(dates, first, month) as month_number  
  38. from  
  39. (SELECT
  40. license_id
  41. , cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date) as dates
  42. FROM `test-project-369620.voyagu.subscriptions`
  43. where operation_type = 'purchase'
  44. and billing_period = 'month'  
  45. GROUP BY
  46. license_id
  47. ,cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date)
  48. ) m
  49. , (SELECT
  50. license_id
  51. , cast((min(concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01"))) as date) AS first
  52. FROM `test-project-369620.voyagu.subscriptions`
  53. where operation_type = 'purchase'
  54. and billing_period = 'month'  
  55. GROUP BY
  56. license_id
  57. ) n
  58. where m.license_id = n.license_id
  59. ) as with_week_number
  60. left join (select
  61. cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date) as date
  62. , count(distinct license_id) as licenses
  63. FROM `test-project-369620.voyagu.subscriptions`
  64. where operation_type = 'purchase'
  65. and billing_period = 'month'  
  66. group by cast((concat(EXTRACT(YEAR FROM sale_date), "-", EXTRACT(MONTH FROM sale_date), "-01")) as date)
  67. ) all_sales on all_sales.date = with_week_number.first
  68. group by first , licenses
  69. order by first
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement