kaibladeProposal Voting Eligibility Status Distribution
Updated 2023-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
›
⌄
-- SELECT *
-- -- TX_ID, (FROM_AMOUNT)/1E6 AS AMOUNT_IN_LUNA
-- FROM terra.core.ez_swaps
-- WHERE FROM_CURRENCY = 'uluna' AND TO_CURRENCY!= 'uluna' AND TX_SUCCEEDED = TRUE AND BLOCK_TIMESTAMP::DATE = '2023-01-02'
-- ORDER BY FROM_AMOUNT DESC LIMIT 1000
WITH
all_grants_raw AS (
SELECT
block_timestamp AS "Proposal Creation Time",
DATEADD('day', 7, "Proposal Creation Time") AS "Deposit End Time",
tx_id,
tx:tx_result.log[0].events[3].attributes[1].value::number AS "Proposal ID",
tx_sender AS "Proposer",
tx:body.messages[0].content.title AS "Proposal Title",
(
tx:body.messages[0].content.amount[0].amount::number
) / 1e6 AS "Grant Amount",
tx:body.messages[0].content.amount[0].denom AS "Grant Currency",
tx:body.messages[0].content.description AS "Grant Description",
tx:body.messages[0].content.recipient AS "Grant Target Wallet"
FROM
terra.core.fact_transactions
WHERE
tx:"body"."messages" [0]."content"."@type" = '/cosmos.distribution.v1beta1.CommunityPoolSpendProposal'
AND tx_succeeded = TRUE
-- WHERE tx:body.messages[0].content.description ILIKE '%grant%'
ORDER BY
block_timestamp
),
all_grants_link AS
(
SELECT d.*, REPLACE(SPLIT(c.value::string, 'https')[1], '://', 'https://') AS "Proposal Link"
FROM all_grants_raw d,
LATERAL FLATTEN (input=>split(d."Grant Description", ' ')) c
WHERE value ILIKE '%https%'
Run a query to Download Data