Skip to content

Instantly share code, notes, and snippets.

@caike
Last active November 21, 2023 15:27
Show Gist options
  • Save caike/71d115337c2626458a3d32e79103f57b to your computer and use it in GitHub Desktop.
Save caike/71d115337c2626458a3d32e79103f57b to your computer and use it in GitHub Desktop.
Query all known addresses for all active delegators of a Cardano Stake Pool
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
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment