S040 · CTEs & Recursive Queries

SELECT dept, avg_sal FROM (
    SELECT dept, AVG(salary) AS avg_sal FROM employees
    WHERE hired > '2020-01-01' GROUP BY dept
) t
WHERE avg_sal > 90000
ORDER BY avg_sal DESC;

WITH recent_hires AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM   employees
    WHERE  hired > '2020-01-01'
    GROUP  BY dept
)
SELECT dept, avg_sal
FROM   recent_hires
WHERE  avg_sal > 90000
ORDER  BY avg_sal DESC;

WITH
  active_users AS (
    SELECT user_id FROM events WHERE event_date > current_date - 30
    GROUP BY user_id
  ),
  user_revenue AS (
    SELECT user_id, SUM(amount) AS rev FROM orders
    WHERE order_date > current_date - 30
    GROUP BY user_id
  ),
  scored AS (
    SELECT u.user_id, u.rev,
           NTILE(10) OVER (ORDER BY u.rev DESC) AS revenue_decile
    FROM active_users a JOIN user_revenue u USING (user_id)
  )
SELECT * FROM scored WHERE revenue_decile = 1;

WITH RECURSIVE reports AS (
    -- base case: the root
    SELECT id, name, manager_id, 0 AS depth
    FROM   employees
    WHERE  id = 1
    UNION ALL
    -- recursive step: children of anyone already in `reports`
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM   employees e
    JOIN   reports r ON e.manager_id = r.id
    WHERE  r.depth < 20         -- safety guard
)
SELECT * FROM reports ORDER BY depth, id;

-- Numbers 1..100 with their squares (SQLite / Postgres portable)
WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n, n * n AS square FROM nums;
 
-- All days in January 2026 (works everywhere without generate_series)
WITH RECURSIVE cal(d) AS (
    SELECT DATE('2026-01-01')
    UNION ALL
    SELECT DATE(d, '+1 day') FROM cal WHERE d < DATE('2026-01-31')
)
SELECT d FROM cal;

"""cte_lab.py — CTE chains and recursive traversals."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER,
    salary INTEGER,
    hired TEXT
);
INSERT INTO employees VALUES
    (1, 'Satya',  NULL, 5000000, '2014-02-04'),
    (2, 'Rajesh',    1, 2200000, '2015-05-10'),
    (3, 'Priya',     1, 2100000, '2016-08-15'),
    (4, 'Alice',     2,  600000, '2020-03-01'),
    (5, 'Bob',       2,  450000, '2021-06-15'),
    (6, 'Carol',     3,  550000, '2019-01-10'),
    (7, 'Dan',       4,  180000, '2023-11-05');
 
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER
);
INSERT INTO categories VALUES
    (1, 'Electronics',   NULL),
    (2, 'Phones',           1),
    (3, 'Smartphones',      2),
    (4, 'Feature Phones',   2),
    (5, 'Laptops',          1),
    (6, 'Gaming Laptops',   5),
    (7, 'Books',         NULL),
    (8, 'Fiction',          7);
""")
 
def run(label: str, sql: str) -> None:
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print(" ", row)
 
# ---- 1. Basic CTE — nested subquery rewritten ----
run("1. CTE — recent hires by manager, above 400k avg", """
    WITH recent AS (
        SELECT manager_id, AVG(salary) AS avg_sal
        FROM employees
        WHERE hired > '2019-01-01'
        GROUP BY manager_id
    )
    SELECT r.*, e.name AS manager_name
    FROM recent r LEFT JOIN employees e ON e.id = r.manager_id
    WHERE r.avg_sal > 400000;
""")
 
# ---- 2. Multi-CTE chain — the analytics-engineering pattern ----
run("2. Multi-CTE — top-band earners with manager attached", """
    WITH banded AS (
        SELECT id, name, salary, manager_id,
               NTILE(3) OVER (ORDER BY salary DESC) AS band
        FROM employees
    ),
    top_band AS (SELECT * FROM banded WHERE band = 1),
    enriched AS (
        SELECT t.name, t.salary, m.name AS manager
        FROM top_band t LEFT JOIN employees m ON m.id = t.manager_id
    )
    SELECT * FROM enriched ORDER BY salary DESC;
""")
 
