DAOSquareProposals by week
    Updated 2024-01-08
    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