Median Employee Salary - Practice Coding Problems

Median Employee Salary - Problem

Database Hard

You're working as a data analyst for a large corporation that tracks employee salaries across multiple companies. Your task is to find the median salary employees for each company in the database.

Given an Employee table with the following structure:

Column Name	Type
id	int
company	varchar
salary	int

Your goal is to write a SQL query that returns the complete employee records (not just salary values) for employees whose salary represents the median for their respective company.

Important rules:

When sorting salaries to find the median, break ties by employee id (ascending order)
For companies with an odd number of employees, return the middle employee
For companies with an even number of employees, return both middle employees
The result can be returned in any order

This problem tests your understanding of window functions, ranking, and median calculation in SQL databases.

Input & Output

example_1.sql — Basic Case

$ Input: Employee 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 | |----|---------|--------| | 2 | A | 341 | | 3 | A | 15000 |

💡 Note: Company A has 6 employees. When sorted by salary (then by id): [341, 2341, 15000, 15000, 15000, 15000]. For 6 employees, median positions are 3 and 4. Position 3 is employee id=2 (341), position 4 is employee id=3 (15000, lowest id among ties).

example_2.sql — Multiple Companies

$ Input: Employee table: | id | company | salary | |----|---------|--------| | 1 | A | 100 | | 2 | A | 200 | | 3 | A | 300 | | 4 | B | 400 | | 5 | B | 500 | | 6 | B | 600 | | 7 | B | 700 |

› Output: | id | company | salary | |----|---------|--------| | 2 | A | 200 | | 5 | B | 500 | | 6 | B | 600 |

💡 Note: Company A (3 employees): median is position 2 → id=2 (salary 200). Company B (4 employees): median positions are 2 and 3 → id=5 (salary 500) and id=6 (salary 600).

example_3.sql — Tie Breaking by ID

$ Input: Employee table: | id | company | salary | |----|---------|--------| | 1 | A | 100 | | 4 | A | 100 | | 3 | A | 100 |

› Output: | id | company | salary | |----|---------|--------| | 3 | A | 100 |

💡 Note: All employees have same salary (100), so we sort by id: [1, 3, 4]. For 3 employees, median position is 2, which corresponds to id=3.

Constraints

1 ≤ number of employees ≤ 10⁴
1 ≤ employee.id ≤ 10⁴
1 ≤ employee.salary ≤ 10⁶
employee.id is unique across all companies
Company names are non-empty strings with length ≤ 100

Visualization

Tap to expand

Understanding the Visualization

Group by Company

Separate employees into company-specific groups, like sorting cards by suit

Sort Within Groups

Within each company, arrange employees by salary (then ID), like ordering cards by value

Number the Positions

Assign position numbers 1, 2, 3... to each employee in their sorted company group

Calculate Median Range

For n employees: median positions are at (n+1)/2 and (n+2)/2

Select Median Employees

Return employees whose position numbers fall within the median range

Key Takeaway

🎯 Key Insight: Window functions allow us to partition, sort, and rank data in a single pass, making median calculation efficient by using mathematical position ranges instead of complex nested queries.

Asked in

G Google 23 f Meta 18 a Amazon 15 ⊞ Microsoft 12

The optimal solution uses window functions with a single CTE to calculate ROW_NUMBER() and COUNT() simultaneously. By partitioning employees by company and ordering by salary (then ID), we can mathematically determine median positions using (total_count + 1) / 2 and (total_count + 2) / 2. This approach achieves O(n log n) time complexity with efficient single-pass processing.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized Window Functions (Single CTE)	O(n log n)	O(n)	Single CTE with optimized window functions for best performance
Window Functions with Ranking	O(n log n)	O(n)	Use ROW_NUMBER() and COUNT() window functions to efficiently find median positions
Brute Force (Subqueries and Manual Ranking)	O(n²)	O(1)	Use nested subqueries to manually count positions and find median

Optimized Window Functions (Single CTE) — Algorithm Steps

Create single CTE with ROW_NUMBER() and COUNT() window functions
Order by salary ASC, then id ASC within each company partition
Calculate exact median boundaries using integer division
Filter rows where row number falls in median range
Return results ordered appropriately

Visualization

Tap to expand

Step-by-Step Walkthrough

Single CTE Scan

Process all employees in one optimized pass

Partition & Rank

Simultaneously partition by company and rank by salary

Calculate Bounds

Compute median boundaries using efficient math

Filter Results

Return only employees in median positions

Code -

solution.c — C

// Optimized SQL solution in C
#include <stdio.h>
#include <mysql/mysql.h>

const char* getMedianQuery() {
    return "WITH MedianRanked AS ("
           "    SELECT id, company, salary,"
           "           ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary, id) as row_num,"
           "           COUNT(*) OVER (PARTITION BY company) as total_count"
           "    FROM Employee"
           ")"
           "SELECT id, company, salary "
           "FROM MedianRanked "
           "WHERE row_num IN ((total_count + 1) / 2, (total_count + 2) / 2) "
           "ORDER BY company, salary, id;";
}

int main() {
    MYSQL *conn = mysql_init(NULL);
    
    if (!mysql_real_connect(conn, "localhost", "user", "password", "database", 0, NULL, 0)) {
        fprintf(stderr, "Connection failed\n");
        return 1;
    }
    
    if (mysql_query(conn, getMedianQuery())) {
        fprintf(stderr, "Query failed\n");
        return 1;
    }
    
    MYSQL_RES *result = mysql_store_result(conn);
    MYSQL_ROW row;
    
    while ((row = mysql_fetch_row(result))) {
        printf("%s %s %s\n", row[0], row[1], row[2]);
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single sort operation per company partition, then linear filtering

⚡ Linearithmic

Space Complexity

O(n)

Space for window function calculations and result set

⚡ Linearithmic Space

28.4K Views

Medium-High Frequency

~25 min Avg. Time

890 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Optimized Window Functions (Single CTE) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler