RANK vs DENSE_RANK: See the Difference Side by Side

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

Problem

A new analyst keeps mixing up RANK() and DENSE_RANK(). The quickest way to

settle it is to put both on the same rows and look at where they part company.

Produce one row per employee carrying both rankings side by side.

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:

• emp_id
• emp_name
• salary
• rank_num — ranked by salary descending, using RANK()
• dense_rank_num — the same ordering, using DENSE_RANK()

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

emp_id ascending.

Where the two differ

Both functions give tied rows the same number. They disagree about what

happens after a tie:

• RANK() leaves a gap. A tie consumes the places it spans, so two people

sharing 3rd are followed by 5th — nobody is 4th.

• DENSE_RANK() closes the gap. The same two people sharing 3rd are followed by

4th.

So rank_num counts *how many employees earn strictly more than you, plus one*,

while dense_rank_num counts *how many distinct salaries are higher than yours,

plus one*.

Two consequences worth checking your answer against:

• With no ties anywhere, the two columns are identical. The difference only

becomes visible once a salary repeats.

• With several ties, the gap compounds: a table ranking 1, 2, 2, 4, 4, 6

under RANK() ranks 1, 2, 2, 3, 3, 4 under DENSE_RANK().

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

The first four rows agree. Alice and Bob both earn 75000, so both functions put

them at 3. Emma is the row that settles the argument: four people earn more

than she does, so RANK() calls her 5th; but only three distinct salaries are

above hers, so DENSE_RANK() calls her 4th.

What this SQL challenge teaches you

“RANK vs DENSE_RANK: See the Difference Side by Side” is a easy-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with RANK, DENSE_RANK, Comparison, OVER — 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

• RANK
• DENSE_RANK
• Comparison
• OVER

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. Both numbers come from the same table and the same ordering, so this is one query with two window expressions -- no join and no subquery needed.
2. Both windows are OVER (ORDER BY salary DESC). Only the function changes.
3. RANK() leaves a gap after a tie: two people sharing 3rd are followed by 5th. DENSE_RANK() closes it and the next person is 4th.
4. If your two columns come out identical everywhere, the data you are testing on has no repeated salary. The difference only appears once a salary is shared.
5. department is in the table but not in the result -- return emp_id, emp_name, salary, rank_num and dense_rank_num.

Where this comes up

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