RETENTION_DAY | AVG_RETENTION_RATE | |
|---|---|---|
1 | 0 | 100 |
2 | 1 | 6.29354839 |
3 | 2 | 3.93866667 |
4 | 3 | 3.37862069 |
5 | 4 | 1.4925 |
6 | 5 | 1.0837037 |
7 | 6 | 1.21384615 |
8 | 7 | 1.1368 |
9 | 8 | 1.02125 |
10 | 9 | 1.02 |
11 | 10 | 0.96772727 |
12 | 11 | 1.04619048 |
13 | 12 | 1.05 |
14 | 13 | 0.94105263 |
15 | 14 | 0.97722222 |
16 | 15 | 0.84705882 |
17 | 16 | 0.953125 |
18 | 17 | 0.88666667 |
19 | 18 | 0.85357143 |
20 | 19 | 0.83307692 |
adriaparcerisasflow users interval retention
Updated 18 hours ago
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: about 18 hours ago
31
424B
5s