CSE Roadmap

Common tier · about 3 hours · 5 parts

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.