7. Query Active Stake Pools
Staking (opens in a new tab) is a big part of the Cardano ecosystem. To get started, the first step is to identify the active stake pools. Our next SQL query retrieves information about active Cardano staking pools, retrievign the pool's ID, name and creation date.
Cardanoscan (opens in a new tab) shows active stake pools by default. There is a toggle button to 'Show Inactive Pools' 🔎
select ph.id ,
COALESCE(po.pool_name, ph."view") as pool_name,
po.ticker_name as pool_ticker,
b."time" AS creation_date,
ROW_NUMBER() OVER (ORDER BY ph.id ASC)
FROM
pool_hash ph
LEFT JOIN pool_offline_data po on
ph.id = po.pool_id
and (po.id is null
or po.id = (SELECT max(po2.id)
FROM pool_offline_data po2
WHERE po2.pool_id = ph.id))
LEFT JOIN pool_update pu on
ph.id = pu.hash_id
and pu.id = (SELECT min(pu2.id)
FROM pool_update pu2
WHERE pu2.hash_id = ph.id)
LEFT JOIN tx ON tx.id = pu.registered_tx_id
LEFT JOIN block b ON tx.block_id = b.id
WHERE ph.id NOT IN (SELECT pr.hash_id
FROM pool_retire pr
LEFT JOIN pool_update pu on pr.hash_id = pu.hash_id
LEFT JOIN pool_hash ph ON ph.id = pr.hash_id
GROUP BY pr.hash_id, pu.cert_index, pr.cert_index
HAVING (max(pu.registered_tx_id) < max(pr.announced_tx_id))
OR ((max(pu.registered_tx_id) = max(pr.announced_tx_id)) AND (max(pu.cert_index) < max(pr.cert_index)))
) Expected results format
id | pool_name | pool_ticker | creation_date
----------+---------------------------+--------------+--------------------------
4 | ANGEL stake pool | ANGEL | 2022-10-20 21:21:59.000
5 | CanadaStakes | CAN1 | 2022-10-20 21:30:58.000
6 | SION stake pool | SION | 2022-10-20 21:35:52.000
7 | PANL Stake Pool(Pre-Prod)| PANL | 2022-10-20 22:24:32.000
. | ................ | ..... | .................
(Note: full results trimmed for readability)🔎 Here's a breakdown of how the query works:
Select Specific Columns
In order to gather information about staking pools, the query focuses on:
- Pool ID
ph.id, the unique identifier for each staking pool - Pool Name
pool_name, the human-readable name of the pool - Ticker
pool_ticker, an abbreviation of the pool creation_date, the date the pool was created
Tables Involved
The query traverses several tables (aliases are in brackets):
- pool_hash (opens in a new tab) (ph) stores the unique hash identifiers for each pool.
- pool_offline_data (opens in a new tab) (po) contains additional information about the pool, such as its name and ticker.
- pool_update (opens in a new tab) (pu) tracks updates or changes made to stake pools. It's joined to find the initial registration transaction of a pool
pu.registered_tx_id. - tx (opens in a new tab) stores transaction data.
- block (opens in a new tab) (b) stores information about blocks, including their timestamps
b.time, which are used to determine the pool's creation date. - pool_retire (opens in a new tab) (pr) holds information about stake pools that have been retired or de-registered.
Joins and Subqueries
LEFT JOIN pool_offline_data po on ...
- Joins pool_hash (opens in a new tab) with pool_offline_data (opens in a new tab) to get the pool name and ticker.
- The complex
ONcondition ensures that for each pool hash:- a) the most recent pool_offline_data (opens in a new tab) entry if any exists, b)
- b) If no pool_offline_data (opens in a new tab) exists for the pool, we still include the pool in the results (due to the
LEFT JOIN).
LEFT JOIN pool_update pu on ... joins pool_hash (opens in a new tab) with pool_update (opens in a new tab) to get the earliest registration transaction for each pool using a subquery to find the minimum pu.id for each pu.hash_id.
LEFT JOIN tx ON tx.id = pu.registered_tx_id joins the pool_update (opens in a new tab) with the tx table to get the transaction associated with the pool's registration.
LEFT JOIN block b ON tx.block_id = b.id joins the tx table with the block (opens in a new tab) table to get the block in which the registration transaction was included, allowing us to retrieve the creation date.
WHERE ph.id NOT IN (SELECT ...) is important for filtering out retired stake pools. The subquery identifies retired pools by comparing the registration and retirement transactions and their certificate indices within those transactions. It ensures that only active pools are included in the final result
SELECT Clause Details:
COALESCE(po.pool_name, ph."view") as pool_name uses COALESCE to prioritize the pool_name from pool_offline_data (opens in a new tab). If it's null, it falls back to the view from pool_hash (opens in a new tab). This ensures we have a name for each pool.
b."time" AS creation_date retrieves the time from the block (opens in a new tab) table (associated with the pool's registration transaction) and labels it as creation_date.
ROW_NUMBER() OVER (ORDER BY ph.id ASC) assigns a sequential row number to each result row, ordered by the pool hash ID in ascending order. This can be useful for pagination or displaying results in a numbered list.
The next query will provide users with detailed information about specific stake pools. This will help them make informed decisions when selecting a specific pool to delegate their ADA.