SQL Intermediate Lesson 1: Common Table Expressions (WITH)

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 1 of 10

Long queries with subqueries inside subqueries are hard to read and harder to fix. A Common Table Expression gives a subquery a name at the top, and the main query reads like a story.

WITH sales AS (
  SELECT c.region, o.qty * p.price AS amount
  FROM orders o
  JOIN customers c USING (customer_id)
  JOIN products  p USING (product_id)
  WHERE o.status <> 'Cancelled'
)
SELECT region, ROUND(SUM(amount), 2) AS total
FROM sales
GROUP BY region
ORDER BY total DESC;

Chain several CTEs

Separate them with commas; each can use the ones before it:

WITH sales AS (...),
     per   AS (SELECT customer, SUM(amount) AS total FROM sales GROUP BY customer)
SELECT customer, total
FROM per
WHERE total > (SELECT AVG(total) FROM per);

That is “customers above the average customer”, which needs the per-customer totals twice. Without a CTE you’d repeat the whole grouping query.

Common mistakes

• A comma before the main SELECT after the last CTE gives a syntax error.
• Expecting a CTE to be stored: it lives only for that one query. For reuse, create a VIEW.
• Integer division in shares: in SQLite and SQL Server, 5/2 = 2. Multiply by 100.0 first.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.