CHURN_PATTERN | USER_COUNT | PERCENT_OF_TOTAL | |
|---|---|---|---|
1 | One-and-done (0d) | 1583798303 | 94.42 |
2 | Churned Early (7d) | 33599415 | 2 |
3 | Churned Mid (30d) | 22026376 | 1.31 |
4 | Long-term User (90d+) | 21131675 | 1.26 |
5 | Churned Late (60D) | 11207358 | 0.67 |
6 | Almost retained (90d) | 5682800 | 0.34 |
fantasySolana Deactivation Pattern
Updated 2025-04-23
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 user_activity AS (
SELECT
signer,
first_tx_date,
last_tx_date,
DATEDIFF('day', first_tx_date, last_tx_date) AS active_days,
DATEDIFF('day', last_tx_date, CURRENT_DATE) AS days_since_last_active,
programs_used
FROM
solana.core.ez_signers
),
churn_buckets AS (
SELECT
signer,
active_days,
programs_used,
CASE
WHEN active_days = 0 THEN 'One-and-done (0d)'
WHEN active_days < 7 THEN 'Churned Early (7d)'
WHEN active_days < 30 THEN 'Churned Mid (30d)'
WHEN active_days < 60 THEN 'Churned Late (60D)'
WHEN active_days < 90 THEN 'Almost retained (90d)'
ELSE 'Long-term User (90d+)'
END AS churn_pattern
FROM
user_activity
WHERE
days_since_last_active > 14 -- considered churned
)
SELECT
churn_pattern,
COUNT(DISTINCT signer) AS user_count,
ROUND(COUNT(DISTINCT signer) * 100.0 / SUM(COUNT(DISTINCT signer)) OVER (), 2) AS percent_of_total
FROM
churn_buckets
Last run: about 1 month ago
6
226B
88s