with newtraders as (select trader as New_Trader, min(block_timestamp) as mindate from osmosis.core.fact_swaps where from_currency = 'uosmo' or to_currency = 'uosmo' group by 1),

osmopricet as (

select recorded_at::date as day,

avg (price) as USD_Price

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by 1)

select case when from_currency ='uosmo' then 'Swap From OSMO' when to_currency ='uosmo' then 'Swap To OSMO' end as trading_type,

date_trunc(hour,block_timestamp) as date,

count (distinct tx_id) as TX_Count,

count (distinct trader) as Active_Traders_Count,

count (distinct new_trader) as New_Traders_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 Total_OSMO_Volume,

sum (case when from_currency = 'uosmo' then (from_amount*USD_Price/pow(10,from_decimal)) when to_currency = 'uosmo' then (to_amount*USD_Price/pow(10,to_decimal)) end) as Total_USD_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_OSMO_Volume,

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

sum (new_traders_Count) over (order by date) as Total_Traders_Count,

sum (tx_count) over (order by date) as Cumulative_TX_Count,

sum (total_osmo_volume) over (order by date) as Cumulative_OSMO_Volume,

sum (total_usd_volume) over (order by date) as Cumulative_USD_Volume,

avg (Total_USD_Volume) over (order by date rows between 168 PRECEDING and current row) as Moving_Average_7_USD,

avg (Total_OSMO_Volume) over (order by date rows between 168 PRECEDING and current row) as Moving_Average_7_OSMO

from osmosis.core.fact_swaps t1 join newtraders t2 on t1.block_timestamp::Date = t2.mindate::date

join osmopricet t3 on t1.block_timestamp::date = t3.day

where tx_status = 'SUCCEEDED'

and block_timestamp::date = '2022-10-28'

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

group by 1,2