Module 12: CTEs and recursive queries

Write long queries as readable steps with WITH, walk hierarchies with recursive CTEs, and build reports.

1. WITH: multi-step queries you can read: WITH name AS (query) names a step for one statement; chain steps with commas and read the query top to bottom.
2. Recursive CTEs: org charts, paths and generated rows: WITH RECURSIVE = anchor rows UNION ALL a part that joins the CTE to find the next rows; it repeats until a round adds nothing, so always make sure it can stop.
3. Reports: ROLLUP, STRING_AGG and pivots: STRINGAGG lists values in one cell; ROLLUP / GROUPING SETS / CUBE add subtotal and total rows (spot them with GROUPING()); FILTER or SUM(CASE) pivots rows into columns.

Ends with a 13-question checkpoint (80% passes) and a project: The org cost report.