SELECT

DATE_TRUNC('week', block_timestamp) AS week_date,

version,

address AS pool_address,

change_data :active :value ::int / POW(10,8) AS active_stake,

change_data :pending_active :value ::int / POW(10,8) AS pending_active_stake,

change_data :inactive :value ::int / POW(10,8) AS inactive_stake,

change_data :pending_inactive :value ::int / POW(10,8) AS pending_inactive_stake

FROM aptos.core.fact_changes

WHERE success

AND change_type = 'write_resource'

AND inner_change_type = '0x1::stake::StakePool'

QUALIFY ROW_NUMBER() OVER (PARTITION BY address, week_date ORDER BY version DESC, change_index DESC) = 1

),

delegation_transactions AS (

SELECT

DATE_TRUNC('week', block_timestamp) AS week_date,

version,

address AS pool_address,

change_data :active_shares :total_coins ::int / POW(10, 8) AS total_active_delegated,

change_data :total_coins_inactive ::int / POW(10, 8) AS total_inactive_delegated

FROM aptos.core.fact_changes

WHERE success

AND change_type = 'write_resource'

AND inner_change_type = '0x1::delegation_pool::DelegationPool'

QUALIFY ROW_NUMBER() OVER (PARTITION BY address, week_date ORDER BY version DESC, change_index DESC) = 1

),

validator_activity AS (

SELECT

week_date,

version,

pool_address,

active_stake,