Flipside Community6 - solana most popular dex power users copy copy
Updated 2024-11-04
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
›
⌄
-- forked from 6 - solana most popular nft power users copy @ https://flipsidecrypto.xyz/edit/queries/160b210b-1225-4f9c-9595-97556d4c2cab
-- forked from 6 - solana most popular dex power users @ https://flipsidecrypto.xyz/edit/queries/18b39f2c-975d-4c21-a9c7-8b79be5a5dcb
with number_of_transactions as (
select
signer as user,
num_txs as cnt
from solana.core.ez_signers
where num_txs >= 100
)
select
case
when swap_program ilike '%jupiter%' then 'jupiter'
when swap_program ilike '%orca%' then 'orca'
when swap_program ilike '%raydium%' then 'raydium'
when swap_program ilike '%saber%' then 'saber'
else swap_program
end as swap_program,
count(distinct swapper) as users
-- ,count(distinct tx_hash) as transactions
from solana.defi.fact_swaps
where swapper in (select user from number_of_transactions)
and block_timestamp::date >='2023-01-01'
and block_timestamp::date < '2024-01-01'
group by 1
order by 2 desc
limit 5
;
-- /*
-- [1] "1 - solana acquired_monthly.csv"
-- [2] "2 - solana acquired_yearly -not-yearlylol.csv"
-- [3] "3 - solana return stats.csv"
-- [4] "4 - solana acquired by tx count bucket.csv"