SocioCryptoRetention Rate
Updated 2024-01-15Copy Reference Fork
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
›
⌄
with
ranked as (
SELECT
block_timestamp,
origin_from_address as user,
rank()over(partition by user ORDER BY block_timestamp) as rank
FROM avalanche.core.fact_event_logs
WHERE origin_to_address = lower('0xa695ea0C90D89a1463A53Fa7a02168Bc46FbBF7e')
),
last_txns as (
SELECT origin_from_address as user,
max(block_timestamp) as last_txn
FROM avalanche.core.fact_event_logs
WHERE origin_to_address = lower('0xa695ea0C90D89a1463A53Fa7a02168Bc46FbBF7e')
GROUP BY 1
)
SELECT x.date as month,
100*n_sustained/(n_sustained+n_leaver+x.n_drop) as one_month_ret_
FROM
(SELECT date_trunc('month',s.last_txn) as date,
count(DISTINCT s.user) as n_drop
FROM last_txns s
GROUP BY date)x
LEFT JOIN (
SELECT date_trunc('month',a.block_timestamp) as date,
count(DISTINCT CASE WHEN datediff(week,a.block_timestamp, b.block_timestamp)>1 THEN a.user END) as n_leaver,
count(DISTINCT CASE WHEN datediff(week,a.block_timestamp,b.block_timestamp)<=1 THEN a.user END) as n_sustained
FROM ranked a, ranked b
WHERE a.rank = b.rank - 1 AND a.user = b.user
GROUP BY date
)y
ON x.date = y.date
ORDER BY month
QueryRunArchived: QueryRun has been archived