binhachon84. [Hard] Governance Participation - Number of proposal on each proposer
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
›
⌄
-- 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
),
VOTED_PROPOSALS_PER_VOTER AS(
SELECT VOTER, COUNT(DISTINCT PROPOSAL_ID) AS NUMBER_OF_VOTED_PROPOSALS 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'
)
SELECT PROPOSER, COUNT(PROPOSAL_ID) FROM PROPOSAL_SUBMIT
GROUP BY PROPOSER
-- PROPOSAL_VOTE AS (
-- SELECT BLOCK_TIMESTAMP,
-- )
Run a query to Download Data