Untitled

YuvalGai

Jun 27th, 2023

145

Never

Add comment

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

text 2.88 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER.T_IAPS()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '
//2. Get joined data that has been inserted since the last insert date in the merged table
var my_sql_command2 = `
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.trophy_group_2, 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 < 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 < 4500 THEN ''3800-4500''
WHEN trophies_cnt < 5500 THEN ''4500-5500''
WHEN trophies_cnt < 7000 THEN ''5500-7000''
WHEN trophies_cnt < 10000 THEN ''7000-10000''
WHEN trophies_cnt < 13000 THEN ''10000-13000''
WHEN trophies_cnt < 16000 THEN ''13000-16000''
WHEN trophies_cnt < 30000 THEN ''16000-30000''
WHEN trophies_cnt >= 30000 THEN ''30000+''
END AS trophy_group_2
,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 <= 1 then ''Low (0-1)''
when iap_30d <= 2 then ''Med (1-3)''
when iap_30d <= 5 then ''High (3-8)''
when iap_30d <= 15 then ''Very High (8-15)''
when iap_30d > 15 then ''VIP (15+)''
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
)
`
var statement2 = snowflake.createStatement({sqlText: my_sql_command2});
statement2.execute();
var my_sql_command99 = ''COMMIT;'';
var statement99 = snowflake.createStatement({sqlText: my_sql_command99});
statement99.execute();
';

Add Comment

Please, Sign In to add comment