nsa2000Hourly variation of the Top 10 Swap Pair according to the Volume (USD) to Swap on Listing Week
    Updated 2022-11-03
    with prices as (
    select
    date_trunc('hour', recorded_at) as p_date,
    symbol,
    avg(price) as price_usd
    from osmosis.core.dim_prices
    where recorded_at::date BETWEEN '2022-10-26' and '2022-10-31'
    group by 1,2
    )
    , tx as (
    SELECT
    date_trunc('day', BLOCK_TIMESTAMP) as date,
    TX_ID,
    TRADER,
    b.PROJECT_NAME as symbolIn,
    c.PROJECT_NAME as symbolOut,
    FROM_AMOUNT/pow(10, FROM_DECIMAL)*d.price_usd as from_amt,
    TO_AMOUNT/pow(10, TO_DECIMAL)*e.price_usd as to_amt
    FROM osmosis.core.fact_swaps a
    join osmosis.core.dim_labels b on a.FROM_CURRENCY = b.Address --PROJECT_NAME
    join osmosis.core.dim_labels c on a.TO_CURRENCY = c.Address
    join prices d on b.PROJECT_NAME = d.symbol and date_trunc('day', a.BLOCK_TIMESTAMP) = d.p_date
    join prices e on c.PROJECT_NAME = e.symbol and date_trunc('day', a.BLOCK_TIMESTAMP) = e.p_date
    WHERE date BETWEEN '2022-10-26' and '2022-10-31'
    )
    SELECT
    date,
    symbolIn,
    symbolOut,
    symbolIn || ' => ' || symbolOut as pair,
    COUNT(DISTINCT TX_ID) as tx_cnt,
    COUNT(DISTINCT TRADER) as TRADER,
    sum(from_amt) as swap_from_amt,
    sum(to_amt) as swap_to_amt,
    ROW_NUMBER() OVER (partition by date order by swap_to_amt desc) rank
    from tx
    Run a query to Download Data