select

raw_metadata[0]:"account_address"::string as validator_address

from osmosis.core.dim_labels

where label_subtype = 'validator'

),

first_time_voters_tb as (

select

'First time voters' as type,

min(block_timestamp) as min_date,

from osmosis.core.fact_governance_votes

where tx_status = 'SUCCEEDED'

group by voter

),

first_time_voter_on_82 as (

select

'First time voter (Proposal #82)' as type,

v.voter

from osmosis.core.fact_governance_votes v JOIN first_time_voters_tb f on v.block_timestamp = f.min_date and v.voter = f.voter

where tx_status = 'SUCCEEDED'

AND proposal_id = '82'

group by 2

),

final_tb as (

select

'Normal voter' as type,

case

when vote_option = 1 then 'YES'

when vote_option = 2 then 'ABSTAIN'

when vote_option = 3 then 'NO WITH VETO'

when vote_option = 4 then 'NO'

END as vote_type,

sum(vote_weight) as votes

from osmosis.core.fact_governance_votes

where proposal_id = '82'