crypto_gostessential-apricot
Updated 2024-10-24
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_first_activity AS (
SELECT
signers[0] AS user_address,
MIN(block_timestamp) AS first_activity_date
FROM solana.core.fact_events
WHERE program_id = 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR'
GROUP BY signers[0]
),
user_activity AS (
SELECT
signers[0] AS user_address,
date_trunc('d', block_timestamp) AS date
FROM solana.core.fact_events
WHERE program_id = 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR'
AND block_timestamp::DATE >= DATEADD(DAY, -60, CURRENT_DATE())
),
daily_users AS (
SELECT
ua.date,
COUNT(DISTINCT CASE
WHEN ua.date = u.first_activity_date THEN ua.user_address
END) AS new_users,
COUNT(DISTINCT CASE
WHEN ua.date != u.first_activity_date THEN ua.user_address
END) AS recurring_users,
COUNT(DISTINCT ua.user_address) AS total_daily_users
FROM user_activity ua
LEFT JOIN user_first_activity u
ON ua.user_address = u.user_address
GROUP BY ua.date
)
SELECT
date,
QueryRunArchived: QueryRun has been archived