date_trunc('hour', block_timestamp) as hour,

count(distinct(tx_id)) as n_swaps,

count(distinct(trader)) as swappers,

sum(iff(from_currency = 'uosmo', from_amount/1e6, to_amount/1e6)) as osmo_amount

from osmosis.core.fact_swaps

where (from_currency = 'uosmo' or to_currency = 'uosmo')

and date_trunc('day', hour) = '2022-10-28'

group by hour),

osmo_price as (select

date_trunc('hour', recorded_at) as hour,

avg(price) as avg_hourly_price

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by hour),

joined as (select * from data1_28

join osmo_price using (hour)),

hourly_data as (select *,

osmo_amount * avg_hourly_price as volume_usd

from joined),

final as (select

date_trunc('day', hour) as date,

sum(n_swaps) as n_swaps,

'13571.733333' as avg_daily_swaps,

'28376.25' as avg_daily_swaps_terra,

sum(swappers) as swappers,

'6820.533333' as avg_daily_swappers,

'18338.633333' as avg_daily_swappers_terra,

sum(osmo_amount) as osmo_amount,

'2051242.9980907168' as avg_daily_osmo_amount,

'2332184.089722417' as avg_daily_osmo_amount_terra,