Untitled

YuvalGai

Feb 20th, 2023

148

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

SQL 1.61 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.ROLLING_WAU_MAU AS
SELECT interval_date, COUNT(DISTINCT user_id) unique_users
,(SELECT COUNT(DISTINCT user_id)
FROM candivore.prod.daily_users_from_params
WHERE interval_date BETWEEN t.interval_date - 2 AND t.interval_date AND is_active = 1
) AS last_3_days_unique_users
,(SELECT COUNT(DISTINCT user_id)
FROM candivore.prod.daily_users_from_params
WHERE interval_date BETWEEN t.interval_date - 2 AND t.interval_date AND is_active = 1 AND login_arena >= 23
) AS last_3_days_unique_users_LL
,(SELECT COUNT(DISTINCT user_id)
FROM candivore.prod.daily_users_from_params
WHERE interval_date BETWEEN t.interval_date - 6 AND t.interval_date AND is_active = 1
) AS WAU_unique_users
,(SELECT COUNT(DISTINCT user_id)
FROM candivore.prod.daily_users_from_params
WHERE interval_date BETWEEN t.interval_date - 6 AND t.interval_date AND is_active = 1 AND login_arena >= 23
) AS WAU_unique_users_LL
,(SELECT COUNT(DISTINCT user_id)
FROM candivore.prod.daily_users_from_params
WHERE interval_date BETWEEN t.interval_date - 29 AND t.interval_date AND is_active = 1
) AS MAU_unique_users
,(SELECT COUNT(DISTINCT user_id)
FROM candivore.prod.daily_users_from_params
WHERE interval_date BETWEEN t.interval_date - 29 AND t.interval_date AND is_active = 1 AND login_arena >= 23
) AS MAU_unique_users_LL
FROM candivore.prod.daily_users_from_params t
WHERE interval_date > '2022-10-01' AND is_active = 1
GROUP BY 1
ORDER BY 1 DESC;

Add Comment

Please, Sign In to add comment