Department Highest Salary - Video Solutions

Problem Overview: Given Employee and Department tables, return the employees who earn the highest salary within each department. The result should include the department name, employee name, and salary.

Approach 1: SQL Group By and Join (Time: O(n log n), Space: O(d))

This approach first computes the maximum salary for each department using GROUP BY. A sub-result like SELECT departmentId, MAX(salary) identifies the top salary per department. Then you JOIN this result back to the Employee table to retrieve employees whose salary matches the department maximum. Finally, join with the Department table to get department names. This approach is straightforward and commonly used in SQL interview questions involving grouped aggregates.

Approach 2: Nested Queries with Join (Time: O(n log n), Space: O(d))

A nested query calculates the highest salary per department inside a derived table. That derived table is then joined with the Employee table to filter matching records. The structure typically looks like a join between Employee and a subquery that returns departmentId with MAX(salary). Compared to the first method, this version simply embeds the aggregation logic directly inside the join statement. It's useful when writing compact SQL queries and appears frequently in database interview problems.

Approach 3: SQL Subquery Approach (Time: O(n log n), Space: O(1))

This solution filters rows using a correlated subquery. For each employee row, a subquery calculates the maximum salary within that employee's department. The query keeps rows where salary = (SELECT MAX(salary) ...). The advantage is readability: the condition directly expresses the business rule. However, correlated subqueries may execute repeatedly depending on the optimizer, which can make them less efficient on very large datasets.

Approach 4: SQL Window Functions Approach (Time: O(n log n), Space: O(n))

Window functions provide a clean and modern solution. Use RANK() or DENSE_RANK() with PARTITION BY departmentId ORDER BY salary DESC. This ranks employees within each department by salary. After ranking, filter rows where the rank equals 1 to get the top earners. Window functions avoid additional joins or subqueries and make the intent very clear. Many production SQL systems prefer this pattern, especially when working with analytical queries using window functions.

Recommended for interviews: The GROUP BY + JOIN solution is the most commonly expected answer because it demonstrates strong understanding of aggregation and relational joins. Window functions are equally valid and often considered the cleanest modern SQL approach. Showing both indicates strong SQL proficiency.