Stin00Daily Velodrom Swapper Swaps by DEX
    Updated 2022-08-17
    --darvishi
    WITH
    velo_swappers as (
    SELECT
    DISTINCT
    origin_from_address
    FROM
    optimism.velodrome.ez_swaps
    ), tab1 as (
    SELECT
    tx_hash
    FROM
    optimism.core.fact_event_logs
    LEFT OUTER JOIN optimism.core.dim_labels
    ON
    address = contract_address
    WHERE
    origin_from_address IN (SELECT * FROM velo_swappers)
    AND
    event_name LIKE 'Swap'
    )

    SELECT
    date_trunc('day', block_timestamp),
    project_name,
    COUNT(DISTINCT tx_hash) as swaps,
    COUNT(DISTINCT origin_from_address) as swappers
    FROM
    optimism.core.fact_event_logs
    LEFT OUTER JOIN optimism.core.dim_labels
    ON address = contract_address

    WHERE
    tx_hash IN (SELECT * from tab1)
    and label_type LIKE 'dex'
    GROUP BY 1,2
    Run a query to Download Data