select date_trunc(day,hour)::date date ,

median (PRICE) usd_price

from crosschain.price.ez_prices_hourly

where symbol ='ETH'

and BLOCKCHAIN = 'ethereum'

and date_trunc(day,hour)::date >= '2024-12-01'

and TOKEN_ADDRESS is NULL

group by 1

),

min_index_deposits as (

select t.tx_hash ,

min(event_index) min_index

from ink.core.fact_transactions t left join eth_price on (block_timestamp::date= date)

left join ink.core.fact_event_logs e on (t.tx_hash = e.tx_hash)

where to_address = '0x4200000000000000000000000000000000000007'

and t.ORIGIN_FUNCTION_SIGNATURE='0xd764ad0b'

and t.TX_SUCCEEDED

group by 1

),

deposits as (

select

t.block_timestamp::date date ,

('0x' || SUBSTRING(topics[ARRAY_SIZE(topics)-1], 27) ) wallet ,

t.value amount ,

t.value * usd_price amount_usd ,

t.tx_hash

from ink.core.fact_transactions t left join eth_price on (block_timestamp::date= date)

left join ink.core.fact_event_logs e on (t.tx_hash = e.tx_hash )

left join min_index_deposits i on (e.tx_hash = i.tx_hash )

where to_address = '0x4200000000000000000000000000000000000007'

and t.ORIGIN_FUNCTION_SIGNATURE='0xd764ad0b'

and e.event_index = i.min_index

and t.TX_SUCCEEDED

),