WITH votes AS ( SELECT VALIDATOR, COUNT(*) AS num_votes FROM osmosis.core.fact_governance_validator_votes v WHERE (v.VALIDATOR ='{{validator_1}}' or v.VALIDATOR ='{{validator_2}}' or v.VALIDATOR ='{{validator_3}}') AND v.CREATED_AT >= '{{start_date}}' AND v.CREATED_AT <= '{{end_date}}' GROUP BY VALIDATOR ), changed_votes AS ( SELECT VALIDATOR, COUNT(*) AS num_changed_votes FROM osmosis.core.fact_governance_validator_votes v WHERE updated_at <> created_at and (v.VALIDATOR ='{{validator_1}}' or v.VALIDATOR ='{{validator_2}}' or v.VALIDATOR ='{{validator_3}}') AND v.CREATED_AT >= '{{start_date}}' AND v.CREATED_AT <= '{{end_date}}' GROUP BY VALIDATOR ), changed_vote_ratio AS ( SELECT v.VALIDATOR, COALESCE(cv.num_changed_votes, 0) / v.num_votes AS changed_vote_ratio FROM votes v LEFT JOIN changed_votes cv ON v.VALIDATOR = cv.VALIDATOR ), all_validators AS ( SELECT DISTINCT VALIDATOR FROM osmosis.core.fact_governance_validator_votes v WHERE (v.VALIDATOR ='{{validator_1}}' or v.VALIDATOR ='{{validator_2}}' or v.VALIDATOR ='{{validator_3}}') AND v.CREATED_AT >= '{{start_date}}' AND v.CREATED_AT <= '{{end_date}}' ), votes_together AS ( SELECT v1.VALIDATOR AS validator1, v2.VALIDATOR AS validator2, COUNT(*) AS num_votes_together FROM osmosis.core.fact_governance_validator_votes v1 JOIN osmosis.core.fact_governance_validator_votes v2 ON v1.PROPOSAL_ID = v2.PROPOSAL_ID AND v1.VALIDATOR < v2.VALIDATOR and (v1.VALIDATOR ='{{validator_1}}' or v1.VALIDATOR ='{{validator_2}}' or v1.VALIDATOR ='{{validator_3}}') AND v1.CREATED_AT >= '{{start_date}}' AND v1.CREATED_AT <= '{{end_date}}' and (v2.VALIDATOR ='{{validator_1}}' or v2.VALIDATOR ='{{validator_2}}' or v2.VALIDATOR ='{{validator_3}}') AND v2.CREATED_AT >= '{{start_date}}' AND v2.CREATED_AT <= '{{end_date}}' GROUP BY v1.VALIDATOR, v2.VALIDATOR ), changed_votes_together AS ( SELECT v1.VALIDATOR AS validator1, v2.VALIDATOR AS validator2, COUNT(*) AS num_changed_votes_together FROM osmosis.core.fact_governance_validator_votes v1 JOIN osmosis.core.fact_governance_validator_votes v2 ON v1.PROPOSAL_ID = v2.PROPOSAL_ID AND v1.VALIDATOR < v2.VALIDATOR AND v1.updated_at <> v1.created_at AND v1.updated_at = v2.updated_at WHERE (v1.VALIDATOR ='{{validator_1}}' or v1.VALIDATOR ='{{validator_2}}' or v1.VALIDATOR ='{{validator_3}}') AND v1.CREATED_AT >= '{{start_date}}' AND v1.CREATED_AT <= '{{end_date}}' and (v2.VALIDATOR ='{{validator_1}}' or v2.VALIDATOR ='{{validator_2}}' or v2.VALIDATOR ='{{validator_3}}') AND v2.CREATED_AT >= '{{start_date}}' AND v2.CREATED_AT <= '{{end_date}}' GROUP BY v1.VALIDATOR, v2.VALIDATOR ) SELECT concat(vt.validator1,'<->',vt.validator2) as validator_pair, vt.num_votes_together, COALESCE(ct.num_changed_votes_together, 0) AS num_changed_votes_together FROM all_validators v1 JOIN all_validators v2 ON v1.VALIDATOR < v2.VALIDATOR LEFT JOIN votes_together vt ON v1.VALIDATOR = vt.validator1 AND v2.VALIDATOR = vt.validator2 LEFT JOIN changed_votes_together ct ON v1.VALIDATOR = ct.validator1 AND v2.VALIDATOR = ct.validator2 where vt.validator1 is not null ORDER BY num_changed_votes_together DESC

Osmosis Validator Comparison Dashboard