Skip to the content

The next rung up

Problem

HR posts the list of salaries, and everyone looks for the same thing: how far am I from the people who earn more than I do?

Show the name, the salary, and how much each person is short of the next salary above theirs, in that order. Two people who earn the same aren't above each other: both are short by the same amount. For whoever earns the most, the cell stays empty.

Sort from the highest salary to the lowest and, on a tie, by name.

What it has to return

These rows, in this order

namesalaryshort_by
Carol5200NULL
Paige41001100
Mark3800300
Lydia3000800
Samuel3000800
Frank2600400
Grace2400200
Nicole2100300
Rachel1900200

This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.

You start with this

SQL

SELECT name, salary,
  LAG(salary) OVER (
    ORDER BY salary DESC, id
  ) AS above
FROM employees;
Solve this challenge

It opens in your browser, with the editor and the tests. This challenge is part of the full plan; the O(1) and O(log n) ones are free.

More O(n²) challenges

See all challenges →