VALUE_CATEGORY | USER_COUNT | AVG_TOTAL_VALUE_PER_USER | AVG_TRANSACTION_VALUE | |
|---|---|---|---|---|
1 | Middle 50% - Medium Value User | 281393 | 0.64 | 0.48 |
2 | Bottom 25% - Low Value User | 134041 | 0.01 | 0.01 |
3 | Top 25% - High Value User | 93262 | 2.02 | 0.57 |
4 | Top 5% - High Roller | 21098 | 11.14 | 1.61 |
5 | Top 1% - Whale User | 5343 | 48650.55 | 2941.8 |
Kruys-CollinsUser Address by Value
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
35
36
›
⌄
WITH contract_addresses AS (
SELECT DISTINCT address FROM monad.testnet.dim_contracts
),
user_value_activity AS (
SELECT
user_address,
SUM(value) AS total_value_transacted,
COUNT(*) AS transaction_count
FROM (
SELECT from_address AS user_address, value FROM monad.testnet.fact_transactions
WHERE block_timestamp >= '2025-02-19'
UNION ALL
SELECT to_address AS user_address, value 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 -- Exclude contract addresses
GROUP BY user_address
),
value_percentiles AS (
SELECT
APPROX_PERCENTILE(total_value_transacted, 0.25) AS low_value_threshold, -- Bottom 25%
APPROX_PERCENTILE(total_value_transacted, 0.75) AS high_value_threshold, -- Top 25%
APPROX_PERCENTILE(total_value_transacted, 0.95) AS high_roller_threshold, -- Top 5%
APPROX_PERCENTILE(total_value_transacted, 0.99) AS whale_threshold -- Top 1%
FROM user_value_activity
)
SELECT
CASE
WHEN ua.total_value_transacted <= (SELECT low_value_threshold FROM value_percentiles) THEN 'Bottom 25% - Low Value User'
WHEN ua.total_value_transacted > (SELECT whale_threshold FROM value_percentiles) THEN 'Top 1% - Whale User'
WHEN ua.total_value_transacted > (SELECT high_roller_threshold FROM value_percentiles) THEN 'Top 5% - High Roller'
WHEN ua.total_value_transacted > (SELECT high_value_threshold FROM value_percentiles) THEN 'Top 25% - High Value User'
ELSE 'Middle 50% - Medium Value User'
END AS value_category,
Last run: 23 days ago
5
235B
5s