AS (

SELECT TX_ID

,MAX(CASE

WHEN ATTRIBUTE_KEY = 'proposal_id'

THEN ATTRIBUTE_VALUE

END) AS proposal_id

,MAX(CASE

WHEN ATTRIBUTE_KEY = 'option'

THEN ATTRIBUTE_VALUE

END) AS vote_option

FROM lava.core.fact_msg_attributes

WHERE MSG_TYPE = 'proposal_vote'

AND ATTRIBUTE_KEY IN (

'option'

,'proposal_id'

)

GROUP BY TX_ID

)

,parsed_votes

AS (

SELECT proposal_id

,'Proposal ID ' || proposal_id AS proposal_id_

,CASE

WHEN vote_option LIKE '%VOTE_OPTION_YES%'

THEN 'YES'

WHEN vote_option LIKE '%VOTE_OPTION_NO%'

THEN 'NO'

WHEN vote_option LIKE '%VOTE_OPTION_ABSTAIN%'

THEN 'ABSTAIN'

WHEN vote_option LIKE '%VOTE_OPTION_NO_WITH_VETO%'

THEN 'NO_WITH_VETO'

ELSE 'UNKNOWN'

END AS vote_decision

FROM proposal_votes

)