PotLockGrowth in Unique Projects (Monthly)
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_projects AS (
SELECT
DATE_TRUNC('month', block_timestamp) AS month,
COUNT(DISTINCT receiver_id) AS unique_projects
FROM
near.core.fact_actions_events_function_call
WHERE
method_name IN ('register', 'donate')
GROUP BY
month
),
monthly_growth AS (
SELECT
month,
unique_projects,
LAG(unique_projects) OVER (ORDER BY month) AS previous_month_projects,
CASE
WHEN LAG(unique_projects) OVER (ORDER BY month) IS NULL THEN 0
WHEN LAG(unique_projects) OVER (ORDER BY month) = 0 THEN 100
ELSE ((unique_projects - LAG(unique_projects) OVER (ORDER BY month)) / LAG(unique_projects) OVER (ORDER BY month)) * 100
END AS project_growth_percentage
FROM
monthly_unique_projects
)
SELECT
month,
unique_projects,
previous_month_projects,
project_growth_percentage
FROM
monthly_growth
ORDER BY
month;
QueryRunArchived: QueryRun has been archived