8. Query Stake Pool details
This query retrieves detailed information about a specific staking pool, including its name, ticker, identifier, hash, fixed cost, margin, declared pledge, total lifetime blocks produced, and associated stake account address. For this qury, we will use pool ID pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4
SELECT
ph.id ,
po.pool_name as pool_name,
po.ticker_name as pool_ticker,
ph.view as pool_id,
ph.hash_raw AS pool_hash,
pu.fixed_cost AS fixed_cost,
pu.margin AS margin,
pu.pledge AS declared_pledge,
ltb.lifetime_blocks as lifetime_blocks,
sa.view AS stake_account
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 max(pu2.id)
FROM pool_update pu2
WHERE pu2.hash_id = ph.id)
LEFT JOIN stake_address sa ON pu.reward_addr_id = sa.id
LEFT JOIN ( SELECT ph.id AS poolId, count(bk.id) AS lifetime_blocks
FROM pool_hash ph
JOIN slot_leader sl ON sl.pool_hash_id = ph.id
JOIN block bk ON bk.slot_leader_id = sl.id
GROUP BY ph.id
) as ltb
on ltb.poolId = ph.id
WHERE ph."view" = 'pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4'Expected results format
id | pool_name | pool_ticker | pool_id | pool_hash | fixed_cost |margin | declared_pledge | lifetime_blocks | stake_account
----------+------------+--------------+----------------------------------------------------------+---------------------------------------------------------------+-------------+-------+-----------------+-----------------+------------------------------------------------------------------
463 | clrPool | CLRPL | pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4 | 929ff8aa67e4e88c66bd6dab59945681f0d64ed1eed1c1c38d3d7d87 | 170,000,000 | 0.1 | 100,000 | | stake_test1uz48zjhv772tacqt93qp0f4425ly0mc44rrgerqhsca4azctyp6hw
(1 rows)8.1 Gather various stake pool data
This query gathers data such as pool metadata, stake addresses, and block minting history from various tables.
🔎 Here's a breakdown of what the query does:
The SELECT ... clause specifies the columns to be included in our results:
ph.id, the id of the pool_hash (opens in a new tab) tablepo.pool_name AS pool_nameis the pool's name, retrieved from the pool_offline_data (opens in a new tab) tablepo.ticker_name AS pool_ticker...the pool's ticker symbol.ph.view AS pool_id...the human-readable pool id.ph.hash_raw AS pool_hash...the pool's hash value.pu.fixed_cost...the pool's fixed cost, from pool_update (opens in a new tab)pu.margin...the pool's margin, also from pool_update (opens in a new tab)pu.pledge AS declared_pledge...the pool's declared pledge amount.ltb.lifetime_blocks... the total number of blocks minted by the pool (calculated in the subquery).sa.view AS stake_account...the stake address associated with the pool's rewards
The FROM pool_hash ph clause specifies that we are selecting data from the pool_hash (opens in a new tab)table, aliased as ph. This table likely stores core pool identification information.
LEFT JOIN pool_offline_data po ON ... performs a left JOIN with the pool_offline_data (opens in a new tab) table. It matches rows based on pool_id and ensures that if multiple entries exist for the same pool, only the latest one is selected (using a subquery to find the maximum id).
LEFT JOIN pool_update pu ON ... is another LEFT JOIN, this time with pool_update (opens in a new tab). It matches on hash_id and again selects the latest update for the pool using a similar subquery approach.
LEFT JOIN stake_address sa ON ... is another LEFT JOIN which connects to the stake_address (opens in a new tab) table to retrieve the stake address linked to the pool's rewards.
LEFT JOIN (...) AS ltb ON ... incorporates a subquery aliased as ltb. This subquery calculates the total number of blocks minted by the pool by joining pool_hash (opens in a new tab), slot_leader (opens in a new tab) and block (opens in a new tab) tables and counting the relevant blocks. The result is then joined back to the main query based on poolId.
WHERE ph."view" = 'pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4' filters the results to include only the data for the specific pool we are interested in.
8.2 Find out who owned the stake pool
If you would like to know then the Stake Pool owner account, you could run the following query:
SELECT
sa."view" AS owner_account
FROM
pool_hash ph
JOIN pool_update pu ON
ph.id = pu.hash_id
AND pu.id = (SELECT MAX(pu2.id) FROM pool_update pu2 WHERE ph.id = pu2.hash_id)
JOIN pool_owner po ON
pu.id = po.pool_update_id
JOIN stake_address sa ON
po.addr_id = sa.id
WHERE
ph."view" = 'pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4'Expected results format
owner_account
--------------------------------------------------------------------
stake_test1uz48zjhv772tacqt93qp0f4425ly0mc44rrgerqhsca4azctyp6hw
stake_test1uz00vukcxsleerg97cpa8fk69pryjsa0f0l57gjl60f78ussjymak
(2 rows)🔎 Lets dig a little deeper to gain a better understanding.
The opening SELECT sa."view" AS owner_account clause specifies what data the query should return. It selects the view column from the stake_address (opens in a new tab) table (aliased as sa) and labels it as owner_account in the results.
FROM pool_hash ph clause means we are inspectig the pool_hash (opens in a new tab) table, giving it the alias ph. This table stores core information about stake pools, including their unique hashes.
JOIN pool_update pu ON ph.id = pu.hash_id performs an INNER JOIN with the pool_update (opens in a new tab) table (aliased as pu).
The join condition is ph.id = pu.hash_id, meaning it connects rows from pool_hash (opens in a new tab) and pool_update (opens in a new tab) where their ids match. This links pool information with its update history.
AND pu.id = (SELECT MAX(pu2.id) FROM pool_update pu2 WHERE ph.id = pu2.hash_id) is a subquery within the JOIN condition. It ensures that only the latest update for each pool is considered. It finds the maximum id from the pool_update (opens in a new tab) table (using the alias pu2) for the given ph.id and then uses that maximum id to filter the pool_update (opens in a new tab) table in the main query.
JOIN pool_owner po ON pu.id = po.pool_update_id is another INNER JOIN with the pool_owner (opens in a new tab) table. It connects based on pu.id = po.pool_update_id, associating the latest pool update with its owner information.
JOIN stake_address sa ON po.addr_id = sa.id The final INNER JOIN connects to the stake_address (opens in a new tab) table. The condition po.addr_id = sa.id links the pool owner's information with the actual stake address details.
WHERE ph."view" = 'pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4' filters the results to include only the data related to the specific pool we provided.
8.3 List of delegators
If you are trying to obtain a list of delegators for a specific pool, then you could run the following query:
SELECT
sa.id AS stakeAddressId,
sa.view AS delegator_address
FROM stake_address sa
WHERE sa.id IN (
SELECT dg1.addr_id
FROM delegation dg1
JOIN pool_hash ph ON dg1.pool_hash_id = ph.id
WHERE ph.view = 'pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4'
AND NOT EXISTS (
SELECT TRUE
FROM delegation dg2
WHERE dg2.addr_id = dg1.addr_id
AND dg2.tx_id > dg1.tx_id
)
AND NOT EXISTS (
SELECT TRUE
FROM stake_deregistration sd
WHERE sd.addr_id = dg1.addr_id
AND sd.tx_id > dg1.tx_id
)
)Expected results format
stakeaddressid | delegator_address
----------------------+-----------------------------------------------------------
10,497 | stake_test1uzhl7a0df7v82xlm8yt06h9zjqlkmu636grf8g63h6e0q0sxt5gz4
145,345 | stake_test1uz48zjhv772tacqt93qp0f4425ly0mc44rrgerqhsca4azctyp6hw
(2 rows)🔎 Looking closer, lets dissect each line:
SELECT sa.id AS stakeAddressId, sa.view AS delegator_address selects the id column (labelling it as stakeAddressId) and the view column (labellting it as delegator_address) from the stake_address table (alias sa). The view column likely represents the human-readable format of the stake address.
FROM stake_address sa means we are looking in the stake_address table.
WHERE sa.id IN (...) is where we filter by checking if the id from the stake_address table exists within the results of the subquery that follows.
The subquery starts with SELECT dg1.addr_id ...selecting the addr_id (stake address id) from the delegation table (alias dg1).
FROM delegation dg1 JOIN pool_hash ph ON dg1.pool_hash_id = ph.id joins the delegation table with the pool_hash table based on the pool_hash_id matching in both tables. This connects delegation records with the associated pool information.
WHERE ph.view = 'pool1j20l32n8un5gce4adk44n9zks8cdvnk3amgursud847cwnpgeg4' filters the joined results to include only delegations to the specific pool we provided.
AND NOT EXISTS (SELECT TRUE FROM delegation dg2 WHERE dg2.addr_id = dg1.addr_id AND dg2.tx_id > dg1.tx_id) ensures that we only consider the latest delegation for each stake address. It checks if there's any other delegation record dg2 for the same addr_id but with a higher tx_id (ie. a later transaction). If such a record exists, it excludes the current delegation dg1 from the results.
Similarly, the AND NOT EXISTS (SELECT TRUE FROM stake_deregistration sd WHERE sd.addr_id = dg1.addr_id AND sd.tx_id > dg1.tx_id) clause checks if there's a stake deregistration record sd for the same addr_id with a higher tx_id. If so, it means the stake address has been deregistered after the delegation, so it's excluded from the results.
Summary
Our first query, 3.1, inspects several tables to return various data on our specific stake pool. Query 3.2 traverses through several tables to pinpoint the owner's stake address for a particular pool. Finally, 3.3 queries the Ledger Sync database schema to return a list of current delegators for the stake pool address we provide.