SQL practice

SQL CTE practice questions

6 problems, ordered from easiest. They run in your browser.

Start with Gaps in order_id sequence

The idea in a minute

A CTE is a named step written with WITH. Instead of nesting one query inside another, you write the first step, give it a name, and use the name in the next step.

The result is the same, but a long query reads top to bottom like a recipe. The problems here ask you to split a question into steps before writing code.

Example: Customers above the average spend

CREATE TABLE orders AS SELECT * FROM (VALUES (1,'Asha',450),(2,'Ravi',300),(3,'Meera',900),(4,'Asha',150),(5,'Ravi',250)) t(order_id, customer, amount);
WITH totals AS (
  SELECT customer, SUM(amount) AS spent FROM orders GROUP BY customer
)
SELECT customer, spent
FROM totals
WHERE spent > (SELECT AVG(spent) FROM totals)
ORDER BY spent DESC

What it prints

customer  spent
Meera     900

What to watch for

  • Write each step so it returns exactly the columns the next step needs.
  • A CTE exists only for the one query that follows it.
  • If a step is wrong, run that step alone to check it.

CTEs problems

  1. Gaps in order_id sequenceintermediate · Locked
  2. Employee hierarchy under Aliceintermediate · Locked
  3. Customers above average spendintermediate · Locked
  4. Peak parallel batch workersadvanced · Locked
  5. Spread sales periods across yearsadvanced · Locked
  6. Possible flight routesadvanced · Locked

Want a guided order across every topic? Open the practice sheets or the SQL hub.