select

hour::date as date,

avg(price) as price_usd

from crosschain.price.ez_prices_hourly

where blockchain = 'solana'

and symbol = 'SOL'

group by 1

),

main as (

select

tx_hash,

block_timestamp,

iff(direction = 'inbound', destination_address, source_address) as user,

platform,

source_chain,

destination_chain,

amount * price_usd as amount_usd,

source_chain || ' - ' || destination_chain as chain_pair

from near.defi.ez_bridge_activity

left join pricet on block_timestamp::date = date

where token_address = '22.contract.portalbridge.near'

and receipt_succeeded = 1

count(distinct tx_hash) as transactions,

count(distinct user) as users,

sum(amount_usd) as volume_usd,

avg(amount_usd) as average_amount_usd,

median(amount_usd) as median_amount_usd,

case

when count(distinct block_timestamp::date) = 0 then NULL

else count(distinct tx_hash) / count(distinct block_timestamp::date)

end as daily_average_transactions,