-
-
Save SmaugPool/892f5bc18bf5c6e52245e1b7bd0bed84 to your computer and use it in GitHub Desktop.
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; |
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 butpool_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)
becausereward.amount
atearned_epoch
will be count as your total delegate start from epochearned_epoch + 1
.
For example: take a look ataddr_id=2133226
, this one has notreasury, reserve, withdraw
, notxt
after delegate topool_id=369
atepoch_282
. Sototal_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 epochT
, it will show up on my wallet at epochT + 2
. So the question is that reward at epoch T will be count in total delegate from epochT+1
orT+2
?
If result isT+2
, we are wasting millions ADA to advance PoS protocol for epochT+1
(15.38m reward distributed in Epoch 290). My formula is correct withepoch_stake.amount
indb-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.
Thanks for sharing 👍