stake_txs as (

select

date_trunc('day', block_timestamp) as 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 active_pending

, change_data :inactive :value ::int / pow(10,8) as inactive_stake

, change_data :pending_inactive :value ::int / pow(10,8) as inactive_pending

from aptos.core.fact_changes

where success

and change_type = 'write_resource'

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

and block_timestamp > '2022-10-19' -- oldest stake pool change

qualify row_number() over (partition by address, date order by version desc, change_index desc) = 1

),

delegate_txs as (

select

date_trunc('day', block_timestamp) as date

, version

, address as pool_address

, change_data :active_shares :total_coins ::int / pow(10, 8) as active_delegated

, change_data :total_coins_inactive ::int / pow(10, 8) as inactive_delegated

from aptos.core.fact_changes

where success

and change_type = 'write_resource'

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