Chapter 11 — Exercises

Run against mercado. The recursive ones use categories.parent_category_id and employees.manager_id. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Basic CTEs

11.1 Using a CTE per_order, compute the average and maximum order total. (answer in Appendix)

11.2 Refactor this nested query into a CTE for readability:

SELECT * FROM (SELECT category_id, COUNT(*) n FROM products GROUP BY category_id) t WHERE t.n > 1;

11.3 Write a CTE per_customer (revenue per customer), then select customers whose revenue exceeds the average revenue. (answer in Appendix)

11.4 ⭐ Use two chained CTEs to: (1) compute per-category revenue, then (2) show each category's share of total revenue (its revenue / grand total).

Group B — Multiple CTEs / pipelines

11.5 Build a 3-step pipeline: per-customer revenue → rank by revenue → join to customers for names; return the top 3. (answer in Appendix)

11.6 ⭐ Using CTEs, list categories whose average product price is above the overall average product price (name each step).

Group C — Recursive: top-down

11.7 Write a recursive CTE that lists all categories with their depth (1 for top-level). (answer in Appendix)

11.8 List all descendant categories of 'Electronics' (its whole subtree).

11.9 Produce an indented tree of the full category hierarchy (use repeat(' ', depth-1) || name).

11.10 ⭐ For each top-level category, count how many descendant categories it has (any depth).

Group D — Recursive: bottom-up & org chart

11.11 Write a recursive CTE giving the ancestor chain of 'Laptops' (Laptops → Computers → Electronics). (answer in Appendix)

11.12 Build the employee org chart with a level column, indented by level.

11.13 List all employees who are (directly or indirectly) under 'Evan Wright'.

11.14 ⭐ For each employee, show their management chain as a string (e.g., Kevin < Julia < Diana). (Hint: accumulate a path in the recursive term.)

Group E — Safety & correctness

11.15 Explain why a recursive CTE could loop forever, and name two ways to prevent it. (answer in Appendix)

11.16 What happens if you use UNION instead of UNION ALL in a recursive CTE? When might UNION actually be desirable?

11.17 ⭐ Add a depth cap (WHERE depth < 10) to your category-tree query and explain why it's a reasonable safety net even when the data has no cycles.

Group F — CTE vs subquery

11.18 Take exercise 9.9 (average order value via derived table) and rewrite it with a CTE. Which reads better, and why?

11.19 ⭐ When is a CTE genuinely better than a subquery (name three situations)? When does it not matter?

Group G — Progressive project

11.20 Refactor one of your complex queries into a named multi-CTE pipeline.

11.21 Traverse a hierarchy in your domain with a recursive CTE (descendants or ancestors, with a depth column). If you lack one, add a self-referencing parent column first.

Self-check. If you can write the anchor/UNION ALL/recursive-term structure from memory and walk a hierarchy both directions, you've got one of SQL's rarest skills. Next: window functions.