with osmopricet as (

select recorded_hour as date1,

price as OSMO_Price

from osmosis.core.ez_prices

where symbol = 'OSMO'

and recorded_hour >= '2022-12-01' )

select date_trunc(hour,block_timestamp) as date,

case when block_timestamp::date = '2022-12-10' then 'December 10'

when block_timestamp::date < '2022-12-10' then 'Previous Days'

when block_timestamp::date > '2022-12-10' then 'Next Days'

end as timespan,

case when from_currency = 'uosmo' then 'Swaps From $OSMO'

when to_currency = 'uosmo' then 'Swaps To $OSMO'

else null end as swap_type,

Osmo_Price,

count (distinct tx_id) as TX_Count,

count (distinct trader) as Swappers_count,

sum (case when from_currency = 'uosmo' then from_amount/pow(10,from_decimal) when to_currency = 'uosmo' then to_amount/pow(10,to_decimal) end) as Volume,

avg (case when from_currency = 'uosmo' then from_amount/pow(10,from_decimal) when to_currency = 'uosmo' then to_amount/pow(10,to_decimal) end) as Average_Volume

from osmosis.core.fact_swaps t1 join osmopricet t2 on date_trunc(hour,t1.block_timestamp) = t2.date1

where block_timestamp::Date >= '2022-12-03' and block_timestamp::date < '2022-12-18'

and tx_succeeded = 'TRUE'

and swap_type is not null

group by 1,2,3,4

order by 1