ACTIVITY_DATE | FEE_CATEGORY | ACTIVE_USERS | CHURNED_USERS | CHURN_RATE_PERCENT | |
|---|---|---|---|---|---|
1 | 2025-01-26 00:00:00.000 | 0.25% Fee | 44812 | 44812 | 100 |
2 | 2025-01-25 00:00:00.000 | 0.25% Fee | 62221 | 51247 | 82.36 |
3 | 2025-01-24 00:00:00.000 | 0.25% Fee | 60873 | 43459 | 71.39 |
4 | 2025-01-23 00:00:00.000 | 0.25% Fee | 64766 | 42728 | 65.97 |
5 | 2025-01-22 00:00:00.000 | 0.25% Fee | 69511 | 43405 | 62.44 |
6 | 2025-01-21 00:00:00.000 | 0.25% Fee | 66895 | 38969 | 58.25 |
7 | 2025-01-20 00:00:00.000 | 0.25% Fee | 68186 | 39792 | 58.36 |
8 | 2025-01-19 00:00:00.000 | 0.25% Fee | 74822 | 42339 | 56.59 |
9 | 2025-01-18 00:00:00.000 | 0.25% Fee | 76287 | 38050 | 49.88 |
10 | 2025-01-17 00:00:00.000 | 0.25% Fee | 69255 | 31272 | 45.15 |
11 | 2025-01-16 00:00:00.000 | 0.25% Fee | 69921 | 30728 | 43.95 |
12 | 2025-01-15 00:00:00.000 | 0.25% Fee | 69832 | 29292 | 41.95 |
13 | 2025-01-14 00:00:00.000 | 0.25% Fee | 67970 | 28239 | 41.55 |
14 | 2025-01-13 00:00:00.000 | 0.25% Fee | 64976 | 25507 | 39.26 |
15 | 2025-01-12 00:00:00.000 | 0.25% Fee | 69449 | 30902 | 44.5 |
16 | 2025-01-11 00:00:00.000 | 0.25% Fee | 69145 | 27593 | 39.91 |
17 | 2025-01-10 00:00:00.000 | 0.25% Fee | 69531 | 25804 | 37.11 |
18 | 2025-01-09 00:00:00.000 | 0.25% Fee | 69487 | 25190 | 36.25 |
19 | 2025-01-08 00:00:00.000 | 0.25% Fee | 73695 | 25637 | 34.79 |
20 | 2025-01-07 00:00:00.000 | 0.25% Fee | 78045 | 26450 | 33.89 |
mondovdaily user churn
Updated 2025-01-26
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 daily_activity AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS activity_date,
origin_from_address AS user,
COUNT(*) AS daily_transactions,
CASE
WHEN block_timestamp < '2023-10-17' THEN 'No Fee'
WHEN block_timestamp >= '2023-10-17' AND block_timestamp < '2024-04-10' THEN '0.15% Fee'
ELSE '0.25% Fee'
END AS fee_category
FROM ethereum.defi.ez_dex_swaps
WHERE lower(platform) LIKE 'uniswap%'
AND block_timestamp >= '2023-01-01'
GROUP BY 1, 2, 4
),
user_last_activity AS (
SELECT
user,
MAX(activity_date) AS last_activity_date
FROM daily_activity
GROUP BY 1
),
daily_churn AS (
SELECT
da.activity_date,
da.fee_category,
COUNT(DISTINCT da.user) AS active_users,
COUNT(DISTINCT CASE WHEN da.activity_date = ula.last_activity_date THEN da.user END) AS churned_users
FROM daily_activity da
JOIN user_last_activity ula ON da.user = ula.user
GROUP BY 1, 2
)
SELECT
activity_date,
fee_category,
active_users,
Last run: 3 months ago
...
757
42KB
62s