select

date_trunc('day', hour) as Date,

avg(price) as Price

from base.price.ez_prices_hourly

where symbol like 'WETH'

group by 1

order by 1

, tab_main as (

select

date_trunc('day', block_timestamp) as Date,

'Base' as Platform,

count(distinct tx_hash) as Transactions,

count(distinct sender) as Users,

sum(case when token_symbol like 'SGETH' then amount*price else amount_usd end) as volume_usd

from base.defi.ez_bridge_activity a left outer join tab_price b on date_trunc('day', block_timestamp) = b.date

where platform like 'stargate'

group by 1,2

UNION ALL

select

date_trunc('day', block_timestamp) as Date,

'Arbitrum' as Platform,

count(distinct tx_hash) as Transactions,

count(distinct sender) as Users,

sum(case when token_symbol like 'SGETH' then amount*price else amount_usd end) as volume_usd

from arbitrum.defi.ez_bridge_activity a left outer join tab_price b on date_trunc('day', block_timestamp) = b.date

where platform like 'stargate'