API tools faq
paste
Advertisement
kirzecy670

Untitled

kirzecy670
May 14th, 2025
666
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 2.78 KB | None | 0 0
raw download clone embed print report
  1. with
  2. event_counts AS (
  3.     SELECT
  4.         ft.uid,
  5.         ft.dt,
  6.         CASE
  7.             WHEN (deviceType = 2 AND appVersion % 2 = 0) THEN 'iOS_Parent'
  8.             WHEN (deviceType = 1 AND appVersion % 2 = 1) THEN 'Android_Parent'
  9.             WHEN (deviceType = 2 AND appVersion % 2 = 1) THEN 'iOS_Child'
  10.             WHEN (deviceType = 1 AND appVersion % 2 = 0) THEN 'Android_Child'
  11.             ELSE 'Unknown'
  12.         END AS platform,
  13.         had_any_payment,
  14.         ft.dt >= pp.full_reg_datetime + interval '30' day as flg_30day_old_user,
  15.         region,
  16.         MAX(ft.action in ('open', 'open_parent_activity', 'open_parent_screen')) AS open,
  17.         COUNT(ft.uid) FILTER (WHERE ft.action in ('open', 'open_parent_activity', 'open_parent_screen')) AS open_count,
  18.         MAX(ft.action = 'feed_opened_full') AS is_feed_opened_full,
  19.         COUNT(ft.uid) FILTER (WHERE ft.action = 'feed_opened_full') AS feed_opened_full_count,
  20.         MAX(ft.action = 'open_function_records') AS is_open_function_records,
  21.         COUNT(ft.uid) FILTER (WHERE ft.action = 'open_function_records') AS  open_records_count,
  22.         MAX(ft.action = 'open_function_noise') AS is_open_function_noise,
  23.         COUNT(ft.uid) FILTER (WHERE ft.action = 'open_function_noise') AS  open_noise_count,
  24.         MAX(ft.action = 'open_function_zones') AS is_open_function_zones,
  25.         COUNT(ft.uid) FILTER (WHERE ft.action = 'open_function_zones') AS  open_zones_count,
  26.         MAX(ft.action = 'open_function_appstat') AS is_open_function_appstat,
  27.         COUNT(ft.uid) FILTER (WHERE ft.action = 'open_function_appstat') AS  open_appstat_count,
  28.         MAX(ft.action IN (
  29.         'open_function_records',
  30.         'open_function_noise',
  31.         'open_function_zones',
  32.         'open_function_appstat'
  33.         )) AS is_any_feature_used
  34.     FROM
  35.         stat.funnelTrack ft join analytics.parent_properties pp using uid
  36.     WHERE ft.dt >= '2025-04-01'
  37.       AND ft.action IN (
  38.           'open', 'open_parent_activity', 'open_parent_screen',
  39.           'feed_opened_full',
  40.           'open_function_records', 'open_function_noise', 'open_function_zones',
  41.           'open_function_appstat'
  42.       )
  43.     GROUP BY 1, 2, 3, 4, 5, 6
  44. )
  45.  
  46. SELECT
  47. dt,
  48. platform,
  49. had_any_payment,
  50. flg_30day_old_user,
  51. region,
  52.     uniq(uid) as total_users,  -- Debug: total users in event_counts
  53.     uniqIf(uid, open) active_users,
  54.     uniqIf(uid, is_any_feature_used) users_with_any_feature,
  55.     uniqIf(uid, is_feed_opened_full) as users_feed_opened,
  56.     uniqIf(uid, is_open_function_records) as users_records,
  57.     uniqIf(uid, is_open_function_appstat) as users_appstat,
  58.     uniqIf(uid, is_open_function_zones) as users_zones,
  59.     round(users_with_any_feature/active_users, 4) as use_any_feature
  60. FROM event_counts
  61. group by 1, 2, 3, 4, 5
  62. ORDER BY dt
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!