Untitled

YuvalGai

Sep 8th, 2024

Never

Add comment

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

text 4.48 KB | None | 0 0

raw download clone embed print report

WITH DATA AS (
SELECT
A.*,
CASE
WHEN is_usd_paid = TRUE THEN 'Paid'
WHEN is_coin_paid = TRUE THEN 'Coin Paid'
ELSE 'Unpayed'
END AS transaction_type,
transaction_sub_source transaction_source_lvl_0,
CASE
WHEN transaction_source = 'booster_select'
OR (
transaction_source = 'daily_deals'
AND is_coin_paid = TRUE
)
OR transaction_source = 'helper_select_popup_chest'
OR transaction_source LIKE 'perk_select%'
OR transaction_source LIKE 'special_offer%'
OR transaction_source = 'spin'
OR transaction_source = 'spin_chest'
OR transaction_source = 'super_spin'
OR transaction_source LIKE '%donation%'
OR transaction_source = 'tickets_pack' THEN TRUE
ELSE FALSE
END AS is_bargain,
CASE
WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
WHEN lo_event_id LIKE 'Solo%'
OR lo_event_id LIKE 'solo%' THEN 'solo'
WHEN lo_event_type = 'Rumble' THEN 'rumble'
when pvp_mode = 'Showdown' Then 'Showndown'
WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
WHEN match_type_id = 4
OR transaction_source LIKE 'tournament%' THEN 'tournament'
WHEN match_type_id = 5 THEN 'daily'
WHEN match_type_id = 2 THEN 'classic'
else transaction_source
END AS match_type
, payers_segment
FROM
MATCH_MASTERS.PROD.f_user_resource_transaction a
left join
(select user_id,interval_date,payers_segment from MATCH_MASTERS.prod.daily_users_from_params where interval_date >= current_date - 121) B
on A.user_id = B.user_id and A.transaction_date = B.interval_date
WHERE
DATE(derived_tstamp) >= current_date - 121
)
--------------------
--end_with_statement
--------------------
SELECT
A.INTERVAL_DATE
,A.LEAGUE
,A.TROPHY_GROUP
,A.payers_segment
,A.LTV_GROUP
,A.LO_EVENT_TYPE
,A.EVENT_TYPE
,A.IS_RECEIVED_RESOURCE
,A.TRANSACTION_TYPE
,A.TRANSACTION_SOURCE_LVL_0
,A.TRANSACTION_SOURCE
,A.IS_BARGAIN
,A.MATCH_TYPE
,A.MATCH_TYPE_ID
,A.MATCH_MODE_TYPE
,A.RESOURCE_TYPE
,A.RESOURCE_SUB_TYPE
,A.RESOURCE_ID
,A.RESOURCE_VERSION
,A.STAKES_MULTIPLIER
,A.RESOURCE_CNT
,A.resource_coin_value_calculated resource_coin_value
,A.resource_cnt * A.resource_coin_value_calculated AS total_coins_value
,A.INTERVAL_DATE as LAST_UPDATED_DT
,current_timestamp() as DW_INSERT_DT
FROM
(
SELECT
DATE(derived_tstamp) interval_date
,CASE
WHEN trophies_cnt < 800 THEN '800'
WHEN trophies_cnt < 3800 THEN 'Studios'
WHEN trophies_cnt < 30000 THEN 'Master_League'
WHEN trophies_cnt >= 30000 THEN 'Legends_League'
END league,
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,
payers_segment,
LTV_group,
LO_EVENT_TYPE_config LO_EVENT_TYPE,
event_type,
is_received_resource,
transaction_type,
transaction_source_lvl_0,
transaction_source,
is_bargain,
match_type,
match_type_id,
match_mode_type,
resource_type,
resource_sub_type,
resource_id,
resource_version,
NVL(STAKES_MULTIPLIER, 1) AS STAKES_MULTIPLIER,
resource_coin_value_calculated,
SUM(resource_cnt) resource_cnt
FROM
DATA
GROUP BY all
) A
WHERE 1=1
AND A.interval_date >= current_date - 121

Add Comment

Please, Sign In to add comment