ACTIVITY_DATE | CHURNED_USER_COUNT | CHURN_RATE | |
---|---|---|---|
1 | 2025-02-03 00:00:00.000 | 283 | 12.032312925 |
2 | 2025-01-27 00:00:00.000 | 464 | 25.108225108 |
3 | 2025-01-20 00:00:00.000 | 501 | 53.928955867 |
4 | 2025-01-13 00:00:00.000 | 259 | 31.896551724 |
5 | 2025-01-06 00:00:00.000 | 182 | 22.114216282 |
6 | 2024-12-30 00:00:00.000 | 262 | 36.592178771 |
7 | 2024-12-23 00:00:00.000 | 145 | 15.743756786 |
8 | 2024-12-16 00:00:00.000 | 273 | 29.323308271 |
9 | 2024-12-09 00:00:00.000 | 255 | 20.935960591 |
10 | 2024-12-02 00:00:00.000 | 386 | 36.761904762 |
11 | 2024-11-25 00:00:00.000 | 379 | 44.958481613 |
12 | 2024-11-18 00:00:00.000 | 286 | 35.929648241 |
13 | 2024-11-11 00:00:00.000 | 283 | 29.60251046 |
14 | 2024-11-04 00:00:00.000 | 360 | 58.536585366 |
15 | 2024-10-28 00:00:00.000 | 179 | 38.085106383 |
16 | 2024-10-21 00:00:00.000 | 141 | 28.031809145 |
17 | 2024-10-14 00:00:00.000 | 164 | 30.314232902 |
18 | 2024-10-07 00:00:00.000 | 161 | 11.899482631 |
19 | 2024-09-30 00:00:00.000 | 1010 | 178.130511464 |
20 | 2024-09-23 00:00:00.000 | 191 | 41.52173913 |
gigiokobamusical-teal
Updated 2025-02-09
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
›
⌄
WITH daily_users AS (
SELECT
DATE_TRUNC('week', block_timestamp) AS activity_date,
origin_from_address AS user_address
FROM avalanche.core.ez_decoded_event_logs
WHERE contract_address = '0xaaab9d12a30504559b0c5a9a5977fee4a6081c6b'
GROUP BY 1, 2
),
previous_day AS (
SELECT
activity_date,
user_address,
LAG(activity_date) OVER (PARTITION BY user_address ORDER BY activity_date) AS prev_active_date
FROM daily_users
),
churned_users AS (
SELECT
activity_date,
user_address
FROM previous_day
WHERE prev_active_date IS NOT NULL
AND prev_active_date < activity_date - INTERVAL '7 day' -- User was active before but skipped at least one day
)
SELECT
activity_date,
COUNT(DISTINCT user_address) AS churned_user_count,
(COUNT(DISTINCT user_address)::FLOAT / NULLIF((SELECT COUNT(DISTINCT user_address) FROM daily_users d WHERE d.activity_date = churned_users.activity_date - INTERVAL '7 day'), 0)) * 100 AS churn_rate
FROM churned_users
GROUP BY 1
ORDER BY 1 DESC;
Last run: 14 days ago
59
3KB
7s