Drill 8 of 12 in HR questions
0

Interview Question Bank · PERCENTILE_CONT per group · join back · same population

8. Paid well below the department median

MediumHRQuestion #188

Concept: PERCENTILE_CONT per group · join back · same populationshow

PERCENTILE_CONT is an ordered-set aggregate, so Postgres won't run it as a window function with OVER. Compute each group's median in a CTE with GROUP BY, then join it back to the detail rows so every row can be compared with its own group. Build both from the same filtered population, or rows that are out of scope shift the median.

Example (different data):

WITH med AS (
  SELECT region, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY rent) AS median_rent
  FROM listings WHERE active GROUP BY region
)
SELECT l.id, l.region, l.rent, med.median_rent
FROM listings l JOIN med USING (region)
WHERE l.active AND l.rent > 1.5 * med.median_rent;

Your task

Quillmoor's pay equity review flags current employees paid at least 10% below the median salary of their department, where the median is taken over current employees too.

For current employees, return name, department, salary, dept_median (median salary of the department's current employees, rounded to 2 decimals) and pct_vs_median (100 * (salary - median) / median, rounded to 1 decimal) for everyone whose salary is at most 90% of their department median. Sort by pct_vs_median, then name.

Starting Postgres in your browser…

Checking your access…