jackguyMarketingDAO Grants Proposal List 3
Updated 2023-02-26
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
›
⌄
-- CTE to get the transaction hash and block timestamp for the Proposals
WITH ProposalTimestamps AS (
SELECT
tx_hash,
block_timestamp
FROM
near.core.fact_transactions
WHERE
TX_RECEIVER LIKE 'marketing.sputnik-dao.near'
),
-- CTE to get the date, details and timestamp of each Proposal
ProposalDetails AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS Date,
PARSE_JSON(args) AS Info,
block_timestamp AS ProposalTimestamp
FROM
near.core.fact_actions_events_function_call
WHERE
tx_hash IN (SELECT tx_hash FROM ProposalTimestamps)
AND method_name LIKE 'act_proposal'
),
-- CTE to get the number of Approve and Reject votes for each Proposal
VotesPerProposal AS (
SELECT
Info:id AS ID,
min(date) as first_day,
COUNT_IF(Info:action LIKE 'VoteApprove') AS Approve,
COUNT_IF(Info:action LIKE 'VoteReject') AS Reject
FROM
ProposalDetails
GROUP BY
1
ORDER BY
Run a query to Download Data