SalehJup_Voting Trends_hour
Updated 2024-05-19
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
›
⌄
--From Kellen
with lst_all as (
select decoded_instruction:args:side::string as side
, decoded_instruction:args:weight::int * power(10, -6) as weight
, signers[0]::string as voter
, value:pubkey::string AS proposal
, value:name::string AS name
, block_timestamp
from solana.core.fact_decoded_instructions i
, LATERAL FLATTEN(input => decoded_instruction:accounts)
where block_timestamp >= '2024-03-06'
and i.program_id in (
'GovaE4iu227srtG2s3tZzB4RmWBzw8sTwrCLZz7kN7rY'
)
and name = 'proposal'
and proposal = '6txWyf3guJrhnNJXcAHxnV2oVxBcvebuSbfYsgB3yUKc'
and side is not null
and weight is not null
qualify(
row_number() over (partition by voter order by block_timestamp desc) = 1
)
)
select date_trunc('hour',block_timestamp) as date,
case when side = '1' then 'Sharky'
when side = '2' then 'Uprock'
when side = '3' then 'Banx'
when side = '4' then 'Zeus Network'
when side = '5' then 'Monkey Dex'
when side = '6' then 'Srcful'
else 'Other' end as Protocol
, round(sum(weight)) as Votes
, count(1) as Voters
from lst_all
where weight > 0
group by 1, 2