Untitled

YuvalGai

May 7th, 2023

163

Never

Add comment

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

SQL 6.58 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_EARN_SPENT_LIGHT AS
WITH DATA AS (SELECT *
, CASE
WHEN is_usd_paid = TRUE THEN 'Paid'
WHEN is_coin_paid = TRUE THEN 'Coin Paid'
ELSE 'Unpayed'
END AS transaction_type
, CASE
WHEN transaction_source LIKE 'admin%' OR transaction_source LIKE 'auto%' OR transaction_source LIKE 'promocode' OR transaction_source LIKE 'promote' THEN 'admin_dashboard'
WHEN transaction_source = 'badge' OR transaction_source = 'best_team' OR transaction_source LIKE 'version_upgrade' OR transaction_source LIKE 'convert_to%' OR transaction_source LIKE 'feature_unlock%' OR
transaction_source = 'fix' OR transaction_source = 'rate_us' OR transaction_source LIKE 'update_%' THEN 'others'
WHEN transaction_source = 'booster_refill' THEN 'booster_refill'
WHEN transaction_source = 'helper_select_popup_chest' THEN 'booster_select'
WHEN transaction_source = 'booster_select_popup' THEN 'booster_select_popup'
WHEN transaction_source = 'booster_select' THEN 'booster_select_bargain'
WHEN transaction_source = 'coin_packs' THEN 'coin_packs'
WHEN transaction_source = 'create_team' THEN 'create_team'
WHEN transaction_source = 'dev_master' THEN 'dev_master'
WHEN transaction_source = 'disconnection_refund' THEN 'disconnection_refund'
WHEN transaction_source = 'team_request' THEN 'team_request'
WHEN transaction_source LIKE 'team_%' THEN 'donation'
WHEN transaction_source LIKE 'expired_claimable_rewards%' THEN 'expired_claimable_rewards'
WHEN transaction_source LIKE 'fb_%' OR transaction_source LIKE 'friend_joined' THEN 'facebook'
WHEN transaction_source LIKE 'free_gift%' THEN 'gift'
WHEN transaction_source = 'home_screen_offer' THEN 'home_screen_offer'
WHEN transaction_source = 'join_team' THEN 'join_team'
WHEN transaction_source LIKE 'live_event%' THEN 'live_event'
WHEN transaction_source LIKE 'master%' THEN 'master_league'
WHEN transaction_source = 'match_end' THEN 'match_end'
WHEN transaction_source LIKE 'no_activity%' THEN 'no_activity'
WHEN transaction_source LIKE '%progression%' THEN 'progression_triggered'
WHEN transaction_source LIKE 'rewarded_video%' THEN 'rewarded_video'
WHEN transaction_source = 'season_end' THEN 'season_end'
WHEN transaction_source LIKE 'special_offer%' THEN 'special_offer'
WHEN transaction_source = 'special_sale' THEN 'special_sale'
WHEN transaction_source = 'spin_deal' THEN 'spin_deal'
WHEN transaction_source LIKE 'perk_select%' THEN 'perk_select'
WHEN transaction_source = 'spin' OR transaction_source = 'spin_chest' THEN 'spins'
WHEN transaction_source LIKE 'sticker_album%' OR transaction_source = 'sticker_token' OR transaction_source = 'stickers_trading' THEN 'sticker_album'
WHEN transaction_source LIKE 'store_special_offer%' THEN 'store_special_offer'
WHEN transaction_source = 'daily_deals' AND is_coin_paid = TRUE THEN 'store_bargain'
WHEN transaction_source = 'daily_deals' AND is_usd_paid = TRUE THEN 'store_payed'
WHEN transaction_source = 'daily_deals' AND is_coin_paid = FALSE AND is_usd_paid = FALSE THEN 'store_free'
WHEN transaction_source LIKE 'subscription%' THEN 'subscription'
WHEN transaction_source = 'super_spin' THEN 'super_spin'
WHEN transaction_source = 'super_spin_deal' THEN 'super_spin_deal'
WHEN transaction_source = 'booster_help' OR transaction_source = 'team_request' THEN 'team_request'
WHEN transaction_source = 'tickets_pack' THEN 'tickets_pack'
WHEN transaction_source LIKE 'tournament%' THEN 'tournament'
WHEN transaction_source = 'unknown' THEN 'unknown'
END 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 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'
END AS match_type
FROM CANDIVORE.PROD.F_CORE_USER_TRANSACTION
WHERE DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE()))
--------------------
--end_with_statement
--------------------
SELECT A.*, B.resource_coin_value, A.resource_cnt*B.resource_coin_value AS total_coins_value FROM
((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 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 lt_purchases_amt > 0 THEN 'paying_users'
WHEN lt_purchases_amt = 0 THEN 'not_paying_users'
END Paying_users
, LTV_group
, 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
, SUM(resource_cnt) resource_cnt
FROM DATA
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
) A
LEFT JOIN
(SELECT resource_name, resource_version, resource_sub_type, resource_coin_value FROM (SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (partition BY resource_name, resource_version ORDER BY db_create_date DESC, resource_id DESC) AS resource_rank
FROM candivore.prod.DIM_RESOURCE_VERSION)
WHERE resource_rank = 1)) B
ON A.resource_id = B.resource_name AND A.resource_version = B.resource_version AND A.resource_sub_type = B.resource_sub_type)

Add Comment

Please, Sign In to add comment