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
3 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 INPUT id company salary 1 A 2341 2 A 341 3 A 15 4 A 15314 5 A 451 6 A 513 7 B 15 8 B 13 9 B 1154 10 B 1345 Company A: 6 employees Company B: 4 employees Find median salary per company Sort by salary, then by id ALGORITHM STEPS 1 Rank by Salary ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary, id) 2 Count per Company COUNT(*) OVER (PARTITION BY company) 3 Find Median Position Odd count: (n+1)/2 Even count: n/2, n/2+1 4 Filter Median Rows WHERE rank IN (floor((cnt+1)/2), floor((cnt+2)/2)) Median Position Formula A: 6 rows -> pos 3,4 B: 4 rows -> pos 2,3 Both middle values for even count FINAL RESULT id company salary 5 A 451 pos 3 6 A 513 pos 4 7 B 15 pos 2 9 B 1154 pos 3 Company A (6 rows) Sorted: 15,341,451,513,2341,15314 Medians at pos 3,4: 451, 513 OK - Both returned Company B (4 rows) Sorted: 13, 15, 1154, 1345 Medians at pos 2,3: 15, 1154 OK - Both returned 4 Median Rows Found Key Insight: For median calculation, use FLOOR((cnt+1)/2) and FLOOR((cnt+2)/2) to get correct positions. This formula works for both odd (returns same pos twice) and even (returns two middle positions) counts. Window functions ROW_NUMBER() and COUNT() with PARTITION BY enable per-company calculations. TutorialsPoint - Median Employee Salary | Optimal Solution

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