-- ============================================================================
-- 05_active_pool_count.sql
-- Q: Count of active pools at each epoch over the last 3 years.
--
-- WINDOW: epochs 433..651. Epoch 433 began 2023-08-28, i.e. three years before
--   the run date (2026-08-29). Epoch 651 is the last COMPLETED epoch; 652 is in
--   progress and is excluded so the block series is not truncated.
--
-- "ACTIVE" (definition, since the brief leaves it to be stated):
--   active_pools(E) = number of distinct pools holding NON-ZERO stake in the
--   stake distribution for epoch E (epoch_stake, epoch_no = E).
--   Appearing in epoch_stake already implies the pool was registered and not
--   retired as at the snapshot for that epoch, so this single condition
--   captures "registered, not retired, non-zero stake".
--
-- A SECOND SERIES is reported alongside: pools that actually minted at least one
--   block in epoch E. The first series counts pools that exist; the second
--   counts pools that are operating. They answer different questions and they
--   have not moved together, so both are given rather than choosing one.
--
-- Uses canonical epoch_stake / block / slot_leader only -- not the local
-- pool_epoch_stake_cache derived table.
-- ============================================================================

\echo '--- active pool count per epoch, 433-651 ---'
WITH staked AS (
  SELECT epoch_no, count(DISTINCT pool_id) AS pools_with_stake, sum(amount) AS total_stake
  FROM epoch_stake WHERE epoch_no BETWEEN 433 AND 651 GROUP BY 1
),
minted AS (
  SELECT b.epoch_no, count(DISTINCT sl.pool_hash_id) AS pools_minting, count(*) AS blocks
  FROM block b JOIN slot_leader sl ON sl.id = b.slot_leader_id
  WHERE b.epoch_no BETWEEN 433 AND 651 AND sl.pool_hash_id IS NOT NULL GROUP BY 1
)
SELECT s.epoch_no,
       e.start_time::date            AS epoch_start,
       s.pools_with_stake            AS active_pools,
       m.pools_minting               AS pools_minting_a_block,
       (s.total_stake/1000000.0)::numeric(20,0) AS total_active_stake_ada
FROM staked s
JOIN minted m ON m.epoch_no = s.epoch_no
JOIN epoch  e ON e.no       = s.epoch_no
ORDER BY s.epoch_no;

\echo ''
\echo '--- endpoints and change over the 3-year window ---'
WITH staked AS (
  SELECT epoch_no, count(DISTINCT pool_id) AS pools_with_stake
  FROM epoch_stake WHERE epoch_no IN (433,651) GROUP BY 1
),
minted AS (
  SELECT b.epoch_no, count(DISTINCT sl.pool_hash_id) AS pools_minting
  FROM block b JOIN slot_leader sl ON sl.id=b.slot_leader_id
  WHERE b.epoch_no IN (433,651) AND sl.pool_hash_id IS NOT NULL GROUP BY 1
)
SELECT 'active pools (non-zero stake)' AS series,
       max(pools_with_stake) FILTER (WHERE epoch_no=433) AS at_epoch_433,
       max(pools_with_stake) FILTER (WHERE epoch_no=651) AS at_epoch_651,
       max(pools_with_stake) FILTER (WHERE epoch_no=651) - max(pools_with_stake) FILTER (WHERE epoch_no=433) AS change,
       round(100.0*(max(pools_with_stake) FILTER (WHERE epoch_no=651) - max(pools_with_stake) FILTER (WHERE epoch_no=433))
             / max(pools_with_stake) FILTER (WHERE epoch_no=433),1) AS pct_change
FROM staked
UNION ALL
SELECT 'pools minting >=1 block',
       max(pools_minting) FILTER (WHERE epoch_no=433),
       max(pools_minting) FILTER (WHERE epoch_no=651),
       max(pools_minting) FILTER (WHERE epoch_no=651) - max(pools_minting) FILTER (WHERE epoch_no=433),
       round(100.0*(max(pools_minting) FILTER (WHERE epoch_no=651) - max(pools_minting) FILTER (WHERE epoch_no=433))
             / max(pools_minting) FILTER (WHERE epoch_no=433),1)
FROM minted;

\echo ''
\echo '--- annual snapshot (one epoch per year, same point in the year) ---'
WITH pick(epoch_no) AS (VALUES (433),(506),(579),(651)),
staked AS (
  SELECT epoch_no, count(DISTINCT pool_id) AS pools_with_stake, sum(amount) AS total_stake
  FROM epoch_stake WHERE epoch_no IN (433,506,579,651) GROUP BY 1
),
minted AS (
  SELECT b.epoch_no, count(DISTINCT sl.pool_hash_id) AS pools_minting
  FROM block b JOIN slot_leader sl ON sl.id=b.slot_leader_id
  WHERE b.epoch_no IN (433,506,579,651) AND sl.pool_hash_id IS NOT NULL GROUP BY 1
)
SELECT p.epoch_no, e.start_time::date AS epoch_start,
       s.pools_with_stake AS active_pools, m.pools_minting AS pools_minting_a_block,
       (s.total_stake/1000000.0)::numeric(20,0) AS total_active_stake_ada
FROM pick p JOIN staked s ON s.epoch_no=p.epoch_no
            JOIN minted m ON m.epoch_no=p.epoch_no
            JOIN epoch  e ON e.no=p.epoch_no
ORDER BY p.epoch_no;