CHURN_MONTH | CHURN_PATTERN | USER_COUNT | |
|---|---|---|---|
1 | 2020-03-01 00:00:00.000 | One-and-done (0d) | 98 |
2 | 2020-10-01 00:00:00.000 | Churned Early (1-6d) | 125 |
3 | 2020-10-01 00:00:00.000 | Churned Mid (7-29d) | 47 |
4 | 2020-10-01 00:00:00.000 | Long-term User (60d+) | 1 |
5 | 2020-10-01 00:00:00.000 | One-and-done (0d) | 382520 |
6 | 2020-11-01 00:00:00.000 | Churned Early (1-6d) | 150 |
7 | 2020-11-01 00:00:00.000 | Churned Late (30-59d) | 24 |
8 | 2020-11-01 00:00:00.000 | Churned Mid (7-29d) | 160 |
9 | 2020-11-01 00:00:00.000 | One-and-done (0d) | 404006 |
10 | 2020-12-01 00:00:00.000 | Churned Early (1-6d) | 23708 |
11 | 2020-12-01 00:00:00.000 | Churned Late (30-59d) | 222 |
12 | 2020-12-01 00:00:00.000 | Churned Mid (7-29d) | 5152 |
13 | 2020-12-01 00:00:00.000 | Long-term User (60d+) | 30 |
14 | 2020-12-01 00:00:00.000 | One-and-done (0d) | 6002823 |
15 | 2021-01-01 00:00:00.000 | Churned Early (1-6d) | 154145 |
16 | 2021-01-01 00:00:00.000 | Churned Late (30-59d) | 12775 |
17 | 2021-01-01 00:00:00.000 | Churned Mid (7-29d) | 176089 |
18 | 2021-01-01 00:00:00.000 | Long-term User (60d+) | 107 |
19 | 2021-01-01 00:00:00.000 | One-and-done (0d) | 10726866 |
20 | 2021-02-01 00:00:00.000 | Churned Early (1-6d) | 76653 |
fantasySolana Monthly 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,
DATE_TRUNC('month', last_tx_date) AS churn_month,
programs_used
FROM
solana.core.ez_signers
),
churn_buckets AS (
SELECT
signer,
churn_month,
active_days,
programs_used,
CASE
WHEN active_days = 0 THEN 'One-and-done (0d)'
WHEN active_days BETWEEN 1 AND 6 THEN 'Churned Early (1-6d)'
WHEN active_days BETWEEN 7 AND 29 THEN 'Churned Mid (7-29d)'
WHEN active_days BETWEEN 30 AND 59 THEN 'Churned Late (30-59d)'
ELSE 'Long-term User (60d+)'
END AS churn_pattern
FROM
user_activity
WHERE
days_since_last_active > 14 -- considered churned
)
SELECT
churn_month,
churn_pattern,
COUNT(*) AS user_count
Last run: about 2 months ago
...
274
15KB
16s