binhachon84. [Hard] Governance Participation - Distribution
Updated 2021-10-10
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
›
⌄
WITH GOVERNANCE_STAKING AS(
SELECT EVENT_ATTRIBUTES:"from"::STRING AS ADDRESS, SUM(EVENT_ATTRIBUTES:"0_amount"/1e6) AS AMOUNT from terra.msg_events
WHERE EVENT_TYPE = 'from_contract'
AND TX_STATUS = 'SUCCEEDED'
AND EVENT_ATTRIBUTES:"1_action"::STRING = 'staking'
AND EVENT_ATTRIBUTES:"1_contract_address"::STRING = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x'
GROUP BY ADDRESS
),
GOVERNANCE_UNSTAKING AS (SELECT MSG_VALUE:"sender"::string AS ADDRESS, SUM(MSG_VALUE:"execute_msg":"withdraw_voting_tokens":"amount"::float/1e6) AS AMOUNT FROM terra.msgs
WHERE MSG_VALUE:"contract"::STRING = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x'
AND MSG_VALUE:"execute_msg":"withdraw_voting_tokens":"amount" IS NOT NULL
AND TX_STATUS = 'SUCCEEDED'
GROUP BY ADDRESS),
GOVERNANCE_VOTING AS (
SELECT MSG_VALUE:"sender"::string as ADDRESS, COUNT(DISTINCT MSG_VALUE:"execute_msg":"cast_vote":"poll_id"::float) AS VOTE_AMOUNT FROM terra.msgs
WHERE MSG_VALUE:"contract"::STRING = 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x'
AND MSG_VALUE:"execute_msg":"cast_vote":"amount" IS NOT NULL
GROUP BY ADDRESS
),
FINAL_TABLE AS(
SELECT GOVERNANCE_STAKING.ADDRESS AS ADDRESS, GOVERNANCE_STAKING.AMOUNT AS STAKING_AMOUNT, GOVERNANCE_UNSTAKING.AMOUNT AS UNSTAKING_AMOUNT,
GOVERNANCE_STAKING.AMOUNT - GOVERNANCE_UNSTAKING.AMOUNT AS FINAL_AMOUNT, VOTE_AMOUNT
FROM GOVERNANCE_STAKING
LEFT JOIN GOVERNANCE_UNSTAKING
ON GOVERNANCE_STAKING.ADDRESS = GOVERNANCE_UNSTAKING.ADDRESS
LEFT JOIN GOVERNANCE_VOTING
ON GOVERNANCE_STAKING.ADDRESS = GOVERNANCE_VOTING.ADDRESS
)
SELECT VOTE_AMOUNT, COUNT(VOTE_AMOUNT) FROM GOVERNANCE_VOTING
GROUP BY VOTE_AMOUNT
Run a query to Download Data