Watch 3 video solutions for Group Employees of the Same Salary, a medium level problem involving Database. This walkthrough by Everyday Data Science has 1,830 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Employees
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | | salary | int | +-------------+---------+ employee_id is the column with unique values for this table. Each row of this table indicates the employee ID, employee name, and salary.
A company wants to divide the employees into teams such that all the members on each team have the same salary. The teams should follow these criteria:
team_id = 1. Note that the salaries for employees not on a team are not included in this ranking.Write a solution to get the team_id of each employee that is in a team.
Return the result table ordered by team_id in ascending order. In case of a tie, order it by employee_id in ascending order.
The result format is in the following example.
Example 1:
Input: Employees table: +-------------+---------+--------+ | employee_id | name | salary | +-------------+---------+--------+ | 2 | Meir | 3000 | | 3 | Michael | 3000 | | 7 | Addilyn | 7400 | | 8 | Juan | 6100 | | 9 | Kannon | 7400 | +-------------+---------+--------+ Output: +-------------+---------+--------+---------+ | employee_id | name | salary | team_id | +-------------+---------+--------+---------+ | 2 | Meir | 3000 | 1 | | 3 | Michael | 3000 | 1 | | 7 | Addilyn | 7400 | 2 | | 9 | Kannon | 7400 | 2 | +-------------+---------+--------+---------+ Explanation: Meir (employee_id=2) and Michael (employee_id=3) are in the same team because they have the same salary of 3000. Addilyn (employee_id=7) and Kannon (employee_id=9) are in the same team because they have the same salary of 7400. Juan (employee_id=8) is not included in any team because their salary of 6100 is unique (i.e. no other employee has the same salary). The team IDs are assigned as follows (based on salary ranking, lowest first): - team_id=1: Meir and Michael, a salary of 3000 - team_id=2: Addilyn and Kannon, a salary of 7400 Juan's salary of 6100 is not included in the ranking because they are not on a team.
Problem Overview: You are given an Employees table containing employee information including salary. The task is to group employees who have the same salary when ordered by employee ID, assigning a unique group identifier whenever the salary changes.
Approach 1: Self Join Comparison (O(n²) time, O(1) extra space)
A straightforward idea compares each employee with previous rows using a SELF JOIN. By checking whether another employee with a different salary appears before the current one, you can infer group boundaries. This works conceptually but becomes inefficient because each row may scan many other rows. SQL engines must repeatedly evaluate joins, leading to quadratic behavior on large tables. This approach mainly helps build intuition about how salary transitions define groups.
Approach 2: Window Functions with LAG + Running SUM (O(n) time, O(n) space)
The efficient solution relies on SQL window functions. First, use LAG(salary) ordered by employee_id to check the previous employee's salary. If the salary differs from the previous row, that row starts a new group. Convert this comparison into a flag using CASE WHEN salary != LAG(salary). Then compute a running SUM() over the ordered rows to assign increasing group IDs. Each time the salary changes, the running sum increments, creating a new group. This pattern is common in SQL problems involving consecutive segments or streak detection. The query scans the table once while computing window values, resulting in linear time complexity.
Recommended for interviews: The window function approach is the expected solution. Interviewers want to see familiarity with analytical SQL features like LAG, ROW_NUMBER, and cumulative SUM. A brute-force self join shows you understand the grouping condition, but the window-based solution demonstrates real SQL proficiency and scales efficiently.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join Salary Comparison | O(n²) | O(1) | Conceptual solution when window functions are unavailable |
| Window Function (LAG + Running SUM) | O(n) | O(n) | Preferred SQL approach for grouping consecutive rows efficiently |