Write an SQL query to add row number and rank to employees based on their salary.

You are given an employees table containing salary column. Write an SQL query to add row number (ROW_NUMBER) and rank (RANK) with column names as salary_row_number and salary_rank. Check the sample employees table and data given below and write an sql query that returns employee_id, name, salary, salary_row_number and salary_rank only for top 10 employees based on their salary

Sample Input:

| employee_id | name          | department  | salary | hire_date  |
|-------------|---------------|-------------|--------|------------|
| 1           | James Wilson  | Engineering | 110000 | 2019-03-15 |
| 2           | Sarah Johnson | Marketing   | 72000  | 2020-07-22 |
| 3           | Michael Brown | Engineering | 110000 | 2018-11-05 |
| 4           | Emily Davis   | HR          | 58000  | 2021-01-10 |
| 5           | Robert Taylor | Finance     | 58000  | 2017-06-30 |

| employee_id | name          | department  | salary | hire_date  |
|-------------|---------------|-------------|--------|------------|
| 1           | James Wilson  | Engineering | 110000 | 2019-03-15 |
| 2           | Sarah Johnson | Marketing   | 72000  | 2020-07-22 |
| 3           | Michael Brown | Engineering | 110000 | 2018-11-05 |
| 4           | Emily Davis   | HR          | 58000  | 2021-01-10 |
| 5           | Robert Taylor | Finance     | 58000  | 2017-06-30 |

SQL Query to assign row number and rank to employees

SELECT
    employee_id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_row_number,
    RANK()       OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY salary DESC
LIMIT 10;

SELECT
    employee_id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_row_number,
    RANK()       OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY salary DESC
LIMIT 10;

Explanation:

ROW_NUMBER() OVER (ORDER BY salary DESC)
ROW_NUMBER() assigns a unique sequential number to each row based on salary from highest to lowest. Even if two employees have the same salary, they still get different row numbers.

RANK() OVER (ORDER BY salary DESC)
RANK() also assigns rank by salary highest to lowest, but if two employees have same salary, they get same rank. The next rank is the skipped and so, if three employees share rank 1, then the next employee gets rank 4, not 2 or 3.

| employee_id | name          | salary | salary_row_number | salary_rank |
|-------------|---------------|--------|-------------------|-------------|
| 1           | James Wilson  | 110000 | 1                 | 1           |
| 3           | Michael Brown | 110000 | 2                 | 1           |
| 2           | Sarah Johnson | 72000  | 3                 | 3           |
| 4           | Emily Davis   | 58000  | 4                 | 4           |
| 5           | Robert Taylor | 58000  | 5                 | 4           |

-- ROW_NUMBER → always unique 1,2,3,4,5
-- RANK → ties get same rank, next rank is skipped → 1,1 jumps to 3, 4,4 stays 4

| employee_id | name          | salary | salary_row_number | salary_rank |
|-------------|---------------|--------|-------------------|-------------|
| 1           | James Wilson  | 110000 | 1                 | 1           |
| 3           | Michael Brown | 110000 | 2                 | 1           |
| 2           | Sarah Johnson | 72000  | 3                 | 3           |
| 4           | Emily Davis   | 58000  | 4                 | 4           |
| 5           | Robert Taylor | 58000  | 5                 | 4           |

-- ROW_NUMBER → always unique 1,2,3,4,5
-- RANK → ties get same rank, next rank is skipped → 1,1 jumps to 3, 4,4 stays 4

Follow up questions from the interviewer

• What is the difference between ROW_NUMBER and RANK?
  • ROW_NUMBER always assigns a unique number to each row, while RANK assigns the same number to ties and skips the next rank.
• What is the difference between RANK and DENSE_RANK?
  • RANK skips numbers after a tie (1,1,3), while DENSE_RANK never skips (1,1,2).
• What is a Window Function?
  • A function that performs a calculation across a set of rows related to the current row without collapsing them into a single row like GROUP BY.
• What does OVER() do?
  • It defines the window, the set of rows the function should operate on, along with ordering.
• Can you use WHERE to filter ROW_NUMBER directly?
  • No, you need to wrap it in a subquery or CTE and then filter but window functions cannot be used in WHERE directly.
• What is the difference between RANK and DENSE_RANK when used with PARTITION BY?
  • PARTITION BY resets the rank for each group, both RANK and DENSE_RANK behave the same way but differ in how they handle ties within each partition.

For more such questions