Median employee salary

SQL data engineering interview problem. Difficulty: advanced. Pattern: Aggregation. About 18 minutes. Part of the Pro drill bank.

The median is the middle value when all values are sorted. It is often better than the average, because a single very high salary does not move it much. Return 1 row with 1 column, median_salary, which is the median of all the salaries in employees that are not NULL. You can use the MEDIAN(salary) function, or another way to get the 50th percentile.

Requirements

• Return exactly 1 row with the column median_salary.
• Use only salaries that are not NULL.
• The result is the middle salary when the values are sorted. With an even number of values, it is the average of the two middle ones.

Constraints

• The table is employees.
• Use MEDIAN(salary) or PERCENTILE_CONT(0.5).
• Return only 1 column: median_salary.

Examples

Input: employees (non-null salaries) salary | 95000 | 95000 | 105000 | 110000 | 120000 | Output: median_salary | 105000.0 | Why this passes: MEDIAN ignores NULL and returns the middle value of the five ordered non-null salaries.

Topics: lakebench, sql, median, percentile.

More SQL interview questions · All interview problems · Learn data engineering