FlippppppaIBC Volume copy
    Updated 2023-09-28
    -- forked from Playwo / IBC Volume @ https://flipsidecrypto.xyz/Playwo/q/fKlPG7s1tGLw/ibc-volume

    WITH timeframe AS (
    SELECT date_day AS date
    FROM crosschain.core.dim_dates
    WHERE date_day >= TO_DATE('2023-05-07') AND date_day <= TO_DATE('2023-05-22')
    )

    SELECT t.date,
    NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_IN', amount, 0)) / POW(10, 18), 0) AS "IBC_IN $ARB",
    NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_IN', amount * p.price, 0)) / POW(10, 18), 0) AS "IBC_IN $USD",
    NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_OUT', amount, 0)) / POW(10, 18), 0) AS "IBC_OUT $ARB",
    NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_OUT', amount * p.price, 0)) / POW(10, 18), 0) AS "IBC_OUT $USD",
    sum("IBC_IN $ARB" - "IBC_OUT $ARB") OVER (ORDER BY t.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "$ARB Supply",
    "$ARB Supply" * p2.price AS "$ARB Supply in $USD"
    FROM timeframe t
    LEFT JOIN osmosis.core.fact_transfers ON transfer_type IN ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')
    AND currency = 'ibc/10E5E5B06D78FFBB61FD9F89209DEE5FD4446ED0550CBB8E3747DA79E10D9DC6'
    AND t.date = TRUNC(block_timestamp, 'day')
    LEFT JOIN osmosis.core.ez_prices p ON p.recorded_hour = TRUNC(block_timestamp, 'hour')
    AND p.currency = 'ibc/10E5E5B06D78FFBB61FD9F89209DEE5FD4446ED0550CBB8E3747DA79E10D9DC6'
    LEFT JOIN osmosis.core.ez_prices p2 ON p2.recorded_hour = t.date
    AND p2.currency = 'ibc/10E5E5B06D78FFBB61FD9F89209DEE5FD4446ED0550CBB8E3747DA79E10D9DC6'
    GROUP BY t.date, p2.price


    Run a query to Download Data