3

Using cardano-db-sync, I want to get the number of delegators given a pool_id.

For example, this pool has 22 delegators. https://cardanoscan.io/pool/18109d01af0c5c4495a64a9de061ad621156729afc699128c0ceee0e?tab=delegators

However, when I used the following query, the result did not match. The following query returns 66 unique values of stake_address.hash_raw.

select pool_hash.view,stake_address.hash_raw::text, stake_address.view,  
delegation.active_epoch_no, delegation.slot_no
from delegation, pool_hash, stake_address
where delegation.pool_hash_id = pool_hash.id and delegation.addr_id =stake_address.id 
and pool_hash.view ='pool1rqgf6qd0p3wyf9dxf2w7qcddvgg4vu56l35ez2xqemhqun2gn7y'

I want to ask that

  1. It seems the the table delegation also contains the delegation history, not only the delegation at current time? If so, which table should I use to be able to filter out to get only the number of current delegators.

  2. How can I get the number of delegators w.r.t a pool_id

    (2.1) at the current epoch

    (2.2) for each epoch

2 Answers 2

4

It seems the the table delegation also contains the delegation history, not only the delegation at current time?

Yes, that makes the delegation table a bit of a pain in the neck for something like this.

Its probably easier to use the epoch_stake table. The simplest query would be something like (choose the epoch you are interested in):

select epoch_no, pool_id, count(*), sum (amount) as delegated from epoch_stake
    where epoch_no = 362 group by (pool_id, epoch_no) ; 

 epoch_no | pool_id | count |   delegated    
----------+---------+-------+----------------
      362 |    3081 |     2 |       70449031
      362 |     361 |    10 |   264344058414
      362 |    1286 |    34 |   709992195312
      362 |     560 |     1 |      507621786
      362 |    5214 |    10 |  4983514457611
      362 |    4090 |     3 |     1893354467
      362 |    4791 |     9 |   335768752617
      362 |    3159 |     3 |     5945300103
      362 |    5215 |    99 |  1358232546301
      362 |    4765 |     7 | 13999900770537
      362 |    2623 |    52 |  3429179709353
...

The pool fingerprint (ie pool1rqgf6qd0...) can be obtained with a join on the pool_hash table like you have in your example. Also, note that unfortunately, the epoch_stake table for epoch N is updated late in epoch N - 1 (I think).

To get the delegation history for a pool, something like this should work:

select epoch_no, pool_id, count(*), sum (amount) as delegated from epoch_stake
    where pool_id = 3081 group by (pool_id, epoch_no) order by epoch_no asc  ; 
 epoch_no | pool_id | count |   delegated    
----------+---------+-------+---------------
      258 |    3081 |     2 |      70643792
      259 |    3081 |     4 | 2105455092524
      260 |    3081 |     4 | 2105455092524
      261 |    3081 |     4 | 2105455092524
      262 |    3081 |     4 | 2105455092524
...

A simple join with the pool_hash table will allow you to use the pool fingerprint.

6
  • 1
    Thanks so much. You save my day.
    – cdt
    Sep 12, 2022 at 21:56
  • I would like to ask a follow-up question. From this epoch_stake table, I can get the delegated amount for each stake_address per epoch. Does this delegated amount already include the reward? Or I need to add the reward mount (from reward table) to get proper delegated amount?
    – cdt
    Oct 9, 2022 at 22:36
  • No, the reward for epoch N is not included in the delegated amount for that epoch because the rewards earned in epoch N are not distributed until epoch N + 2. From this I hope you can see that you should not add the reward amount to the delegated amount. Oct 10, 2022 at 6:49
  • I see. Thank you. So the delegated amount for epoch N (in epoch_stake table) already includes rewards for epoch N-2? Or not include yet?
    – cdt
    Oct 11, 2022 at 1:04
  • Yes, the delegated amount for epoch N already includes rewards earned in epoch N - 2 and earlier. Oct 11, 2022 at 2:42
0

The poor man's way to find out is to go to pool.pm and type in the pool of interest ticker, and at the top header is the number of delegators.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.