from cosmos.core.fact_msg_attributes

where msg_type = 'proposal_vote'

and attribute_key = 'proposal_id'

and attribute_value = '82'),

vote_option as (select

tx_id,

parse_json(attribute_value):option as vote

from cosmos.core.fact_msg_attributes

where tx_id in (select tx_id from vote_tx)

and attribute_key = 'option'

and array_size(split(attribute_value, '\n')) = 1),

voter as (select

tx_id,

attribute_value as voter

from cosmos.core.fact_msg_attributes

where tx_id in (select tx_id from vote_tx)

and attribute_key = 'sender'),

dupli as (select * from vote_tx

join vote_option using (tx_id)

join voter using (tx_id)),

grouped as (select tx_id, block_timestamp, vote, voter

from dupli

group by tx_id, block_timestamp, vote, voter),

final as (select *,

case

when vote = '1' then 'yes'

when vote = '2' then 'abstain'

when vote = '3' then 'no'

when vote = '4' then 'no with veto'

end as vote_parsed