CTEs, recursion, subqueries

-- scalar: must return exactly one value, or it is a runtime error
SELECT name, (SELECT MAX(total) FROM orders) AS biggest FROM users;

-- correlated scalar: one per outer row
SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS n
  FROM users u;

-- table subquery in FROM: a derived table. it MUST be aliased.
SELECT * FROM (SELECT id, total FROM orders WHERE total > 100) big;

-- correlated: conceptually one execution per user
SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS n
  FROM users u;

-- the join form: one pass, aggregated once
SELECT u.name, COALESCE(o.n, 0) AS n
  FROM users u
  LEFT JOIN (SELECT user_id, COUNT(*) AS n
              FROM orders GROUP BY user_id) o
    ON o.user_id = u.id;

WITH recent AS (
  SELECT * FROM orders WHERE created_at >= '2026-01-01'
),
per_user AS (
  SELECT user_id, COUNT(*) AS n, SUM(total) AS spend
    FROM recent GROUP BY user_id      -- a CTE can use an earlier CTE
)
SELECT u.name, p.n, p.spend
  FROM per_user p JOIN users u ON u.id = p.user_id
 WHERE p.spend > 1000;

-- Postgres 12+: force materialization when you WANT the fence
WITH expensive AS MATERIALIZED (
  SELECT ... -- computed once, reused by every reference
)
SELECT ... FROM expensive a JOIN expensive b ON ...;

WITH RECURSIVE tree AS (
  -- ANCHOR: the starting rows. runs once.
SELECT id, name, manager_id, 1 AS depth
    FROM emp WHERE manager_id IS NULL
UNION ALL
-- RECURSIVE TERM: joins the table to the PREVIOUS iteration's rows.
-- runs repeatedly until it produces no new rows.
SELECT e.id, e.name, e.manager_id, t.depth + 1
FROM emp e
    JOIN tree t ON e.manager_id = t.id      -- ← references itself
)
SELECT * FROM tree ORDER BY depth, name;

CREATE TABLE emp (id INT, name VARCHAR(20), manager_id INT);
INSERT INTO emp VALUES
  (1,'ada',NULL), (2,'bode',1), (3,'chi',1),
  (4,'dami',2), (5,'eze',4);

WITH RECURSIVE tree AS (
  SELECT id, name, 1 AS depth,
         CAST(name AS CHAR(200)) AS path
    FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1,
         CONCAT(t.path, ' > ', e.name)
    FROM emp e JOIN tree t ON e.manager_id = t.id
)
SELECT depth, LPAD(name, depth*2 + LENGTH(name), ' ') AS tree, path
  FROM tree ORDER BY path;

--  depth  tree        path
--    1    ada         ada
--    2      bode      ada > bode
--    3        dami    ada > bode > dami
--    4          eze   ada > bode > dami > eze
--    2      chi       ada > chi

-- NOTE the CAST on the anchor's path column. The anchor
-- determines the column TYPE for the whole CTE, so without it
-- the path is sized to the first name and gets truncated.
-- This is the commonest recursive-CTE error.

-- carry the visited path and refuse to revisit
WITH RECURSIVE reach AS (
  SELECT src, dst,
         CAST(CONCAT(',', src, ',', dst, ',') AS CHAR(1000)) AS path,
         1 AS hops
    FROM edges WHERE src = 1
UNION ALL
SELECT r.src, e.dst,
         CONCAT(r.path, e.dst, ','),
         r.hops + 1
FROM edges e
    JOIN reach r ON e.src = r.dst
   WHERE r.path NOT LIKE CONCAT('%,', e.dst, ',%')   -- ← the guard
AND r.hops < 20 -- ← belt and braces
)
SELECT DISTINCT dst, MIN(hops) AS shortest
  FROM reach GROUP BY dst;

-- Postgres 14+
WITH RECURSIVE reach AS (...)
  CYCLE dst SET is_cycle USING path
SELECT * FROM reach WHERE NOT is_cycle;

-- every day in a range, whether or not data exists
WITH RECURSIVE days AS (
  SELECT DATE('2026-01-01') AS d
  UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days
   WHERE d < '2026-01-31'
)
SELECT days.d, COALESCE(SUM(s.amount), 0) AS revenue
  FROM days
  LEFT JOIN sales s ON DATE(s.created_at) = days.d
 GROUP BY days.d
 ORDER BY days.d;
--   without the generated series, days with no sales are simply
--   absent from the result — and a chart silently skips them.

-- Postgres
SELECT d::date FROM generate_series(
  '2026-01-01'::date, '2026-01-31'::date, '1 day') d;

-- closure table: the most flexible model
CREATE TABLE tree_paths (
  ancestor   INT NOT NULL,
  descendant INT NOT NULL,
  depth      INT NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  INDEX (descendant, ancestor)       -- for upward queries
);
--   every node also has a self-row at depth 0.
-- entire subtree, no recursion:
SELECT n.* FROM nodes n
  JOIN tree_paths p ON p.descendant = n.id
 WHERE p.ancestor = 4;

-- all ancestors of a node, ordered from the root:
SELECT n.* FROM nodes n
  JOIN tree_paths p ON p.ancestor = n.id
 WHERE p.descendant = 9 ORDER BY p.depth DESC;

-- MySQL: hard iteration cap, default 1000
SELECT @@cte_max_recursion_depth;
SET SESSION cte_max_recursion_depth = 10000;

-- also bounded by execution time:
SET SESSION max_execution_time = 5000;   -- ms, SELECT only

-- Postgres has NO recursion limit. an unbounded recursive CTE
-- will consume memory until the query is killed. bound it:
--   SET statement_timeout = '5s';

-- the portable guard is in the query itself:
--   ... WHERE depth < 50