binhachon84. [Hard] Governance Participation - Number of proposal on each voter and balance
Updated 2021-10-11
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
›
⌄
-- Get the data on the proposal and the proposer as well as the balance of the proposer at the proposing time
WITH PROPOSAL_SUBMIT AS (
SELECT BLOCK_TIMESTAMP, TX_ID, PROPOSER, PROPOSAL_ID, COALESCE(PROPOSAL_TYPE, 'NA') AS PROPOSAL_TYPE, COALESCE(SUM(BALANCE_USD),0) AS BALANCE_USD FROM terra.gov_submit_proposal LEFT JOIN terra.daily_balances
ON DATE_TRUNC('Day', BLOCK_TIMESTAMP) = date AND PROPOSER = ADDRESS
WHERE TX_STATUS = 'SUCCEEDED'
GROUP BY BLOCK_TIMESTAMP, TX_ID, PROPOSER, PROPOSAL_ID, PROPOSAL_TYPE
),
-- Get data on who votes on what proposal as well as what is the balance of that voter at the voting time
PROPOSAL_VOTE AS(
SELECT BLOCK_TIMESTAMP, TX_ID, VOTER, PROPOSAL_ID, VOTING_POWER, COALESCE(SUM(BALANCE_USD),0) AS BALANCE_USD FROM terra.gov_vote LEFT JOIN terra.daily_balances
ON DATE_TRUNC('Day', BLOCK_TIMESTAMP) = date AND VOTER = ADDRESS
WHERE TX_STATUS = 'SUCCEEDED'
GROUP BY BLOCK_TIMESTAMP, TX_ID, VOTER, PROPOSAL_ID, VOTING_POWER
),
-- Table contains the voter addresses as well as the total number of proposals that address has voted on
VOTED_PROPOSALS_PER_VOTER AS(
SELECT VOTER, COUNT(DISTINCT PROPOSAL_ID) AS NUMBER_OF_VOTED_PROPOSALS, AVG(BALANCE_USD) AS BALANCE FROM PROPOSAL_VOTE
GROUP BY VOTER
ORDER BY NUMBER_OF_VOTED_PROPOSALS
),
PROPOSAL_VOTE_WITH_BALANCE AS(
SELECT BLOCK_TIMESTAMP, TX_ID, VOTER, PROPOSAL_ID, BALANCE FROM PROPOSAL_VOTE LEFT JOIN terra.daily_balances
ON VOTER = ADDRESS AND DATE_TRUNC('Day',BLOCK_TIMESTAMP) = date AND CURRENCY = 'LUNA'
)
-- Count number of addresses that have voted 1, 2, 3,... times
SELECT NUMBER_OF_VOTED_PROPOSALS, SUM(NUMBER_OF_VOTED_PROPOSALS), AVG(BALANCE) AS BALANCE FROM VOTED_PROPOSALS_PER_VOTER
GROUP BY NUMBER_OF_VOTED_PROPOSALS
-- PROPOSAL_VOTE AS (
-- SELECT BLOCK_TIMESTAMP,
-- )
Run a query to Download Data