Project: the best-paid in each department

SQL · Unit 13: Window functions

Full plan
SQL
Project
3 exercises

HR is putting together a report on the best paid in each department. In unit 3, "the three most expensive" were an ORDER BY and a LIMIT 3. For a single department that still works. For all of them at once, it doesn't. LIMIT cuts once, at the end, and knows nothing about groups.

You already have the pieces: a number that starts over in each department, and a step so you can filter on it. Carol is filtered out inside the step itself, because she has no department.

Look at Tech, department 2. Paige and Samuel make the list. Lydia earns the same 3000 as Samuel but isn't there. Her id left her out.

WITH with_num AS (
  SELECT name, dept_id, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept_id
      ORDER BY salary DESC, id
    ) AS num
  FROM employees
  WHERE dept_id IS NOT NULL
)
SELECT dept_id, name, salary
FROM with_num
WHERE num <= 2
ORDER BY dept_id, num;

Returns

dept_id	name	salary
1	Mark	3800
1	Nicole	2100
2	Paige	4100
2	Samuel	3000
3	Grace	2400

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Find the case that fails
The report has a rule. Whoever earns one of the two highest salaries in their department is in, and if two people earn the same, both are in. This query, with ROW_NUMBER, gets Sales and Design, departments 1 and 3, right today. Which of these changes makes it leave out someone who should be in?
2. Write the code
The report, with its rule. Write a query with three columns, in this order: the dept_id, the name, and the salary of whoever earns one of the two highest salaries in their department. If several people earn the same, they're all in. Carol, who has no department, isn't. Calculate the place in a step called with_place and filter below. Sort by department, then from the highest salary to the lowest, then by name.
3. Write the code
For management, the report carries each department's name and one more column. The with_place step is already written, this time without removing anyone. Below it, join it with departments and write four columns, in this order: the department's name, the person's name, their salary, and how far they are from the highest salary in their department. The same rule of the two highest salaries applies, and someone with no department doesn't belong in a report by department. Give the step the alias wp and departments the alias d. Sort by the department's name, then from the highest salary to the lowest, then by the person's name.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →