Data Analysis & SQL · Practitioner lesson 18 of 19

SQL CTE Example: Find Above-Average Customers

Break a hard question into named steps with WITH, then compare each group to the overall average.

Why this matters

Hard questions get easier when you split them into steps. A CTE lets you name each step and read the query like a story.

WITH Gives a Query a Name

A CTE is a named mini-query. You write it with WITH, then use its name like a table. Long queries become easier to read.

In real life: WITH gives a small query a name, like customer_spend. Then you use that name like a normal table.

WITH customer_spend AS (
  SELECT customer_name, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_name
)
SELECT * FROM customer_spend;

Solve It in Two Steps

First write the query that totals each customer. Then use that result in the final SELECT to filter or rank.

In real life: Build it in two steps. First total each customer. Then compare those totals in the final SELECT.

A Query in Brackets Gives One Value

A query in brackets, like (SELECT AVG(total_spend) FROM customer_spend), can sit inside WHERE. Every row is then compared to one number.

In real life: A query in brackets can give back one value, such as the average. You can compare every row to it in WHERE.

Your practice task

Management wants to reward customers who spend more than the typical customer. Using only COMPLETED orders, total the spend per customer as total_spend, then return customer_name and total_spend for customers whose total is above the average customer total. Show the biggest spenders first.

How to do it

Step 1: total the completed spend for each customer.
Step 2: work out the average of those totals.
Step 3: keep only customers above that average, biggest first.
Build step 1 as a named query, then press Run proof.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: CTE Pattern

Name each stage of your analysis. Easier to read, test, and debug than nested sub-queries.

WITH step_one AS (
  SELECT ... FROM ... GROUP BY ...
)
SELECT ... FROM step_one
WHERE x > (SELECT AVG(x) FROM step_one);

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL