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, B.ltv_group, B.ltv_group_30d FROM(
- (SELECT
- user_id
- , DATE(derived_tstamp) AS interval_date
- , purchase_type
- , purchase_subtype
- , price_point
- , template_id
- , package
- , special_offer_id
- , sub_offer_id
- , iap_price
- , lt_purchases_amt
- , date_trunc("month",DATE(first_install_dt)) install_month
- 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
- , ltv_group
- , DATE(first_install_dt) AS install_date
- , 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 finish_arena<=13 THEN "Studios"
- WHEN finish_arena<=22 THEN "Master_League"
- WHEN finish_arena>=23 THEN "Legends_League"
- END league
- ,CASE
- WHEN iap_30d = 0 THEN "0"
- WHEN iap_30d <= 0.2 THEN "low_0-0.2"
- WHEN iap_30d <= 2 THEN "med_0.2-2"
- WHEN iap_30d <= 5 THEN "high_2-5"
- WHEN iap_30d <= 15 THEN "very_high_5-15"
- WHEN iap_30d > 15 THEN "VIP_15+"
- ELSE "check"
- END ltv_group_30d
- FROM candivore.prod.daily_users_from_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
