freshgeek2024-07-13 01:08 PM
Updated 2024-07-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
34
35
36
›
⌄
WITH user_activity AS (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,
BUYER_ADDRESS,
ROW_NUMBER() OVER (PARTITION BY BUYER_ADDRESS ORDER BY BLOCK_TIMESTAMP) AS first_purchase_rank
FROM
avalanche.nft.ez_nft_sales
WHERE
BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE)
),
daily_users AS (
SELECT
day,
COUNT(DISTINCT BUYER_ADDRESS) AS active_users,
COUNT(DISTINCT CASE WHEN first_purchase_rank = 1 THEN BUYER_ADDRESS END) AS new_users
FROM
user_activity
GROUP BY
day
),
cumulative_users AS (
SELECT
day,
active_users,
new_users,
SUM(active_users) OVER (ORDER BY day) AS cumulative_active_users,
SUM(new_users) OVER (ORDER BY day) AS cumulative_new_users
FROM
daily_users
)
SELECT
day,
active_users,
new_users,
cumulative_active_users,
cumulative_new_users
QueryRunArchived: QueryRun has been archived