Common Table Expressions (WITH)

A CTE — Common Table Expression, spelled WITH — is a named subquery you define up front and then use like a table. It's the single biggest readability upgrade in SQL: the deeply-nested, inside-out subqueries from the last lesson turn into a top-to-bottom sequence of named steps that reads like the paragraph you'd write to explain the query.

From nested to named

Compare. A subquery in the FROM clause, nested:

SELECT category, total FROM (
    SELECT category, SUM(price) AS total FROM products GROUP BY category
) WHERE total > 100;

The same logic as a CTE:

WITH totals AS (
    SELECT category, SUM(price) AS total
    FROM products
    GROUP BY category
)
SELECT category, total
FROM totals
WHERE total > 100;

Both compute the same thing. The second names the intermediate result (totals) and defines it before the query that uses it, so you read the steps in the order they happen: first compute totals, then filter them. For a two-level query it's a modest win; for a five-level one it's the difference between maintainable and write-only. This is your exercise — compute per-category totals in a CTE, then filter to those over 100.

The syntax, precisely

WITH cte_name AS (
    -- any SELECT
)
SELECT ... FROM cte_name ...;    -- the "main" query MUST follow immediately

The CTE exists only for the statement it's attached to (unlike a view, which persists). And you can define several, comma-separated, each able to reference the ones before it — which is what makes CTEs a genuine pipeline:

WITH
  category_totals AS (
      SELECT category, SUM(price) AS total FROM products GROUP BY category
  ),
  big_categories AS (
      SELECT category FROM category_totals WHERE total > 100
  )
SELECT * FROM big_categories ORDER BY category;

Read top to bottom: totals, then the big ones, then output. Each step is small, named, and testable — you can run the query with SELECT * FROM category_totals alone to check step one. That decomposability is why analysts and data engineers reach for CTEs constantly.

Why prefer CTEs over subqueries?

• Readability — named steps beat nested parentheses, especially past two levels.
• Reuse — reference a CTE multiple times in the same query; a FROM-subquery you'd have to write twice.
• Debuggability — swap the final SELECT to select from any intermediate CTE to inspect it.

The tradeoff to know: historically some databases materialized CTEs (computed them fully, even when the outer query only needed part), which could be slower than an equivalent subquery the optimizer could rewrite. Modern engines (recent SQLite, Postgres 12+) largely inline them, so the readability is usually free. When in doubt, write it clearly first.

The superpower: recursive CTEs

CTEs unlock something subqueries can't do at all — recursion, for hierarchical data:

WITH RECURSIVE ancestors AS (
    SELECT id, parent_id, name FROM people WHERE id = 42   -- base case
    UNION ALL
    SELECT p.id, p.parent_id, p.name                        -- recursive step
    FROM people p JOIN ancestors a ON p.id = a.parent_id
)
SELECT * FROM ancestors;

This walks an org chart, a category tree, a graph of dependencies — a query "calling itself" until the recursive part returns no new rows. You won't need it for this exercise, but knowing SQL can do tree-walking (and that CTEs are the door) changes what you believe SQL is capable of. It is a genuinely more powerful language than "SELECT from tables" suggests.

Your exercise: Top Categories

A CTE computes SUM(price) per category; the main query keeps those over 100 and prints category|total sorted alphabetically. The graded details: the | in the output is SQLite's default column separator — you can SELECT category, total (two columns, SQLite joins them with |) or explicitly SELECT category || '|' || total; match what the expected output shows. Filter with WHERE total > 100 in the main query (the CTE computes, the outer query filters), and ORDER BY category for the alphabetical requirement. Named step, then use it — the shape of every serious query you'll write from here on.