Untitled

CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_OVERVIEW_DASHBOARD AS

 

WITH staging AS (SELECT DISTINCT

daily_summary.USER_ID

, daily_summary.DATE

, daily_summary.LOGIN_ARENA

, daily_summary.LEAGUE

, daily_summary.seniority_bin

, daily_summary.engagement_group

, daily_summary.LEAGUE_CHANGE

, daily_summary.COUNTRY

, daily_summary.platform

, daily_summary.LTV_GROUP

, daily_summary.TEAM_NAME

, daily_summary.TEAM_ID

, daily_summary.MATCH_CNT

, daily_summary.SESSIONS_CNT

, daily_summary.MATCH_SESSIONS_CNT

, daily_summary.DAILY_IAP

, daily_summary.LT_PURCHASES_AMT

, daily_summary.ON_FIRE_GAMES

, daily_summary.TOTAL_COMPLETED_ALBUMS

, daily_summary.IS_SUBSCRIBER

, daily_summary.SUBSCRIPTION_TIER

, daily_summary.FIRST_PURCHASE_TS

, daily_summary.PURCHASES_CNT

, daily_summary.NEW_USER

, daily_summary.New_organic_User

, daily_summary.daily_ad_value

, daily_summary.lt_ad_value

, daily_summary.iap_7d

, daily_summary.iap_30d

, daily_summary.is_ftd

, daily_summary.is_otd

, daily_summary.trophy_group

, daily_summary.TOTAL_MATCH_DURATION_MIN

, daily_summary.fb_user_id

, daily_summary.google_user_id

, daily_summary.apple_user_id

, daily_summary.New_invited_user

, daily_summary.ltv_group_30d

, daily_summary.trophy_group_2

, daily_summary.trophy_group_3

, RESOURCE_TRANSACTION.STICKERS_TRADED

, RESOURCE_TRANSACTION.TEAM_REQUESTS

, IN_APP_PURCHASE.SUBSCRIPTION_PACKAGE

, IN_APP_PURCHASE.SUBSCRIPTION_ACTION

, NOTIFICATION_OPENED.OPEN_NOTIF

, NOTIFICATION_OPENED.PLAYERS_OPEN_NOTIF

, USER_MATCH.SOLO_MATCHES

, USER_MATCH.SOLO_WINS

, USER_MATCH.USER_DAILY_WINRATE_SOLO

, USER_MATCH.SOLO_LEGENDARY_MATCHES

, USER_MATCH.SOLO_LEGENDARY_WINS

, USER_MATCH.USER_DAILY_WINRATE_SOLO_LEGENDARY

, USER_MATCH.RUMBLE_MATCHES

, USER_MATCH.RUMBLE_WINS

, USER_MATCH.USER_DAILY_WINRATE_RUMBLE

, USER_MATCH.RUMBLE_LEGENDARY_MATCHES

, USER_MATCH.RUMBLE_LEGENDARY_WINS

, USER_MATCH.USER_DAILY_WINRATE_RUMBLE_LEGENDARY

 

 

FROM

(SELECT

user_id

,interval_date AS DATE

,LOGIN_ARENA

,CASE

WHEN trophies_cnt<3800 THEN 'Studios'

WHEN trophies_cnt<30000 THEN 'Master_League'

WHEN trophies_cnt>=30000 THEN 'Legends_League'

END AS league

, 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 < 1000 THEN '0-999'

WHEN trophies_cnt < 2000 THEN '1000-1999'

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_3

, 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 < 30000 THEN '16000-30000'

WHEN trophies_cnt >= 30000 THEN '30000+'

END AS trophy_group_2

,CASE

WHEN (login_arena <= 22 AND finish_arena >= 23) THEN 'moved_to_LL'

WHEN (login_arena <= 13 AND finish_arena >= 14) THEN 'moved_to_ML'

ELSE 'no CHANGE'

END AS LEAGUE_CHANGE

,country

,ltv_group

,case

when iap_30d = 0 then '0'

when iap_30d <= 1 then 'Low (0-1)'

when iap_30d <= 2 then 'Med (1-3)'

when iap_30d <= 5 then 'High (3-8)'

when iap_30d <= 15 then 'Very High (8-15)'

when iap_30d > 15 then 'VIP (15+)'

end ltv_group_30d

,seniority_bin

,engagement_group

,team_name

,team_id

,daily_match_cnt match_cnt

,daily_sessions_cnt sessions_cnt

,daily_match_sessions_cnt match_sessions_cnt

,DAILY_IAP

,platform

,on_fire_games

,friends_from_link_cnt

,total_completed_albums

,is_subscriber

,subscription_tier

,first_purchase_ts

,purchases_cnt

,iap_7d

,iap_30d

,lt_purchases_amt

,is_ftd

,is_otd

,daily_ad_value

,lt_ad_value

,TOTAL_MATCH_DURATION_MIN

, fb_user_id

, google_user_id

, apple_user_id

,CASE WHEN is_new_user = 1 AND is_restored = 0 and user_id not in (select distinct user_id from candivore.prod.f_client_user_login where action = 'reconnected' and date(derived_tstamp) >= DATEADD(DAY,-121,GETDATE())) THEN 1 ELSE 0 END AS New_user

,CASE WHEN is_new_user = 1 and is_restored = 0 and user_id not in (select distinct user_id from candivore.prod.f_client_user_login where action = 'reconnected' and date(derived_tstamp) >= DATEADD(DAY,-121,GETDATE())) and (media_source='organic' OR media_source='branch_int' OR media_source='challenge_link' OR media_source='friend_link' OR media_source='sticker_sharing') then 1 else 0 end as New_organic_User

,CASE WHEN is_new_user = 1 AND invited_by_uuid IS NOT NULL THEN 1 ELSE 0 END AS New_invited_user

FROM CANDIVORE.PROD.DAILY_USERS_FROM_PARAMS WHERE is_active = 1

-- and interval_date= 2022-08-30

) AS daily_summary

 

LEFT JOIN //F_USER_RESOURCE_TRANSACTION

 

(SELECT user_id, TRANSACTION_DATE

,SUM(CASE WHEN transaction_source = 'stickers_trading' THEN 1 ELSE 0 END) AS stickers_traded

,SUM(CASE WHEN transaction_source = 'team_request' THEN 1 ELSE 0 END) AS team_requests

FROM CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION

GROUP BY user_id, TRANSACTION_DATE) AS RESOURCE_TRANSACTION

 

ON daily_summary.user_id = RESOURCE_TRANSACTION.user_id AND daily_summary.DATE = RESOURCE_TRANSACTION.TRANSACTION_DATE

 

LEFT JOIN //F_IN_APP_PURCHASE

 

(SELECT

user_id

,DATE(derived_tstamp) AS DATE

,mode(CASE

WHEN package LIKE 'subscription_20%' THEN 'subscription_20'

WHEN package LIKE 'subscription_10%' THEN 'subscription_10'

WHEN package LIKE 'subscription_5%' THEN 'subscription_5'

ELSE NULL

END) AS subscription_package

,mode(SUBSCRIPTION_ACTION) AS SUBSCRIPTION_ACTION

FROM CANDIVORE.PROD.F_IN_APP_PURCHASE WHERE PACKAGE LIKE '%subscription%'

GROUP BY USER_ID, DATE(derived_tstamp)) AS IN_APP_PURCHASE

 

ON daily_summary.user_id = IN_APP_PURCHASE.user_id AND daily_summary.DATE = IN_APP_PURCHASE.DATE

 

LEFT JOIN //F_NOTIFICATION_OPENED

 

(SELECT

user_id,

DATE(derived_tstamp) AS DATE,

COUNT(user_id) AS open_notif,

COUNT(DISTINCT user_id) AS players_open_notif

FROM CANDIVORE.PROD.F_NOTIFICATION_OPENED

GROUP BY user_id, DATE(derived_tstamp)) AS NOTIFICATION_OPENED

 

ON daily_summary.user_id = NOTIFICATION_OPENED.user_id AND daily_summary.DATE = NOTIFICATION_OPENED.DATE

 

LEFT JOIN //F_USER_MATCH

 

(SELECT

user_id,

match_end_date AS DATE,

COUNT(CASE WHEN pvp_mode = 'Solo' THEN 1 END) AS solo_matches,

COUNT(CASE WHEN pvp_mode = 'Solo' AND is_won = 1 THEN 1 END) AS solo_wins,

CASE WHEN solo_matches > 0 THEN solo_wins/solo_matches ELSE NULL END AS user_daily_winrate_solo,

COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Solo' THEN 1 ELSE NULL END) AS solo_legendary_matches,

COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Solo' AND is_won = 1 THEN 1 ELSE NULL END) AS solo_legendary_wins,

CASE WHEN solo_legendary_matches > 0 THEN solo_legendary_wins/solo_legendary_matches ELSE NULL END AS user_daily_winrate_solo_legendary,

 

COUNT(CASE WHEN pvp_mode = 'Rumble' THEN 1 END) AS rumble_matches,

COUNT(CASE WHEN pvp_mode = 'Rumble' AND is_won = 1 THEN 1 END) AS rumble_wins,

CASE WHEN rumble_matches > 0 THEN rumble_wins/rumble_matches ELSE NULL END AS user_daily_winrate_rumble,

COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Rumble' THEN 1 ELSE NULL END) AS rumble_legendary_matches,

COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Rumble' AND is_won = 1 THEN 1 ELSE NULL END) AS rumble_legendary_wins,

CASE WHEN rumble_legendary_matches > 0 THEN rumble_legendary_wins/rumble_legendary_matches ELSE NULL END AS user_daily_winrate_rumble_legendary

 

FROM CANDIVORE.PROD.F_USER_MATCH

GROUP BY user_id, match_end_date) AS USER_MATCH

 

ON daily_summary.user_id = USER_MATCH.user_id AND daily_summary.DATE = USER_MATCH.DATE

)

 

