TX_ACTIVITY_CATEGORY | USER_COUNT | |
|---|---|---|
1 | Single User | 224933 |
2 | Regular User(3-10) | 148653 |
3 | Tried Twice | 81594 |
4 | Active User(11-50) | 70617 |
5 | Very Active User(51-100) | 5773 |
6 | Super User(>100) | 3567 |
Kruys-Collinsgleaming-silver
Updated 2025-02-20
99
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
›
⌄
WITH contract_addresses AS (
-- Get all contract addresses
SELECT DISTINCT address FROM monad.testnet.dim_contracts
),
user_activity AS (
-- Count transactions per user (EOAs only for senders and receivers)
SELECT
user_address,
COUNT(*) AS tx_count
FROM (
SELECT from_address AS user_address FROM monad.testnet.fact_transactions
WHERE block_timestamp >= '2025-02-19'
UNION ALL
SELECT to_address AS user_address FROM monad.testnet.fact_transactions
WHERE block_timestamp >= '2025-02-19'
) tx
LEFT JOIN contract_addresses c ON tx.user_address = c.address
WHERE c.address IS NULL -- Ensure it's an EOA
GROUP BY user_address
)
SELECT
CASE
WHEN tx_count = 1 THEN 'Single User'
WHEN tx_count = 2 THEN 'Tried Twice'
WHEN tx_count BETWEEN 3 AND 10 THEN 'Regular User(3-10)'
WHEN tx_count BETWEEN 11 AND 50 THEN 'Active User(11-50)'
WHEN tx_count BETWEEN 51 AND 100 THEN 'Very Active User(51-100)'
ELSE 'Super User(>100)'
END AS tx_activity_category,
COUNT(*) AS user_count
FROM user_activity
GROUP BY tx_activity_category
ORDER BY user_count DESC;
Last run: 23 days ago
6
165B
2s