Untitled

YuvalGai

Mar 12th, 2024

112

Never

Add comment

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

text 7.16 KB | None | 0 0

raw download clone embed print report

select
distinct u.DT AS INTERVAL_DATE,
TIMESTAMP_FROM_PARTS(YEAR(u.DT), MONTH(u.DT), DAY(u.DT), 0, 0, 0) AS INTERVAL_DATE_TIME,
u.arena_group,
u.seniority_bin,
u.ltv_group,
u.engagement_group,
u.is_subscriber,
u.subscription_tier,
d.DAU,
u.PLAYERS,
d.SESSIONS,
u.MATCH_SESSIONS,
u.MATCHES,
u.on_fire_matches,
u.ACTIVE_PAYERS,
u.wins,
u.total_match_score,
u.total_ON_FIRE_GAME,
u.total_REACHED_ON_FIRE,
u.total_COINS_WON_AMT,
u.total_TROPHIES_WON_AMT,
u.total_match_duration_mins
from
(
select
dt,
arena_group,
ltv_group,
engagement_group,
payers_segment,
is_subscriber,
SUBSCRIPTION_TIER,
seniority_bin,
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,
count(distinct user_id) as PLAYERS,
count(distinct concat(match_id, user_id, rematch_cnt)) as MATCHES,
COUNT(DISTINCT MATCH_SESSION_ID) AS MATCH_SESSIONS,
sum(IS_ON_FIRE_GAME) as on_fire_matches,
sum(
case
when is_payer = 1 then 1
else 0
end
) as ACTIVE_PAYERS,
sum(is_won) as wins,
sum(MATCH_SCORE) as total_match_score,
sum(IS_ON_FIRE_GAME) as total_ON_FIRE_GAME,
sum(IS_REACHED_ON_FIRE) as total_REACHED_ON_FIRE,
sum(COINS_WON_AMT) as total_COINS_WON_AMT,
sum(TROPHIES_WON_AMT) as total_TROPHIES_WON_AMT,
sum(match_duration_sec) / 60 as total_match_duration_mins
from
(
select
m.MATCH_END_DATE AS DT,
m.EVENT_ID,
m.DERIVED_TSTAMP,
m.MATCH_END_DATE,
m.MATCH_SESSION_ID,
s.SENIORITY_BIN,
s.LTV_GROUP,
s.trophies_cnt
case
when s.LTV_GROUP != '0' then 1
else 0
end as is_payer,
s.ENGAGEMENT_GROUP,
s.ARENA_GROUP,
s.IS_SUBSCRIBER,
s.SUBSCRIPTION_TIER,
s.payers_segment,
m.USER_ID,
m.MATCH_ID,
m.REMATCH_CNT,
m.pvp_mode,
m.MATCH_TYPE_ID,
m.MATCH_TYPE,
m.MATCH_SUB_TYPE,
m.MATCH_MODE,
m.MODIFIER_1,
m.MODIFIER_2,
m.CALENDAR_ENTRY_ID,
m.ORIGINAL_CALENDAR_ENTRY_ID,
m.DYNAMIC_CONFIG_ID,
m.CONFIG_ID,
m.LO_EVENT_ID,
m.LEADERBOARD_CONFIG_TYPE,
m.EVENT_SEGMENT,
m.EVENT_SEGMENT_LOGIC,
m.IS_PUSH_NOTIFICATION_ENABLE,
m.IS_RANKED,
m.TOURNAMENT_UI_ORDER,
m.TOURNAMENT_SIZE,
m.TOURNAMENT_ENTRY_COIN_PRICE,
m.TOURNAMENT_MN_BOOSTER_RARITY,
m.TOURNAMENT_MX_BOOSTER_RARITY,
m.match_rank,
m.elimination_round,
m.BOOSTER_TIER_ID,
m.BOOSTER_TIER,
m.RESOURCE_TYPE_ID,
m.RESOURCE_TYPE,
m.RESOURCE_SUB_TYPE_ID,
m.RESOURCE_SUB_TYPE,
m.RESOURCE_ID,
m.BOOSTER_NAME,
m.BOOSTER_RESOURCE_VERSION,
m.BOOSTER_VALUE,
m.LO_EVENT_TYPE,
m.LO_EVENT_CONFIG_ID,
m.match_duration_sec,
m.IS_WON,
m.MATCH_SCORE,
m.IS_ON_FIRE_GAME,
m.IS_REACHED_ON_FIRE,
m.COINS_WON_AMT,
m.TROPHIES_WON_AMT
FROM
CANDIVORE.PROD.F_USER_MATCH M
left join candivore.prod.daily_users_from_params S on (m.user_id = s.user_id and m.MATCH_END_DATE = s.interval_date)
where
is_bot = false
and m.MATCH_END_DATE = dateadd(day, -1, date(current_date()))
and m.MATCH_END_DATE > (
select
max(interval_date)
from
candivore.semantic_layer.T_ENGAGEMENT_DASHBOARD
)
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9
) U
left join (
select
interval_Date
,engagement_group
,arena_group
,seniority_bin
,ltv_group
,is_subscriber
,subscription_tier
,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
,count(distinct user_id) DAU
,sum(sessions) Sessions
from candivore.prod.daily_users_from_params where interval_date = dateadd(day, -1, date(current_date()))
) D on (
u.DT = d.interval_Date
and u.ARENA_GROUP = d.ARENA_GROUP
and u.ENGAGEMENT_GROUP = d.ENGAGEMENT_GROUP
and u.SENIORITY_BIN = d.SENIORITY_BIN
and u.LTV_GROUP = d.LTV_GROUP
and u.payers_segment = d.payers_segment
and u.trophy_group = d.trophy_group
and case
when u.is_subscriber = true then u.subscription_tier = d.subscription_tier
else u.is_subscriber = d.is_subscriber
end
;
select
*
from
STAGING.TRANSFORM_TABLES.DAU order by dt desc limit 10
select
interval_Date
,engagement_group
,arena_group
,seniority_bin
,ltv_group
,is_subscriber
,subscription_tier
,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
,count(distinct user_id) DAU
,sum(sessions) Sessions
from candivore.prod.daily_users_from_params where interval_date = dateadd(day, -1, date(current_date()))

Add Comment

Please, Sign In to add comment