HardSQL challenge #262WINDOW FUNCTIONLAGANALYTICS

Find the 3 Largest Jumps Between Adjacent Salaries

Sort salaries, compare each with the next lower one, and return the three biggest differences.

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 employees with largest salary difference compared to previous.

A correct answer returns 3 rows with 3 columns: id, salary, diff.

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
idsalarydiff
281511505698
27702795349
101782864843

The complete result: 3 rows. This is the output of the model solution on the practice database.

What you practise

  • Reading the previous row's value with LAG()
  • Sorting the result with ORDER BY
  • Returning only the first rows with LIMIT
  • Deriving a new column from an expression and naming it with an alias

Hint and approach

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

Show the hint

LAG salary

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.
  2. Fetch salary from the previous row with LAG(), ordered by salary. Where there is no such row the value is NULL.
  3. Combine those values into diff: salary - LAG(salary) OVER (ORDER BY salary).
  4. Sort the result by diff descending.
  5. Keep only the first 3 rows with LIMIT.

Model solution

Checking your session…

What to watch for

The window is ordered by salary itself, so LAG() returns the next lower salary and the difference is the gap to that neighbour. The outer ORDER BY then sorts by the gap, which is a different order from the one inside OVER.

  • LAG(). LAG() returns NULL for the first row of each partition, and any arithmetic with that NULL is NULL too. Supply a default as the third argument if you need a number.
  • Calculated columns. Dividing one integer by another truncates the result in SQLite. Multiply one side by 1.0 to get decimals.
  • LIMIT. LIMIT without ORDER BY returns an arbitrary set of rows. LIMIT is also dialect-specific: SQL Server uses TOP and Oracle uses FETCH FIRST.
Solve This SQL Challenge

Keep learning