datavortexnew stakers retention rate
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 new_stakers AS (
SELECT
delegator,
MIN(DATE_TRUNC('week', block_timestamp)) AS first_stake_week
FROM
flow.gov.ez_staking_actions
WHERE
tx_succeeded = TRUE
AND block_timestamp >= '2024-08-01'
GROUP BY
delegator
),
staker_retention AS (
SELECT
ns.delegator,
ns.first_stake_week,
DATE_TRUNC('week', sa.block_timestamp) AS action_week
FROM
new_stakers ns
JOIN
flow.gov.ez_staking_actions sa
ON
ns.delegator = sa.delegator
WHERE
DATE_TRUNC('week', sa.block_timestamp) = ns.first_stake_week + INTERVAL '1 week'
)
SELECT
ns.first_stake_week AS "week_start_date",
COUNT(DISTINCT sr.delegator) AS "retained_stakers",
(COUNT(DISTINCT sr.delegator) * 100.0) / COUNT(DISTINCT ns.delegator) AS "weekly_retention_rate"
FROM
new_stakers ns
LEFT JOIN
staker_retention sr
ON
ns.delegator = sr.delegator
QueryRunArchived: QueryRun has been archived