BlockTrackerretention rate users phoenix
Updated 2023-09-04
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
tb0 as (
SELECT
DISTINCT signers[0] as old_user,
date_trunc('d', block_timestamp) as date
FROM solana.core.fact_events
WHERE program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
AND block_timestamp > DATEADD('week', -2 , current_date)
AND block_timestamp < current_date - 7
),
tb1 as (
SELECT
DISTINCT signers[0] as retern_user,
date_trunc('d', block_timestamp) as date
FROM solana.core.fact_events
WHERE program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
AND block_timestamp > DATEADD('week', -1, current_date)
AND block_timestamp < current_date
AND signers[0] IN (SELECT old_user FROM tb0)
)
SELECT
count(DISTINCT a.old_user) as old_users,
count(DISTINCT b.retern_user) as reterned_users,
(reterned_users/old_users ) * 100 as retention_rate
FROM tb0 a
LEFT OUTER JOIN tb1 b ON a.old_user = b.retern_user
with base_day as (
SELECT
DISTINCT signers [0] as base_users
FROM
Run a query to Download Data