API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Sep 8th, 2024
61
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.21 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. interval_date,
  3. count(DISTINCT user_id) DAU,
  4. count(
  5. distinct case
  6. when trophies_cnt >= 30000 then user_id
  7. end
  8. ) DAU_LL,(
  9. SELECT
  10. count(DISTINCT user_id)
  11. FROM
  12. MATCH_MASTERS.prod.daily_users_from_params
  13. WHERE
  14. interval_date BETWEEN t.interval_date - 2
  15. AND t.interval_date
  16. and is_active = 1
  17. ) AS last_3_days_unique_users,(
  18. SELECT
  19. count(DISTINCT user_id)
  20. FROM
  21. MATCH_MASTERS.prod.daily_users_from_params
  22. WHERE
  23. interval_date BETWEEN t.interval_date - 2
  24. AND t.interval_date
  25. and is_active = 1
  26. and login_arena >= 23
  27. ) AS last_3_days_unique_users_LL,(
  28. SELECT
  29. count(DISTINCT user_id)
  30. FROM
  31. MATCH_MASTERS.prod.daily_users_from_params
  32. WHERE
  33. interval_date BETWEEN t.interval_date - 6
  34. AND t.interval_date
  35. and is_active = 1
  36. ) AS WAU,(
  37. SELECT
  38. count(DISTINCT user_id)
  39. FROM
  40. MATCH_MASTERS.prod.daily_users_from_params
  41. WHERE
  42. interval_date BETWEEN t.interval_date - 6
  43. AND t.interval_date
  44. and is_active = 1
  45. and login_arena >= 23
  46. ) AS WAU_LL,(
  47. SELECT
  48. count(DISTINCT user_id)
  49. FROM
  50. MATCH_MASTERS.prod.daily_users_from_params
  51. WHERE
  52. interval_date BETWEEN t.interval_date - 29
  53. AND t.interval_date
  54. and is_active = 1
  55. ) AS MAU,(
  56. SELECT
  57. count(DISTINCT user_id)
  58. FROM
  59. MATCH_MASTERS.prod.daily_users_from_params
  60. WHERE
  61. interval_date BETWEEN t.interval_date - 29
  62. AND t.interval_date
  63. and is_active = 1
  64. and login_arena >= 23
  65. ) AS MAU_LL
  66. FROM
  67. MATCH_MASTERS.prod.daily_users_from_params t
  68. WHERE
  69. interval_date > '2022-10-01'
  70. and is_active = 1
  71. and interval_date > (
  72. select
  73. max(interval_date)
  74. from
  75. MATCH_MASTERS.semantic_layer.ROLLING_WAU_MAU
  76. )
  77. GROUP BY
  78. 1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!