select

date_trunc('day', recorded_at) as p_date,

avg(price) as price_usd

from osmosis.core.dim_prices

where symbol = 'OSMO'

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

group by 1

select

date_trunc('day', BLOCK_TIMESTAMP) as date,

case

WHEN block_timestamp::date BETWEEN '2022-03-10' and '2022-05-15' THEN '> L Collapse: FROM OSMO'

WHEN block_timestamp::date BETWEEN '2022-10-26' and '2022-10-31' THEN 'List Week: FROM OSMO'

ELSE 'Other day: 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-03-10' and '2022-10-31'

and from_currency = 'uosmo'

group by 1,2

UNION

select

date_trunc('day', BLOCK_TIMESTAMP) as date,

case

WHEN block_timestamp::date BETWEEN '2022-03-10' and '2022-05-15' THEN '> L Collapse: TO OSMO'

WHEN block_timestamp::date BETWEEN '2022-10-26' and '2022-10-31' THEN 'List Week: TO OSMO'

ELSE 'Other day: TO OSMO' end as swap,

COUNT(DISTINCT TX_ID) as tx_cnt_to_osmo,

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