The next rung up
- O(n²) · Very hard
- Full plan
- SQL
- window functions
- subqueries
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
| name | salary | short_by |
|---|---|---|
| Carol | 5200 | NULL |
| Paige | 4100 | 1100 |
| Mark | 3800 | 300 |
| Lydia | 3000 | 800 |
| Samuel | 3000 | 800 |
| Frank | 2600 | 400 |
| Grace | 2400 | 200 |
| Nicole | 2100 | 300 |
| Rachel | 1900 | 200 |
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;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
- The register, day by daySQL · window functions · grouping · conditionals
- The third stampSQL · window functions · joins · dates
- The week of the 4thSQL · dates · grouping · strings
- What went out todaySQL · changing data · subqueries · nulls
- Which day do we close?SQL · dates · grouping · conditionals
- You started with a KeyboardSQL · window functions · joins