Median CTC for Each Job Level

Hard
245.

Median CTC for Each Job Level

The compensation team benchmarks pay bands by the median annual CTC of each job level, because a few very high packages make the average misleading. Only people on the payroll count (status is not 'exited'). With an odd headcount the median is the middle CTC; with an even headcount it is the average of the two middle CTCs.

Return one row per job level that has at least one on-payroll employee, with job_level, headcount and median_ctc. Order by job_level.

Tables

Table: Employee

ColumnTypeValues
emp_idint
job_levelenumL1, L2, L3, L4, L5
annual_ctcint
statusenumactive, notice, exited

Primary key: emp_id.

annual_ctc is the yearly cost to company in rupees. Several people can have exactly the same CTC.

Example

The rows must come back in the order shown.

Employee

emp_idjob_levelannual_ctcstatus
1L1600000active
2L1750000active
3L1700000notice
4L15000000exited
5L21200000active
6L21500000active
7L21300000active
8L21800000active
9L32400000active
10L43600000exited

Output

job_levelheadcountmedian_ctc
L13700000
L241400000
L312400000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Median CTC for Each Job Level — SQL Window Functions Problem

The compensation team benchmarks pay bands by the median annual CTC of each job level, because a few very high packages make the average misleading.

  • Difficulty: Hard
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #245

Problem statement

The compensation team benchmarks pay bands by the median annual CTC of each job level, because a few very high packages make the average misleading. Only people on the payroll count (status is not 'exited'). With an odd headcount the median is the middle CTC; with an even headcount it is the average of the two middle CTCs.

Return one row per job level that has at least one on-payroll employee, with job_level, headcount and median_ctc. Order by job_level.

Tables

Table: Employee

ColumnType
emp_idint
job_levelenum(L1, L2, L3, L4, L5)
annual_ctcint
statusenum(active, notice, exited)

Primary key: emp_id.

annual_ctc is the yearly cost to company in rupees. Several people can have exactly the same CTC.

Examples

Example 1

Employee

emp_idjob_levelannual_ctcstatus
1L1600000active
2L1750000active
3L1700000notice
4L15000000exited
5L21200000active
6L21500000active
7L21300000active
8L21800000active
9L32400000active
10L43600000exited

Output

job_levelheadcountmedian_ctc
L13700000
L241400000
L312400000

How to solve Median CTC for Each Job Level

SQL has no portable MEDIAN, so we locate the middle rows ourselves. After dropping exited employees, number each level's rows by CTC with ROW_NUMBER() OVER (PARTITION BY job_level ORDER BY annual_ctc, emp_id) and attach the level's size with COUNT(*) OVER (PARTITION BY job_level). Equal CTCs get distinct numbers (the emp_id tie-break), which is fine: equal values are interchangeable for the median.

For n rows the middle positions are (n + 1) DIV 2 and n DIV 2 + 1. When n is odd they are the same position (for n = 3: 2 and 2); when n is even they are the two middle ones (for n = 4: 2 and 3). Keeping those rows and taking AVG(annual_ctc) per level therefore gives the median in both cases without a CASE; MAX(cnt) reports the headcount.

The window-free alternative uses the definition: a value v is a middle value when at least half of the level's CTCs are ≤ v and at least half are ≥ v. For odd n only the median satisfies both; for even n exactly the two middle values do. Duplicates can make several rows qualify with the same value, so the average is taken over DISTINCT values. That version is quadratic per level; the window version is one sort.

Reference solution (MySQL)

WITH ranked AS (
  SELECT job_level, annual_ctc,
         ROW_NUMBER() OVER (PARTITION BY job_level ORDER BY annual_ctc, emp_id) AS rn,
         COUNT(*) OVER (PARTITION BY job_level) AS cnt
  FROM Employee
  WHERE status <> 'exited'
)
SELECT job_level, MAX(cnt) AS headcount, AVG(annual_ctc) AS median_ctc
FROM ranked
WHERE rn IN ((cnt + 1) DIV 2, cnt DIV 2 + 1)
GROUP BY job_level
ORDER BY job_level

Another way

WITH a AS (SELECT job_level, annual_ctc FROM Employee WHERE status <> 'exited'),
c AS (SELECT job_level, COUNT(*) AS n FROM a GROUP BY job_level)
SELECT x.job_level, c.n AS headcount, AVG(DISTINCT x.annual_ctc) AS median_ctc
FROM a x JOIN c ON c.job_level = x.job_level
WHERE 2 * (SELECT COUNT(*) FROM a y WHERE y.job_level = x.job_level AND y.annual_ctc <= x.annual_ctc) >= c.n
  AND 2 * (SELECT COUNT(*) FROM a y WHERE y.job_level = x.job_level AND y.annual_ctc >= x.annual_ctc) >= c.n
GROUP BY x.job_level, c.n
ORDER BY x.job_level

← Unplanned Absence Streaks of Three Working Days · 90- and 180-Day Retention of 2024 Hires by Quarter →