stake_txs as (

select

date_trunc('day', block_timestamp) as date

, address as pool_address

, change_data :active :value ::int / pow(10,8) as active_stake

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'

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

),

dates as (

select distinct

date_trunc('day', date_hour) as date

, pool_address

from crosschain.core.dim_date_hours

cross join (select distinct pool_address from stake_txs) pools

),

filled_dates as (

select

date

, pool_address

, coalesce(active_stake

, lag(active_stake) ignore nulls over (partition by pool_address order by date)

, 0) as active_stake

from dates

full outer join stake_txs

using(date, pool_address)

where active_stake is not null

),

aggregated as (

select

date