Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- t_videos_dashboard = {
- 'create_or_replace': '''
- CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_VIDEOS_DASHBOARD AS
- select
- a.interval_date,
- b.league ARENA_GROUP,
- b.trophy_group,
- b.country,
- b.LANGUAGE,
- b.platform,
- a.current_arena_index,
- b.app_version,
- b.app_minor_version,
- b.payers_segment,
- b.ltv_group,
- ACTION,
- video_type,
- REWARD_TYPE,
- sum(ad_value) total_ad_value,
- count(a.*) total_ads_watched,
- count(distinct a.user_id) users
- from
- (select *,date(derived_tstamp) interval_date from CANDIVORE.PROD.F_VIDEO_WATCHED where date(derived_tstamp) > '2022-10-01') a
- LEFT JOIN
- (SELECT
- user_id
- , interval_date
- , country
- , LANGUAGE
- , platform
- , app_version
- , app_minor_version
- , ltv_group
- , DATE(first_install_dt) AS install_date
- , payers_segment
- , CASE
- WHEN trophies_cnt < 1500 THEN '0-1500'
- WHEN trophies_cnt < 3800 THEN '1500-3800'
- WHEN trophies_cnt < 8000 THEN '3800-8000'
- WHEN trophies_cnt < 17000 THEN '8000-17000'
- WHEN trophies_cnt < 30000 THEN '17000-30000'
- ELSE '30000+'
- END AS trophy_group
- ,CASE
- WHEN trophies_cnt < 800 THEN '800'
- WHEN finish_arena<=13 THEN 'Studios'
- WHEN finish_arena<=22 THEN 'Master_League'
- WHEN finish_arena>=23 THEN 'Legends_League'
- END league
- FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-10-01') B
- ON a.user_id = B.user_id AND a.interval_date = B.interval_date
- GROUP BY
- 1,
- 2,
- 3,
- 4,
- 5,
- 6,
- 7,
- 8,
- 9,
- 10,
- 11,
- 12,
- 13,
- 14
- '''
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
