SELECT

raw_metadata[0] :rank AS rank,

raw_metadata[0] :account_address :: STRING AS validator_address,

label as validator_name

FROM osmosis.core.dim_labels

WHERE label_type = 'operator'

AND label_subtype = 'validator'

),

validator_balances AS (

SELECT

date,

address,

balance / POW(10, decimal) AS balance

FROM OSMOSIS.CORE.FACT_DAILY_BALANCES d

INNER JOIN validators v

ON d.address = v.validator_address

WHERE currency = 'uosmo'

AND balance_type = 'staked'

),

validator_votes AS (

SELECT

block_timestamp :: date AS vote_date,

tx_id,

voter,

validator_name,

proposal_id,

vote_option,

vote_weight,

balance AS voting_power

FROM OSMOSIS.CORE.FACT_GOVERNANCE_VOTES v

INNER JOIN validators vd

ON v.voter = vd.validator_address