msafadoostmonthly user retention on flow in last 3 months
Updated 2022-07-17
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 users as (
SELECT DISTINCT(PROPOSER) as user,
'1' as month
FROM flow.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2022-04-20'
AND BLOCK_TIMESTAMP < '2022-05-20'
UNION
SELECT DISTINCT(PROPOSER) as user,
'2' as month
FROM flow.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2022-05-20'
AND BLOCK_TIMESTAMP < '2022-06-20'
UNION
SELECT DISTINCT(PROPOSER) as user,
'3' as month
FROM flow.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2022-06-20'
AND BLOCK_TIMESTAMP < '2022-07-20'
),
retention AS (
SELECT USER,
COUNT(user) as counts
FROM users
GROUP by 1
ORDER by 2 DESC
),
total_users AS (
SELECT COUNT(DISTINCT(PROPOSER)) as total_user
FROM flow.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2022-04-20'
),
act as (
SELECT
CASE
WHEN counts = 3 then '3 month active'
WHEN counts = 2 then '2 month active'
Run a query to Download Data