kaibladeActive Users Count vs OP Price
    Updated 2022-12-21
    WITH dex_swaps AS
    (SELECT *
    FROM optimism.core.fact_event_logs
    WHERE event_name = 'Swap'),


    op_swaps AS
    (SELECT *
    FROM dex_swaps
    WHERE (event_inputs:tokenIn = '0x4200000000000000000000000000000000000042'
    OR event_inputs:tokenOut = '0x4200000000000000000000000000000000000042')
    AND block_timestamp::date >= CURRENT_DATE() -INTERVAL '2 months'
    ),



    op_prices AS
    (SELECT *
    FROM optimism.core.fact_hourly_token_prices
    WHERE symbol = 'OP'
    AND hour::date >= CURRENT_DATE() - INTERVAL '2 months'),

    daily_swap_users AS
    (SELECT DATE_TRUNC('day', block_timestamp) AS "Days",
    COUNT(DISTINCT origin_from_address) AS "Active Users Count"
    FROM optimism.core.fact_token_transfers
    WHERE block_timestamp::date >= CURRENT_DATE() -INTERVAL '2 months'
    AND contract_address = '0x4200000000000000000000000000000000000042'
    GROUP BY "Days"),

    daily_prices AS
    (SELECT DATE_TRUNC('day', hour) AS "Days",
    AVG(price) AS "Average Price"
    FROM op_prices
    GROUP BY "Days")

    Run a query to Download Data