KARTODSolana program users by weeks
Updated 2022-07-03
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 programs_called AS (
SELECT
SIGNERS as payer,
INSTRUCTIONS[0]:programId as program,
BLOCK_TIMESTAMP
FROM solana.core.fact_transactions
WHERE SUCCEEDED = true
),
this_week AS (
SELECT
COUNT(DISTINCT(payer)) AS distinct_payers,
program
FROM programs_called
WHERE BLOCK_TIMESTAMP != current_date()
AND BLOCK_TIMESTAMP >= current_date() - INTERVAL'7 day'
GROUP BY program
),
previous_week AS (
SELECT
COUNT(DISTINCT(payer)) AS distinct_payers,
program
FROM programs_called
WHERE BLOCK_TIMESTAMP >= current_date() - INTERVAL'14 day'
AND BLOCK_TIMESTAMP < current_date() - INTERVAL'7 day'
GROUP BY program
)
--
SELECT
previous_week.distinct_payers AS "Distinct users on previous week",
this_week.distinct_payers AS "Distinct users on this week",
this_week.distinct_payers - previous_week.distinct_payers AS "Users increase",
(this_week.distinct_payers - previous_week.distinct_payers) / previous_week.distinct_payers * 100 AS "Users increase in %",
this_week.program AS "Program"
FROM this_week
LEFT JOIN previous_week ON this_week.program = previous_week.program
WHERE previous_week.distinct_payers != 0
Run a query to Download Data