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,

sender as signer,

COUNT(DISTINCT tx_hash) AS id_count

FROM

aptos.core.fact_transactions

WHERE success

AND block_timestamp >= timestamp '2024-08-01 00:00:00'

AND block_timestamp < timestamp '2024-11-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

, aptos_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,

signer