Each Salary and the Next Lower One

List the instructors from the best paid down: return first_name, last_name, salary, next_lower (the salary on the following row, via LEAD()) and gap (the current salary minus next_lower). Order the window and the output by salary descending, then id, so the two instructors on 82000 line up the same way every time. The last row has NULL in both new columns.

Expected result:

+------------+-----------+--------+------------+------+
| first_name | last_name | salary | next_lower |  gap |
+------------+-----------+--------+------------+------+
| Olivia     | Grant     | 105000 |     101000 | 4000 |
| Samuel     | Whitaker  | 101000 |      98500 | 2500 |
| Grace      | Okafor    |  98500 |      94000 | 4500 |
| Amara      | Diallo    |  94000 |      91000 | 3000 |
| Helen      | Brooks    |  91000 |      88000 | 3000 |
| Mei        | Chen      |  88000 |      82000 | 6000 |
| Daniel     | Reyes     |  82000 |      82000 |    0 |
| Tomasz     | Nowak     |  82000 |      79000 | 3000 |
| Yuki       | Tanaka    |  79000 |      76500 | 2500 |
| Priya      | Natarajan |  76500 |      72000 | 4500 |
+------------+-----------+--------+------------+------+
(showing 10 of 12 rows)

Hints3 available

Topics:

Window Functions

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.