HardSQL challenge #284SELF JOINGROUP BYLOGIC

Find Managers Who Earn More Than All Their Direct Reports

Return the managers whose salary is higher than that of every employee reporting to them.

The challenge runs on the SQLabHub practice database (SQLite) and uses the employees table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find managers whose salary is greater than all their subordinates.

A correct answer returns 1 row with one column: id.

Tables used in this challenge

employees 120 rows

id INTEGER (PK) · first_name TEXT · last_name TEXT · email TEXT · department TEXT · department_id INTEGER · title TEXT · salary REAL · hire_date TEXT · manager_id INTEGER · city TEXT · status TEXT

First 3 rows of the employees table
idfirst_namelast_nameemaildepartmentdepartment_idtitlesalaryhire_datemanager_idcitystatus
1WilliamLewiswlewis18@corp.ioCustomer Success7Senior CS Manager617742022-07-24NULLSan Diegoactive
2JohnMillerjmiller51@corp.ioLegal8Paralegal1916942021-08-09NULLSan Diegoactive
3DanielRiveradrivera50@corp.ioEngineering1VP Engineering1350672016-06-06NULLHoustonactive

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
id
6

The complete result: 1 row. This is the output of the model solution on the practice database.

What you practise

  • Summarising many rows into one value with COUNT, SUM, AVG, MIN or MAX
  • Grouping rows with GROUP BY to get one result row per group
  • Filtering groups after aggregation with HAVING
  • Combining rows from two tables on a matching key with an inner join
  • Joining a table to itself under two aliases

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

Self join + GROUP BY

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Read from the employees table, under the alias m.
  2. Join employees again, under the alias e, on e.manager_id = m.id.
  3. Group the rows by m.id, so the result has one row for each value.
  4. For each group, compute MIN(m.salary > e.salary).
  5. Filter the groups in HAVING: keep those where MIN(m.salary > e.salary) is true.

Model solution

Checking your session…

What to watch for

In SQLite a comparison evaluates to 1 or 0, so MIN(m.salary > e.salary) is 1 only when it is true for every report. That is a universal check written as an aggregate. The portable form is HAVING m.salary > MAX(e.salary).

  • Self join. Without a condition such as a.id < b.id, a self join pairs every row with itself and lists each pair twice.
  • INNER JOIN. An inner join drops rows that have no partner on the other side, and it repeats a row once for every match, which inflates SUM and COUNT.
  • HAVING. Conditions on single rows belong in WHERE and conditions on aggregates in HAVING. Putting a row filter in HAVING works, but it groups rows that are about to be thrown away.
Solve This SQL Challenge

Keep learning