with json as (

SELECT livequery.live.udf_api ('https://api.flipsidecrypto.com/api/v2/queries/92d28ac2-933b-44e6-98c1-c61bac6eebc3/data/latest') as resp

,

account as (

SELECT value:ADDRESS as address,

value:ACCOUNT_ADDRESS as account_address

FROM json,

LATERAL FLATTEN(input => resp:data)

,

vote as (

SELECT

voter,

CASE when vote_option = 1 then 'YES'

when vote_option = 2 then 'ABSTAIN'

when vote_option = 3 then 'NO'

when vote_option = 4 then 'NO WITH VETO' end as type

FROM cosmos.gov.fact_governance_votes

WHERE proposal_id = 848

AND voter not in (SELECT account_address FROM account)

AND tx_succeeded = 'true'

qualify row_number() over (partition by voter order by block_timestamp DESC) = 1

SELECT

type,

n_vote,

round(100 * n_vote / total.vote,2) as "% n_votes"

FROM (

SELECT

type,

count(voter) as n_vote

FROM vote a

GROUP BY 1