mrwildcatPyth Oracle on Base, Top Contracts
    Updated 2024-09-24
    -- forked from Pyth Oracle on BSC, Top Contracts @ https://flipsidecrypto.xyz/edit/queries/c5bddb69-cd5a-48ad-84cd-0ddae8ee1ae2

    WITH price_updates AS (
    SELECT
    from_address AS contract_address,
    COUNT(*) AS update_count
    FROM base.core.fact_traces
    WHERE to_address = LOWER('0x8250f4aF4B972684F7b336503E2D6dFeDeB1487a')
    AND block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '6 months'
    AND SUBSTRING(input, 1, 10) IN (
    '0xef9e5e28', '0xb9256d28', '0x785bdda0', '0xf4347f83',
    '0x4716e9c5', '0xaccca7f9', '0x0d4702e1', '0x631a316c'
    )
    AND type = 'CALL'
    GROUP BY 1
    ),
    user_counts AS (
    SELECT
    to_address AS contract_address,
    COUNT(DISTINCT from_address) AS unique_users
    FROM base.core.fact_transactions
    WHERE block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '6 months'
    AND status = 'SUCCESS'
    AND to_address IN (
    SELECT contract_address
    FROM price_updates
    )
    GROUP BY 1
    )
    SELECT
    pu.contract_address,
    pu.update_count AS price_updates,
    COALESCE(uc.unique_users, 0) AS number_of_users,
    COALESCE(dl.PROJECT_NAME, 'Unknown') AS project_name
    FROM price_updates pu
    LEFT JOIN user_counts uc ON pu.contract_address = uc.contract_address
    QueryRunArchived: QueryRun has been archived