ACTIVITY_TIER | ADDRESS_COUNT | PERCENTAGE_OF_TOTAL | TOTAL_SUCCESSFUL_TXS | AVG_SUCCESS_RATE | AVG_TXS_PER_ADDRESS | AVG_DAYS_ACTIVE | EARLIEST_TRANSACTION | LATEST_TRANSACTION | |
|---|---|---|---|---|---|---|---|---|---|
1 | 01. One-time user (1 tx) | 5052252 | 46.29 | 4983794 | 98.65 | 1 | 0 | 2024-08-29 00:00:02.000 | 2025-02-01 03:34:17.000 |
2 | 02. Tried twice (2 tx) | 2549752 | 23.36 | 5099096 | 99.99 | 2 | 8.47 | 2024-08-29 00:00:27.000 | 2025-02-01 03:34:17.000 |
3 | 03. Occasional (3-10 tx) | 3033999 | 27.8 | 13915635 | 99.96 | 4.59 | 53.8 | 2024-08-29 00:00:02.000 | 2025-02-01 03:34:17.000 |
4 | 04. Regular (11-20 tx) | 129610 | 1.19 | 1733781 | 99.73 | 13.42 | 41.27 | 2024-08-29 00:00:37.000 | 2025-02-01 03:34:17.000 |
5 | 05. Active (21-50 tx) | 46261 | 0.42 | 1446873 | 99.56 | 31.41 | 46.5 | 2024-08-29 00:00:02.000 | 2025-02-01 03:34:17.000 |
6 | 06. Very Active (51-100 tx) | 22795 | 0.21 | 1607838 | 99.67 | 70.76 | 73.5 | 2024-08-29 00:00:03.000 | 2025-02-01 03:34:14.000 |
7 | 07. Power User (101-500 tx) | 74361 | 0.68 | 14534693 | 99.88 | 195.74 | 129.26 | 2024-08-29 00:00:01.000 | 2025-02-01 03:34:17.000 |
8 | 08. Super User (501-1000 tx) | 1821 | 0.02 | 1277458 | 99.31 | 706.26 | 113.94 | 2024-08-29 00:00:11.000 | 2025-02-01 03:34:17.000 |
9 | 09. Elite (1000+ tx) | 2445 | 0.02 | 65993145 | 97.92 | 27463.06 | 119.79 | 2024-08-29 00:00:00.000 | 2025-02-01 03:34:17.000 |
Kruys-Collinsappropriate-turquoise
Updated 2025-02-01
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 AddressMetrics AS (
SELECT
address,
COUNT(*) AS transaction_count,
COUNT(CASE WHEN TX_SUCCEEDED = 'TRUE' THEN 1 END) AS successful_tx_count,
MIN(block_timestamp) AS first_transaction,
MAX(block_timestamp) AS last_transaction
FROM (
SELECT
block_timestamp,
from_address AS address,
value,
TX_SUCCEEDED
FROM kaia.core.fact_transactions
WHERE from_address NOT IN (SELECT address FROM kaia.core.dim_contracts)
AND block_timestamp >= DATE_TRUNC('day', TRY_TO_TIMESTAMP('{{lookback_period}}', 'YYYY-MM-DD'))
UNION ALL
SELECT
block_timestamp,
to_address AS address,
value,
TX_SUCCEEDED
FROM kaia.core.fact_transactions
WHERE to_address NOT IN (SELECT address FROM kaia.core.dim_contracts)
AND block_timestamp >= DATE_TRUNC('day', TRY_TO_TIMESTAMP('{{lookback_period}}', 'YYYY-MM-DD'))
) AS combined
GROUP BY address
),
ActivityTiers AS (
SELECT
address,
transaction_count,
successful_tx_count,
Last run: 3 months ago
9
1KB
12s