Median Employee Salary

Median Employee Salary

What is this problem about?

The "Median Employee Salary" interview question, often posed in database or SQL-focused interviews, requires you to find the median salary for employees, potentially grouped by department or company. The median is the middle value in a sorted list of numbers; if the list has an even number of elements, it's typically the average of the two middle values. This problem goes beyond simple AVG() functions and tests your ability to use window functions, common table expressions (CTEs), or subqueries to achieve ranking and conditional aggregation in SQL. It's a key question for assessing SQL proficiency for data analysis and reporting roles.

Why is this asked in interviews?

The Median Employee Salary coding problem is a popular choice in interviews, especially at companies like Google, to evaluate a candidate's advanced SQL skills. It's not just about knowing SELECT statements; it requires understanding ranking functions (ROW_NUMBER(), NTILE(), PERCENTILE_CONT()), handling odd/even counts, and potentially dealing with duplicates. Interviewers use this to gauge problem-solving with set-based logic, attention to detail in handling edge cases, and the ability to write efficient and correct SQL queries for complex data aggregation. It differentiates candidates who can merely query data from those who can perform sophisticated data analysis.

Algorithmic pattern used

For the "Median Employee Salary" database interview question, the most common algorithmic patterns involve using SQL Window Functions or a combination of ranking and subqueries.

1. Window Functions (ROW_NUMBER() or NTILE()): You can assign a row number to each salary within its group (e.g., department) after ordering by salary. Then, filter for rows where the row number corresponds to the middle position(s). NTILE(2) can also divide the dataset into two halves, making it easier to identify the median group.
2. PERCENTILE_CONT() (PostgreSQL, SQL Server): Some modern SQL databases provide a direct function for calculating percentiles, which can be used to find the median (50th percentile).
3. Subqueries with COUNT(): Without window functions, you can count the total number of employees and then use subqueries to find the salary at (COUNT + 1) / 2 and (COUNT + 2) / 2 positions for odd and even counts, respectively, after ordering.

Example explanation

Let's assume we have an Employees table with id, name, department, and salary. We want to find the median salary for each department.

id	name	department	salary
1	Alice	HR	50000
2	Bob	HR	60000
3	Carol	HR	55000
4	David	Engineering	80000
5	Eve	Engineering	90000
6	Frank	Engineering	70000
7	Grace	Engineering	85000

For the HR department: [50000, 55000, 60000]. Sorted: [50000, 55000, 60000]. Median is 55000. For the Engineering department: [80000, 90000, 70000, 85000]. Sorted: [70000, 80000, 85000, 90000]. Median is (80000 + 85000) / 2 = 82500.

A SQL query using ROW_NUMBER() would involve:

1. Partitioning by department.
2. Ordering by salary.
3. Assigning ROW_NUMBER().
4. Calculating COUNT(*) over the partition.
5. Selecting salaries where ROW_NUMBER() is equal to (count + 1) / 2 or (count + 2) / 2.
6. Averaging these selected salaries.

Common mistakes candidates make

A common mistake for the "Median Employee Salary" database problem is attempting to use AVG() directly or applying LIMIT and OFFSET without proper ordering and handling of odd/even counts, which can lead to incorrect medians. Candidates often forget to partition by department when the problem requires a median per group. Misunderstanding how ROW_NUMBER() or RANK() works with ties, or not correctly averaging the two middle elements for even-sized sets, are also frequent errors. Not accounting for the difference between database systems' median functions (e.g., PERCENTILE_CONT() vs. manual calculation) can also be a pitfall.

Interview preparation tip

For the Median Employee Salary interview question, thoroughly understand SQL Window Functions, especially ROW_NUMBER(), RANK(), and NTILE(). Practice how to apply PARTITION BY and ORDER BY clauses within these functions. Solve variations of finding Nth highest/lowest values. Understand the logic for calculating the median for both odd and even sized datasets. If your target database supports it, familiarize yourself with PERCENTILE_CONT(). Work through examples manually to confirm your SQL logic. Focus on writing clear, readable SQL using CTEs to break down complex logic.