Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Write a simple CTE (15 minutes)
Rewrite SELECT title FROM books WHERE price > (SELECT AVG(price) FROM books); using a CTE naming the average price.
Done when: a correct WITH avg_price AS (...) version is given.
Hint
WITH avg_price AS (SELECT AVG(price) AS avg FROM books) SELECT title FROM books, avg_price WHERE books.price > avg_price.avg;
Easy 2: Name the two parts (15 minutes)
Name the two required parts of a recursive CTE.
Done when: base case and recursive case are both correctly named.
Hint
Base case and recursive case.
Easy 3: Reference a CTE twice (15 minutes)
Explain why referencing a CTE's name twice in the main query does not require writing its definition twice.
Done when: the explanation correctly notes the CTE is defined once and simply referenced by name afterward.
Hint
The CTE's definition is evaluated once and simply named; every later reference just points at that same named result.
Medium
Medium 1: CTE with a join (25 minutes)
Write a CTE naming each customer's total order count, then join it to customers in the main query to show full_name alongside the count.
Done when: a correct CTE plus a joining main query is given.
Hint
WITH order_counts AS (SELECT customer_id, COUNT(*) AS c FROM orders GROUP BY customer_id) SELECT cu.full_name, oc.c FROM customers cu JOIN order_counts oc ON cu.customer_id = oc.customer_id;
Medium 2: Recursive CTE for an employee hierarchy (25 minutes)
Using employees (employee_id, manager_id, name), write a recursive CTE finding every employee who reports, directly or indirectly, to employee 1.
Done when: a correct recursive CTE with a base case on employee 1 and a recursive case joining on manager_id is given.
Hint
Base case: SELECT * FROM employees WHERE employee_id = 1; recursive case: join employees to the CTE on employees.manager_id = cte.employee_id.
Medium 3: Explain the termination condition (25 minutes)
Explain exactly what condition causes the recursive CTE from Medium 2 to stop adding more rows.
Done when: the explanation correctly states recursion stops once a step finds no employee whose manager_id matches an employee found in the previous step.
Hint
Recursion stops once no remaining employee's manager_id matches any employee_id found in the most recent step.
Hard
Hard 1: CTE versus temporary table (30 minutes)
Explain one concrete difference between a CTE and creating an actual temporary table holding the same intermediate result.
Done when: a correct difference is given, such as a CTE existing only for the duration of one query, while a temporary table persists until explicitly dropped or the session ends.
Hint
A CTE is scoped to the single query it appears in and disappears afterward; a temporary table persists and can be queried again later in the same session.
Hard 2: Depth-limited recursion (35 minutes)
Modify the subcategories recursive CTE to stop after at most 3 levels deep, even if the real hierarchy goes deeper.
Done when: a correct version is given that tracks a depth counter in the CTE and filters on it, such as WHERE depth < 3.
Hint
Add a depth column starting at 0 in the base case, incrementing by 1 in the recursive case, and add WHERE depth < 3 to the recursive case's join.
Hard 3: Detecting a cycle before it causes an infinite loop (40 minutes)
Describe how a recursive CTE could detect and stop a cycle in the underlying data, such as employee A reporting to B and B reporting back to A, that would otherwise cause infinite recursion.
Done when: a correct approach is described, such as tracking the path of visited IDs and refusing to revisit one already seen.
Hint
Carry an array or string of every category_id visited so far through each recursive step, and add a condition in the recursive case refusing to join to any ID already present in that path.