SELECT

voter,

address,

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 = 'CommunityPoolSpend'

OR proposal_title ilike '%funding%'

OR proposal_title ilike '%token swap%'

OR proposal_title ilike '%grant%'

OR proposal_title ilike '%loan%')

GROUP BY voter, address, 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 = 'CommunityPoolSpend'

OR proposal_title ilike '%funding%'

OR proposal_title ilike '%token swap%'

OR proposal_title ilike '%grant%'

OR proposal_title ilike '%loan%'

),

votes_val AS (

SELECT

v.voter,

min(v.proposal_id) as first_prop_voted