select

date_trunc('hour', recorded_at) as p_date,

avg(price) as price_usd

from osmosis.core.dim_prices

where symbol = 'OSMO'

and recorded_at::date BETWEEN '2022-10-24' and '2022-10-31'

group by 1

select

date_trunc('hour', BLOCK_TIMESTAMP) as date,

case

WHEN block_timestamp::date = '2022-10-28' THEN 'Listing day Swap from OSMO'

ELSE 'Other day Swap from OSMO' end as swap,

COUNT(DISTINCT TX_ID) as tx_cnt_from_osmo,

COUNT(DISTINCT TRADER) as "TRADER from Osmo",

SUM((FROM_AMOUNT/1e6)*price_usd) as "Vol from Osmo($)",

SUM(FROM_AMOUNT/1e6) as "Vol from Osmo(OSMO)",

avg((FROM_AMOUNT/1e6)*price_usd) as "Avg from Osmo($)",

avg(FROM_AMOUNT/1e6) as "Avg from Osmo(OSMO)"

from osmosis.core.fact_swaps s join prices p on p.p_date = date_trunc('day',block_timestamp)

where s.block_timestamp::date BETWEEN '2022-10-24' and '2022-10-31'

and from_currency = 'uosmo'

group by 1,2

UNION

select

date_trunc('hour', BLOCK_TIMESTAMP) as date,

case

WHEN block_timestamp::date = '2022-10-28' THEN 'Listing day Swap to OSMO'

ELSE 'Other day Swap to OSMO' end as swap,

COUNT(DISTINCT TX_ID) as tx_cnt_to_osmo,

COUNT(DISTINCT TRADER) as "TRADER to Osmo",

SUM((TO_AMOUNT/1e6)*price_usd) as "Vol to Osmo($)",

SUM(TO_AMOUNT/1e6) as "Vol to Osmo(OSMO)",