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))

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

and status = 'SUCCESS'

),

e as (

select

distinct

tx_hash

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

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

),

d as (

select t.dt, t.tx_hash, t.address, e.contract_address, e.contract_name, e.label_type

from t

left join e

on t.tx_hash = e.tx_hash