SniperDaily total number of users in the top 10 Optimism projects
    Updated 2022-10-19
    SELECT
    date_trunc('day', block_timestamp) as date,
    PROJECT_NAME,
    COUNT(DISTINCT tx_hash) as total_tx,
    COUNT(DISTINCT origin_from_address) as total_users
    FROM optimism.core.fact_event_logs
    LEFT outer JOIN optimism.core.dim_labels ON address = contract_address
    where project_name in (
    SELECT project_name
    FROM (
    SELECT
    PROJECT_NAME,
    COUNT(DISTINCT origin_from_address)
    FROM optimism.core.fact_event_logs
    LEFT outer JOIN optimism.core.dim_labels
    ON address = contract_address
    where not project_name is NULL
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10
    )
    )
    AND not project_name IS NULL
    and block_timestamp::date >= '2022-01-01'
    GROUP BY 1,2

    Run a query to Download Data