date_trunc('week', BLOCK_TIMESTAMP)::date as date,

f.value:addr as "Staking Pool Address",

coalesce(f.value:voting_power/1e8

, lag(f.value:voting_power/1e8) ignore nulls over (partition by f.value:addr order by date)

, 0) as "Total Stake"

FROM aptos.core.fact_changes, lateral flatten (input => CHANGE_DATA:active_validators) f

WHERE inner_change_type = '0x1::stake::ValidatorSet'

qualify row_number() over (partition by f.value:addr, date order by version desc, change_index desc) = 1

ORDER by 1

,

active_set as (

SELECT DISTINCT

"Staking Pool Address" as validator,

sum("Total Stake") as power

FROM tb1

GROUP by 1

ORDER BY 2 DESC

LIMIT 100

,

tot_staked as (

SELECT SUM(power) as all_apt_staked

FROM active_set

),

lino2 as (

SELECT

validator,

power as staked,

all_apt_staked,

ROUND(staked / all_apt_staked * 100, 2) as power_percentage,

SUM(power_percentage) OVER (ORDER BY power) as cumulative_stake_perc