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('day', block_timestamp) as days,

'before & after Binance Listing' as period,

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,

(count (distinct tx_id))/(count (distinct trader)) as avg_trade_per_trader,

(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))/(count (distinct trader)) as avg_USD_per_trader

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-20' and block_timestamp <= '2022-10-31'

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

group by 1,2, 3

union ALL

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

date_trunc('day', block_timestamp) as days,