Median Employee Salary - Problem

Given an Employee table with employee information including their company and salary, write a SQL solution to find the rows that contain the median salary of each company.

Key Requirements:

  • Find median salary for each company separately
  • When sorting salaries, break ties by id (ascending order)
  • Return all rows that represent the median salary for their respective company
  • If a company has even number of employees, return both middle values

The median is the middle value in a sorted list. For odd-sized groups, it's the single middle value. For even-sized groups, both middle values should be returned.

Table Schema

Employee
Column Name Type Description
id PK int Primary key, unique employee identifier
company varchar Company name where employee works
salary int Employee salary amount
Primary Key: id
Note: Each row represents one employee with their company and salary information

Input & Output

Example 1 — Multiple Companies with Different Counts
Input Table:
id company salary
1 A 2341
2 A 341
3 A 15000
4 A 15000
5 A 15000
6 A 15000
Output:
id company salary
3 A 15000
4 A 15000
💡 Note:

Company A has 6 employees. After sorting by salary then id: [341, 2341, 15000, 15000, 15000, 15000]. For even count (6), median positions are 3rd and 4th (rows with id=3 and id=4). Both have salary 15000.
Example 2 — Odd Count with Tie Breaking
Input Table:
id company salary
1 B 1000
2 B 2000
3 B 1000
Output:
id company salary
1 B 1000
💡 Note:

Company B has 3 employees. After sorting by salary, then by id: [(1,1000), (3,1000), (2,2000)]. For odd count (3), median is the 2nd position, which is employee id=1 with salary 1000 (id=1 comes before id=3 due to tie-breaking).

Constraints

  • 1 ≤ Employee.id ≤ 1000
  • 1 ≤ company.length ≤ 100
  • 1 ≤ salary ≤ 100000
  • Each company has at least 1 employee

Visualization

Tap to expand
Median Employee Salary ProblemInput: Employeesidcompanysalary1A23412A3413B10004B2000PARTITION BYcompanyOutput: Median Salariesidcompanysalaryposition2A341median4B2000medianWindow function finds median position within each company
Understanding the Visualization
1
Input
Employee table with id, company, salary
2
Window Rank
ROW_NUMBER within each company
3
Output
Employees at median salary positions
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER with PARTITION BY to find median positions within groups

Related Problems

Asked in
Facebook 12 Amazon 8 Microsoft 6
28.5K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen