How best to derive "Live Stake" for a stake pool?

Hi all - hoping someone could point me in the right direction. I’d like to be able to derive Live Stake for my pool on demand internally. The GraphQL api allows me to determine Active Stake and Delegations as well as Stake Registration, but these only give me Stake Addresses - I’m assuming I would need to find the wallet addresses attached to these Stake Addresses and sum the values. Looking at the Transactions for Delegations and Stake Registration only shows the wallet addresses involved with that particular transaction, ie. no easily derived information about the wallet containing the actual stake - what’s the best way to get this?

Hi leslie, not sure if still relevant to you but might be to others. Deriving a pools live stake is something you could do using StakeSync with a simple
SELECT sum(balance) FROM stake WHERE pool_id = ‘[POOLID]’. It uses DB Sync as data source, here is roughly how I went about it (check its database for reference):

#1) Loop through all UTXO’s

SELECT, tx_out.tx_id, tx_out.index, tx_out.address, tx_out.address_raw, tx_out.payment_cred, tx_out.stake_address_id, tx_out.value FROM (tx_out LEFT JOIN tx_in ON (((tx_out.tx_id = tx_in.tx_out_id) AND ((tx_out.index)::smallint = (tx_in.tx_out_index)::smallint)))) WHERE (tx_in.tx_in_id IS NULL);

and store addresses and balances in a new table. (keep in mind one address can have multiple outputs)

Use the bech32 binary to get the corresponding stake key of the addresses (only those starting with addr1q…), and sum up their balances as well.

#2) From here process all transactions block by block. Something like this for each block:

SELECT tx_out.address, tx_out.value, tx_out.tx_id FROM tx_out, tx WHERE tx_out.tx_id = AND tx.block_id = [CURRENT_BLOCK] ORDER BY ASC;

To ensure integrity, allow for roll-backs in case of interruptions.

#3) Process stake registrations / delegations to store the current pool id in the stake key table.

#4) Process stake deregistrations to remove pool ids from deregistered stake keys.

Try to avoid redundancies and unnecessary computation to save resources and allow for sudden peaks in transaction throughput without lagging behind.