elvisOP Price action Analysis, T5: velodrome OP flows and balance
    Updated 2022-12-24
    WITH
    OP_price_table as (
    SELECT 'OP_price' as provider, hour as block_timestamp, symbol, price price_OP
    FROM optimism.core.fact_hourly_token_prices
    WHERE hour > '2022-10-01'
    AND SYMBOL ='OP'
    ),
    velo_op_swaps_in AS (
    SELECT block_timestamp::date date, sum(amount_in) as OP_IN
    FROM optimism.velodrome.ez_swaps
    WHERE symbol_in = 'OP'
    AND block_timestamp > '2022-10-01'
    GROUP BY date
    ),
    velo_op_swaps_out AS (
    SELECT block_timestamp::date date, sum(amount_out) as OP_OUT
    FROM optimism.velodrome.ez_swaps
    WHERE symbol_out = 'OP'
    AND block_timestamp > '2022-10-01'
    GROUP BY date
    ),
    velo_op_swaps_t AS (
    SELECT date, OP_IN, -OP_out OP_out, sum(OP_in-op_out) OVER (ORDER BY date rows between unbounded preceding and current row) OP_locked_change
    FROM velo_op_swaps_in FULL OUTER JOIN velo_op_swaps_out USING (date)
    ),
    velo_op_swaps AS (
    SELECT *
    FROM OP_price_table P LEFT JOIN velo_op_swaps_t S ON P.block_timestamp::date = S.date
    )

    SELECT *
    FROM velo_op_swaps
    Run a query to Download Data