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

d.date,

st.pool_address,

COALESCE(st.active_stake, 0) AS active_stake

FROM dates d

LEFT JOIN stake_txs st ON d.pool_address = st.pool_address AND d.date = st.date

),

TotalStake AS (

SELECT

date,

SUM(active_stake) AS daily_total_stake

FROM filled_dates

GROUP BY date