SELECT

proposal_id,

tx_id,

block_timestamp,

voter,

CASE vote_option

when '1' then 'Yes'

when '3' then 'No'

when '4' then 'Veto'

when '2' then 'Abstain'

end as type

FROM axelar.gov.fact_governance_votes

WHERE tx_succeeded = 'true'

SELECT

voter,

count(DISTINCT tx_id) as n_votes,

count(DISTINCT proposal_id) as n_proposal,

count(DISTINCT CASE when type = 'Yes' then tx_id end )as "Yes 👍",

count(DISTINCT CASE when type = 'No' then tx_id end ) as "NO 👎",

count(DISTINCT CASE when type = 'Veto' then tx_id end )as "Veto 🚫",

count(DISTINCT CASE when type = 'Abstain' then tx_id end) as "Abstain 🤷‍♂️"

FROM main

GROUP BY voter

ORDER BY n_votes DESC