FIRST_VALUE: Show Highest Salary in Each Department for Every Employee

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

Problem

The compensation dashboard shows every employee next to the **highest salary paid

in their own department**, so each person can see how far they sit from the top of

their team.

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 | employee id |
| emp_name | VARCHAR | employee name |
| department | VARCHAR | the department they work in |
| salary | INT | their salary |

Return every employee row, with one extra column:

dept_top_salary — the highest salary among all employees in **that same

department**

Sort by department, then salary descending.

Every row is kept

This is not a "find the top earner" question. All employees come back — the top

earner and everyone below them — and each one carries their department's maximum

alongside their own salary. The employee earning the most in a department is the

one row where salary and dept_top_salary are equal.

Each department stands alone

The maximum is computed per department. A high salary in one department must

never appear next to an employee of another. A department with a single employee

reports that person's own salary.

Edge cases the tests cover

Two people tied at the top — both report that shared figure, and so does

everyone below them.

Every salary in a department equal — the top is that same value.
Negative salaries (a loss-making cost centre): the maximum is the *least

negative* number, not zero and not the one closest to zero in magnitude.

The window needs its own ordering

Be careful: this is where the question actually bites. A window function that is

told which partition to look at but not how to order it takes rows in

whatever order the storage engine hands them over. That can look correct on one

dataset and silently break on the same data loaded in a different order. Your

window must pin down *which* row of the partition it means — the hidden tests

include the same five employees inserted in a different order, and an answer that

depends on row order will pass the visible case and fail that one.

MAX() as a window function is an equally valid way to express this and is

accepted — the two are the same answer written two ways.

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 | dept_top_salary |
| --- | --- | --- | --- | --- |
| 104 | David | HR | 80000 | 80000 |
| 105 | Emma | HR | 65000 | 80000 |
| 101 | John | IT | 90000 | 90000 |
| 102 | Alice | IT | 75000 | 90000 |
| 103 | Bob | IT | 75000 | 90000 |

David tops HR, so his own row reads 80000 twice; Emma sits 15000 below him.

In IT, John's 90000 is the figure Alice and Bob both see — not the 75000

they earn, and not David's 80000 from the other department.

What this SQL challenge teaches you

“FIRST_VALUE: Show Highest Salary in Each Department for Every Employee” is a easy-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with FIRST_VALUE, PARTITION BY, ORDER BY, Window Functions — 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

FIRST_VALUE
PARTITION BY
ORDER BY
Window Functions

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:

Every employee stays in the result. You are adding a column to each row, not reducing the table to one row per department -- so this is a window function over a partition, not a GROUP BY.
Partition by department so each team's maximum is computed independently, and nothing leaks from one department to another.
If you pick the first row of a partition, you must tell the window what 'first' means. A window with a partition but no ordering inside it takes rows in whatever order the engine stored them -- that can pass one dataset and fail the same data loaded differently. Order the partition so the highest salary really is first.
MAX() used as a window function over the same partition is the same answer and is accepted -- it needs no ordering at all, because a maximum does not depend on which row comes first.
Ties at the top are not a problem here: you are returning the salary itself, and two people on the same top salary give the same number either way. A department of one reports that person's own salary.

Where this comes up

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