luisraSolana defi users
Updated 2022-06-30Copy Reference Fork
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
›
⌄
WITH
users as (
SELECT
signers[0] as users,
trunc(x.block_timestamp,'month') as months
from solana.core.fact_events x
join solana.core.fact_transactions y on x.tx_id=y.tx_id
where x.block_timestamp>='2022-01-01' and program_id in ('DjVE6JNiYqPL2QXyCUUh8rNjHrbz9hXHNYt99MQ59qw1','9HzJyW1qZsEiSfMUf6L2jo3CcTKAyBmSyKdwQeYisHrC',
'9HzJyW1qZsEiSfMUf6L2jo3CcTKAyBmSyKdwQeYisHrC','9W959DqEETiGZocYWCQPaJ6sBmUzgfxXfqGeTEdp3aQP','9KEPoZmtHUrBbhWN1v1KWLMkkvwY6WLtAVUCPRtRjP4z',
'Crt7UoUR6QgrFrN7j8rmSQpUTNWNSitSwWvsWGf1qZ5t','675kPX9MHTjS2zt1qfr1NYHuzeLXfQM9H24wFSUt1Mp8',
'SSwpkEEcbUqx4vtoEByFjSkhKdCT862DNVb52nZg1UZ','EhhTKczWMGQt46ynNeRX1WfeagwwJd7ufHvCDjRxjo5Q')
),
users_1 as (
SELECT
distinct users,
count(distinct months) as counts
from users
group by 1
having counts>5
)
SELECT
trunc(block_timestamp,'day') as date,
count(distinct swapper) as n_users_swapping,
count(distinct tx_id) as n_swaps
--count(distinct tx_from) as n_users_trasnferring,
--count(distinct y.tx_id) as n_trasnfers
from solana.core.fact_swaps --x,solana.core.fact_transfers y where trunc(x.block_timestamp,'day')=trunc(y.block_timestamp,'day')
where swapper in (select users from users_1) and block_timestamp>='2022-01-01'
group by 1
order by 1 asc
Run a query to Download Data