Untitled

YuvalGai

Mar 13th, 2024

118

Never

Add comment

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

text 6.51 KB | None | 0 0

raw download clone embed print report

'03_insert_delta_into_trans_table': '''
INSERT INTO CANDIVORE.TRANSFORMATION.TRANS_SPECIAL_ALBUMS(
USER_ID
,ALBUM_ID
,DATE
,TOTAL_IAP_AMT
,UNIQUE_STICKERS
,DUPLICATE_STICKERS
,LTV_GROUP
,IS_COMPLETED
,IS_OPENED
,PAGES_COMPLETED
,ARENA
,payers_segment
,LAST_UPDATED_DT
,DW_INSERT_DT
)
select
t.user_id
,t.album_id
,t.date
,t.total_iap_amt
,t.unique_stickers
,t.duplicate_stickers
,CASE
WHEN total_iap_amt = 0 THEN '0'
WHEN total_iap_amt <= 10 THEN 'Low'
WHEN total_iap_amt <= 100 THEN 'Med'
WHEN total_iap_amt <= 299 THEN 'High'
WHEN total_iap_amt <= 999 THEN 'Very High'
WHEN total_iap_amt >= 1000 THEN 'VIP'
END AS LTV_group
,case
when s.is_completed = 1 then 1
else 0
end as is_completed
,case
when s.is_opened = 1 then 1
else 0
end as is_opened
,case
when s.pages_completed > 0 then pages_completed
else 0
end as pages_completed
,t.arena
,t.payers_segment
,t.date as LAST_UPDATED_DT
,current_timestamp() as DW_INSERT_DT
from
(
(
select
a.user_id,
a.album_id,
a.transaction_date as date,
b.payers_segment
max(a.current_arena_index) as arena,
max(a.total_iap_amt) as total_iap_amt,
sum(
case
when resource_item_status = 'unique' then resource_cnt
else 0
end
) as unique_stickers,
sum(
case
when resource_item_status = 'duplicate' then resource_cnt
else 0
end
) as duplicate_stickers
from
CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION a
left join candivore.prod.daily_users_from_params b on a.user_id = b.user_id and a.transaction_date = b.interval_date
where
resource_type = 'Sticker'
and transaction_date >= DATEADD(day, -2, $T_SPECIAL_ALBUMS_UPDATED_DT_MNG)
and (
resource_id = 'StickerSeasonal'
or resource_id = 'StickerAdventure'
)
group by all
) as t
left join (
select
user_id,
album_id,
to_date(derived_tstamp) as date,
payers_segment,
max(
case
when set_type = 'AlbumOpen' then 1
else 0
end
) as is_opened,
max(
case
when set_type = 'AlbumComplete' then 1
else 0
end
) as is_completed,
count(
distinct case
when set_type = 'PageComplete' then set_number
end
) as pages_completed
from
CANDIVORE.PROD.F_ALBUM_SET_COMPLETE aa
left join candivore.prod.daily_users_from_params bb on aa.user_id = bb.user_id and aa.to_date(derived_tstamp) = bb.interval_date
where
to_date(derived_tstamp) >= DATEADD(day, -2, $T_SPECIAL_ALBUMS_UPDATED_DT_MNG)
group by all
) as s on t.user_id = s.user_id
and t.album_id = s.album_id
and t.date = s.date
)
where 1=1
and t.date >= DATEADD(day, -1, $T_SPECIAL_ALBUMS_UPDATED_DT_MNG)
and t.album_id is not null
;
''',
'04_set_merge_error_helper': '''
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;
''',
'05_merge_temp_to_prod': '''
MERGE INTO CANDIVORE.SEMANTIC_LAYER.T_SPECIAL_ALBUMS AS t
USING CANDIVORE.TRANSFORMATION.TRANS_SPECIAL_ALBUMS AS S
ON s.DATE = t.DATE
AND s.USER_ID = t.USER_ID
AND s.ALBUM_ID = t.ALBUM_ID
WHEN MATCHED THEN UPDATE SET
t.USER_ID = s.USER_ID
,t.ALBUM_ID = s.ALBUM_ID
,t.DATE = s.DATE
,t.TOTAL_IAP_AMT = s.TOTAL_IAP_AMT
,t.UNIQUE_STICKERS = s.UNIQUE_STICKERS
,t.DUPLICATE_STICKERS = s.DUPLICATE_STICKERS
,t.LTV_GROUP = s.LTV_GROUP
,t.IS_COMPLETED = s.IS_COMPLETED
,t.IS_OPENED = s.IS_OPENED
,t.PAGES_COMPLETED = s.PAGES_COMPLETED
,t.ARENA = s.ARENA
,t.payers_segment = s.payers_segment
,t.LAST_UPDATED_DT = s.LAST_UPDATED_DT
,t.DW_INSERT_DT = s.DW_INSERT_DT
WHEN NOT MATCHED THEN INSERT (
USER_ID
,ALBUM_ID
,DATE
,TOTAL_IAP_AMT
,UNIQUE_STICKERS
,DUPLICATE_STICKERS
,LTV_GROUP
,IS_COMPLETED
,IS_OPENED
,PAGES_COMPLETED
,ARENA
,payers_segment
,LAST_UPDATED_DT
,DW_INSERT_DT
)
VALUES (
s.USER_ID
,s.ALBUM_ID
,s.DATE
,s.TOTAL_IAP_AMT
,s.UNIQUE_STICKERS
,s.DUPLICATE_STICKERS
,s.LTV_GROUP
,s.IS_COMPLETED
,s.IS_OPENED
,s.PAGES_COMPLETED
,s.ARENA
,s.payers_segment
,s.LAST_UPDATED_DT
,s.DW_INSERT_DT
);
''',
'06_update_mng_table': '''
INSERT INTO CANDIVORE.MANAGE.MANAGEMENT_TABLE(
TABLE_NAME
,DW_INSERT_DATE
,LAST_LOAD_DATE
,ROWS_LOADED
) VALUES (
'CANDIVORE.SEMANTIC_LAYER.T_SPECIAL_ALBUMS'
,current_timestamp()
,(select max(LAST_UPDATED_DT) from CANDIVORE.TRANSFORMATION.TRANS_SPECIAL_ALBUMS)
,(select count(*) from CANDIVORE.TRANSFORMATION.TRANS_SPECIAL_ALBUMS)
)

Add Comment

Please, Sign In to add comment