ROW_NUMBER: Assign Unique Rank to Each Employee

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

Problem

An internal salary transparency report lists every employee from the highest

paid down, and gives each one a unique sequential number — 1, 2, 3 and so on

with no gaps and no repeats, even when two people earn exactly the same.

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 every row of employees_wf, with one extra column:

• row_num — a sequential number starting at 1, assigned in order of

salary descending, with ties broken by emp_id ascending

Sort the result by salary descending, then emp_id ascending.

Ties must not be left to chance

Two employees can earn the same salary, and they must still receive **different

numbers**. Salary alone cannot decide which of them comes first, so the report

would be free to come out differently from one run to the next — the same data

could produce two different answers.

Break the tie on emp_id ascending: of two employees on the same salary,

the lower emp_id gets the smaller number. Put that second key inside the

window's own ORDER BY, not only in the query's final ORDER BY. The final

sort decides how rows are *displayed*; the window's sort decides which number

each row is *given*, and those are different things.

Use ROW_NUMBER(). Note that once the tiebreaker makes the ordering total —

no two rows sharing the same (salary, emp_id) — there are no ties left for a

ranking function to treat specially. ROW_NUMBER() is the function that

expresses "number these rows 1, 2, 3" directly, and it is what this exercise is

about.

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 | department | salary | row_num |
| --- | --- | --- | --- | --- |
| 101 | John | IT | 90000 | 1 |
| 104 | David | HR | 80000 | 2 |
| 102 | Alice | IT | 75000 | 3 |
| 103 | Bob | IT | 75000 | 4 |
| 105 | Emma | HR | 65000 | 5 |

John earns the most, so he is 1, and David follows at 80000. Alice and Bob both

earn 75000 — they take 3 and 4 rather than sharing a number, and Alice takes 3

because 102 is lower than 103. Emma is last on 65000.

The numbering does not restart per department: row_num runs once across the

whole table.

What this SQL challenge teaches you

“ROW_NUMBER: Assign Unique Rank to Each Employee” is a easy-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with ROW_NUMBER, OVER, ORDER BY — 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

• ROW_NUMBER
• OVER
• ORDER BY

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. ROW_NUMBER() OVER (...) numbers rows 1, 2, 3 with no gaps and no repeats. RANK() would give tied rows the same value, which is not what this asks for.
2. The window's own ORDER BY decides which row gets which number. Start with salary DESC so the highest paid is 1.
3. Salary alone leaves two people on the same amount in an undefined order, so the answer could change between runs. Add emp_id ASC as a second key inside the OVER clause.
4. Sorting the final result is not the same as sorting the window. The query's trailing ORDER BY only arranges the rows for display -- the numbers were already assigned by the OVER clause.

Where this comes up

Variations of this problem have been reported in interviews at Amazon, Flipkart, Databricks, Google. 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

• Top 3 Products per Category
• Running Total Revenue
• Median Salary per Department
• Latest Order Per Customer
• 3-Day Rolling Sum of Sales
• 7-Day Rolling Purchase Amount by Customer

Helpful resources

• Learn this concept
• Practice this topic
• Open the notebook

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