ChiefHades Users (Power Users)
Updated 2023-01-21
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 all_dates AS (
SELECT current_date - 30 as date
FROM (select date_trunc('day', block_timestamp) as date FROM solana.core.fact_transactions)
),
daily_users AS (
SELECT
signers[0] as user,
date_trunc('day', block_timestamp) as date
FROM solana.core.fact_transactions
WHERE instructions[0]:programId = 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu'
AND block_timestamp >= current_date - 30
AND succeeded = true
GROUP BY date, user
),
daily_users_count AS (
SELECT
date,
count(distinct user) as "daily active users"
FROM daily_users
GROUP BY date
ORDER BY date
),
user_activity AS (
SELECT
user,
count(distinct date) as "active days"
FROM daily_users
GROUP BY user
),
one as (
SELECT
count(distinct user) as "active 1+"
FROM user_activity
WHERE "active days" > 1
),
Run a query to Download Data