flipside

    Distribution of Swapper Count by Swap Volume($) on "OSMOSIS" Distribution of Tx Count by Swap Volume($) on "OSMOSIS"
    vendettaDistribution of Swapper Count by Swap Volume($) on "OSMOSIS" Distribution of Tx Count by Swap Volume($) on "OSMOSIS"
    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
    )
    , osmoTb as (
    SELECT
    BLOCK_TIMESTAMP::date as date,
    tx_id,
    TRADER,
    coalesce(avg(case
    when TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (TO_AMOUNT/pow(10,TO_DECIMAL))*usd_price
    when FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373' then (FROM_AMOUNT/pow(10,FROM_DECIMAL))*usd_price
    end),0) avg_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 (FROM_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373'
    OR TO_CURRENCY = 'ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373')
    AND TX_SUCCEEDED = TRUE
    GROUP by 1,2,3
    ORDER by 1
    )
    , avaxTb as (
    select tx_hash
    from avalanche.core.fact_event_logs
    where event_name = 'Swap'
    and block_timestamp::date >= '2023-01-21'
    )
    , avalancheTb as (
    select