Interview Questions/SQL/Salary Quartile Distribution Per Department

Salary Quartile Distribution Per Department

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Salary Quartile Distribution Per Department

You are given an Employees table. Using a recursive CTE to generate the 4 quartile bucket numbers (1-4), compute for each department and quartile: how many employees fall in that bucket (via NTILE(4)), and the min and max salary in that bucket. Return (department, quartile, employee_count, min_sal, max_sal) for ALL 4 quartiles of every department, even if a quartile has 0 employees (show NULL for min/max in that case). Order by department, then quartile. Table: Employees

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Employee name
department	VARCHAR	Department name
salary	INT	Annual salary

Examples

Example 1

Use a recursive CTE to generate bucket numbers 1-4. CROSS JOIN with distinct departments. LEFT JOIN the NTILE(4) result. NTILE distributes rows as evenly as possible, adding extras to earlier buckets when the count is not divisible by 4.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql