CTEs & Window Functions SQL practice problemMediumVerified answer

Salary Quartiles Per Department (NTILE)

Return each employee with their salary quartile (1–4).

  • CTEs
  • Window functions
  • Joins
  • Sorting

Interview brief

Understand the request

People analytics manager The quartile distribution model buckets employees into four salary tiers using NTILE.

Divide employees into 4 salary quartiles within each department (NTILE(4), ascending salary). Label them Q1–Q4. Return department_name, first_name, last_name, salary, salary_quartile — ordered by department_name, salary, employee_id.

Return

  • Return employee_name, department_id, salary, and salary_quartile.

Constraints

  • Use NTILE(4) OVER (ORDER BY salary) to assign quartiles.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

NTILE(4) assigns bucket numbers 1–4; earlier buckets get the extra row when the count is not divisible.

Hint 2

Add employee_id to the window ORDER BY so bucket assignment is deterministic on ties.

Hint 3

SQL Server uses + for string concat; here `'Q' || n` works on SQLite/Postgres (see Engine Notes).

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.department_name, e.first_name, e.last_name, e.salary, 'Q' || NTILE(4) OVER (PARTITION BY e.department_id ORDER BY e.salary, e.employee_id) AS salary_quartile FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.salary, e.employee_id;

Why this works

NTILE splits an ordered partition into N near-equal groups — the basis of quartile/decile/percentile-bucket reporting. With small partitions the buckets won't all be the same size; NTILE front-loads the remainder.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_namefirst_namelast_namesalarysalary_quartile
EngineeringAmandaWhite75000Q1
EngineeringLisaAnderson90000Q1
EngineeringRobertWilliams95000Q2
EngineeringEmilyChen125000Q2
EngineeringDavidMartinez130000Q3
EngineeringMichaelScott180000Q4
ExecutiveJenniferKing250000Q1
FinanceDanielLee70000Q1
FinanceJessicaMoore105000Q2
Human ResourcesChristopherWilson65000Q1

Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: