Advertisement
YuvalGai

Untitled

Mar 5th, 2024
1,017
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.77 KB | None | 0 0
  1. t_revenue_dashboard = {
  2.     'create or replace' :'''
  3.        CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_REVENUE_DASHBOARD AS
  4. SELECT
  5. DATE(A.derived_tstamp) AS interval_date
  6. ,B.league
  7. ,B.trophy_group
  8. , B.LTV_group
  9. , B.payers_segment
  10. , date_trunc('MONTH',DATE(A.first_install_dt)) AS monthly_cohort
  11. , B.country
  12. , B.platform
  13. , A.purchase_type
  14. , A.purchase_subtype
  15. , A.price_point
  16. , A.template_id
  17. , A.package
  18. , A.special_offer_id
  19. , A.sub_offer_id
  20. , CASE WHEN A.lt_purchases_amt = 1 THEN 1 ELSE 0 END ftds
  21. , CASE WHEN A.lt_purchases_amt = 2 THEN 1 ELSE 0 END stds
  22. , CASE WHEN A.lt_purchases_amt = 3 THEN 1 ELSE 0 END ttds
  23. , COUNT(A.iap_price) AS Purchases
  24. , SUM(A.iap_price) AS Revenue
  25. , COUNT(DISTINCT A.user_id) AS Active_Users
  26. FROM
  27. ((SELECT *, DATE(derived_tstamp) interval_Date FROM CANDIVORE.PROD.F_IN_APP_PURCHASE WHERE DATE(derived_tstamp) >= '2022-10-01') A
  28.  
  29. LEFT JOIN
  30.  
  31. (SELECT
  32. user_id
  33. , interval_date
  34. , country
  35. , LANGUAGE
  36. , platform
  37. , app_version
  38. , app_minor_version
  39. , ltv_group
  40. , DATE(first_install_dt) AS install_date
  41. , payers_segment
  42. , CASE
  43. WHEN trophies_cnt < 1500 THEN '0-1500'
  44.    WHEN trophies_cnt < 3800 THEN '1500-3800'
  45.    WHEN trophies_cnt < 8000 THEN '3800-8000'
  46.    WHEN trophies_cnt < 17000 THEN '8000-17000'
  47.    WHEN trophies_cnt < 30000 THEN '17000-30000'
  48.    ELSE '30000+'
  49. END AS trophy_group
  50. ,CASE
  51.    WHEN trophies_cnt < 800 THEN '800'
  52.    WHEN finish_arena<=13 THEN 'Studios'
  53.    WHEN finish_arena<=22 THEN 'Master_League'
  54.    WHEN finish_arena>=23 THEN 'Legends_League'
  55. END league
  56.  
  57. FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-10-01') B
  58.  
  59. ON A.user_id = B.user_id AND A.interval_date = B.interval_date)        
  60.  
  61. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
  62.  
  63.    '''
  64. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement