DAOSquareProposals by week
Updated 2024-01-08
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
›
⌄
WITH
cycles AS (
SELECT
ROW_NUMBER() OVER (ORDER BY MIN(proposal_start_time) ASC) AS "No",
CONCAT(EXTRACT(year FROM proposal_start_time), '-', EXTRACT(WEEK FROM proposal_start_time)) AS week,
COUNT(DISTINCT proposal_id) AS proposal,
COUNT(voter, proposal_id) AS vote_cnt,
SUM(voting_power) AS total_score,
ARRAY_AGG(DISTINCT proposal_title) AS proposals,
ARRAY_AGG(DISTINCT proposal_id) AS proposal_ids
FROM external.snapshot.ez_snapshot
WHERE space_id = '{{DAO}}'
GROUP BY week
ORDER BY MIN(proposal_start_time) DESC
)
, basic_stats AS (
SELECT
ANY_VALUE(proposal_start_time) AS proposal_start_time,
ANY_VALUE(proposal_title) AS proposal_title,
SUM(case when vote_option[0] = '1' then 1 else 0 end) AS "For", -- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1
SUM(case when vote_option[0] = '1' then voting_power else 0 end) AS "ForScore", -- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1
SUM(case when vote_option[0] = '2' then 1 else 0 end) AS "Against", -- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1
SUM(case when vote_option[0] = '2' then voting_power else 0 end) AS "AgainstScore", -- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1
SUM(case when vote_option[0] = '3' then 1 else 0 end) AS "Abstain", -- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1
SUM(case when vote_option[0] = '3' then voting_power else 0 end) AS "AbstainScore", -- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1
COUNT(*) AS "Total",
SUM(voting_power) AS "TotalScore",
ANY_VALUE(quorum) AS quorum,
proposal_id
FROM external.snapshot.ez_snapshot
WHERE space_id LIKE '{{DAO}}'
-- AND ARRAY_SIZE(choices) = 3 AND ARRAY_SIZE(vote_option) = 1 -- Basic voting
GROUP BY proposal_id
)
, approve_stats AS (
SELECT