Helix Systems' HR director needs three salary benchmarks from current pay records to calibrate compensation bands.
Write a query to return the 25th-percentile, 50th-percentile, and 75th-percentile actual-value salary across every current pay record as a single row.
Assumptions:
- The
salariestable has one row per pay record with anamountand anend_date. - A current pay record has a missing
end_date. Only current pay records contribute to the benchmarks. - Each percentile threshold is the actual
amountvalue at or above that percentile position when the values are sorted ascending — the result is always one of the actual salary amounts in the data.
Output:
- A single row with columns
p25_salary,p50_salary, andp75_salary.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
PERCENTILE_DISC(0.25) WITHIN GROUP (
ORDER BY
amount
) AS p25_salary,
PERCENTILE_DISC(0.5) WITHIN GROUP (
ORDER BY
amount
) AS p50_salary,
PERCENTILE_DISC(0.75) WITHIN GROUP (
ORDER BY
amount
) AS p75_salary
FROM
salaries
WHERE
end_date IS NULLThe shape
Three PERCENTILE_DISC calls on the same column at three percentile positions, filtered to current pay records, return the 25th, 50th, and 75th salary thresholds on a single row. PERCENTILE_DISC always returns an actual salary from the data, which is the HR director's requirement: the benchmarks have to be real numbers that exist in someone's pay record, not interpolated values.
Clause by clause
SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amount) AS p25_salary, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS p50_salary, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amount) AS p75_salaryreturns the three thresholds in a single row. EachPERCENTILE_DISCwalks the same sorted set ofamountvalues; each picks the actual value at or just above its percentile position. The three calls run independently, but they share the same sort.FROM salariesis the row source.WHERE end_date IS NULLkeeps only the current pay records. A NULLend_dateis the marker for "still active"; any row with a non-NULLend_dateis a historical record and is excluded before the percentile calculations run.
Why PERCENTILE_DISC and not PERCENTILE_CONT
The prompt is explicit: each benchmark must be one of the actual salary amounts in the data. PERCENTILE_CONT would interpolate between two surrounding salaries at the 25th, 50th, and 75th positions, and the interpolated value would typically not match any real salary in the table. PERCENTILE_DISC always returns a value that exists in the data, which is what calibrating compensation bands to real pay records requires.
The trap
WHERE end_date IS NULL is doing load-bearing work. NULL means "no end date set, so the pay record is current." Comparing with = instead of IS NULL would return no rows, because nothing equals NULL in SQL. If the filter is left off entirely, the percentile calculations include every historical pay record alongside the current ones, and the benchmarks shift to reflect a much larger population than the HR director intended.
You practiced PERCENTILE_DISC at multiple percentile positions over a pre-restricted record set — three thresholds in one query, each returning a value drawn from the input data.
