MLDZMNbbon10
    Updated 2023-01-04
    with tb1 as (select
    date_trunc('day',recorded_at) as day,
    avg (price) as usd_price
    from osmosis.core.dim_prices
    where symbol='OSMO'
    and recorded_at::date>='2022-03-01'
    group by 1
    )

    select
    date_trunc('week',block_timestamp) as date,
    'Swap from OSMO' as swap_type,
    count (distinct tx_id) as no_transfer,
    count (distinct trader) as no_senders,
    sum((from_amount/POW (10,from_decimal))*usd_price) as transfer_usd,
    avg((from_amount/POW (10,from_decimal))*usd_price) as avg_transfer_usd,
    sum(no_transfer) over (order by date) as cum_transfer,
    sum(transfer_usd) over (order by date) as cum_transfer_usd,
    avg(avg_transfer_usd) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days
    from osmosis.core.fact_swaps s left join tb1 b on s.block_timestamp::date = b.day
    where FROM_CURRENCY='uosmo'
    and TX_STATUS = 'SUCCEEDED'
    and block_timestamp>='2022-03-12'
    group by 1,2
    union all
    select
    date_trunc('week',block_timestamp) as date,
    'Swap To OSMO' as swap_type,
    count (distinct tx_id) as no_transfer,
    count (distinct trader) as no_senders,
    sum((to_amount/POW (10,to_decimal))*usd_price) as transfer_usd,
    avg((to_amount/POW (10,to_decimal))*usd_price) as avg_transfer_usd,
    sum(no_transfer) over (order by date) as cum_transfer,
    sum(transfer_usd) over (order by date) as cum_transfer_usd,
    avg(avg_transfer_usd) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days
    from osmosis.core.fact_swaps s left join tb1 b on s.block_timestamp::date = b.day
    Run a query to Download Data