with hashes as ( select DISTINCT tx_hash,

block_timestamp,

origin_from_address as user,

case when CONTRACT_ADDRESS = '0x4300000000000000000000000000000000000004' then 'WETH'

when contract_address = '0x4300000000000000000000000000000000000003' then 'USDB' end as token

from blast.core.fact_event_logs

where tx_hash in (select DISTINCT tx_hash from blast.core.fact_event_logs

where contract_address = lower('0xc748532c202828969b2ee68e0f8487e69cc1d800'))

and ORIGIN_FUNCTION_SIGNATURE = '0x8e5d588c'

and tx_status = 'SUCCESS'

,

amount as (select a.tx_hash,

trunc(a.block_timestamp,'hour') as hourly,

user,

token,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,

ethereum.public.udf_hex_to_int(segmented_data[0])/pow(10,18)::float as amount

from blast.core.fact_event_logs a join hashes b on a.tx_hash = b.tx_hash

where contract_address = '0xc748532c202828969b2ee68e0f8487e69cc1d800'

and token is not null

,

price as ( select hour,

avg(price) as avg_price

from ethereum.price.ez_hourly_token_prices

where symbol = 'WETH'

group by 1)

,

final as ( select tx_hash,

hourly,

user,

token,

amount,