from cosmos.core.fact_msg_attributes

where ATTRIBUTE_KEY= 'proposal_id' and ATTRIBUTE_VALUE= '82'and tx_succeeded = 'TRUE'),

voter as (select block_timestamp, tx_id,

attribute_value as Voter

from cosmos.core.fact_msg_attributes

where ATTRIBUTE_KEY= 'spender' and tx_id in (select tx_id from txn_for_proposal)

and tx_succeeded = 'TRUE'),

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'

when vote_option = '3' then 'No'

when vote_option = '4' then 'No With Veto'

end as vote_type

from cosmos.core.fact_msg_attributes

where ATTRIBUTE_KEY= 'option'

and data:option is not null

and tx_id in (select tx_id from txn_for_proposal)

and tx_succeeded = 'TRUE'),

summary as (

select t2.block_timestamp, t1.tx_id, Voter, vote_option, vote_type

from txn_for_proposal t1 join voter t2 on t1.tx_id = t2.tx_id

join voteoption t3 on t1.tx_id = t3.tx_id )

select block_timestamp::date as date, vote_type,

count (distinct tx_id) as number_of_votes,

count (distinct voter) as number_of_voters,

number_of_votes/number_of_voters as avg_votes_per_voter

from summary

group by 1,2