When do CTEs spark joy over nested subqueries?

Category: SQL for AI Engineering

Answer

WITH … AS (SELECT …) makes the query read top-down, allowing window functions and ORDER BY in the inner step. Useful for “compute over partition, then filter top-K”.

Concrete examples from the fca project context

Example 1

Latest order per customer: with ranked as (select *, row_number() over (partition by customer order by ts desc) rn from orders) select * from ranked where rn = 1;

Example 2

Complex pipelines: cte1 joins + filter; cte2 ranks; outer = top-K. Each cte is a single page of SQL.

Example 3

Recursive CTEs: hierarchical data (org charts, comments threads).

Data flow / flow chart

with ranked as (... window ...)
  select * from ranked where rn = 1
(top-down reads like a function)

Takeaway

Use CTEs when the inner computation needs a window function. Otherwise a subquery is fine.