Raj needs all sales where the revenue is above the average for that salesperson's region — not the overall average, the per-region average. This requires knowing each region's average first, then using it to filter. That is a two-step problem. Subqueries and CTEs solve it.

What a subquery is

A subquery is a SELECT query nested inside another query. The inner query runs first, produces a result, and the outer query uses that result. Subqueries can appear in WHERE, FROM, or SELECT clauses.

Subquery in WHERE

-- Find all orders above the overall average revenue
SELECT Salesperson, Revenue
FROM SalesFact
WHERE Revenue > (
  SELECT AVG(Revenue) FROM SalesFact
);

Subquery in FROM

A subquery in FROM creates a temporary table (called a derived table) that the outer query selects from. This is how you build multi-step aggregations.

SELECT Region, TotalRevenue
FROM (
  SELECT Region, SUM(Revenue) AS TotalRevenue
  FROM SalesFact
  GROUP BY Region
) AS RegionTotals
WHERE TotalRevenue > 50000;

Common Table Expressions (CTEs)

A CTE does the same thing as a subquery in FROM, but is defined before the main query using WITH. The result is dramatically more readable — each step is named and visible at the top of the query.

WITH RegionTotals AS (
  SELECT Region, SUM(Revenue) AS TotalRevenue
  FROM SalesFact
  GROUP BY Region
)
SELECT Region, TotalRevenue
FROM RegionTotals
WHERE TotalRevenue > 50000;
💡
Analyst tip: Always prefer CTEs over deeply nested subqueries when writing queries that others will read or that you will maintain. The WITH block reads like a story — each named step explains what it does before the main query uses it.

Multiple CTEs

WITH
RegionTotals AS (
  SELECT Region, SUM(Revenue) AS RegRevenue
  FROM SalesFact GROUP BY Region
),
TopRegions AS (
  SELECT Region FROM RegionTotals
  WHERE RegRevenue > 50000
)
SELECT s.*
FROM SalesFact s
INNER JOIN TopRegions t ON s.Region = t.Region;
⚠️
Common mistake: Adding a comma after the last CTE before the main SELECT. WITH cte1 AS (...), cte2 AS (...), SELECT... — that trailing comma errors. Only commas between CTEs, not after the last one.
🎯 Your Challenge

Using a CTE, find all salespersons whose total revenue is above the average total revenue across all salespersons. Step 1: CTE to calculate each salesperson's total. Step 2: Main query to filter to those above the average of those totals.

FAQ

In most databases, CTEs and equivalent subqueries produce the same execution plan — performance is identical. The advantage of CTEs is purely readability and maintainability, not speed. Some databases (like PostgreSQL) can materialise CTEs, which can be faster or slower depending on context.
Yes — CTEs are evaluated in order and each one can reference any CTE defined before it in the WITH block. This is exactly what the multiple CTE example above does (TopRegions references RegionTotals).
`