--------------------------

--- end with statement ---

--------------------------

 

SELECT

A.interval_date

,A.country

,A.ltv_group

,A.ltv_group_30d

,A.LOGIN_ARENA

,A.league

,A.trophy_group

,A.trophy_group_2

,A.trophy_group_3

,A.seniority_bin

,A.engagement_group

,A.platform

,A.ACTIVE_USERS

,A.active_paying_users

,A.NEW_USERS

,A.New_organic_Users

,A.New_invited_users

,A.daily_matches

,A.daily_onfire_matches

,A.daily_sessions

,A.daily_active_sessions

,A.DAILY_REVENUE

,A.users_payed_today

,A.daily_purchases

,A.FTDs

,A.OTDs

,A.Users_moved_to_ML

,A.Users_moved_to_LL

,A.Active_subscribers

,A.solo_matches

,A.solo_wins

,A.average_user_daily_winrate_solo

,A.median_user_daily_winrate_solo

,A.solo_legendary_matches

,A.solo_legendary_wins

,A.average_user_daily_winrate_solo_legendary

,A.median_user_daily_winrate_solo_legendary

,A.rumble_matches

,A.rumble_wins

,A.average_user_daily_winrate_rumble

,A.median_user_daily_winrate_rumble

,A.rumble_legendary_matches

