SocioCryptoNew User - Project Explorer
Updated 2023-11-08
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
›
⌄
-- forked from New User per Project @ https://flipsidecrypto.xyz/edit/queries/c82bd9c3-9c9f-4452-a78d-e71bac3c1ea8
with main as (
SELECT *, rank()over(partition by tx_signer order by block_timestamp) as rank
FROM near.core.fact_transactions
),
near_new_users as (
SELECT date_trunc('{{interval}}',block_timestamp) as date,
count(DISTINCT tx_signer) as n_new_user
FROM main a
LEFT JOIN near.core.dim_address_labels b
on a.tx_RECEIVER = b.address
WHERE rank = '1' and block_timestamp is not null and project_name is not null
GROUP by 1
),
projets_new_users as (
SELECT date_trunc('{{interval}}',block_timestamp) as date,
--project_name,
count(DISTINCT tx_signer) as n_new_user
FROM main a
LEFT JOIN near.core.dim_address_labels b
on a.tx_RECEIVER = b.address
WHERE rank = '1' and block_timestamp is not null and project_name = '{{project_name}}'
GROUP by 1--,2
)
SELECT a.date,
a.n_new_user as near_new_users,
b.n_new_user as project_new_users
FROM near_new_users a
LEFT JOIN projets_new_users b
on a.date = b.date
WHERE a.date >= '2022-09-01'
Run a query to Download Data