SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…