Free SQL queries lessonsSQL queries · Stage 4 · Subqueries and window functions · Step 3

Build the answer in steps with WITH

Name an intermediate result with a CTE and reuse it. About 9 minutes.

Give the first answer a name

"Which cities earn more than the average city?" needs two layers: first the total for each city, then the average of those totals, then the comparison. Written as nested subqueries, it reads inside-out.

WITH lets you write the first layer on top and give it a name, then use that name like a table below. It is called a common table expression, or CTE, and it exists only for the one statement that follows.

WITH name AS (...): a named step, read top to bottom.

In code

WITH totals AS (
  SELECT city, SUM(amount) AS revenue
  FROM orders
  GROUP BY city
)
SELECT city, revenue
FROM totals;

Reuse it, or chain several

The named step can be used more than once in the query below it: once in FROM and once in a subquery, as in the picture. You can also chain: separate several steps with commas and let a later one use an earlier one.

A CTE doesn't store anything. It's a way of writing, so you can read each layer on its own.

A CTE is not saved: it exists for that one statement only.

In code

WITH totals AS (...),
     big AS (
       SELECT city FROM totals
       WHERE revenue > 1000
     )
SELECT * FROM big;

Cities above the average city total

orders

id	city	amount
1	Pune	500
2	Delhi	300
3	Pune	700
4	Mumbai	200
5	Mumbai	400
6	Delhi	100

Step one, the CTE: Pune's orders add up to 1200.

Move 1 of 6

Quick check

What is a CTE (a WITH clause)?

AA named query that exists only for the statement it belongs to
BA table saved in the database
CA faster kind of join

Show the answer

A named query that exists only for the statement it belongs to. It's a name for an intermediate result, usable like a table in the query below.

Your problem

Top-heavy departments

The employees table has one row per employee. Show each department whose total salary is above the average of the departments' total salaries, with its total as payroll. Order by payroll, highest first, then by department.

Tables

employees(id, name, dept, salary)

Up to 2,000 employees

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.