How to get number of delegators of a pool?

Question

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

Answer 1

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.

Answer 2

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.