COHORT_DATE | MONTHS | RETENTION_RATE | |
|---|---|---|---|
1 | 2025-01 | 1 | 22.46 |
2 | 2024-12 | 1 | 19.92 |
3 | 2024-12 | 2 | 10.63 |
4 | 2024-11 | 1 | 25.39 |
5 | 2024-11 | 2 | 19.76 |
6 | 2024-11 | 3 | 12.64 |
7 | 2024-10 | 1 | 25.54 |
8 | 2024-10 | 2 | 20.5 |
9 | 2024-10 | 3 | 17.22 |
10 | 2024-10 | 4 | 12.22 |
11 | 2024-09 | 1 | 23.35 |
12 | 2024-09 | 2 | 23.18 |
13 | 2024-09 | 3 | 20.23 |
14 | 2024-09 | 4 | 20.83 |
15 | 2024-09 | 5 | 10.5 |
16 | 2024-08 | 1 | 20.1 |
17 | 2024-08 | 2 | 19.47 |
18 | 2024-08 | 3 | 17.86 |
19 | 2024-08 | 4 | 16.47 |
20 | 2024-08 | 5 | 21.77 |
Flipside Axelar Analyststot RETENTION
Updated 2025-02-18
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 base as (
select
TX_FROM as TX_SIGNER,
min(date_trunc('month', BLOCK_TIMESTAMP)) over (partition by TX_SIGNER) as signup_date,
date_trunc('month', BLOCK_TIMESTAMP) as activity_date,
datediff('month', signup_date, activity_date) as difference
from axelar.core.fact_transactions
),
unp as (
select
TO_VARCHAR(signup_date, 'yyyy-MM') as cohort_date,
difference as months,
count (distinct TX_SIGNER) as users
from
base
where
datediff('month', signup_date, current_date()) <= 12
group by
1,2
order by
1
),
fine as (
select
u.*,
p.USERS as user0
from
unp u
left join unp p on u.COHORT_DATE = p.COHORT_DATE
where
p.MONTHS = 0
)
select
COHORT_DATE,
MONTHS,
round(100 * users / user0 , 2 ) as retention_rate
Last run: 2 months ago
78
2KB
51s