Problem Statement
Derived table subqueries and CTEs can both modularize logic. How do you choose between them for readability and performance?
Explanation
Use a CTE when naming the step improves clarity, you reuse the step, or the engine supports readable recursion. A derived table is fine for small, local transformations and keeps the scope tight.
Performance often depends on the optimizer. Many engines inline both forms. When reuse, indexing, or materialization control is needed, consider a temporary table with indexes instead.
Code Solution
SolutionRead Only
SELECT * FROM ( SELECT customer_id, SUM(total) AS s FROM orders GROUP BY 1 ) t JOIN customers c ON c.id = t.customer_id;
