Group Employees of the Same Salary

Group Employees of the Same Salary

What is this problem about?

The Group Employees of the Same Salary coding problem is a SQL task. You are given an Employees table with employee_id, name, and salary. You need to find all employees who share the exact same salary with at least one other employee. The result should be ordered by salary (ascending), and then by employee_id (ascending).

Why is this asked in interviews?

This Database interview pattern problem is used to test your ability to filter datasets based on aggregate properties. It evaluates whether you can use subqueries, window functions, or GROUP BY with HAVING to identify duplicate values in a specific column and then retrieve the full row details for those duplicates.

Algorithmic pattern used

There are two primary SQL patterns for this:

1. Subquery with IN:
   • Find the salaries that occur more than once: SELECT salary FROM Employees GROUP BY salary HAVING COUNT(*) > 1.
   • Select all employees whose salary is in that list: SELECT * FROM Employees WHERE salary IN (...).
2. Window Function:
   • Count the occurrences of each salary using a window function: COUNT(employee_id) OVER(PARTITION BY salary) as sal_count.
   • Wrap this in a CTE or subquery and filter where sal_count > 1.

Example explanation

Employees:

• ID 1, Alice, 50000
• ID 2, Bob, 60000
• ID 3, Charlie, 50000
• ID 4, David, 70000

1. Group and Count:
   • 50000: Count 2 (Alice, Charlie)
   • 60000: Count 1 (Bob)
   • 70000: Count 1 (David)
2. Filter: Only salary 50000 has a count $> 1$.
3. Result: Return Alice and Charlie, sorted by salary then ID.

Common mistakes candidates make

• Just using GROUP BY: Writing SELECT employee_id, name, salary FROM Employees GROUP BY salary HAVING COUNT(*) > 1. This is invalid SQL because employee_id and name are not aggregated and aren't in the GROUP BY clause.
• Missing Order By: Forgetting the specific secondary sorting requirement (ORDER BY salary ASC, employee_id ASC).

Interview preparation tip

The WHERE column IN (SELECT column FROM table GROUP BY column HAVING COUNT(*) > 1) pattern is the most universal and readable way to solve "find all rows that have duplicate values in column X." Memorize this construct.