Interview Drills · NTILE() · buckets
41. Salary quartiles
Concept: NTILE() · bucketsshow
NTILE(n) OVER (ORDER BY x) deals the rows into n numbered buckets as evenly as possible, in sort order. When the rows don't divide evenly, the first buckets get one extra row. NTILE(4) gives quartiles, NTILE(10) deciles.
Example (different data):
SELECT name, score, NTILE(10) OVER (ORDER BY score DESC) AS decile FROM students;
Your task
Comp review wants a company-wide view of pay bands: split everyone into four equal-sized groups by salary.
Assign every employee a pay_quartile with NTILE(4) over the whole company, ordered by salary highest first (quartile 1 = best paid). Return pay_quartile, employees (headcount), min_salary and max_salary for each quartile, sorted by pay_quartile.
Starting Postgres in your browser…
Checking your access…