user7239: 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

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.

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



user5926: 
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.

7239: <p>Using cardano-db-sync, I want to get the number of delegators given a pool_id.</p>
<p>For example, this pool has 22 delegators.
<a href="https://cardanoscan.io/pool/18109d01af0c5c4495a64a9de061ad621156729afc699128c0ceee0e?tab=delegators" rel="nofollow noreferrer">https://cardanoscan.io/pool/18109d01af0c5c4495a64a9de061ad621156729afc699128c0ceee0e?tab=delegators</a></p>
<p>However, when I used the following query, the result did not match. The following query returns 66 unique values of stake_address.hash_raw.</p>
<pre><code>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'
</code></pre>
<p>I want to ask that</p>
<ol>
<li><p>It seems the the table <code>delegation</code> 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.</p>
</li>
<li><p>How can I get the number of delegators w.r.t a pool_id</p>
<p>(2.1) at the current epoch</p>
<p>(2.2) for each epoch</p>
</li>
</ol>
 5926: <blockquote>
<p>It seems the the table delegation also contains the delegation history, not only the delegation at current time?</p>
</blockquote>
<p>Yes, that makes the <code>delegation</code> table a bit of a pain in the neck for something like this.</p>
<p>Its probably easier to use the <code>epoch_stake</code> table. The simplest query would be something like (choose the epoch you are interested in):</p>
<pre><code>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
...
</code></pre>
<p>The pool fingerprint (ie <code>pool1rqgf6qd0...</code>) can be obtained with a join on the <code>pool_hash</code> table like you have in your example. Also, note that unfortunately, the <code>epoch_stake</code> table for epoch <code>N</code> is updated late in epoch <code>N - 1</code> (I think).</p>
<p>To get the delegation history for a pool, something like this should work:</p>
<pre><code>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
...
</code></pre>
<p>A simple join with the <code>pool_hash</code> table will allow you to use the pool fingerprint.</p>
