Understanding Subqueries and CTEs in SQL

Sometimes a single SQL query isn’t enough. To answer one question, you may first need to answer another. That’s where subqueries and Common Table Expressions (CTEs) come in. They let you nest logic, reuse results, and keep queries readable.

Exploring both concepts using a retail sales database with customers, products, and sales.

Retail Dataset
customers

customer_id	name	city
1	Alice Kariuki	Nairobi
2	Brian Oduor	Kisumu
3	Carol Wanjiru	Mombasa
4	Daniel Mwangi	Eldoret

products

sale_id	customer_id	product_id	quantity	sale_date	discount
1	1	101	1	2026-08-01	NULL
2	2	102	2	2026-08-02	10
3	3	103	1	2026-08-03	NULL
4	4	104	5	2026-08-04	5

sales

sale_id	customer_id	product_id	quantity	sale_date	discount
1	1	101	1	2026-08-01	NULL
2	2	102	2	2026-08-02	10
3	3	103	1	2026-08-03	NULL
4	4	104	5	2026-08-04	5

1. Subqueries

A subquery is a query inside another query. The inner query runs first, and its result is used by the outer query.

Example 1: Sales above the average discount

SELECT sale_id, customer_id, discount
FROM sales
WHERE discount > (SELECT AVG(discount) FROM sales WHERE discount IS NOT NULL);

Output:

sale_id	customer_id	discount
2	2	10

The subquery calculates the average discount, and the outer query finds sales above it.

Example 2: Highest product price

SELECT product_id, product_name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Output:

product_id	product_name	price
101	Laptop	75000

Example 3: Customers who bought more than Alice’s order quantity

SELECT sale_id, customer_id, quantity
FROM sales
WHERE quantity > (
    SELECT quantity
    FROM sales
    WHERE customer_id = 1
);

Output:

sale_id	customer_id	quantity
4	4	5

2. Common Table Expressions (CTEs)

A CTE is a temporary result set defined with WITH. It makes queries easier to read and reuse.

Example 1: Sales above the average discount (CTE way)

WITH avg_discount AS (
    SELECT AVG(discount) AS avg_disc
    FROM sales
    WHERE discount IS NOT NULL
)
SELECT sale_id, customer_id, discount
FROM sales, avg_discount
WHERE discount > avg_disc;

Output:

sale_id	customer_id	discount
2	2	10

Example 2: Customers with multiple purchases

WITH customer_sales AS (
    SELECT customer_id, COUNT(*) AS total_sales
    FROM sales
    GROUP BY customer_id
)
SELECT c.name, cs.total_sales
FROM customers c
JOIN customer_sales cs ON c.customer_id = cs.customer_id
WHERE cs.total_sales > 1;

Output:

name	total_sales
Alice Kariuki	2

Example 3: Products priced above the average

WITH avg_price AS (
    SELECT AVG(price) AS avg_price
    FROM products
)
SELECT product_name, price
FROM products, avg_price
WHERE price > avg_price;

Output:

product_name	price
Laptop	75000

General Notes
Subqueries → Useful for filtering, comparisons, and nested logic.

CTEs → Useful for readability, reusability, and breaking down complex queries.

Both help you write cleaner, more powerful SQL.

Key Takeaway
Subqueries and CTEs are two sides of the same coin:

Use subqueries when you need quick, inline calculations.

Use CTEs when you want clarity and reusability in complex queries.

Together, they make SQL flexible enough to handle real-world reporting needs - whether you’re analyzing discounts, product prices, or customer purchases.