Nouns stats on basement.dev

nounish_vote_stats.sql

WITH votes as (
    SELECT
        tl.topic1 as voter_address,
        -- data is non dynamic for CastVote, so we can safely slice the uint8 out.
        substring(tl.data, 64, 1) as support,
        tl.block_number as block_number
    FROM 
        transaction_log AS tl 
    WHERE 
        -- CAST VOTE LOG
        tl.topic0 = '\xb8e138887d0aa13bab447e82de9d5c1777041ecd21ca36ba824ff1e6c07ddda4'
        -- Nouns Gov Contract Addy
        AND tl.contract_address = '\x6f3e6272a167e8accb32072d08e0957f9c79223d'
) 
SELECT 
    substring(v.voter_address, 13) as voter_address,
    count(v.voter_address) as number_of_votes,
    max(v.block_number) as last_block_number_vote,
    count (*) FILTER (WHERE v.support = '\x00') as against,
    count (*) FILTER (WHERE v.support = '\x01') as "for",
    count (*) FILTER (WHERE v.support = '\x02') as abstain,
FROM 
    votes AS v 
GROUP BY
    v.voter_address
ORDER BY
    number_of_votes DESC