Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_OFFERS_DASHBOARD AS
- SELECT AA.*, BB.prize_raw, BB.occasions_bought, BB.unique_users_bought, BB.level, BB.Coin_price, BB.IAP_price, BB.video_price, BB.rev, BB.dollars_rev FROM
- (SELECT
- offer_start_date
- ---
- , ifnull(ltv_group2,''n/a'') ltv_group
- , ifnull(platform,''n/a'') platform
- , ifnull(trophy_group2,''n/a'') trophy_group
- , ifnull(league2,''n/a'') league
- /*, ifnull(country,''n/a'') country
- , ifnull(language,''n/a'') language */
- ---
- , offer_id
- , template_id
- , offer_context
- ---
- , count(distinct user_id) unique_users_shown
- , count(*) occasions_shown
- FROM (
- SELECT A.*, B.ltv_group, B.platform , B.trophy_group , B.league, B.country, B.language
- ,first_value(B.ltv_group) over(partition by A.user_id,template_id order by B.interval_date) ltv_group2
- ,first_value(B.trophy_group) over(partition by A.user_id,template_id order by B.interval_date) trophy_group2
- ,first_value(B.league) over(partition by A.user_id,template_id order by B.interval_date) league2
- from(
- (select
- date(derived_tstamp) interval_date
- , uuid user_id
- , template_id
- , offer_context
- , ifnull(config_index,''0'') config_index
- , CASE when length(offer_id) != 8 then offer_id else ''0'' end offer_id --
- , ifnull(coin_price,0) coin_price --
- , ifnull(video_price,0) video_price --
- , ifnull(iap_price,0) iap_price --
- , min(interval_date) over(partition by template_id) offer_start_date
- from candivore.prod.f_special_offer_shown 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 trophies_cnt < 3800 THEN ''Studios''
- WHEN trophies_cnt < 30000 THEN ''Master_League''
- WHEN trophies_cnt >= 30000 THEN ''Legends_League''
- END league
- 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
- )) GROUP BY 1,2,3,4,5,6,7,8) AA
- left JOIN
- (SELECT
- -- interval_date
- ---
- ifnull(ltv_group2,''n/a'') ltv_group
- , ifnull(platform,''n/a'') platform
- , ifnull(trophy_group2,''n/a'') trophy_group
- , ifnull(league2,''n/a'') league
- /*, ifnull(country,''n/a'') country
- , ifnull(language,''n/a'') language*/
- ---
- , offer_id -- offer_id
- , template_id
- ---
- , prize_raw
- , coin_price
- , video_price
- , iap_price
- , level
- --, min(interval_date) over(partition by offer_id) offer_start_date
- , count(*) occasions_bought
- , count(distinct user_id) unique_users_bought
- , sum(iap_price) rev
- , sum(dollar_price) dollars_rev
- /*, sum(case when level = 1 then 1 else 0 end) bought_lvl1
- , sum(case when level = 2 then 1 else 0 end) bought_lvl2
- , sum(case when level = 3 then 1 else 0 end) bought_lvl3
- , sum(case when level = 4 then 1 else 0 end) bought_lvl4
- , sum(case when level = 5 then 1 else 0 end) bought_lvl5*/
- FROM (
- SELECT A.*, B.ltv_group, B.platform, B.trophy_group, B.league, B.country, B.language
- ,first_value(B.ltv_group) over(partition by A.user_id,template_id order by B.interval_date) ltv_group2
- ,first_value(B.trophy_group) over(partition by A.user_id,template_id order by B.interval_date) trophy_group2
- ,first_value(B.league) over(partition by A.user_id,template_id order by B.interval_date) league2
- from (
- (select
- date(derived_tstamp) interval_date
- , uuid user_id
- , ifnull(offer_context,''0'') offer_context
- , ifnull(purchase_type,''0'') purchase_type
- , ifnull(purchase_subtype,''0'') purchase_subtype
- , ifnull(prize_raw,''0'') prize_raw
- , ifnull(offer_index,''0'') offer_index
- , template_id --
- , level
- , CASE when length(shown_offer_id) != 8 then shown_offer_id else ''0'' end offer_id
- , ifnull(coin_price,0) coin_price
- , ifnull(video_price,0) video_price
- , ifnull(iap_price,0) iap_price
- , ifnull(dollar_price,0) dollar_price
- from candivore.prod.f_special_offer_bought) 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 trophies_cnt < 3800 THEN ''Studios''
- WHEN trophies_cnt < 30000 THEN ''Master_League''
- WHEN trophies_cnt >= 30000 THEN ''Legends_League''
- END league
- 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
- )) GROUP BY 1,2,3,4,5,6,7,8,9,10,11) BB
- ON /*AA.offer_start_date = BB.offer_start_date and*/ AA.ltv_group = BB.ltv_group and AA.trophy_group = BB.trophy_group and AA.platform = BB.platform and AA.league = BB.league /*and AA.country = BB.country
- and AA.language = BB.language and AA.config_index = BB.offer_index*/ and AA.offer_id = BB.offer_id and AA.template_id = BB.template_id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
