Skip to content

Instantly share code, notes, and snippets.

@SmaugPool

SmaugPool/cardano-db-sync_pool_live_stake.sql

Last active November 1, 2022 23:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save SmaugPool/892f5bc18bf5c6e52245e1b7bd0bed84 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save SmaugPool/892f5bc18bf5c6e52245e1b7bd0bed84 to your computer and use it in GitHub Desktop.
Download ZIP
Pool Live Stake with cardano-db-sync 11.0.0
Raw
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;
@ljttl3q04t
Copy link

ljttl3q04t commented Sep 23, 2021

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.

@SmaugPool
Copy link
Author

SmaugPool commented Sep 23, 2021 •
edited
Loading

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment