from terra.validator_voting_power

group by 1,2),

label as (select label, vp_address, delegator_address as voter_add

from terra.validator_labels),

vplabel as (select a.dt, a.address, b.voter_add, b.label, a.vp

from vp a

left outer join label b on a.address = b.vp_address),

prop as (select date(block_timestamp) as datez, proposal_id, option, voter, case

when option = 'VOTE_OPTION_NO' then 'No'

when option = 'VOTE_OPTION_NO_WITH_VETO' then 'NoWithVeto'

when option = 'VOTE_OPTION_ABSTAIN' then 'Abstain'

when option = 'VOTE_OPTION_YES' then 'Yes'

else option end as vote_option,

CASE when voter_label_subtype is null then 'user'

else voter_label_subtype end as type

from terra.gov_vote),

final as (select a.proposal_id, a.vote_option, a.voter, a.type, b.vp, b.label

from prop a

left outer join vplabel b on a.datez = b.dt and a.voter = b.voter_add)

select proposal_id, label, voter, avg(vp) as voting_power --remove duplicate voter

from final

where vote_option = 'Yes' and vp is not null

group by 1,2,3