adriaparcerisasSolana 5 (1a part a dash)
Updated 2023-01-26
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
›
⌄
with t1 as (
SELECT
distinct signers[0] as users,
min(block_timestamp) as debut
from solana.core.fact_transactions
group by 1
),
t2 as (
select distinct users from t1 where debut>=CURRENT_DATE-INTERVAL '{{period}}'
),
t3 as (
SELECT
trunc(block_timestamp,'{{granularity}}') as date,
count(distinct tx_id) as txs,
sum(txs) over (order by date) as cum_txs,
count(distinct signers[0]) as active_users,
sum(active_users) over (order by date) as cum_active_users
from solana.core.fact_transactions x
--join solana.core.dim_labels y on x.signers[0]=y.address
where block_timestamp>=CURRENT_DATE- INTERVAL '{{period}}' and signers[0] in (select * from t2)
group by 1
),
t4 as (
SELECT
trunc(block_timestamp,'{{granularity}}') as date,
count(distinct tx_id) as txs,
sum(txs) over (order by date) as cum_txs,
count(distinct signers[0]) as active_users,
sum(active_users) over (order by date) as cum_active_users
from solana.core.fact_transactions
where block_timestamp>=CURRENT_DATE- INTERVAL '{{period}}' and signers[0] not in (select * from t2)
group by 1 order by 1 asc
)
select 'New users' as type,* from t3
union select 'Active users' as type,* from t4 order by 1 asc
Run a query to Download Data