adriaparcerisasFlow user retention 4
Updated 2023-06-01
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
›
⌄
--How does Flow compare to other L1s in terms of user retention?
--Is a user who made a transaction previously likely to make another transaction a week or a month later?
--Compare and contrast this type of activity vs other L1s like Solana and Ethereum
WITH
new_users as (
SELECT
distinct tx_from as users,
min(block_timestamp::date) as debut
from osmosis.core.fact_transactions
where block_timestamp between CURRENT_DATE-INTERVAL '3 WEEKS' and CURRENT_DATE-INTERVAL '2 WEEKS'
group by 1
),
users_retention as (
SELECT
distinct tx_from as users,
max(block_timestamp::date) as date
from osmosis.core.fact_transactions
where tx_from in (select users from new_users)
--join new_users y on x.payer=y.users and block_timestamp::date BETWEEN debut and debut + INTERVAL '1 WEEK'
and block_timestamp >=CURRENT_DATE-INTERVAL '1 WEEK'
group by 1
)
select
count(distinct x.users) as total_users,
count(distinct y.users) as retained_users,
(retained_users/total_users)*100 as pcg_retention
from new_users x, users_retention y
Run a query to Download Data