Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_IAPS AS
- SELECT A.*, B.country, B.LANGUAGE , B.platform , B.app_version , B.app_minor_version , B.install_date , B.trophy_group , B.league FROM(
- (SELECT
- user_id
- , DATE(derived_tstamp) AS interval_date
- , last_value(ltv_group) OVER(partition BY user_id,DATE(derived_tstamp) ORDER BY derived_tstamp) ltv_group
- , purchase_type
- , purchase_subtype
- , price_point
- , template_id
- , package
- , special_offer_id
- , sub_offer_id
- , iap_price
- , lt_purchases_amt
- FROM CANDIVORE.PROD.F_IN_APP_PURCHASE WHERE DATE(derived_tstamp) >= '2022-10-01') A
- LEFT JOIN
- (SELECT
- user_id
- , interval_date
- , country
- , LANGUAGE
- , platform
- , app_version
- , app_minor_version
- , DATE(first_install_dt) AS install_date
- , seniority
- , seniority_bin
- , 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 < 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
- FROM candivore.prod.user_daily_params WHERE interval_date >= '2022-10-01') B
- ON A.user_id = B.user_id AND A.interval_date = B.interval_date
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
