abaQuery - Flow monthly user retention
Updated 2022-07-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
›
⌄
with raw as (
SELECT PROPOSER, date_trunc('month', block_timestamp :: date) AS months, count(*) AS item_transactions,
lag(date_trunc('month', block_timestamp :: date)
) OVER (PARTITION BY PROPOSER ORDER BY date_trunc('month', block_timestamp :: date)) = date_trunc('month', block_timestamp :: date) - interval '30 day'
OR NULL AS repeat_transaction
FROM flow.core.fact_transactions
WHERE block_timestamp :: date >= '2022-04-18'
GROUP BY 1, 2
)
SELECT months, sum(item_transactions) AS number_of_transactions, count(*) AS number_of_users, count(repeat_transaction) AS repeat_users,
round(CASE WHEN sum(item_transactions) > 0 THEN count(repeat_transaction) / sum(item_transactions) * 100 ELSE 0 END, 2) AS user_retention
FROM raw
GROUP BY 1
ORDER BY 1
Run a query to Download Data