SyndicaOptimism Wallets Transactions Count Grouped
    Updated 2024-12-02
    -- forked from Optimism Wallets Transactions Count @ https://flipsidecrypto.xyz/studio/queries/5a62bf35-9142-40e8-8d4c-c22350e1c469

    with

    five_min_counts AS (
    SELECT
    DATEADD('minute', floor(DATEDIFF('minute', timestamp '1970-01-01 00:00:00', block_timestamp) / 5) * 5, timestamp '1970-01-01 00:00:00') AS interval_start,
    date_trunc('month', block_timestamp) AS month,
    from_address as signer,
    COUNT(DISTINCT tx_hash) AS id_count
    FROM
    optimism.core.fact_transactions
    WHERE status = 'SUCCESS'
    AND block_timestamp >= timestamp '2024-09-01 00:00:00'
    AND block_timestamp < timestamp '2024-12-01 00:00:00'
    GROUP BY
    DATEADD('minute', floor(DATEDIFF('minute', timestamp '1970-01-01 00:00:00', block_timestamp) / 5) * 5, timestamp '1970-01-01 00:00:00'),
    date_trunc('month', block_timestamp),
    signer
    )

    , bots as (
    SELECT
    month,
    signer,
    AVG(id_count) AS avg_id_count,
    MAX(id_count) AS max_id_count,
    MIN(id_count) AS min_id_count
    FROM
    five_min_counts
    GROUP BY
    month,
    signer
    HAVING MAX(id_count) >= 50
    ORDER BY
    month,
    QueryRunArchived: QueryRun has been archived