This is a common SQL (Window Functions) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem A company wants to identify the employee(s) earning the second highest salary in every department. If multiple employees have the same second highest salary, return all of them. Employees employee_id employee_name department salary 101 Alice Engineering 120000 102 Bob Engineering 110000 103 Charlie Engineering 110000 104 David Engineering 90000 105 Emma Finance 95000 106 Frank Finance 90000 107 Grace Finance 85000 108 Henry Marketing 100000 109 Ivy Marketing 90000 Task Return all employees having the second highest distinct salary within their department. Expected Output employee_name department salary Bob Engineering 110000 Charlie Engineering 110000 Frank Finance 90000 Ivy Marketing 90000

Answer & Explanation

**What the problem is asking**

You have a table of employees with their department and salary.
For each department you must return every employee whose salary is the **second‑highest distinct** salary in that department.
If two or more employees share that second‑highest amount, all of them belong in the result.

**Why a simple “MAX” won’t work**

If you just pick the maximum salary and then the next maximum with a sub‑query, you have to be careful to ignore duplicates.
For example, in Engineering the salaries are 120 000, 110 000, 110 000, 90 000.
The highest distinct salary is 120 000, the second‑highest distinct salary is 110 000, and both Bob and Charlie earn it.
A query that uses `ROW_NUMBER()` would give one of them rank 2 and the other rank 3, which is wrong because the rank must be based on **distinct** salary values, not on rows.

**The right tool: DENSE_RANK()**

`DENSE_RANK()` is a window function that:

1. Is evaluated **per partition** – here the partition is each department.
2. Orders the rows inside the partition by the column you care about – here `salary DESC`.
3. Assigns the same rank to rows that have the same salary (that’s the “dense” part).
So all rows with the highest salary get rank 1, the next distinct salary gets rank 2, and so on.

Because the rank is based on distinct salary values, every employee that shares the second‑highest salary will receive rank 2.

**Step‑by‑step construction**

1. **Create a derived table (or CTE) that adds the rank**
```sql
SELECT
employee_name,
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM Employees
```
- `PARTITION BY department` restarts the ranking for each department.
- `ORDER BY salary DESC` makes the highest salary get rank 1.
- The column `salary_rank` now tells you whether a row is first, second, third … distinct salary in its department.

2. **Filter for rank 2**
```sql
WHERE salary_rank = 2
```
This keeps only the rows whose salary is the second highest distinct value.

3. **Select the columns you need and optionally order the output**
```sql
SELECT employee_name, department, salary
FROM ( …the ranked query… ) AS ranked
WHERE salary_rank = 2
ORDER BY department, employee_name;
```

**Complete query**

```sql
WITH ranked_salaries AS (
SELECT
employee_name,
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM Employees
)
SELECT
employee_name,
department,
salary
FROM ranked_salaries
WHERE salary_rank = 2
ORDER BY department, employee_name;
```

**Why this query returns exactly the expected rows**

- **Engineering**: salaries are 120 000 (rank 1), 110 000 (rank 2), 110 000 (rank 2), 90 000 (rank 3).
Both Bob and Charlie have rank 2, so they appear.

- **Finance**: salaries are 95 000 (rank 1), 90 000 (rank 2), 85 000 (rank 3).
Frank is the only employee with rank 2, so he appears.

- **Marketing**: salaries are 100 000 (rank 1), 90 000 (rank 2).
Ivy has rank 2, so she appears.

If a department has fewer than two distinct salary values (e.g., only one unique salary), no row from that department will have `salary_rank = 2`, so nothing is returned for it – which matches the problem statement.

**Alternative approaches (for completeness)**

1. **Correlated sub‑query**
```sql
SELECT e.employee_name, e.department, e.salary
FROM Employees e
WHERE e.salary = (
SELECT DISTINCT salary
FROM Employees e2
WHERE e2.department = e.department
ORDER BY salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
);
```
This works but is less readable and may be slower because the sub‑query runs for every row.

2. **Two‑step aggregation**
- First find the maximum salary per department.
- Then find the maximum salary that is **less than** that maximum.
- Finally join back to the employee table.
This requires three passes over the data and extra joins, whereas `DENSE_RANK()` does everything in a single pass.

**Complexity considerations**

`DENSE_RANK()` is implemented as a sort within each partition, so the overall time complexity is O(N log N) where N is the number of rows. The memory usage is linear in the size of the largest partition (the biggest department). This is optimal for this type of problem; any solution that needs to compare each salary with every other salary would be O(N²) and far slower.

**Key take‑aways for the interview**

- Use `DENSE_RANK()` (or `RANK()`) when you need ranking based on **distinct** values.
- Always partition by the grouping column (`department`) before ordering by the metric (`salary`).
- Filtering on the rank after the window function is much clearer than trying to express the same logic with nested aggregates.
- Remember that `ROW_NUMBER()` gives a unique sequence even for duplicate values, so it is not suitable when you need “second highest distinct salary”.

Practice writing the query from memory, and be ready to explain why `DENSE_RANK()` is the right choice and how the `PARTITION BY` clause isolates each department’s ranking. This will demonstrate both your SQL syntax knowledge and your ability to reason about ranking semantics.