Untitled

YuvalGai

Jun 27th, 2023 (edited)

149

Never

Add comment

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

text 1.97 KB | None | 0 0

raw download clone embed print report

select date(derived_tstamp) interval_date
, CASE
WHEN trophies_cnt < 800 THEN '800'
WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
END ARENA_GROUP
, CASE
WHEN trophies_cnt < 125 then '0-125'
WHEN trophies_cnt < 1400 then '125-1400'
WHEN trophies_cnt < 2000 then '1400-2000'
WHEN trophies_cnt < 3800 then '2000-3800'
WHEN trophies_cnt < 7000 then '3800-7000'
WHEN trophies_cnt < 16000 then '7000-16000'
WHEN trophies_cnt < 30000 then '16000-30000'
WHEN trophies_cnt >= 30000 then '30000+'
END as trophy_group
,CASE
WHEN trophies_cnt < 125 THEN '0-125'
WHEN trophies_cnt < 1400 THEN '125-1400'
WHEN trophies_cnt < 2000 THEN '1400-2000'
WHEN trophies_cnt < 3800 THEN '2000-3800'
WHEN trophies_cnt < 4500 THEN '3800-4500'
WHEN trophies_cnt < 5500 THEN '4500-5500'
WHEN trophies_cnt < 7000 THEN '5500-7000'
WHEN trophies_cnt < 10000 THEN '7000-10000'
WHEN trophies_cnt < 13000 THEN '10000-13000'
WHEN trophies_cnt < 16000 THEN '13000-16000'
WHEN trophies_cnt < 20000 THEN '16000-20000'
WHEN trophies_cnt < 30000 THEN '20000-30000'
WHEN trophies_cnt >= 30000 THEN '30000+'
END AS trophy_group_2
, CASE
WHEN lt_purchases_amt > 0 THEN 'paying_users'
WHEN lt_purchases_amt = 0 THEN 'not_paying_users'
END Paying_users
, CASE WHEN TO_NUMBER(total_iap_amt) = 0 THEN '0'
WHEN total_iap_amt <= 10 THEN 'Low'
WHEN total_iap_amt <= 100 THEN 'Med'
WHEN total_iap_amt <= 299 THEN 'High'
WHEN total_iap_amt <= 999 THEN 'Very High'
WHEN total_iap_amt >= 1000 THEN 'VIP'
ELSE NULL
END AS LTV_group
, country
, platform
, ACTION
, video_type
, REWARD_TYPE
, current_arena_index
, sum(ad_value) total_ad_value
, count(*) total_ads_watched
from CANDIVORE.PROD.F_VIDEO_WATCHED where date(derived_tstamp) > '2022-10-01'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

Add Comment

Please, Sign In to add comment