cypherosmosis avg before terra collapse
    Updated 2022-11-01
    -- analyse trading activity for week of 28th vs weekly averages
    -- analyse trading activity for 28th vs daily averages.

    -- daily average should be :
    -- 1) last 60 days
    -- 2) last 60 days before May terra collapse.

    -- analyse metrics for other tokens as well.


    with data as (select
    date_trunc('hour', block_timestamp) as hour,
    count(distinct(tx_id)) as n_swaps,
    count(distinct(trader)) as swappers,
    sum(iff(from_currency = 'uosmo', from_amount/1e6, to_amount/1e6)) as osmo_amount

    from osmosis.core.fact_swaps
    where (from_currency = 'uosmo' or to_currency = 'uosmo')
    and hour >= '2022-3-8'
    and hour < '2022-5-7'
    group by hour),

    osmo_price as (select
    date_trunc('hour', recorded_at) as hour,
    avg(price) as avg_hourly_price
    from osmosis.core.dim_prices
    where symbol = 'OSMO'
    group by hour),

    joined as (select * from data
    join osmo_price using (hour)),

    hourly_data as (select *,
    osmo_amount * avg_hourly_price as volume_usd