Find Employees Who Share a Salary

What the question is really asking

There are two readings of "employees with the same salary", so ask which one the interviewer means. One wants a list of people (every employee whose salary matches someone else's). The other wants pairs (who earns the same as whom). In this employees table, 37 people hold 19 different salaries, and 30 of them share a salary with at least one colleague.

The first step for both readings is the same: find the salaries that appear more than once.

SELECT salary, COUNT(*) AS employees
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1
ORDER BY employees DESC, salary DESC;

That returns 12 salaries. Two of them, 115000 and 105000, are held by four people each.

Approach 1: IN with GROUP BY and HAVING

GROUP BY collapses the people into one row per salary, so you can't select names from it directly. Wrap it in a subquery instead:

SELECT id, first_name, last_name, salary
FROM employees
WHERE salary IN (
  SELECT salary FROM employees GROUP BY salary HAVING COUNT(*) > 1
)
ORDER BY salary DESC, id;

This returns the 30 employees. It's the same pattern as finding duplicate emails, applied to a number instead of a string.

Approach 2: a window function

COUNT(*) OVER (PARTITION BY salary) gives every row the size of its salary group without collapsing anything. Because WHERE runs before window functions, the filter has to sit outside a CTE or subquery, which is what the solution above does. The bonus is that the count comes along for free, so you can show that Emily Davis and Christopher Garcia are the two people at 140000. If window functions are new to you, the window functions guide covers PARTITION BY.

Approach 3: EXISTS

You can also ask, row by row, whether anyone else has the same salary:

SELECT COUNT(*)
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees o
  WHERE o.salary = e.salary AND o.id <> e.id
);

That counts 30 too. The o.id <> e.id part is what matters: without it, every employee matches themselves and the query returns all 37.

The self join and its duplicates

If the interviewer wants pairs, a self join is the natural tool, and it's easy to get wrong. Joining on salary alone matches everyone with themselves:

SELECT COUNT(*)
FROM employees a
JOIN employees b ON a.salary = b.salary;

That gives 89 rows. Adding a.id <> b.id drops the self matches but still lists every pair twice (A with B, then B with A), so you get 52. Use a.id < b.id to keep each pair once:

SELECT a.first_name || ' ' || a.last_name AS employee_1,
       b.first_name || ' ' || b.last_name AS employee_2,
       a.salary
FROM employees a
JOIN employees b ON a.salary = b.salary AND a.id < b.id
WHERE a.salary = 135000
ORDER BY a.id, b.id;

Three people earn 135000 (David Brown, Amanda Rodriguez and Jason Reed), and that gives three pairs. Across the whole table there are 26 pairs. Notice how fast pairs grow: a salary held by four people produces six pairs. That's a good reason to return a list with a count rather than pairs, unless pairs are what was asked for.

Same salary in the same department

A common follow-up limits matches to one department. Add the department to the grouping:

SELECT department_id, salary, COUNT(*) AS employees
FROM employees
GROUP BY department_id, salary
HAVING COUNT(*) > 1;

On this data it returns no rows. Every shared salary crosses departments (the two people at 140000 work in Engineering and Sales). An empty result is a correct answer here, and saying so out loud shows you checked the data rather than assumed.

What to say in the interview

Start with the GROUP BY and HAVING subquery, since it's short and works in every dialect. Mention the window version when they want the count on each row, and the a.id < b.id trick when they want pairs. The related Employees Hired on the Same Day question uses the same idea on dates.