ValiMohammaditoltal users overtime(daily)
Updated 2023-02-11
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 aurory_users as (
SELECT
CASE WHEN program_id='STkwf3sbMapjy7KV3hgrJtcVvY4SvRxWQ8pj4Enw1i5' then signers[0] END as users,
'aurory' as game,
min(date_trunc('day', block_timestamp)) as days
FROM solana.core.fact_events
GROUP BY 1,2
), staratlas_users as (
SELECT
CASE WHEN program_id='ATLocKpzDbTokxgvnLew3d7drZkEzLzDpzwgrgWKDbmc' then signers[0] END AS users,
'staratlas' as game,
min(date_trunc('day', block_timestamp)) as days
FROM solana.core.fact_events
GROUP BY 1,2
), genopets_users as (
SELECT
CASE WHEN program_id='StaKe9nb7aUjXpjpZ45o6uJBsZxj2BWCDBtjk8LCg2v' then signers[0] END AS users,
'genopets' as game,
min(date_trunc('day', block_timestamp)) as days
FROM solana.core.fact_events
GROUP BY 1,2
)
SELECT
*,
sum(new_users) over (partition by game ORDER BY days) as total_users
FROM (
SELECT
days,
game,
COUNT(*) as new_users
FROM (
SELECT * FROM aurory_users
Run a query to Download Data