rmasVelodrome Health - Prep Query
    Updated 2022-08-17
    WITH

    lp_actions AS (
    SELECT tx_hash
    , block_timestamp
    , pool_address
    , lp_action
    , (CASE WHEN lp_token_action = 'mint' THEN 1.000
    WHEN lp_token_action = 'burn' THEN -1.000 END) AS sign
    , lp_token_action
    , lp_token_amount * sign AS lp_token_amount
    , lp_token_amount_usd / lp_token_amount AS lp_token_price_usd

    FROM optimism.velodrome.ez_lp_actions
    WHERE block_timestamp >= '2022-06-16 00:00:00.000'
    ),


    daily_lp_token_supply_change AS (
    SELECT pool_address
    , block_timestamp::date AS utc_date
    , sum(lp_token_amount) AS lp_token_change
    -- , avg(lp_token_price_usd) AS lp_token_price_usd
    FROM lp_actions
    GROUP BY 1,2
    ),


    dim_date AS (
    SELECT dateadd('day', -seq4(), CURRENT_DATE) AS utc_date
    FROM TABLE(GENERATOR(rowcount => 10000))
    WHERE utc_date BETWEEN '2022-06-16' AND CURRENT_DATE
    ),


    Run a query to Download Data