from cosmos.core.fact_msg_attributes

where MSG_TYPE = 'proposal_vote' and ATTRIBUTE_KEY in ('proposal_id') and TX_SUCCEEDED = 'TRUE' and TX_ID != '39EB7E51D62B710782FEDE96AF4DA83293D83AA64A88A6E8686D4AC523C595C0'),

dd as (

select block_timestamp as date, tx_id, substring(UNIQUE_KEY, 1, (len(UNIQUE_KEY) -2)) as u_key

from cosmos.core.fact_msg_attributes --, table(flatten (input => ATTRIBUTE_VALUE:option))

where MSG_TYPE = 'proposal_vote' and attribute_key = 'option' and TX_SUCCEEDED = 'TRUE'

),

tabl as (

select

date, dd.tx_id

from dd join df on dd.tx_id = df.tx_id and dd.u_key = df.u_key),

balances as (

select replace(ADDRESS,'osmo') as wal,

substring(wal, 1, (len(wal) -6)) as wallet,

balance/pow(10,decimal) as balance

from osmosis.core.fact_daily_balances

where currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' and date = '2022-11-14' and BALANCE_TYPE != 'liquid'

),

valid as (

select replace(ACCOUNT_ADDRESS,'osmo') as wal,

substring(wal, 1, (len(wal) -6)) as wallet,

LABEL, DELEGATOR_SHARES, JAILED, RANK

from osmosis.core.fact_validators where RANK > 125 and jailed != 'FALSE'

),

voters as (select date, tabl.tx_id, replace(TX_FROM,'cosmos') as Vot,

substring(Vot, 1, (len(Vot) -6)) as voter,

fee/pow(10, 6) as Fee

from tabl join cosmos.core.fact_transactions on tabl.tx_id = cosmos.core.fact_transactions.tx_id),

bal_wallet as (SELECT * FROM balances WHERE wallet IN (SELECT VOTER FROM VOTERS)),