LeetCode 569. Median Employee Salary SQL Solution

Problem

LeetCode SQL Problem

Median Employee Salary

Employee table

Id	Company	Salary
1	A	2341
2	A	341
3	A	15
4	A	15314
5	A	451
6	A	513
7	B	15
8	B	13
9	B	1154
10	B	1345
11	B	1221
12	B	234
13	C	2345
14	C	2645
15	C	2645
16	C	2652
17	C	65

Solution

MySQL
TSQL

Median Employee Salary
WITH median_salary_analysis
AS (
	SELECT *,
		CASE
			WHEN count(e.Salary) OVER (PARTITION BY e.Company) % 2 = 0
				THEN 1
			ELSE 0
			END has_even_employees,
		ceiling(count(e.Salary) OVER (PARTITION BY e.Company) / 2) AS mid_position,
		row_number() OVER (
			PARTITION BY e.Company ORDER BY e.Salary
			) AS row_num
	FROM Employee e
	)
SELECT msa.Id,
	msa.Company,
	msa.Salary
FROM median_salary_analysis msa
WHERE (
		msa.has_even_employees = 1
		AND (
			msa.row_num BETWEEN msa.mid_position
				AND msa.mid_position + 1
			)
		)
	OR (
		msa.has_even_employees = 0
		AND msa.row_num = msa.mid_position
		)
ORDER BY msa.Company,
	msa.Salary;

Median Employee Salary
WITH median_salary_analysis
AS (
	SELECT *,
		CASE
			WHEN count(e.Salary) OVER (PARTITION BY e.Company) % 2 = 0
				THEN 1
			ELSE 0
			END has_even_employees,
		ceiling(count(e.Salary) OVER (PARTITION BY e.Company) / 2) AS mid_position,
		row_number() OVER (
			PARTITION BY e.Company ORDER BY e.Salary
			) AS row_num
	FROM Employee e
	)
SELECT msa.Id,
	msa.Company,
	msa.Salary
FROM median_salary_analysis msa
WHERE (
		msa.has_even_employees = 1
		AND (
			msa.row_num BETWEEN msa.mid_position
				AND msa.mid_position + 1
			)
		)
	OR (
		msa.has_even_employees = 0
		AND msa.row_num = msa.mid_position
		)
ORDER BY msa.Company,
	msa.Salary;

This SQL query calculates the median salary for each company in the Employee table.

The query starts by defining a common table expression (CTE) called median_salary_analysis, which selects all columns from the Employee table and also adds several computed columns. These computed columns include:

has_even_employees: A flag indicating whether the company has an even number of employees.
mid_position: The middle position of the sorted salaries (i.e., the position of the median).
row_num: A row number assigned to each employee within their company based on the ascending order of their salary.

The query then selects the Id, Company, and Salary columns from the median_salary_analysis CTE. The WHERE clause filters the results based on whether the company has an even or odd number of employees and whether the current row number falls within the range of the median position or is equal to the median position.

Finally, the results are ordered in ascending order by company name and salary.

Query Output

Id	Company	Salary
5	A	451
6	A	513
12	B	234
9	B	1154
14	C	2645

Problem​

Solution​

Problem

Solution