Difficulty: Hard | Concepts: Window Functions, PERCENTILE_CONT, ROW_NUMBER, COUNT OVER(), Median Calculation

One of the most frequently asked SQL interview questions for data engineers and data analysts is how to calculate the median in SQL — because unlike AVG(), there is no simple aggregate function that works the same way across every database. This is a great test of whether you truly understand SQL window functions, not just memorized syntax.

Median in SQL Interview Question — Problem Statement

You are given a table of employee salaries across departments. Find the median salary per department using SQL. Your solution should work correctly whether the department has an even or odd number of employees.

Dataset

CREATE TABLE Employee_Salary
(
Emp_ID INT,
Department VARCHAR(30),
Salary INT
);

INSERT INTO Employee_Salary VALUES
(1,'Engineering',55000),
(2,'Engineering',62000),
(3,'Engineering',58000),
(4,'Engineering',71000),
(5,'Sales',48000),
(6,'Sales',52000),
(7,'Sales',50000);

Expected Output

Department	Median Salary
Engineering	60000
Sales	50000

Solution 1 — Calculate Median Using PERCENTILE_CONT

The cleanest way to find the median value in a SQL table on databases that support it (PostgreSQL, Snowflake, Oracle, BigQuery) is the PERCENTILE_CONT window function:

SELECT DISTINCT
    Department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY Department) AS Median_Salary
FROM Employee_Salary;

Solution 2 — Calculate Median in SQL Without PERCENTILE_CONT

Many interviewers specifically ask you to calculate median in SQL Server or MySQL, where PERCENTILE_CONT either isn't supported or behaves differently. Here's the manual approach using ROW_NUMBER() and COUNT() OVER():

WITH Ranked AS (
    SELECT
        Department,
        Salary,
        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary) AS rn,
        COUNT(*) OVER (PARTITION BY Department) AS cnt
    FROM Employee_Salary
)
SELECT
    Department,
    AVG(Salary * 1.0) AS Median_Salary
FROM Ranked
WHERE rn IN ( (cnt + 1) / 2, (cnt + 2) / 2 )
GROUP BY Department;

Explanation

The manual method ranks salaries within each department using ROW_NUMBER() PARTITION BY, then uses COUNT() OVER() to know how many rows exist per group. For an odd count, both computed row positions point to the same middle row; for an even count, they point to the two middle rows, and averaging them gives the correct median — this is exactly how median calculation with window functions is expected to work in a real interview whiteboard session.

Visual Representation

Engineering salaries sorted: 55000, 58000, 62000, 71000 ^mid1 ^mid2 Median = (58000 + 62000) / 2 = 60000 Sales salaries sorted: 48000, 50000, 52000 ^mid Median = 50000

Conclusion

Knowing how to find median without built-in functions in SQL is a strong signal to interviewers that you understand window function mechanics deeply, not just syntax. This pattern — ROW_NUMBER() + COUNT() OVER() + conditional averaging — shows up constantly in SQL interview questions for data analyst and data engineer roles at product-based companies.