Subqueries and CTEs

By now you can filter, aggregate, and join. But some questions need an answer to compute the answer - "which products cost more than average?" needs the average first, then the comparison. That is a subquery: a query nested inside another. And when you stack several such steps, a CTE (Common Table Expression, written with WITH) lets you name each step so the whole thing stays readable. Both run against the same six Daybreak tables you already know.

A scalar subquery is a query that returns exactly one value - one number, one date, one name - which you then use anywhere a single value is allowed. The classic case: compare each row against an aggregate of the whole table, like "price above the average price". SQLite runs the inner query first, gets one number, then runs the outer query using it.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

SELECT name, price,
       (SELECT ROUND(AVG(price),2) FROM products) AS avg_price
FROM products
ORDER BY price DESC;

The other everyday shape: a subquery that returns many values, which you match against with IN. Instead of "is this price above one number", it is "is this customer in the set of customers who ever had a refund". The inner query builds the list; the outer query keeps rows whose value appears in it.

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status='refunded');

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE status='refunded')
ORDER BY name;

Subqueries are powerful, but stack a few and they nest inward - you read a big query from the deepest parentheses outward, which is exactly backwards from how you think. A CTE fixes this. WITH name AS (query) lets you name an intermediate result, then use that name like a table in the query below. The whole thing reads top-to-bottom.

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity*oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  WHERE o.status='completed'
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(s.total,2) AS total
FROM spend s
JOIN customers c ON c.customer_id = s.customer_id
ORDER BY s.total DESC
LIMIT 5;

Same answer as Part 1 - products priced above the average - but let us feel the difference between the nested form and the CTE form. You will see the identical result, and you will read the second one far more easily. This is the habit that pays off every day you write SQL.

WITH avg_p AS (
  SELECT AVG(price) AS a FROM products
)
SELECT p.name, p.price
FROM products p, avg_p
WHERE p.price > avg_p.a
ORDER BY p.price DESC;

• Write a scalar subquery that lists customers who signed up before the earliest order date. (Hint: (SELECT MIN(order_date) FROM orders).)
• Use IN to find every customer who has an active subscription. (Hint: the inner query pulls customer_id from subscriptions WHERE cancel_date IS NULL.)
• Take any two-step query you wrote in b3-b5 and rewrite it with a WITH CTE. Read both versions - notice which one you would rather hand to a teammate.
• Chain two CTEs: one that sums spend per customer, a second that keeps only those above 100, then select their names.
• Bring one question that needs "the value above/below an aggregate" to session b7 - window functions will let you compute per-group ranks without a subquery at all.

This session teaches the subquery and CTE core of the major free SQL curricula, run on live Daybreak data instead of screenshots. Graded problem sets and certificates stay on the official sites (linked below). This page covers: