select

hour::date as date,

avg(price) as ton_price

crosschain.price.ez_prices_hourly

token_address = '0x76a797a59ba2c17726896976b7b3747bfd1d220f'

and blockchain = 'bsc'

group by 1

),

latest_metadata as (

select

address,

symbol,

row_number() over (partition by address order by update_timestamp_onchain desc nulls last) as rn

from ton.core.fact_jetton_metadata

),

main as (

select

tx_hash,

block_timestamp,

trader_address as swapper,

case

when volume_usd is not null then volume_usd

when volume_ton is not null then volume_ton * tp.ton_price

else null

end as amount_usd,

token_bought_address,

token_sold_address,

coalesce(jm_bought.symbol, 'TON') as bought_symbol,

coalesce(jm_sold.symbol, 'TON') as sold_symbol,

project,

pool_address,