A team of one made the above-average pay list

See the problem

HR keeps a list of the departments paid above the company average. Staff on probation, hired on or after 1 May 2026, don’t count yet. The list came from this query:

The above-average pay list

SELECT d.name, round(avg(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.hired_on < '2026-05-01'
GROUP BY d.name
HAVING avg(e.salary) > (
  SELECT avg(dept_avg)
  FROM (
    SELECT avg(salary) AS dept_avg
    FROM employees
    WHERE hired_on < '2026-05-01'
    GROUP BY department_id
  ) AS per_department
)
ORDER BY d.name;

It listed Engineering, the one department above average, and the list was right.

Then the company started a Data team: Kai, paid 6000.00. With Kai, the company average is 6209.09, so he’s below it. But the next list said Data was above average.

Define the task

List the departments whose average salary is above the company’s average salary: name and avg_salary, rounded to cents, by name.

• The company average is over people: each person counts once.
• Staff hired on or after 2026-05-01 are on probation. They don’t count anywhere, in a department or in the company average.

Pause and think

⏸ Pause and think

The query averages the four department averages. Then Data, with one person, joins. How much does Kai’s salary count in that average, compared with each Engineering salary?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

The average of the averages gives each department one vote. Kai’s one salary counts as much as all four Engineering salaries together.

Hint 2 of 3

The company average is avg(salary) over the staff rows, not over the department averages.

Hint 3 of 3

The probation rule must hold in two places: the departments and the company average. Name the staff once, WITH staff AS (…), and read staff in both.

Understand the idea

A CTE gives a step of your query a name. CTE stands for common table expression. You write it in WITH, before the main query: WITH staff AS (SELECT …). After that, the main query reads staff as if it were a table. It’s like a recipe that says “make the sauce”, then uses “the sauce” twice, without repeating its steps.

★Name it once, and every part reads the same rows

A subquery written twice can drift: change one copy, forget the other. A CTE is written once, so every part that reads it sees the same rows, with the same rules.

WITH reads top to bottom. Each CTE can read the ones named before it: WITH a AS (…), b AS (SELECT … FROM a). A long query becomes a list of small, named steps, which is easier to read and to test. Run the CTE’s own SELECT alone to check one step.

A CTE lasts for one statement. The next statement can’t see it: “relation “staff” does not exist”. To keep a query for later, make a view.

Postgres runs a CTE you read twice once. Since Postgres 12, a CTE read once is folded into the main query, as if you had written a subquery. A CTE read twice is worked out once and kept for this query. WITH staff AS MATERIALIZED (…) or NOT MATERIALIZED (…) asks for one or the other.

Watch the pay list run on a few people. Then type your own, like a department of one:

The pay list, step by step

⤢ Full view

1. WITH staff AS (…)
2. (SELECT avg(salary) FROM staff)
3. FROM staff GROUP BY department
4. HAVING avg(salary) > the average
5. SELECT name, round(avg(salary), 2)
6. ORDER BY name

employees

Kept?	department	salary	hired_on
✓kept	Engineering	9500.00	2023-02-01
✓kept	Engineering	5400.00	2025-09-01
✓kept	Support	4300.00	2025-03-17
✗dropped	Support	30000.00	2026-10-01
✓kept	Sales	6800.00	2024-07-29
✓kept	Sales	4900.00	2026-01-05
✓kept	Data	6000.00	2024-03-01

Step 1 of 6. WITH staff runs first. It keeps the people hired before 2026-05-01, and leaves out the one still on probation. From now on, the query reads staff. Question: Next, the company average reads staff. Will it count the Support hire paid 30000.00, still on probation?

What happens next? Next, the company average reads staff. Will it count the Support hire paid 30000.00, still on probation?

Yes: they work at the companyNo: staff left them outJust show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1WITH staff AS (
2  SELECT d.name AS department, e.salary
3  FROM employees e
4  JOIN departments d ON d.department_id = e.department_id
5  WHERE e.hired_on < '2026-05-01'
6)
7SELECT department AS name, round(avg(salary), 2) AS avg_salary
8FROM staff
9GROUP BY department
10HAVING avg(salary) > (SELECT avg(salary) FROM staff)
11ORDER BY name;

Try your own input

Employees: department, salary, hired_onEngineering, 9500, 2023-02-01 Engineering, 5400, 2025-09-01 Support, 4300, 2025-03-17 Support, 30000, 2026-10-01 Sales, 6800, 2024-07-29 Sales, 4900, 2026-01-05 Data, 6000, 2024-03-01Show it

Climb the ladder

All three give Engineering on the store’s data. What changes is what the company average counts, and whether the probation rule holds everywhere.

Okay

The average of the department averages

The idea: Average each department in a subquery, then average those averages.

It looks right because, on the store’s data, both averages put the line between Engineering and the rest.

SELECT d.name, round(avg(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.hired_on < '2026-05-01'
GROUP BY d.name
HAVING avg(e.salary) > (
  SELECT avg(dept_avg)
  FROM (
    SELECT avg(salary) AS dept_avg
    FROM employees
    WHERE hired_on < '2026-05-01'
    GROUP BY department_id
  ) AS per_department
)
ORDER BY d.name;

Gets right

Four departments, as in the store

Gets wrong

A small department

Why

Each department gets one vote in the average, so a department of one counts as much as a big one.

Where it breaks: the Data team of one joins. The average of the five averages is 5951.67, and Kai’s 6000.00 is above it, so Data is listed. Okay passes 1 of the 3 checks below.

Better

avg(salary) over employees

The idea: Compare each department with the average over all employees.

The gap in Okay: it averaged departments, not people.

Highlighted lines are new in this rung.

SELECT d.name, round(avg(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.hired_on < '2026-05-01'
GROUP BY d.name
HAVING avg(e.salary) > (SELECT avg(salary) FROM employees)
ORDER BY d.name;

Gets right

A small departmentwas Four departments, as in the store

Gets wrong

A new hire on probation

Why

The outer WHERE doesn’t reach into the subquery, so the company average counts people on probation.

What changed from the previous rung

SELECT d.name, round(avg(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.hired_on < '2026-05-01'
GROUP BY d.name
HAVING avg(e.salary) > (
  SELECT avg(dept_avg)
  FROM (
    SELECT avg(salary) AS dept_avg
    FROM employees
    WHERE hired_on < '2026-05-01'
    GROUP BY department_id
  ) AS per_department
)
HAVING avg(e.salary) > (SELECT avg(salary) FROM employees)
ORDER BY d.name;

Where it breaks: Nia joins Support on 1 October at 30000.00. The departments leave her out, but the company average counts her and jumps to 8191.67, so Engineering drops off. Better passes 2 of 3.

Best

WITH staff, read twice

The idea: Name the staff past probation once, and read staff for the departments and the company average.

The gap in Better: the rule was written in one place but needed in two.

Highlighted lines are new in this rung.

WITH staff AS (
  SELECT d.name AS department, e.salary
  FROM employees e
  JOIN departments d ON d.department_id = e.department_id
  WHERE e.hired_on < '2026-05-01'
)
SELECT department AS name, round(avg(salary), 2) AS avg_salary
FROM staff
GROUP BY department
HAVING avg(salary) > (SELECT avg(salary) FROM staff)
ORDER BY name;

Gets right

Every checkwas A small department

Gets wrong

Nothing the checks try

Why

The probation rule is written once, in staff. Both parts read staff, so they always count the same people.

What changed from the previous rung

SELECT d.name, round(avg(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.hired_on < '2026-05-01'
GROUP BY d.name
HAVING avg(e.salary) > (SELECT avg(salary) FROM employees)
ORDER BY d.name;
WITH staff AS (
  SELECT d.name AS department, e.salary
  FROM employees e
  JOIN departments d ON d.department_id = e.department_id
  WHERE e.hired_on < '2026-05-01'
)
SELECT department AS name, round(avg(salary), 2) AS avg_salary
FROM staff
GROUP BY department
HAVING avg(salary) > (SELECT avg(salary) FROM staff)
ORDER BY name;

When Best isn’t best: for a list used every day, a view keeps the rule in one place for every query, not just this one. And when the CTE is big but each part needs only a few rows, check EXPLAIN: NOT MATERIALIZED can be faster.

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed. Show the steps under the editor runs each CTE first, then the main query.

The above-average pay list

Ctrl + Enter runs

The tables

• departments (department_id, name)
• employees (employee_id, name, department_id, manager_id, salary, hired_on)

-- Departments paid above the company average. Staff hired on or after 2026-05-01 don't count. WITH staff AS ( SELECT d.name AS department, e.salary, e.hired_on FROM employees e JOIN departments d ON d.department_id = e.department_id ) SELECT * FROM staff;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: no one on probation (row order matters)
2. ○Not run yet: A new Data team of one: Kai, paid 6000.00 (row order matters)
   What this check changes

   INSERT INTO departments (department_id, name) VALUES (5, 'Data');
   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Mensah', 5, 1, 6000.00, '2024-03-01');

3. ○Not run yet: Kai’s Data team, and Nia joins Support on 1 October at 30000.00, still on probation (row order matters)
   What this check changes

   INSERT INTO departments (department_id, name) VALUES (5, 'Data');
   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Mensah', 5, 1, 6000.00, '2024-03-01');
   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (12, 'Nia Brooks', 2, 1, 30000.00, '2026-10-01');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: average of the averages	Four departments	A small department	You really want each department to count once
Better: avg(salary) over employees	A small department	A new hire on probation	No rule leaves anyone out
Best: WITH staff, read twice	Every check	Nothing the checks try	Two parts of a query need the same rows

Common mistakes and edge cases

!A filter in one copy, not the other

When two parts of a query need the same rows, a rule written in one part is easy to miss in the other. The outer WHERE doesn’t reach into a subquery. Put the rule in one CTE, and read the CTE in both parts.

• The average of averages isn’t the average. It gives every group one vote. Average the rows unless the task says otherwise.
• A CTE lasts one statement. A second statement can’t read it. Use a view, or a temporary table, to keep it longer.
• Order matters inside WITH. A CTE can read the ones before it, not after.
• A CTE can hide a table with the same name. WITH orders AS (…) means every orders in that query is the CTE. Pick a new name.
• A CTE isn’t always faster. Read once, it’s folded into the query. Read twice, it’s kept, which saves work but can stop Postgres using an index inside it. Check with EXPLAIN, covered in the Advanced level.
• One aggregate inside another needs two steps. avg(avg(salary)) stops with “aggregate function calls cannot be nested”. Average in a CTE, then average that.

Say it in the interview

Questions to ask first

• Is the company average over people, or over departments?
• Who doesn’t count: people on probation, contractors, people who left?
• Do those rules apply to the company average too?

A 60-second walkthrough

“I name the staff who count in a CTE: employees joined to their department, past probation. Then the main query reads staff twice. It groups staff by department, and HAVING compares each average with the average over all of staff. Because both parts read staff, the probation rule is written once and can’t be missed in one place. Postgres works staff out once, since it’s read twice.”

Likely follow-ups

• CTE or subquery? Same result. A CTE reads top to bottom and can be read twice. A subquery is fine for a small step used once.
• CTE or temporary table? A CTE lasts one statement. A temporary table lasts the session, can have an index, and suits a big result used by many queries.
• Is a CTE an optimization fence? It was before Postgres 12, which always ran it once and kept it. Now a CTE read once is folded in, and MATERIALIZED asks for the old way.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. Each category’s share of sales (easy)

The sales deck shows each category’s revenue and its share of all sales. List every category, with 0 for one that sold nothing: category, revenue, and share, a percentage rounded to 1 decimal. Cancelled orders don’t count. Biggest revenue first, then by category.

Hint 1 of 3

Name the category totals once: WITH sales AS (…). Then the share is revenue / (SELECT sum(revenue) FROM sales).

Hint 2 of 3

Don’t divide by sum(total) from orders. An order’s total can include more than its items, like a delivery fee.

Hint 3 of 3

A category nobody bought from has no item rows. Start from products, LEFT JOIN what was sold, and use coalesce(sum(…), 0).

Each category’s share of sales

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)

SELECT p.category, i.quantity * i.unit_price AS amount FROM order_items i JOIN products p ON p.product_id = i.product_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: each order’s total is the sum of its items (row order matters)
2. ○Not run yet: Hugo’s order 114 adds a 5.00 delivery fee to its total (row order matters)
   What this check changes

   UPDATE orders SET total = total + 5.00 WHERE order_id = 114;

3. ○Not run yet: The delivery fee, and a new toys category whose kite nobody has bought (row order matters)
   What this check changes

   UPDATE orders SET total = total + 5.00 WHERE order_id = 114;
   INSERT INTO products (product_id, name, category, price) VALUES (9, 'Kite', 'toys', 15.00);

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

Divide by the orders’ totals

The idea: Total each category in a CTE, and divide by the sum of the orders’ totals.

Okay passes 1 of 3. When Hugo’s order gets a 5.00 delivery fee, the shares add up to 99.2%.

WITH sales AS (
  SELECT p.category, sum(i.quantity * i.unit_price) AS revenue
  FROM order_items i
  JOIN orders o ON o.order_id = i.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.category
)
SELECT category, revenue,
  round(100 * revenue / (SELECT sum(total) FROM orders WHERE status <> 'cancelled'), 1) AS share
FROM sales
ORDER BY revenue DESC, category;

Gets right

Totals equal to their items, as in the store

Gets wrong

A delivery fee in a total

Why

The share’s top and bottom come from two different tables, which agree only while every total is the sum of its items.

Better

Divide by the CTE’s own sum

The idea: Divide each revenue by the sum of the same CTE.

The gap in Okay: the top and the bottom of the share counted different things.

Highlighted lines are new in this rung.

WITH sales AS (
  SELECT p.category, sum(i.quantity * i.unit_price) AS revenue
  FROM order_items i
  JOIN orders o ON o.order_id = i.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.category
)
SELECT category, revenue,
  round(100 * revenue / (SELECT sum(revenue) FROM sales), 1) AS share
FROM sales
ORDER BY revenue DESC, category;

Gets right

A delivery fee in a total

Gets wrong

A category nothing was sold from

Why

sales starts from the items sold, so a category with no sales has no row.

What changed from the previous rung

WITH sales AS (
  SELECT p.category, sum(i.quantity * i.unit_price) AS revenue
  FROM order_items i
  JOIN orders o ON o.order_id = i.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.category
)
SELECT category, revenue,
  round(100 * revenue / (SELECT sum(total) FROM orders WHERE status <> 'cancelled'), 1) AS share
  round(100 * revenue / (SELECT sum(revenue) FROM sales), 1) AS share
FROM sales
ORDER BY revenue DESC, category;

Where it breaks: the shop adds a toys category with a kite nobody has bought. The deck should show toys at 0, but Better leaves it out. Better passes 2 of 3.

Best

Two CTEs: what was sold, then every category

The idea: Name the sold items first, then LEFT JOIN them to every product.

The gap in Better: a category with no sales had no row.

Highlighted lines are new in this rung.

WITH sold AS (
  SELECT i.product_id, i.quantity * i.unit_price AS amount
  FROM order_items i
  JOIN orders o ON o.order_id = i.order_id
  WHERE o.status <> 'cancelled'
),
sales AS (
  SELECT p.category, coalesce(sum(s.amount), 0) AS revenue
  FROM products p
  LEFT JOIN sold s ON s.product_id = p.product_id
  GROUP BY p.category
)
SELECT category, revenue,
  round(100 * revenue / (SELECT sum(revenue) FROM sales), 1) AS share
FROM sales
ORDER BY revenue DESC, category;

Gets right

Every checkwas A delivery fee in a total

Gets wrong

Nothing the checks try

Why

sold drops cancelled orders before the LEFT JOIN, so every category stays, with 0 when nothing matched.

What changed from the previous rung

WITH sales AS (
  SELECT p.category, sum(i.quantity * i.unit_price) AS revenue
WITH sold AS (
  SELECT i.product_id, i.quantity * i.unit_price AS amount
  FROM order_items i
  JOIN orders o ON o.order_id = i.order_id
  JOIN products p ON p.product_id = i.product_id
  WHERE o.status <> 'cancelled'
),
sales AS (
  SELECT p.category, coalesce(sum(s.amount), 0) AS revenue
  FROM products p
  LEFT JOIN sold s ON s.product_id = p.product_id
  GROUP BY p.category
)
SELECT category, revenue,
  round(100 * revenue / (SELECT sum(revenue) FROM sales), 1) AS share
FROM sales
ORDER BY revenue DESC, category;

When Best isn’t best: if every report needs the same “sold” rule, put it in a view, so each query doesn’t repeat it.

2. The top 3 spenders (medium)

The shop sends a thank-you card to its top 3 spenders, on orders that weren’t cancelled. If customers tie for third place, they all get one. List customer_id and spent, biggest first, then by customer_id.

Hint 1 of 3

Name each customer’s spend once: WITH spend AS (…). The rest of the query reads spend.

Hint 2 of 3

LIMIT 3 keeps 3 rows, even when the fourth ties with the third.

Hint 3 of 3

A customer is in the top 3 when fewer than 3 customers spent more: (SELECT count(*) FROM spend more WHERE more.spent > s.spent) < 3.

The top 3 spenders

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)

WITH spend AS ( SELECT customer_id, sum(total) AS spent FROM orders WHERE status <> 'cancelled' GROUP BY customer_id ) SELECT * FROM spend ORDER BY spent DESC;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Elif orders 74.50 more, so she ties Hugo for third (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 5, '2026-10-21', 'placed', 74.50);

3. ○Not run yet: Every order but Asha’s and Ben’s is cancelled, so two customers have spent anything (row order matters)
   What this check changes

   UPDATE orders SET status = 'cancelled' WHERE customer_id NOT IN (1, 2);

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

ORDER BY spent DESC LIMIT 3

The idea: Sort the spend and keep three rows.

Okay passes 2 of 3. When Elif spends 74.50 more, she ties Hugo at 142.99, and only one of them gets a card.

WITH spend AS (
  SELECT customer_id, sum(total) AS spent
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT customer_id, spent
FROM spend
ORDER BY spent DESC, customer_id
LIMIT 3;

Gets right

No tie for third, as in the store

Gets wrong

A tie for third

Why

LIMIT 3 keeps three rows, so a customer tied with the third is left out.

Better

At least the third row’s spend

The idea: Find the third biggest spend, and keep everyone at or above it.

The gap in Okay: LIMIT cut a tie.

Highlighted lines are new in this rung.

WITH spend AS (
  SELECT customer_id, sum(total) AS spent
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT customer_id, spent
FROM spend
WHERE spent >= (SELECT spent FROM spend ORDER BY spent DESC LIMIT 1 OFFSET 2)
ORDER BY spent DESC, customer_id;

Gets right

A tie for third

Gets wrong

Fewer than 3 customers

Why

With only two customers, OFFSET 2 finds no row, so the subquery is NULL, and spent >= NULL keeps no one.

What changed from the previous rung

WITH spend AS (
  SELECT customer_id, sum(total) AS spent
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT customer_id, spent
FROM spend
ORDER BY spent DESC, customer_id
LIMIT 3;
WHERE spent >= (SELECT spent FROM spend ORDER BY spent DESC LIMIT 1 OFFSET 2)
ORDER BY spent DESC, customer_id;

Where it breaks: every order but Asha’s and Ben’s is cancelled, so only two customers have spent anything. They should both get a card, but Better gives no rows. Better passes 2 of 3.

Best

Fewer than 3 spent more

The idea: Keep a customer when fewer than 3 customers spent more than they did.

The gap in Better: it needed a third row to exist.

Highlighted lines are new in this rung.

WITH spend AS (
  SELECT customer_id, sum(total) AS spent
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT customer_id, spent
FROM spend s
WHERE (SELECT count(*) FROM spend more WHERE more.spent > s.spent) < 3
ORDER BY spent DESC, customer_id;

Gets right

Every checkwas A tie for third

Gets wrong

Nothing the checks try

Why

Counting who spent more works for ties, and for any number of customers. spend is read twice, so Postgres works it out once.

What changed from the previous rung

WITH spend AS (
  SELECT customer_id, sum(total) AS spent
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY customer_id
)
SELECT customer_id, spent
FROM spend
WHERE spent >= (SELECT spent FROM spend ORDER BY spent DESC LIMIT 1 OFFSET 2)
FROM spend s
WHERE (SELECT count(*) FROM spend more WHERE more.spent > s.spent) < 3
ORDER BY spent DESC, customer_id;

When Best isn’t best: the count runs once for each customer, so it slows down with many customers. rank() OVER (ORDER BY spent DESC) <= 3, a window function from a later lesson, does it in one pass.

3. Each department’s top earner (medium)

The pay review starts with the best-paid person in each department, all of them if there’s a tie. List every department, with NULL for one with no one in it yet: department, name and salary, by department, then name.

Hint 1 of 3

salary IN (SELECT max(salary) … GROUP BY department_id) forgets which department each top came from.

Hint 2 of 3

Name each department’s top pay in a CTE: WITH top_pay AS (SELECT department_id, max(salary) …). Then join on both department_id and salary.

Hint 3 of 3

A department with no one has no row in employees, so it has none in top_pay either. Start from departments and LEFT JOIN.

Each department’s top earner

Ctrl + Enter runs

The tables

• departments (department_id, name)
• employees (employee_id, name, department_id, manager_id, salary, hired_on)

SELECT department_id, max(salary) AS top_pay FROM employees GROUP BY department_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Omar, in Support, gets a raise to 5600.00: the top pay in Operations (row order matters)
   What this check changes

   UPDATE employees SET salary = 5600.00 WHERE employee_id = 6;

3. ○Not run yet: Omar’s raise, and a new Data department with no one in it yet (row order matters)
   What this check changes

   UPDATE employees SET salary = 5600.00 WHERE employee_id = 6;
   INSERT INTO departments (department_id, name) VALUES (5, 'Data');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

salary IN (each department’s max)

The idea: Keep anyone whose salary is some department’s top pay.

Okay passes 1 of 3. When Omar, in Support, gets a raise to 5600.00, the top pay in Operations, he’s listed as a top earner in Support.

SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary IN (SELECT max(salary) FROM employees GROUP BY department_id)
ORDER BY department, e.name;

Gets right

No one paid another department’s top, as in the store

Gets wrong

A salary equal to another department’s top

Why

The list holds only amounts. It doesn’t say which department each top came from.

Better

Join a CTE of top pay

The idea: Name each department’s top pay, and join on the department and the salary.

The gap in Okay: the list forgot the department.

Highlighted lines are new in this rung.

WITH top_pay AS (
  SELECT department_id, max(salary) AS salary
  FROM employees
  GROUP BY department_id
)
SELECT d.name AS department, e.name, e.salary
FROM top_pay t
JOIN employees e ON e.department_id = t.department_id AND e.salary = t.salary
JOIN departments d ON d.department_id = t.department_id
ORDER BY department, e.name;

Gets right

A salary equal to another department’s top

Gets wrong

A department with no one in it

Why

top_pay comes from employees, so a department with no one has no row to join.

What changed from the previous rung

WITH top_pay AS (
  SELECT department_id, max(salary) AS salary
  FROM employees
  GROUP BY department_id
)
SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary IN (SELECT max(salary) FROM employees GROUP BY department_id)
FROM top_pay t
JOIN employees e ON e.department_id = t.department_id AND e.salary = t.salary
JOIN departments d ON d.department_id = t.department_id
ORDER BY department, e.name;

Where it breaks: a new Data department starts, with no one in it yet. The review should list it, but Better leaves it out. Better passes 2 of 3.

Best

From departments, LEFT JOIN the CTE

The idea: Start from every department, and LEFT JOIN its top pay and top earners.

The gap in Better: a department with no one had no row.

Highlighted lines are new in this rung.

WITH top_pay AS (
  SELECT department_id, max(salary) AS salary
  FROM employees
  GROUP BY department_id
)
SELECT d.name AS department, e.name, e.salary
FROM departments d
LEFT JOIN top_pay t ON t.department_id = d.department_id
LEFT JOIN employees e ON e.department_id = t.department_id AND e.salary = t.salary
ORDER BY department, e.name;

Gets right

Every checkwas A salary equal to another department’s top

Gets wrong

Nothing the checks try

Why

Every department has a row, and NULL shows where there’s no one to list.

What changed from the previous rung

WITH top_pay AS (
  SELECT department_id, max(salary) AS salary
  FROM employees
  GROUP BY department_id
)
SELECT d.name AS department, e.name, e.salary
FROM top_pay t
JOIN employees e ON e.department_id = t.department_id AND e.salary = t.salary
JOIN departments d ON d.department_id = t.department_id
FROM departments d
LEFT JOIN top_pay t ON t.department_id = d.department_id
LEFT JOIN employees e ON e.department_id = t.department_id AND e.salary = t.salary
ORDER BY department, e.name;

When Best isn’t best: rank() OVER (PARTITION BY department_id ORDER BY salary DESC) = 1 reads employees once, with no join back. A later lesson covers window functions.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputWhat does this give?

   WITH a AS (SELECT 2 AS x),
        b AS (SELECT x * 10 AS y FROM a)
   SELECT y FROM b;

   220It stops with an error: b can’t read aTwo rows: 2 and 20

   Check answer
2. Predict the outputThese are two statements, run one after the other. What does the second do?

   WITH big AS (SELECT * FROM orders WHERE total > 100)
   SELECT count(*) FROM big;
   
   SELECT * FROM big;

   It lists the big ordersIt gives no rowsIt lists every orderIt stops with an error: relation "big" does not exist

   Check answer
3. Spot the bugStaff on probation (hired on or after 1 May 2026) shouldn’t count anywhere. A new hire on probation with a big salary joins. What goes wrong?

   SELECT d.name, avg(e.salary)
   FROM employees e
   JOIN departments d ON d.department_id = e.department_id
   WHERE e.hired_on < '2026-05-01'
   GROUP BY d.name
   HAVING avg(e.salary) > (SELECT avg(salary) FROM employees);

   The new hire raises the company average, so a department can drop offNothing: WHERE leaves the new hire out everywhereIt stops with an errorThe new hire’s department gets listed twice

   Check answer
4. Multiple choiceIn Postgres 12 and later, what happens to a CTE that the query reads twice?

   It runs twiceIt stops with an errorIt runs once, and both places read the same rowsIt is saved as a table until you drop it

   Check answer
5. Multiple choiceWhy average the staff rows, not the department averages, for the company average?

   It’s fasterThe department averages can’t be averaged in SQLEach person should count once. A department of one would count as much as a department of tenThere’s no difference

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1What is a CTE?Show answer
• Card 2How long does a CTE last?Show answer
• Card 3Why use a CTE instead of the same subquery twice?Show answer
• Card 4Is a CTE faster than a subquery?Show answer
• Card 5Can a later CTE read an earlier one?Show answer

Add 5 cards to my review deck

Cheat sheet

• WITH name AS (SELECT …) names a step. Later CTEs and the main query read it like a table.
• Write a rule once, in a CTE, when two parts of the query need it.
• A CTE lasts one statement. Use a view to keep it.
• Read once, a CTE is folded into the query. Read twice, it runs once and is kept.
• The average of averages gives each group one vote. Average the rows instead.

Go deeper

• In the PostgreSQL docs: WITH queries, with MATERIALIZED and NOT MATERIALIZED.
• Try your own query: any query on the SQL visualizer runs each CTE first. The pay list takes your own staff.
• Next on the SQL roadmap: the whole map, with correlated subqueries, which run once for each row.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Name a step of a query with WITH, and read it like a table
• Read one CTE in two places, so both use the same rows and rules
• Chain CTEs, each reading the ones before it
• Say how long a CTE lasts, and when Postgres runs it once

✓Mark as completeI already know this

Status: Not started