Salary Quartile Distribution Per Department

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

Medium

Description

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

Database Schema (Inferred)

Employees

Column Name	Example Value
id	1
name	A
department	Eng
salary	90000

Example

Employees

id	name	department	salary
1	A	Eng	90000
2	B	Eng	85000
3	C	Eng	70000
4	D	Eng	60000
5	E	Eng	55000
6	F	Sales	50000
7	G	Sales	45000
8	H	Sales	40000
9	I	Sales	38000
10	J	HR	80000
11	K	HR	75000
12	L	HR	65000

Output

department	quartile	employee_count	min_sal	max_sal
Eng	Q1	2	55000	60000
Eng	Q2	1	70000	70000
Eng	Q3	1	85000	85000
Eng	Q4	1	90000	90000
HR	Q1	1	65000	65000
HR	Q2	1	75000	75000
HR	Q3	1	80000	80000
HR	Q4	0	NULL	NULL
Sales	Q1	1	38000	38000
Sales	Q2	1	40000	40000
Sales	Q3	1	45000	45000
Sales	Q4	1	50000	50000

Explanation:

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.