t_albums_general = { '01_truncate_transformation_table': '''

t_albums_general = {

'01_truncate_transformation_table': '''

truncate CANDIVORE.TRANSFORMATION.TRANS_ALBUMS_GENERAL

''',

'02_set_last_update_variable': '''

set T_ALBUMS_GENERAL_UPDATED_DT_MNG = (

SELECT

date(

NVL(max(LAST_LOAD_DATE), to_timestamp('2023-06-01'))

) as LAST_UPDATED_DT_MNG

FROM

candivore.manage.management_table

WHERE

1 = 1

AND table_name = 'CANDIVORE.SEMANTIC_LAYER.T_ALBUMS_GENERAL'

ORDER BY

ID DESC

LIMIT

1

);

''',

'03_insert_delta_into_trans_table': '''

INSERT INTO CANDIVORE.TRANSFORMATION.TRANS_ALBUMS_GENERAL(

DATE,

ARENA,

payers_segment,

DUPLICATE_ADVENTURE,

UNIQUE_ADVENTURE,

DUPLICATE_SEASONAL,

UNIQUE_SEASONAL,

DUPLICATE_DIAMOND,

UNIQUE_DIAMOND,

DUPLICATE_GOLD,

UNIQUE_GOLD,

DUPLICATE_SILVER,

UNIQUE_SILVER,

DUPLICATE_WHITE,

UNIQUE_WHITE,

OPEN_ALBUMS,

COMPLETE_ALBUMS,

PAGES_COMPLETED_TODAY,

ALBUMS_COMPLETED_TODAY,

ALBUMS_RESETED_TODAY,

ALBUMS_OPENED_TODAY,

LAST_UPDATED_DT,

DW_INSERT_DT

)

select

transactions.DAY_DATE as DATE,

transactions.ARENA,

transactions.payers_segment,

transactions.DUPLICATE_ADVENTURE,

transactions.UNIQUE_ADVENTURE,

transactions.DUPLICATE_SEASONAL,

transactions.UNIQUE_SEASONAL,

transactions.DUPLICATE_DIAMOND,

transactions.UNIQUE_DIAMOND,

transactions.DUPLICATE_GOLD,

transactions.UNIQUE_GOLD,

transactions.DUPLICATE_SILVER,

transactions.UNIQUE_SILVER,

transactions.DUPLICATE_WHITE,

transactions.UNIQUE_WHITE,

transactions.OPEN_ALBUMS,

transactions.COMPLETE_ALBUMS,

set_complete.pages_completed_today,

set_complete.albums_completed_today,

set_complete.albums_reseted_today,

set_complete.albums_opened_today,

to_timestamp(transactions.DAY_DATE) as LAST_UPDATED_DT,

current_timestamp() as DW_INSERT_DT

from

(

select

stickers.*,

albums.open_albums,

albums.complete_albums

from

(

SELECT

day_date,

arena,

payers_segment,

sum(complete_albums) as complete_albums,

sum(open_albums) as open_albums

FROM

(

SELECT

user_id AS uuid,

DATE(derived_tstamp) AS day_date,

payers_segment payers_segment,

MAX(current_arena_index) AS arena,

MAX(

CASE

WHEN RESOURCE_STATUS = 'incomplete' THEN start_session_resource_cnt

ELSE 0

END

) AS open_albums,

MAX(

CASE

WHEN RESOURCE_STATUS = 'complete' THEN start_session_resource_cnt

ELSE 0

END

) AS complete_albums

FROM

(select aa.*, bb.payers_segment from

(

SELECT

inventory.derived_tstamp,

date(inventory.derived_tstamp) interval_date,

inventory.user_id,

current_arena_index,

RESOURCE_STATUS,

start_session_resource_cnt,

ROW_NUMBER() OVER (

PARTITION BY user_id,

DATE(derived_tstamp),

RESOURCE_STATUS

ORDER BY

derived_tstamp DESC

) AS row_number

FROM

CANDIVORE.PROD.F_USER_SESSION_INVENTORY AS inventory

JOIN CANDIVORE.PROD.F_USER_SESSION_START AS sess USING (SESSION_ID)

WHERE

DATE(derived_tstamp) > DATEADD(day, -1, $T_ALBUMS_GENERAL_UPDATED_DT_MNG)

AND RESOURCE_TYPE = 'Album'

AND RESOURCE_NAME = 'AlbumRegular'

) aa

LEFT JOIN

(SELECT

user_id

, interval_date

, country

, LANGUAGE

, platform

, app_version

, app_minor_version

, ltv_group

, DATE(first_install_dt) AS install_date

, payers_segment

, CASE

WHEN trophies_cnt < 1500 THEN '0-1500'

WHEN trophies_cnt < 3800 THEN '1500-3800'

WHEN trophies_cnt < 8000 THEN '3800-8000'

WHEN trophies_cnt < 17000 THEN '8000-17000'

WHEN trophies_cnt < 30000 THEN '17000-30000'

ELSE '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

FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-10-01') BB

ON aa.user_id = BB.user_id AND aa.interval_date = BB.interval_date

WHERE

row_number = 1)

GROUP BY

1,

2,

3

)

group by

1,

2,

3

) as albums full

outer join (

select

transaction_date as day_date,

current_arena_index as arena,

payers_segment,

sum(

case

when resource_id = 'StickerAdventure'

and resource_item_status = 'duplicate' then resource_cnt

else 0

end

) as duplicate_adventure,

sum(

case

when resource_id = 'StickerAdventure'

and resource_item_status = 'unique' then resource_cnt

else 0

end

) as unique_adventure,

sum(

case

when resource_id = 'StickerSeasonal'

and resource_item_status = 'duplicate' then resource_cnt

else 0

end

) as duplicate_seasonal,

sum(

case

when resource_id = 'StickerSeasonal'

and resource_item_status = 'unique' then resource_cnt

else 0

end

) as unique_seasonal,

sum(

case

when resource_id = 'StickerDiamond'

and resource_item_status = 'duplicate' then resource_cnt

else 0

end

) as duplicate_diamond,

sum(

case

when resource_id = 'StickerDiamond'

and resource_item_status = 'unique' then resource_cnt

else 0

end

) as unique_diamond,

sum(

case

when resource_id = 'StickerGold'

and resource_item_status = 'duplicate' then resource_cnt

else 0

end

) as duplicate_gold,

sum(

case

when resource_id = 'StickerGold'

and resource_item_status = 'unique' then resource_cnt

else 0

end

) as unique_gold,

sum(

case

when resource_id = 'StickerSilver'

and resource_item_status = 'duplicate' then resource_cnt

else 0

end

) as duplicate_silver,

sum(

case

when resource_id = 'StickerSilver'

and resource_item_status = 'unique' then resource_cnt

else 0

end

) as unique_silver,

sum(

case

when resource_id = 'StickerWhite'

and resource_item_status = 'duplicate' then resource_cnt

else 0

end

) as duplicate_white,

sum(

case

when resource_id = 'StickerWhite'

and resource_item_status = 'unique' then resource_cnt

else 0

end

) as unique_white

from

(select * from CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION where

resource_type = 'Sticker'

and is_received_resource = 1

and transaction_date > DATEADD(day, -1, $T_ALBUMS_GENERAL_UPDATED_DT_MNG)) 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

, payers_segment

, CASE

WHEN trophies_cnt < 1500 THEN '0-1500'

WHEN trophies_cnt < 3800 THEN '1500-3800'

WHEN trophies_cnt < 8000 THEN '3800-8000'

WHEN trophies_cnt < 17000 THEN '8000-17000'

WHEN trophies_cnt < 30000 THEN '17000-30000'

ELSE '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

FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-10-01') B

ON a.user_id = B.user_id AND a.transaction_date = B.interval_date

group by

1,

2,

3

) as stickers on albums.day_date = stickers.day_date

and albums.arena = stickers.arena and albums.payers_segment = stickers.payers_segment

) as transactions

full outer join (

select

date(derived_tstamp) as day_date,

current_arena_index as arena,

payers_segment,

sum(

case

when set_type = 'PageComplete' then 1

else 0

end

) as pages_completed_today,

sum(

case

when set_type = 'AlbumComplete' then 1

else 0

end

) as albums_completed_today,

sum(

case

when set_type = 'AlbumReset' then 1

else 0

end

) as albums_reseted_today,

sum(

case

when set_type = 'AlbumOpen' then 1

else 0

end

) as albums_opened_today

from

(select *, date(derived_tstamp) day_date from CANDIVORE.PROD.F_ALBUM_SET_COMPLETE where

date(derived_tstamp) > DATEADD(day, -1, $T_ALBUMS_GENERAL_UPDATED_DT_MNG)) 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

, payers_segment

, CASE

WHEN trophies_cnt < 1500 THEN '0-1500'

WHEN trophies_cnt < 3800 THEN '1500-3800'

WHEN trophies_cnt < 8000 THEN '3800-8000'

WHEN trophies_cnt < 17000 THEN '8000-17000'

WHEN trophies_cnt < 30000 THEN '17000-30000'

ELSE '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

FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-10-01') B

ON a.user_id = B.user_id AND a.day_date = B.interval_date

group by

1,

2,

3

) as set_complete on transactions.day_date = set_complete.day_date

and transactions.arena = set_complete.arena and transactions.payers_segment = set_complete.payers_segment

WHERE 1=1

AND transactions.DAY_DATE > DATEADD(day, -1, $T_ALBUMS_GENERAL_UPDATED_DT_MNG)

;

''',

'04_set_merge_error_helper': '''

ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;

''',

'05_merge_temp_to_prod': '''

MERGE INTO CANDIVORE.SEMANTIC_LAYER.T_ALBUMS_GENERAL AS t

USING CANDIVORE.TRANSFORMATION.TRANS_ALBUMS_GENERAL AS S

ON s.DATE = t.DATE

AND s.ARENA = t.ARENA

WHEN MATCHED THEN UPDATE SET

t.DATE = s. DATE

,t.ARENA = s.ARENA

,t.payers_segment = s.payers_segment

,t.DUPLICATE_ADVENTURE = s.DUPLICATE_ADVENTURE

,t.UNIQUE_ADVENTURE = s.UNIQUE_ADVENTURE

,t.DUPLICATE_SEASONAL = s.DUPLICATE_SEASONAL

,t.UNIQUE_SEASONAL = s.UNIQUE_SEASONAL

,t.DUPLICATE_DIAMOND = s.DUPLICATE_DIAMOND

,t.UNIQUE_DIAMOND = s.UNIQUE_DIAMOND

,t.DUPLICATE_GOLD = s.DUPLICATE_GOLD

,t.UNIQUE_GOLD = s.UNIQUE_GOLD

,t.DUPLICATE_SILVER = s.DUPLICATE_SILVER

,t.UNIQUE_SILVER = s.UNIQUE_SILVER

,t.DUPLICATE_WHITE = s.DUPLICATE_WHITE

,t.UNIQUE_WHITE = s.UNIQUE_WHITE

,t.OPEN_ALBUMS = s.OPEN_ALBUMS

,t.COMPLETE_ALBUMS = s.COMPLETE_ALBUMS

,t.PAGES_COMPLETED_TODAY = s.PAGES_COMPLETED_TODAY

,t.ALBUMS_COMPLETED_TODAY = s.ALBUMS_COMPLETED_TODAY

,t.ALBUMS_RESETED_TODAY = s.ALBUMS_RESETED_TODAY

,t.ALBUMS_OPENED_TODAY = s.ALBUMS_OPENED_TODAY

,t.LAST_UPDATED_DT = s.LAST_UPDATED_DT

,t.DW_INSERT_DT = s.DW_INSERT_DT

WHEN NOT MATCHED THEN INSERT (

DATE

,ARENA

,payers_segment

,DUPLICATE_ADVENTURE

,UNIQUE_ADVENTURE

,DUPLICATE_SEASONAL

,UNIQUE_SEASONAL

,DUPLICATE_DIAMOND

,UNIQUE_DIAMOND

,DUPLICATE_GOLD

,UNIQUE_GOLD

,DUPLICATE_SILVER

,UNIQUE_SILVER

,DUPLICATE_WHITE

,UNIQUE_WHITE

,OPEN_ALBUMS

,COMPLETE_ALBUMS

,PAGES_COMPLETED_TODAY

,ALBUMS_COMPLETED_TODAY

,ALBUMS_RESETED_TODAY

,ALBUMS_OPENED_TODAY

,LAST_UPDATED_DT

,DW_INSERT_DT

)

VALUES (

s.DATE

,s.ARENA

,s.payers_segment

,s.DUPLICATE_ADVENTURE

,s.UNIQUE_ADVENTURE

,s.DUPLICATE_SEASONAL

,s.UNIQUE_SEASONAL

,s.DUPLICATE_DIAMOND

,s.UNIQUE_DIAMOND

,s.DUPLICATE_GOLD

,s.UNIQUE_GOLD

,s.DUPLICATE_SILVER

,s.UNIQUE_SILVER

,s.DUPLICATE_WHITE

,s.UNIQUE_WHITE

,s.OPEN_ALBUMS

,s.COMPLETE_ALBUMS

,s.PAGES_COMPLETED_TODAY

,s.ALBUMS_COMPLETED_TODAY

,s.ALBUMS_RESETED_TODAY

,s.ALBUMS_OPENED_TODAY

,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_ALBUMS_GENERAL'

,current_timestamp()

,(select max(LAST_UPDATED_DT) from CANDIVORE.TRANSFORMATION.TRANS_ALBUMS_GENERAL)

,(select count(*) from CANDIVORE.TRANSFORMATION.TRANS_ALBUMS_GENERAL)

)

'''

}

Untitled