Flipside Teamproject users growth (Project Scope)
Updated 2024-11-27
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
›
⌄
-- forked from project users growth (Project Scope) @ https://flipsidecrypto.xyz/edit/queries/e4ceafea-9b63-4112-b678-ff67f0bfb45c
with near_projects as
(select ADDRESS, initcap(ADDRESS_NAME) as ADDRESS_NAME,
initcap(PROJECT_NAME) as PROJECT_NAME,
initcap(LABEL_TYPE) as LABEL_TYPE
from near.core.dim_address_labels
where LABEL_TYPE not in ('cex','token') and PROJECT_NAME is not null
),
projects_events as (
select BLOCK_TIMESTAMP, SIGNER_ID, METHOD_NAME, RECEIVER_ID, ADDRESS_NAME, PROJECT_NAME, LABEL_TYPE, TX_HASH, DEPOSIT/1e24 as Volume
from near.core.fact_actions_events_function_call
join near_projects
on RECEIVER_ID=ADDRESS
where RECEIPT_SUCCEEDED=true
),
users as
(
select SIGNER_ID as user, min(BLOCK_TIMESTAMP) as min_date
from projects_events
where replace(PROJECT_NAME,' (', ' (')=initcap('{{project_name}}')
group by 1
)
select date_trunc(day,min_date) as date, count(*) as "New User",
sum("New User") over (order by date) as "Total Users"
from users
where min_date>='{{starting_date}}'
group by 1
order by 2 desc
Auto-refreshes every 3 hours
QueryRunArchived: QueryRun has been archived