KaskoazulMonkeDAO - Whale Voters CTE
Updated 2022-04-13
999
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
›
⌄
WITH SMB AS (
SELECT --tx_id,
signers[0] as voter,
case instructions[0]:accounts[6]
when 'BD9XQhU1DXA9V4GLdgTq7xdQY5UBnShfq4caTSEL9Lr' then 'Q1'
when 'EkugdcWDJD1zcmfYgPbC7PcqjMnQkNWMLSCJxmtLgztv' then 'Q2'
when 'GGKCaPonSGvyXas2uxUdBea2Gsa9X5RK91iN6Xu2V7v9' then 'Q3'
else NULL
end as question,
case instructions[0]:data
when 'Yjf5DvKUCfa2bPkYz4AiWs' then 'YES'
when 'Yjf5DvKUCfa2Rh5YBEg7FM' then 'NO'
when 'YrpUV6EMK2i4BLkLvFxTRH' then 'YES'
when 'YrpUV6EMK2i41e5L7STr9m' then 'NO'
when 'YyyskG9ERPr5mHk8rTkCKh' then 'AIRDROP'
when 'YyyskG9ERPr5bb583eFb4B' then 'ENTANGLE'
end as voting_result,
--COUNT(1) AS n,
SUM(ARRAY_SIZE(inner_instructions)) AS votes
FROM solana.fact_transactions
WHERE block_timestamp <= '2022-03-05'
AND block_timestamp >= '2022-02-15'
AND succeeded = 'True'
AND instructions[0]:programId = 'Daovoteq2Y28gJyme6TNUXT9TxXrePiouFuHezkiozci'
AND question is not NULL
--AND instructions[0]:accounts[6] = 'BD9XQhU1DXA9V4GLdgTq7xdQY5UBnShfq4caTSEL9Lr'
GROUP BY 1, 2, 3--, 4
ORDER BY 1
),
SMB_Q1 as (
select case
when votes = 1 then '1 SBM'
when votes = 2 or votes = 3 then '2-3 SBMs'
when votes >= 4 then '4+ SBMs'
else NULL