,A.rumble_legendary_wins

,A.average_user_daily_winrate_rumble_legendary

,A.median_user_daily_winrate_rumble_legendary

,A.total_daily_team_request

,A.total_daily_stickers_traded

,A.active_users_in_teams

,A.open_notif

,A.players_open_notif

,A.daily_ad_value

,A.lt_ad_value

,A.is_iap_7d

,A.is_iap_30d

,A.TOTAL_MATCH_DURATION_MIN

,A.fb_connected_users

,A.google_connected_users

,A.apple_connected_users

,A.unconnected_users

,B.fake_users

,B.restored_users

,B.fake_restored_users

FROM (

(SELECT

DATE AS interval_date

,country

,ltv_group

,ltv_group_30d

,LOGIN_ARENA

,league

,trophy_group

,trophy_group_2

,trophy_group_3

,seniority_bin

,engagement_group

,platform

,COUNT(DISTINCT user_id) AS ACTIVE_USERS

,COUNT(DISTINCT(CASE WHEN LT_PURCHASES_AMT>0 THEN user_id END)) AS active_paying_users

,SUM(New_invited_user) New_invited_users

,sum(New_organic_User) AS New_organic_Users

,SUM(new_user) AS NEW_USERS

,SUM(match_cnt) AS daily_matches

,SUM(on_fire_games) AS daily_onfire_matches

,SUM(sessions_cnt) AS daily_sessions

,SUM(match_sessions_cnt) AS daily_active_sessions

,SUM(DAILY_IAP) AS DAILY_REVENUE

,COUNT(DISTINCT(CASE WHEN DAILY_IAP IS NOT NULL THEN user_id END)) AS users_payed_today

,SUM(purchases_cnt) AS daily_purchases

,SUM(CASE WHEN is_ftd = TRUE THEN 1 END) AS FTDs

,SUM(CASE WHEN is_otd = TRUE THEN 1 END) AS OTDs

,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_ML' THEN 1 ELSE 0 END) AS Users_moved_to_ML

,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_LL' THEN 1 ELSE 0 END) AS Users_moved_to_LL

,SUM(CASE WHEN is_subscriber = TRUE THEN 1 ELSE 0 END) AS Active_subscribers

,SUM(solo_matches) AS solo_matches

,SUM(solo_wins) AS solo_wins

,avg(user_daily_winrate_solo) AS average_user_daily_winrate_solo

,median(user_daily_winrate_solo) AS median_user_daily_winrate_solo

,SUM(solo_legendary_matches) AS solo_legendary_matches

,SUM(solo_legendary_wins) AS solo_legendary_wins

,avg(user_daily_winrate_solo_legendary) AS average_user_daily_winrate_solo_legendary

,median(user_daily_winrate_solo_legendary) AS median_user_daily_winrate_solo_legendary

,SUM(rumble_matches) AS rumble_matches

,SUM(rumble_wins) AS rumble_wins

,avg(user_daily_winrate_rumble) AS average_user_daily_winrate_rumble

,median(user_daily_winrate_rumble) AS median_user_daily_winrate_rumble

,SUM(rumble_legendary_matches) AS rumble_legendary_matches

,SUM(rumble_legendary_wins) AS rumble_legendary_wins

,avg(user_daily_winrate_rumble_legendary) AS average_user_daily_winrate_rumble_legendary

,median(user_daily_winrate_rumble_legendary) AS median_user_daily_winrate_rumble_legendary

,SUM(team_requests) AS total_daily_team_request

,SUM(stickers_traded) AS total_daily_stickers_traded

,COUNT(team_name) AS active_users_in_teams

,SUM(open_notif) AS open_notif

,SUM(players_open_notif) AS players_open_notif

,SUM(daily_ad_value) AS daily_ad_value

,SUM(lt_ad_value) AS lt_ad_value

,SUM(TOTAL_MATCH_DURATION_MIN) TOTAL_MATCH_DURATION_MIN

,SUM(CASE WHEN iap_7d > 0 THEN 1 ELSE 0 END) AS is_iap_7d

,SUM(CASE WHEN iap_30d > 0 THEN 1 ELSE 0 END) AS is_iap_30d

,SUM(CASE WHEN fb_user_id IS NOT NULL THEN 1 ELSE 0 END) fb_connected_users

,SUM(CASE WHEN google_user_id IS NOT NULL THEN 1 ELSE 0 END) google_connected_users

,SUM(CASE WHEN apple_user_id IS NOT NULL THEN 1 ELSE 0 END) apple_connected_users

,SUM(CASE WHEN apple_user_id IS NULL AND google_user_id IS NULL AND fb_user_id IS NULL THEN 1 ELSE 0 END) unconnected_users

FROM staging

GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform, ltv_group_30d, trophy_group_2, trophy_group_3

) A

LEFT JOIN

(SELECT

interval_date

,country

,ltv_group

,LOGIN_ARENA

,CASE

WHEN trophies_cnt<3800 THEN 'Studios'

WHEN trophies_cnt<30000 THEN 'Master_League'

WHEN trophies_cnt>=30000 THEN 'Legends_League'

END AS league

, 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 < 30000 THEN '16000-30000'

WHEN trophies_cnt >= 30000 THEN '30000+'

END AS trophy_group_2

, CASE

WHEN trophies_cnt < 1000 THEN '0-999'

WHEN trophies_cnt < 2000 THEN '1000-1999'

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_3

,seniority_bin

,engagement_group

,case

when iap_30d = 0 then '0'

when iap_30d <= 1 then 'Low (0-1)'

when iap_30d <= 2 then 'Med (1-3)'

when iap_30d <= 5 then 'High (3-8)'

when iap_30d <= 15 then 'Very High (8-15)'

when iap_30d > 15 then 'VIP (15+)'

end ltv_group_30d

,platform

,SUM(is_fake) AS fake_users

,SUM(is_restored) AS restored_users

,SUM(CASE WHEN is_restored = 1 AND is_fake = 1 THEN 1 END) AS fake_restored_users

FROM CANDIVORE.PROD.daily_users_from_params

GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform , ltv_group_30d, trophy_group_2, trophy_group_3

) B

ON A.interval_date = B.interval_date AND A.country = B.country AND A.ltv_group = B.ltv_group AND A.LOGIN_ARENA = B.LOGIN_ARENA AND A.league = B.league AND A.trophy_group = B.trophy_group AND A.seniority_bin = B.seniority_bin AND A.engagement_group = B.engagement_group AND A.platform = B.platform AND A.ltv_group_30d = B.ltv_group_30d and A.trophy_group_2 = B.trophy_group_2 and A.trophy_group_3 = B.trophy_group_3

)