Search⌘ K
AI Features
Home
Courses
Data Science Bootcamp

Subqueries and CTEs

Explore how to decompose complex SQL queries into manageable parts using subqueries and common table expressions. This lesson teaches you to create intermediate result tables, perform correlated scalar subqueries, derived tables, and filters within WHERE clauses, enabling you to analyze product pricing against category averages accurately. By the end, you'll understand query organization, readability with CTEs, and practical techniques to filter and compare grouped data in SQL.

We'll cover the following...

Which products are priced above their category’s average price? A flat query can return product_name, category, and price, but it cannot answer that question until it also has a category-average value to compare against.

The intermediate result we need is a table with one row per category, such as Books with its average price and Electronics with its average price. Query decomposition means splitting a multi-step question into intermediate result tables that each answer one smaller question. A subquery is a query nested inside another SQL query. We can plan the SQL by first writing down the intermediate table we wish existed, then writing the nested query that produces it.

The complete pipeline in this lesson creates a small products table, calculates average prices, compares each product to the relevant average, and returns the products that pass the comparison. Each code example creates its own data because no earlier query result persists.

A scalar subquery in SELECT

The first form puts the intermediate calculation beside each product row. A scalar subquery returns exactly one value, so SELECT can place that value in one output column.

For a product in category Books, the nested query filters p2 to Books rows and calculates one average. For a product in category Electronics, the same nested query calculates the average from Electronics rows instead. The subquery is correlated because its WHERE condition refers to p1.category from the outer query.

SQL
DROP TABLE IF EXISTS products;
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  category TEXT NOT NULL,
  product_name TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);
INSERT INTO products (product_id, category, product_name, price) VALUES
  (1, 'Books', 'SQL for Beginners', 25.00),
  (2, 'Books', 'Data Science Handbook', 40.00),
  (3, 'Books', 'Pocket Dictionary', 15.00),
  (4, 'Electronics', 'Wireless Mouse', 30.00),
  (5, 'Electronics', 'USB-C Hub', 45.00),
  (6, 'Electronics', 'Noise-Cancelling Headphones', 120.00),
  (7, 'Stationery', 'Pen Set', 9.00),
  (8, 'Stationery', 'Notebook', 18.00);
SELECT
  p1.product_name,
  p1.category,
  p1.price,
  (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
  ) AS category_avg_price
FROM products p1
WHERE p1.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = p1.category
)
ORDER BY p1.category, p1.price DESC;

  • Lines 1–18: The code recreates the products table and inserts eight products across three categories.

  • Lines 24–28: The ...