-- some addresses is extracted from https://flipsidecrypto.xyz/Vamp-2819/q/1qHIUIe23ItH/transfers-gaming

with label as (

SELECT

address,

FROM avalanche.core.dim_labels

WHERE project_name IN ('snail trail','castle crush','pizza game',

'battle for giostone','imperium empires')

,

DAU_u as (

SELECT

date_trunc('{{interval}}', block_timestamp) as date,

CASE

when contract_address = '0xd4cf279b7352cd0bf9327cd2ecea078ebbed5676' then 'Monstera'

when contract_address = '0x7761e2338b35bceb6bda6ce477ef012bde7ae611' or address = '0xbe2c359109299cd25f213a901bbb19159f1d39b4' then 'Chikn'

when contract_address = '0xbbd9678aeaf31b24520587adbaff9ce45c8674ea' or address='0x7b231745d83c09dcde1bd91653b1e6311d27a8a7' or address='0x86bafe5880e41a81692d6d2550ac0b6e092cd597' then 'Defi Kingdoms'

when contract_address = '0xfd538ca3f58dc309da55b11f007ff53fb4602876' then 'BattleForGiostone'

when contract_address = '0xd350bc520a8255af9772e86cce45096b25c1ad2e' or address = '0xe9a4ce4123e0be2f4c8da06020bab157953c3a47' then 'Heroes of NFT'

when contract_address in ('0xe0bb6fed446a2dbb27f84d3c27c4ed8ea7603366', '0x7b536aa4deaa421f0cc42ae26c9362001cdd2c92', '0x5085434227ab73151fad2de546210cbc8663df96', '0x6bd3db958ffb54f3a9c4bf2ce87a481a0196985b') then 'MetaDerby'

else project_name end as project_name,

count(DISTINCT tx_hash) as n_txs,

count(DISTINCT origin_from_address) as DAU

FROM avalanche.core.fact_event_logs a

LEFT JOIN label b ON a.contract_address = b.address AND a.origin_to_address = b.address

GROUP BY 1 , 2

,new as (

SELECT

date_trunc('{{interval}}', first_tx) as date,

project_name,