warriorgem11
    Updated 2023-06-09
    with ev_price_tbl as (
    select date(RECORDED_AT) as pdate, avg(price) as price
    from osmosis.core.dim_prices
    where symbol = 'ATOM'
    group by pdate
    )
    select date_trunc('day', block_timestamp) as date,
    count(distinct TX_ID) as swaps,
    count(distinct TRADER) as swappers,
    sum(case when FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then FROM_AMOUNT/1e6 * price else TO_AMOUNT/1e6 * price end) as usd_vol,
    avg(case when FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then FROM_AMOUNT/1e6 * price else TO_AMOUNT/1e6 * price end) as avg_usd_vol,
    sum(usd_vol) over (order by date) as cumu_usd_vol,
    sum(swaps) over (order by date) as cumu_swaps,
    price
    from osmosis.core.fact_swaps
    join ev_price_tbl on ev_price_tbl.pdate = date(block_timestamp)
    where (FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' OR TO_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' )
    group by date, price
    Run a query to Download Data