Objective

Write an SQL query to calculate the median salary for each job title within a company. The median salary is the middle value in the list of salaries for a particular job title when the list is sorted in ascending order. If the number of salaries is even, the median is the average of the two middle numbers. Your query should return the job titles and their corresponding median salaries, rounded to two decimal places, in descending order of the median salary.

Additional information

The employees table:

• Constraints:

  • The number of employees can range from 1 to 10^5.
  • The salary values are positive integers.

• Clarifications:

  • Ensure that the median salary is calculated correctly based on the number of employees for each job title.
  • The results should be sorted in descending order of the median salary.
  • If multiple job titles have the same median salary, order them alphabetically by job title.

WITH SalaryRanks AS (
    SELECT
        job_title,
        salary,
        COUNT(*) OVER (PARTITION BY job_title) AS count_per_title,
        ROW_NUMBER() OVER (PARTITION BY job_title ORDER BY salary) AS row_num
    FROM employees
)
SELECT
    job_title,
    ROUND(AVG(salary)::numeric, 2) AS median_salary
FROM SalaryRanks
WHERE row_num IN ((count_per_title + 1) / 2, (count_per_title + 2) / 2)
GROUP BY job_title
ORDER BY
    median_salary DESC,
    job_title ASC;

Explanation

Step 1: Rank salaries and count totals per job title

ROW_NUMBER() OVER (PARTITION BY job_title ORDER BY salary) AS row_num,
COUNT(*) OVER (PARTITION BY job_title) AS count_per_title

ROW_NUMBER() assigns a sequential rank to each salary within a job title, sorted ascending. COUNT(*) as a window function records the total number of salaries for that title in every row. Both are needed to identify which row(s) sit at the median position.

Step 2: Identify the median row(s)

WHERE row_num IN ((count_per_title + 1) / 2, (count_per_title + 2) / 2)

For an odd count, both expressions resolve to the same middle row. For an even count, they point to the two middle rows. This handles both cases in a single filter.

Step 3: Average the median row(s)

ROUND(AVG(salary)::numeric, 2) AS median_salary

AVG(salary) on the filtered rows returns the single middle value for odd counts and the average of the two middle values for even counts. ROUND(..., 2) formats to two decimal places.

Step 4: Order results

ORDER BY
    median_salary DESC,
    job_title ASC;

Lists the highest median salaries first. When multiple job titles share the same median salary, they are ordered alphabetically.