Untitled

YuvalGai

Feb 20th, 2023

141

Never

Add comment

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

SQL 0.72 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.METRICS_BY_ACTIVITY_DAY AS
SELECT first_install_dt,activity_day_since_24_08_2022, installed_before_or_after_24_8_2022, SUM(CASE WHEN is_ftd = TRUE THEN 1 ELSE 0 END) ftds
, SUM(DAILY_IAP) AS Revenue
, COUNT(DISTINCT user_id) AS active_users
, COUNT(DISTINCT CASE WHEN lt_purchases_amt > 0 THEN user_id END) active_paying_users FROM
(SELECT *,CASE WHEN first_install_dt >= '2022-08-24' THEN 'installed_after' ELSE 'installed_before' END installed_before_or_after_24_8_2022,ROW_NUMBER() OVER(partition BY user_id ORDER BY interval_date) activity_day_since_24_08_2022 FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-08-24' AND is_active = 1)
GROUP BY 1,2,3

Add Comment

Please, Sign In to add comment