# ---- 3. Recursive — full org tree with depth and path ----
run("3. Recursive — everyone under Satya, with depth and path", """
    WITH RECURSIVE reports AS (
        SELECT id, name, manager_id, 0 AS depth,
               CAST(name AS TEXT) AS path
        FROM employees WHERE id = 1
        UNION ALL
        SELECT e.id, e.name, e.manager_id, r.depth + 1,
               r.path || ' > ' || e.name
        FROM employees e
        JOIN reports r ON e.manager_id = r.id
        WHERE r.depth < 20
    )
    SELECT depth, name, path FROM reports ORDER BY depth, id;
""")
 
# ---- 4. Recursive upward — Dan's management chain to the CEO ----
run("4. Recursive — Dan's management chain up to the top", """
    WITH RECURSIVE chain AS (
        SELECT id, name, manager_id, 0 AS level
        FROM employees WHERE name = 'Dan'
        UNION ALL
        SELECT e.id, e.name, e.manager_id, c.level + 1
        FROM employees e JOIN chain c ON e.id = c.manager_id
        WHERE c.level < 20
    )
    SELECT level, name FROM chain ORDER BY level;
""")
 
# ---- 5. Recursive number generator — no source table ----
run("5. Recursive number gen — 1..10 with squares", """
    WITH RECURSIVE nums(n) AS (
        SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 10
    )
    SELECT n, n * n AS square FROM nums;
""")
 
# ---- 6. Recursive date series — every day in Jan 2026 ----
run("6. Recursive date series — all days in Jan 2026 (first 5)", """
    WITH RECURSIVE cal(d) AS (
        SELECT DATE('2026-01-01')
        UNION ALL
        SELECT DATE(d, '+1 day') FROM cal WHERE d < DATE('2026-01-31')
    )
    SELECT d FROM cal LIMIT 5;
""")
 
# ---- 7. Recursive summarisation — headcount rollup ----
run("7. Total direct + indirect reports under each manager", """
    WITH RECURSIVE tree AS (
        SELECT id AS root, id, name
        FROM employees
        UNION ALL
        SELECT t.root, e.id, e.name
        FROM tree t JOIN employees e ON e.manager_id = t.id
    )
    SELECT r.name AS manager, COUNT(*) - 1 AS reports_count
    FROM tree t JOIN employees r ON r.id = t.root
    GROUP BY t.root, r.name
    HAVING reports_count > 0
    ORDER BY reports_count DESC;
""")
 
# ---- 8. Category hierarchy — full path per category ----
run("8. Recursive — category paths (Electronics / Phones / Smartphones)", """
    WITH RECURSIVE cat_path AS (
        SELECT id, name, parent_id, name AS path, 0 AS depth
        FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.name, c.parent_id,
               cp.path || ' / ' || c.name, cp.depth + 1
        FROM categories c
        JOIN cat_path cp ON c.parent_id = cp.id
        WHERE cp.depth < 20
    )
    SELECT depth, path FROM cat_path ORDER BY path;
""")
 
# ---- 9. Cycle-detection demo — what happens without a guard ----
# We intentionally do NOT introduce a cycle here (it would loop forever).
# Instead, show the guard IS the fix.
run("9. Guarded recursion — safe even with pathological data", """
    WITH RECURSIVE safe_walk AS (
        SELECT id, name, manager_id, 0 AS depth FROM employees WHERE id = 1
        UNION ALL
        SELECT e.id, e.name, e.manager_id, s.depth + 1
        FROM employees e JOIN safe_walk s ON e.manager_id = s.id
        WHERE s.depth < 5                 -- HARD STOP at depth 5
    )
    SELECT COUNT(*) AS rows_returned FROM safe_walk;
""")
 
conn.close()