with cosmos_fact_governance_votes as (

select a.block_timestamp,

a.tx_id,

b.ATTRIBUTE_VALUE::string as voter,

a.TX_SUCCEEDED,

a.attribute_value as PROPOSAL_ID,

TRY_PARSE_JSON(c.attribute_value):weight as VOTE_WEIGHT,

TRY_PARSE_JSON(c.attribute_value):option as VOTE_OPTION,

case

when TRY_PARSE_JSON(c.attribute_value):option = '1' then 'Yes'

when TRY_PARSE_JSON(c.attribute_value):option = '3' then 'No'

when TRY_PARSE_JSON(c.attribute_value):option = '4' then 'No with Veto'

when TRY_PARSE_JSON(c.attribute_value):option = '2' then 'Abstain'

else null end as VOTE_OPTION_STRING

from cosmos.core.fact_msg_attributes a

join cosmos.core.fact_msg_attributes b on a.tx_id = b.tx_id and b.ATTRIBUTE_KEY = 'sender' and b.MSG_TYPE = 'transfer'

join cosmos.core.fact_msg_attributes c on a.tx_id = c.tx_id and c.ATTRIBUTE_KEY = 'option' and c.MSG_TYPE = 'proposal_vote'

where a.msg_type = 'proposal_vote'

and a.attribute_key = 'proposal_id'

and VOTE_OPTION_STRING is not NULL

),

validator as (

select REPLACE(ATTRIBUTE_VALUE, 'valoper', '') as validator , left(validator, 35) as validators

from cosmos.core.fact_msg_attributes

where ATTRIBUTE_KEY = 'validator'

),

new_voters as (

select voter,

count(DISTINCT proposal_id) as proposals_voted_count

from cosmos_fact_governance_votes

where TX_SUCCEEDED = 'TRUE'

and proposal_id <= 82