Pool Live Stake with cardano-db-sync 11.0.0

cardano-db-sync_pool_live_stake.sql

WITH stake AS
  (SELECT d1.addr_id
   FROM delegation d1, pool_hash
   WHERE pool_hash.id=d1.pool_hash_id
     AND pool_hash.hash_raw='\xabacadaba9f12a8b5382fc370e4e7e69421fb59831bb4ecca3a11d9b'
     AND NOT EXISTS
       (SELECT TRUE
        FROM delegation d2
        WHERE d2.addr_id=d1.addr_id 
          AND d2.tx_id>d1.tx_id)
     AND NOT EXISTS
       (SELECT TRUE
        FROM stake_deregistration
        WHERE stake_deregistration.addr_id=d1.addr_id
          AND stake_deregistration.tx_id>d1.tx_id))
SELECT sum(total)
FROM
  (SELECT sum(value) total
   FROM utxo_view
     INNER JOIN stake ON utxo_view.stake_address_id=stake.addr_id
   UNION SELECT sum(amount)
   FROM reward
     INNER JOIN stake ON reward.addr_id=stake.addr_id
     WHERE reward.spendable_epoch <= (SELECT MAX(epoch_no) FROM block)
   UNION SELECT -sum(amount)
   FROM withdrawal
     INNER JOIN stake ON withdrawal.addr_id=stake.addr_id
  ) AS t;

Thanks for sharing 👍

We should use pool_hash.id in line 5 because it's index column but pool_hash.view isn't

We should use pool_hash.id in line 5 because it's index column but pool_hash.view isn't

pool_hash.id is an internal value that requires a query anyway to get it.

I have replaced pool_hash.view by pool_hash.hash_raw as it has an index but this will make almost no difference in practice as pool_hash.id is used for joins so pool_hash.view was used only once to get the pool_hash.id from a relatively small table.

In live 24, I think should be WHERE reward.earned_epoch <= (SELECT MAX(epoch_no) FROM block) because reward.amount at earned_epoch will be count as your total delegate start from epoch earned_epoch + 1.
For example: take a look at addr_id=2133226 , this one has no treasury, reserve, withdraw, no txt after delegate to pool_id=369 at epoch_282. So total_stake at epoch 285 = wallet fund + reward.amount at earned_epoch=284
Sorry for my bad English. Please correct me if i'm wrong, thanks!

In live 24, I think should be WHERE reward.earned_epoch <= (SELECT MAX(epoch_no) FROM block) because reward.amount at earned_epoch will be count as your total delegate start from epoch earned_epoch + 1.
For example: take a look at addr_id=2133226 , this one has no treasury, reserve, withdraw, no txt after delegate to pool_id=369 at epoch_282. So total_stake at epoch 285 = wallet fund + reward.amount at earned_epoch=284
Sorry for my bad English. Please correct me if i'm wrong, thanks!

If you use earned_epoch <= current_epoch, this will include some rewards that are already calculated but not yet available (spendable), because they are calculated in advance. For example, we are now epoch 291, and the last elements in the reward table are:

cardano=> select * from reward order by id desc limit 3;
    id    | addr_id |  type  |  amount  | pool_id | earned_epoch | spendable_epoch 
----------+---------+--------+----------+---------+--------------+-----------------
 26673336 |  652763 | member |     1676 |    3279 |          290 |             292
 26673335 | 1873200 | member |   118181 |    1522 |          290 |             292
 26673334 | 1274249 | member | 64612470 |     375 |          290 |             292

Your query would include these rewards but they are not yet part of the pool stake.

my mistake...It should be earned_epoch < current_epoch. We know that if I have reward at epoch T, it will show up on my wallet at epoch T + 2. So the question is that reward at epoch T will be count in total delegate from epoch T+1 or T+2?
If result is T+2, we are wasting millions ADA to advance PoS protocol for epoch T+1 (15.38m reward distributed in Epoch 290). My formula is correct with epoch_stake.amount in db-sync.

my mistake...It should be earned_epoch < current_epoch. We know that if I have reward at epoch T, it will show up on my wallet at epoch T + 2. So the question is that reward at epoch T will be count in total delegate from epoch T+1 or T+2?
If result is T+2, we are wasting millions ADA to advance PoS protocol for epoch T+1 (15.38m reward distributed in Epoch 290). My formula is correct with epoch_stake.amount in db-sync.

A pool live stake is generally considered as the sum of all wallets balances as currently seen by their users. If you use earned_epoch < current_epoch at epoch n, again, before the end of the epoch you will get new rows included for the rewards earned epoch n-1 and being calculated progressively during the epoch but not yet spendable (they will be spendable epoch n+1).

This would be a different definition of a pool live stake and it would include before the end of epochs some rewards that no one has in their wallet yet. You could use earned_epoch < current_epoch - 1, but then it's exactly the same as spendable_epoch <= current_epoch as used in the current query.