RETENTION_DAY | AVG_RETENTION_RATE | |
|---|---|---|
1 | 0 | 100 |
2 | 1 | 4.66392857 |
3 | 2 | 1.83 |
4 | 3 | 1.87807692 |
5 | 4 | 1.29 |
6 | 5 | 1.12916667 |
7 | 6 | 1.01173913 |
8 | 7 | 0.92727273 |
9 | 8 | 0.94238095 |
10 | 9 | 0.9135 |
11 | 10 | 0.60894737 |
12 | 11 | 1.23055556 |
13 | 12 | 1.11941176 |
14 | 13 | 0.834375 |
15 | 14 | 0.85466667 |
16 | 15 | 0.71642857 |
17 | 16 | 0.67769231 |
18 | 17 | 0.7025 |
19 | 18 | 1.00363636 |
20 | 19 | 0.793 |
adriaparcerisasflow users interval retention
Updated 2025-03-17
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 (
-- First get all user activity from both chains with daily granularity
SELECT
CAST(value AS VARCHAR) AS user_address,
DATE_TRUNC('day', b.block_timestamp) AS activity_day
FROM
flow.core.ez_transaction_actors AS b,
LATERAL FLATTEN(INPUT => b.actors) AS a
WHERE
block_timestamp >= DATEADD(month, -4, CURRENT_DATE)
UNION ALL
SELECT
from_address AS user_address,
DATE_TRUNC('day', block_timestamp) AS activity_day
FROM
flow.core_evm.fact_transactions
WHERE
block_timestamp >= DATEADD(month, -4, CURRENT_DATE)
),
user_first_day AS (
-- Get each user's first day of activity
SELECT
user_address,
MIN(activity_day) AS first_activity_day
FROM user_activity
GROUP BY 1
),
base_cohort_size AS (
SELECT
first_activity_day,
COUNT(DISTINCT user_address) as cohort_size
FROM user_first_day
Last run: 17 days ago
29
390B
10s