SyndicaOptimism Wallets Transactions Count Grouped
Updated 2024-12-02
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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