select

t1.block_timestamp,

t1.tx_id,

tx_from as Voter,

left (voter,35) as Valid_Voter,

attribute_value as Proposal_ID

from cosmos.core.fact_msg_attributes t1 join cosmos.core.fact_transactions t2 on t1.tx_id = t2.tx_id

where ATTRIBUTE_KEY= 'proposal_id'

and proposal_id = '{{proposal_id}}'

and t1.tx_succeeded = 'TRUE'

)

,validatort1 as (

select

attribute_value as Validator_Operator,

REPLACE (ATTRIBUTE_VALUE,'valoper','') as Validator_Address,

left (Validator_Address,35) as Valid_Voter

from cosmos.core.fact_msg_attributes

where ATTRIBUTE_KEY = 'validator'

),

validatort2 as (

select

tx_id,

t1.Valid_Voter as Voter_Validator

from proposaltx t1 join validatort1 t2 on t1.Valid_Voter = t2.Valid_Voter),

voteoption as (

select

block_timestamp,

tx_id,

try_parse_json (attribute_value) as data,

data:option as vote_option,

case

when vote_option = '1' then 'Yes'

when vote_option = '2' then 'Abstain'