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)

, main as (

SELECT

rank,

label as validator_name,

round(delegator_shares/1e6,2) as voting_power,

round(100 * voting_power / total_staked.total_value_staked,2) as "Rate of Share %",

round(sum("Rate of Share %") over (ORDER BY rank),2) as "Cumulative Share %",

a.address as validator_address,

b.account_address

FROM cosmos.gov.fact_validators a

JOIN account b using(address)

CROSS JOIN (

SELECT

sum(delegator_shares/1e6) as total_value_staked

FROM

cosmos.gov.fact_validators a

JOIN account b using(address)

) AS total_staked

ORDER BY rank

),

vote as (

SELECT

voter,

CASE when vote_option = 1 then 'YES'