tx_id,ATTRIBUTE_VALUE as proposal_ids

from cosmos.core.fact_msg_attributes

where MSG_TYPE ilike '%proposal_vote%'

and ATTRIBUTE_KEY='proposal_id'

),

tb2 as (select

t1.BLOCK_TIMESTAMP,

t1.tx_id,

proposal_ids,

t1.ATTRIBUTE_VALUE as voter

from cosmos.core.fact_msg_attributes t1 left join tb1 t2 on t1.tx_id=t2.tx_id

where t1.tx_id in (select tx_id from tb1)

and t1.MSG_TYPE='message'

and t1.ATTRIBUTE_KEY='sender'),

raw_data as (

select

s.BLOCK_TIMESTAMP,

s.tx_id,

proposal_ids,

a.voter,

TRY_PARSE_JSON(ATTRIBUTE_VALUE):option as vote_option,

case

when vote_option='1' then 'YES'

when vote_option='2' then 'ABSTAIN'

when vote_option='3' then 'NO'

when vote_option='4' then 'NO WITH VETO'

end as description,

TRY_PARSE_JSON(ATTRIBUTE_VALUE):weight as vote_weight

from cosmos.core.fact_msg_attributes s full outer join tb2 a on s.tx_id=a.tx_id

where s.tx_id in (select tx_id from tb1)

and MSG_TYPE='proposal_vote'

and ATTRIBUTE_KEY='option'