Median salary per company without MEDIAN()

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

employee_salaries records salaries with columns id, company, and salary. Find the median salary row(s) for each company without calling any built-in MEDIAN function. If a company has an odd count of employees, return the single middle row. If it has an even count, return both middle rows. Order results by company, salary, and id ascending.

Requirements

• Output columns: id, company, salary.
• Sort by company ascending, salary ascending, id ascending.

Constraints

• id is unique.
• salary is an integer.
• Do not use MEDIAN() or PERCENTILE_CONT().

Examples

Input: employee_salaries id,company,salary 1,A,2341 2,A,341 3,A,15 4,A,15314 5,A,451 6,A,513 7,B,15 ... Output: id | company | salary 5 | A | 451 6 | A | 513 12 | B | 234 9 | B | 1154 14 | C | 2645 Why this passes: Company A has 6 rows so the two middle rows (451 and 513) are returned; B also has 6 (234, 1154); C has 5, so one middle row (2645).

Input: employee_salaries id | company | salary 1 | M | 50 2 | M | 100 3 | M | 150 Output: id | company | salary 2 | M | 100 Why this passes: Odd count of 3 returns the single middle record at index 2.

Topics: lakebench, sql, median, odd-even.

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