RANK: Rank Employees by Salary (With Gaps)

SQL coding challenge · Difficulty: easy · Topic: Window Functions · +50 XP

Problem

HR is building a salary leaderboard. Employees on the same salary should **share

a place, and the place after a tie should skip forward** — exactly how

Olympic medals work: two silvers and no bronze.

Engine: queries run on MySQL 8.0, so use MySQL-compatible syntax and functions.

Tables

employees_wf — one row per employee.

| column | type | description |
| --- | --- | --- |
| emp_id | INT | identifies the employee |
| emp_name | VARCHAR | the employee's name |
| department | VARCHAR | department they work in |
| salary | INT | annual salary |

Return one row per employee, with these columns only:

descending

department is not part of the result. Sort by salary descending, then

emp_id ascending.

How ties and gaps work

Use RANK(). Everyone on the same salary gets the same rank_num, and the

tie then consumes the places it spans, so the next employee down resumes

after them:

This is what "with gaps" means: rank_num counts how many employees earn

*strictly more* than you, plus one. Numbers can be skipped, and never repeat

except within a tie. A ranking that closes those gaps (1, 2, 2, 3) is a

different function and a different answer.

Example input

employees_wf

| emp_id | emp_name | department | salary |
| --- | --- | --- | --- |
| 101 | John | IT | 90000 |
| 102 | Alice | IT | 75000 |
| 103 | Bob | IT | 75000 |
| 104 | David | HR | 80000 |
| 105 | Emma | HR | 65000 |

Expected output

| emp_id | emp_name | salary | rank_num |
| --- | --- | --- | --- |
| 101 | John | 90000 | 1 |
| 104 | David | 80000 | 2 |
| 102 | Alice | 75000 | 3 |
| 103 | Bob | 75000 | 3 |
| 105 | Emma | 65000 | 5 |

John is 1st on 90000 and David 2nd on 80000. Alice and Bob both earn 75000, so

they share 3rd. Between them they occupy places 3 and 4, which is why Emma

is 5th rather than 4th — four people (John, David, Alice and Bob) earn

strictly more than she does, plus one.

What this SQL challenge teaches you

“RANK: Rank Employees by Salary (With Gaps)” is a easy-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with RANK, OVER, ORDER BY, Gaps in ranking — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

  1. You want every employee back, each carrying a number derived from the whole table. That is a window function, not a GROUP BY -- grouping would collapse the rows you need to keep.
  2. Order the window by salary descending so the highest earner is 1.
  3. Choose the ranking function by what it does to ties. One gives tied rows the same number and then SKIPS ahead; one gives them the same number and does NOT skip; one refuses to tie at all and just counts 1, 2, 3.
  4. Check your answer on the gap: with two people sharing 3rd, the next employee must be 5th. If they come out 4th, the ranking is closing the gap and is the wrong function for this task.
  5. department is in the table but not in the result -- return only emp_id, emp_name, salary and rank_num.

Where this comes up

Variations of this problem have been reported in interviews at Amazon, Google, Microsoft, Flipkart. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

Helpful resources

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated easy and covers Window Functions.

Solve this challenge free on PySpark.in