Query all known addresses for all active delegators of a Cardano Stake Pool

db-sync.sql

SELECT DISTINCT address
FROM utxo_view WHERE stake_address_id IN (
SELECT DISTINCT sa.id
FROM stake_address sa
JOIN (
    SELECT d.addr_id, MAX(d.active_epoch_no) AS max_active_epoch_no
    FROM delegation d
    GROUP BY d.addr_id
) max_epochs ON sa.id = max_epochs.addr_id
JOIN stake_registration sr ON (sr.addr_id = sa.id)
JOIN delegation d ON sa.id = d.addr_id AND d.active_epoch_no = max_epochs.max_active_epoch_no
JOIN pool_hash ph ON d.pool_hash_id = ph.id
-- Using AWP3 pool as an example, which has the most number of delegators (59k)
WHERE ph.view = 'pool13annzt9hjfc822f0ejvxjf7fsmxd6cc28whpk5kagec6ggfmm7u'
AND (
  sr.tx_id > (
    SELECT COALESCE(MAX(sdr.tx_id), 0)
        FROM stake_deregistration sdr
        WHERE sdr.addr_id = sa.id
  )
)
AND (
  ph.id = (
      SELECT pool_hash_id FROM delegation WHERE addr_id IN (
      SELECT id FROM stake_address WHERE id = sa.id
    ) ORDER BY tx_id DESC LIMIT 1
  )
)
);