PotLockGrowth in Unique Users
Updated 2024-06-13
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
›
⌄
WITH monthly_unique_users AS (
SELECT
DATE_TRUNC('month', block_timestamp) AS month,
COUNT(DISTINCT signer_id) AS unique_users
FROM
near.core.fact_actions_events_function_call
WHERE
method_name IN ('register', 'donate')
GROUP BY
month
),
monthly_growth AS (
SELECT
month,
unique_users,
LAG(unique_users) OVER (ORDER BY month) AS previous_month_users,
CASE
WHEN LAG(unique_users) OVER (ORDER BY month) IS NULL THEN 0
WHEN LAG(unique_users) OVER (ORDER BY month) = 0 THEN 100
ELSE ((unique_users - LAG(unique_users) OVER (ORDER BY month)) / LAG(unique_users) OVER (ORDER BY month)) * 100
END AS user_growth_percentage
FROM
monthly_unique_users
)
SELECT
month,
unique_users,
previous_month_users,
user_growth_percentage
FROM
monthly_growth
ORDER BY
month;
QueryRunArchived: QueryRun has been archived