nimasadjadiUntitled Query
Updated 2022-07-30
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
›
⌄
with DEFI as (select b.label, count(distinct a.signers[0]) as number_users
from solana.fact_transactions a
inner join solana.dim_labels b
on a.instructions[0]:programId = b.address
where b.label_subtype != 'token_contract'
and b.label != 'solana'
and a.succeeded = 'TRUE'
and a.block_timestamp::date >= current_date - 90
group by 1
order by number_users desc
limit 10) -- Top 10 DEFi in Solana
select a.block_timestamp::date as dt, b.label, count(distinct a.signers[0]) as number_users,
sum(number_users) over (partition by b.label order by dt) as cum_tx,
avg(number_users) OVER (partition by b.label order by dt rows between 7 preceding and current row) as users_7
from solana.fact_transactions a
inner join solana.dim_labels b
on a.instructions[0]:programId = b.address
where b.label_subtype != 'token_contract'
and b.label != 'solana'
and a.succeeded = 'TRUE'
and a.block_timestamp::date >= current_date - 90
and b.label in (select label from DEFI)
group by 1, 2
order by number_users desc
Run a query to Download Data