mehdimarjanOsmosis Average Swap From Fee
    Updated 2022-06-27
    WITH swaps AS (
    SELECT s.BLOCK_TIMESTAMP AS TIME, s.TX_ID, TO_CURRENCY, PROJECT_NAME, TRADER, FEE
    FROM osmosis.core.fact_swaps s
    INNER JOIN osmosis.core.fact_transactions t ON s.TX_ID = t.TX_ID
    INNER JOIN osmosis.core.dim_labels ON ADDRESS = TO_CURRENCY
    WHERE s.TX_STATUS = 'SUCCEEDED'
    ),
    fees AS (
    SELECT TIME, split_part(FEE, 'uosmo', 1)/1e6 AS FEE, 'OSMO' AS fee_currency, (split_part(FEE, 'uosmo', 1)/1e6) * PRICE AS usd_fee
    FROM swaps
    INNER JOIN osmosis.core.dim_prices ON 'OSMO' = SYMBOL
    WHERE FEE ilike '%uosmo%'
    -- AND FEE != '0uosmo'
    UNION
    SELECT TIME, (split_part(FEE, 'ibc', 1)/1e6) AS FEE, (split_part(FEE, 'ibc', 1)/1e6) * PRICE AS usd_fee,
    (CASE
    WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/1480B8FD20AD5FCAE81EA87584D269547DD4D436843C1D20F15E00EB64743EF4' THEN 'AKT'
    WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' THEN 'ATOM'
    WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/0EF15DF2F02480ADE0BB6E85D9EBB5DAEA2836D3860E9F97F9AADE4F57A31AA0' THEN 'LUNC'
    END
    ) AS fee_currency
    FROM swaps s
    INNER JOIN osmosis.core.dim_prices ON SYMBOL = 'ATOM'
    WHERE CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) IN ('ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2')
    )

    SELECT TIME, split_part(FEE, 'uosmo', 1)/1e6 AS FEE, 'OSMO' AS fee_currency, (split_part(FEE, 'uosmo', 1)/1e6) * PRICE AS usd_fee
    FROM swaps
    INNER JOIN osmosis.core.dim_prices ON 'OSMO' = SYMBOL
    WHERE FEE ilike '%uosmo%'
    -- AND FEE != '0uosmo'
    UNION
    SELECT TIME, (split_part(FEE, 'ibc', 1)/1e6) AS FEE, (split_part(FEE, 'ibc', 1)/1e6) * PRICE AS usd_fee,
    (CASE
    WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/1480B8FD20AD5FCAE81EA87584D269547DD4D436843C1D20F15E00EB64743EF4' THEN 'AKT'
    WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' THEN 'ATOM'
    Run a query to Download Data