from cosmos.core.fact_msg_attributes

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

dd as (

select block_timestamp as date, tx_id,

parse_json(ATTRIBUTE_VALUE):option as choice, parse_json(ATTRIBUTE_VALUE):weight as weight,

ATTRIBUTE_VALUE, 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,

CASE

WHEN choice = 1 then 'Yes'

WHEN choice = 2 then 'No'

WHEN choice = 3 then 'Abstain'

when choice = 4 then 'No With Veto' end as choice, weight

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'

),