select date(date_trunc('day' , block_timestamp)) dt

, tx_hash

, address

from ethereum.core.fact_transactions

unpivot(address for type in (from_address, to_address)) a

where date(block_timestamp) >= '{{start_dt}}' and date(block_timestamp) <= '{{end_dt}}'

and status = 'SUCCESS'

),

e as (

select

distinct

tx_hash

, case when event_inputs:tokenId is not null then (contract_address || ' - ' || event_inputs:tokenId) else contract_address end contract_address

, case when contract_address = '0x00000000006c3852cbef3e08e8df289169ede581' then 'OpenSea' else name end contract_name

--, event_name

, case when label_type in ('dex', 'defi') then 'defi' when label_type = 'dapp' then 'dapp' when label_type = 'nft' then 'nft' when label_type = 'token' then 'token'

when contract_address = '0x00000000006c3852cbef3e08e8df289169ede581' then 'nft'

when contract_address = '0x0000000000000000000000000000000000001010' then NULL

else null end label_type

from ethereum.core.fact_event_logs e

left join ethereum.core.dim_contracts c

on e.contract_address = c.address

left join ethereum.core.dim_labels l

on c.address = l.address

where date(e.block_timestamp) >= '{{start_dt}}' and date(e.block_timestamp) <= '{{end_dt}}'

and e.contract_address is not null

--and e.topics[0] != '0x4dfe1bbbcf077ddc3e01291eea2d5c70c2b422b415d95645b9adcfd678cb1d63' -- some standard thing in all MATIC txns

),

d as (

select t.dt, t.tx_hash, t.address, e.contract_address, e.contract_name, e.label_type, 'ethereum' chain

from t

left join e

on t.tx_hash = e.tx_hash