flipside

    Total # & % of Swapper on Pool 899 by Swap Pair Daily # of Swapper on Pool 899 by Swap Pair Total # & % of Swap Tx on Pool 899 by Swap Pair Total Amount & % of Swapped Volume($) on Pool 899 by Swap Pair Daily Volume($) on Pool 899 by Swap Pair Daily Tx Count on Pool 899 by Swap Pair
    vendettaTotal # & % of Swapper on Pool 899 by Swap Pair Daily # of Swapper on Pool 899 by Swap Pair Total # & % of Swap Tx on Pool 899 by Swap Pair Total Amount & % of Swapped Volume($) on Pool 899 by Swap Pair Daily Volume($) on Pool 899 by Swap Pair Daily Tx Count on Pool 899 by Swap Pair
    Updated 2023-02-08
    with priceTb as (
    select
    HOUR::date as p_date,
    avg(PRICE) as usd_price
    from ethereum.core.fact_hourly_token_prices
    where SYMBOL = 'WAVAX'
    AND HOUR::date > CURRENT_DATE - 90
    AND HOUR::date <= CURRENT_DATE - 1
    group by 1
    )
    SELECT
    BLOCK_TIMESTAMP::date as date,
    tt1.PROJECT_NAME || ' => ' || tt2.PROJECT_NAME as pair,
    COUNT(DISTINCT tx_id) as trade_cnt,
    COUNT(DISTINCT TRADER) as trader_cnt,
    round(coalesce(sum(case
    when FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (FROM_AMOUNT/pow(10,FROM_DECIMAL))*usd_price
    when TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (TO_AMOUNT/pow(10,TO_DECIMAL))*usd_price
    end),0),2) swap_vol
    FROM osmosis.core.fact_swaps ts LEFT JOIN osmosis.core.dim_tokens tt1 on ts.FROM_CURRENCY = tt1.address
    LEFT JOIN osmosis.core.dim_tokens tt2 on ts.TO_CURRENCY = tt2.address
    join priceTb p ON ts.BLOCK_TIMESTAMP::date = p.p_date
    WHERE POOL_IDS[0] ilike '899'
    AND TX_SUCCEEDED = TRUE
    GROUP by 1,2
    ORDER by 1
    -- LIMIT 10
    --*/
    Run a query to Download Data