elvisSOL Q52. What do Sol stakers do in their accounts?
Updated 2022-05-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
›
⌄
⌄
/*
Q52. How frequently are SOL stakers interacting with DeFi protocols over the past month?
Which protocols are they interacting most with? Which actions are they mostly taking? Staking? Farming? Swapping? Mining?
*/
WITH Stakers AS (
SELECT SIGNERS[0] AS stakers
FROM solana.fact_staking_lp_actions
WHERE SUCCEEDED = TRUE AND
BLOCK_TIMESTAMP > '2022-01-01' AND
(EVENT_TYPE = 'delegate' OR EVENT_TYPE = 'deactivate')
GROUP BY 1
), App_Interactions AS (
SELECT Program_ID, COUNT(*) AS Staker_Interactions
FROM solana.transactions AS T LEFT JOIN Stakers AS S ON T.TX_FROM_ADDRESS = S.Stakers
WHERE date_trunc('day',BLOCK_TIMESTAMP) > CURRENT_DATE-30 AND
SUCCEEDED = TRUE
GROUP BY 1
ORDER BY 2 DESC
)
SELECT Program_ID, staker_interactions,
CASE
WHEN PROGRAM_ID = 'FsJ3A3u2vn5cTVofAjvy6y5kwABJAqYWpe4975bi2epH' THEN 'manual'
WHEN PROGRAM_ID = 'DtmE9D2CSB4L5D6A15mraeEjrGMm6auWVzgaD8hK2tZM' THEN 'manual'
WHEN PROGRAM_ID = 'SW1TCH7qEPTdLsDHRgPuMQjbQxKdH2aBStViMFnt64f' THEN 'manual'
WHEN PROGRAM_ID = 'GDDMwNyyx8uB6zrqwBFHjLLG3TBYk2F8Az4yrQC5RzMp' THEN 'manual'
WHEN PROGRAM_ID = 'EWWy8ipWs2Lf4dA6CunwB2uYpevPTQUHHyiHewbwCfMp' THEN 'manual'
WHEN PROGRAM_ID = 'Zo1ggzTUKMY5bYnDvT5mtVeZxzf2FaLTbKkmvGUhUQk' THEN 'manual'
ELSE creator
END AS Creator,
CASE
WHEN PROGRAM_ID = 'FsJ3A3u2vn5cTVofAjvy6y5kwABJAqYWpe4975bi2epH' THEN 'oracle'
WHEN PROGRAM_ID = 'DtmE9D2CSB4L5D6A15mraeEjrGMm6auWVzgaD8hK2tZM' THEN 'oracle'
WHEN PROGRAM_ID = 'SW1TCH7qEPTdLsDHRgPuMQjbQxKdH2aBStViMFnt64f' THEN 'dex'
WHEN PROGRAM_ID = 'GDDMwNyyx8uB6zrqwBFHjLLG3TBYk2F8Az4yrQC5RzMp' THEN 'dex'
Run a query to Download Data