proposals_info as (

SELECT

attribute_value as proposal_id,

tx_id,block_timestamp

--min(block_timestamp) as first_vote

FROM cosmos.core.fact_msg_attributes

where msg_type='proposal_vote' and attribute_key='proposal_id' --and attribute_value=82

--group by 1

),

votes as (

SELECT

proposal_id,

x.block_timestamp,

x.tx_id,

attribute_value as vote

from cosmos.core.fact_msg_attributes x

join proposals_info y on x.tx_id=y.tx_id

where attribute_key='option'

)

SELECT

case when x.proposal_id =82 then 'Proposal 82' else 'Others' end as proposal,

case when vote='VOTE_OPTION_YES' or vote like '%{"option":1%' then 'Yes'

when vote='VOTE_OPTION_NO' or vote like '%{"option":3%' then 'No'

when vote='VOTE_OPTION_NO_WITH_VETO' or vote like '%{"option":4%' then 'No with veto'

else 'Abstain' end as vote,

count(distinct x.tx_id) as votes,

count(distinct tx_from) as voters

FROM votes x

join cosmos.core.fact_transactions z on x.tx_id=z.tx_id

--where vote='{"option":2,"weight":"1.000000000000000000"}'

--where vote in ('VOTE_OPTION_NO_WITH_VETO','VOTE_OPTION_NO','VOTE_OPTION_YES','VOTE_OPTION_ABSTAIN')

GROUP by 1,2 order by 1 asc