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_title ilike '%governance%'

OR proposal_title ilike '%voting period%'

OR proposal_title ilike '%deposit%'

OR proposal_title ilike '%standards%')

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_title ilike '%governance%'

OR proposal_title ilike '%voting period%'

OR proposal_title ilike '%deposit%'

OR proposal_title ilike '%standards%'

),

votes_val AS (

SELECT

v.voter,

min(v.proposal_id) as first_prop_voted

FROM osmosis.core.fact_governance_votes v

LEFT OUTER JOIN osmosis.core.fact_governance_submit_proposal p

ON v.proposal_id = p.proposal_id