select block_timestamp::date as date,

attribute_value as proposal,

tx_id

from osmosis.core.fact_msg_attributes

where msg_type = 'proposal_vote' and attribute_key = 'proposal_id'

and attribute_value = '225'

),

vote as (

select tx_id,

case when try_parse_json(attribute_value):option = 1 then 1 else null end as yes,

case when try_parse_json(attribute_value):option = 2 then 1 else null end as abstain,

case when try_parse_json(attribute_value):option = 3 then 1 else null end as no,

case when try_parse_json(attribute_value):option = 4 then 1 else null end as no_with_veto

from osmosis.core.fact_msg_attributes

where msg_type = 'proposal_vote' and attribute_key = 'option'

group by 1, 2, 3, 4, 5

select count(vote.yes) as "Yes",

count(vote.abstain) as "Abstain",

count(vote.no) as "No",

count(vote.no_with_veto) as "No with Veto"

from id

join vote

on id.tx_id = vote.tx_id