SELECT

voter,

label as validator_name,

COALESCE(COUNT(DISTINCT v.proposal_id), 0) as num_votes

FROM osmosis.core.fact_governance_votes v

LEFT OUTER JOIN osmosis.core.fact_governance_submit_proposal p

ON v.proposal_id = p.proposal_id

INNER JOIN osmosis.core.dim_labels l

ON v.voter = l.raw_metadata[0] :account_address :: STRING

WHERE label_subtype = 'validator'

AND vote_option = 1

AND (proposal_type = 'StoreCode'

OR proposal_type = 'InstantiateContract'

OR proposal_type = 'SoftwareUpgrade'

OR proposal_type = 'CommunityPoolSpend'

OR proposal_title ilike '%integration%'

OR proposal_title ilike '%composability%'

GROUP BY voter, label

),

total_votes AS (

SELECT

COALESCE(count(DISTINCT v.proposal_id), 0) as num_props

FROM osmosis.core.fact_governance_votes v

LEFT OUTER JOIN osmosis.core.fact_governance_submit_proposal p

ON v.proposal_id = p.proposal_id

WHERE proposal_type = 'StoreCode'

OR proposal_type = 'InstantiateContract'

OR proposal_type = 'SoftwareUpgrade'

OR proposal_type = 'CommunityPoolSpend'

OR proposal_title ilike '%integration%'

OR proposal_title ilike '%composability%'

),

votes_val AS (

SELECT