FIRST_VALUE: Show Highest Salary in Each Department for Every Employee

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The compensation dashboard shows every employee alongside the top salary in their department — so they can see how far they are from the department maximum.

Tables

Table: 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 |

• Return: emp_id, emp_name, department, salary, dept_top_salary
• Sort by department, then salary descending
• Function: FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC)