Appraisal Rating Mix by Department — SQL Problem
After the FY2024-25 appraisal cycle, HR checks how ratings (1 to 5) were spread in each department.
- Difficulty: Medium
- Topics: Conditional Logic, Joins, Aggregation
- Dialect: MySQL
- Problem: #233
Problem statement
After the FY2024-25 appraisal cycle, HR checks how ratings (1 to 5) were spread in each department. Ratings 4 and 5 are exceeds, 3 is meets, 1 and 2 are below. A NULL rating means the manager has not submitted it yet; it is left out of every count. Ratings from other cycles do not matter.
Return one row per department with at least one submitted FY2024-25 rating: department, exceeds, meets, below, and exceeds_pct = exceeds × 100 / (exceeds + meets + below), rounded to 2 decimals. Order by exceeds_pct descending, then department ascending.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| department | varchar |
Primary key: emp_id.
Table: Appraisal
| Column | Type |
|---|---|
| emp_id | int |
| cycle | varchar |
| rating | int |
Primary key: emp_id, cycle.
One row per employee per appraisal cycle ('FY2023-24', 'FY2024-25', …). rating is 1–5, or NULL until the manager submits it.
Examples
Example 1
Employee
| emp_id | full_name | department |
|---|---|---|
| 1 | Aarav Sharma | Engineering |
| 2 | Diya Iyer | Engineering |
| 3 | Ishaan Gupta | Engineering |
| 4 | Kavya Nair | Sales |
| 5 | Rohan Das | Sales |
| 6 | Meera Rao | Finance |
| 7 | Zara Khan | Legal |
Appraisal
| emp_id | cycle | rating |
|---|---|---|
| 1 | FY2024-25 | 5 |
| 2 | FY2024-25 | 3 |
| 3 | FY2024-25 | 4 |
| 3 | FY2023-24 | 2 |
| 4 | FY2024-25 | 2 |
| 5 | FY2024-25 | 4 |
| 6 | FY2024-25 | NULL |
| 7 | FY2024-25 | 1 |
Output
| department | exceeds | meets | below | exceeds_pct |
|---|---|---|---|---|
| Engineering | 2 | 1 | 0 | 66.67 |
| Sales | 1 | 0 | 1 | 50 |
| Legal | 0 | 0 | 1 | 0 |
How to solve Appraisal Rating Mix by Department
Conditional aggregation over a join. Each Appraisal row is joined to Employee for its department; the WHERE keeps this cycle and drops unsubmitted (NULL) ratings, so the rows left are exactly the ratings that count.
Grouping by department, three SUM(CASE …) expressions sort each rating into its bucket. Because NULLs are already gone, COUNT(*) equals exceeds + meets + below, and the share is exceeds × 100 / COUNT(*), rounded to 2 decimals. A department whose only rating is NULL has no rows left and forms no group — that is the "at least one submitted rating" rule, with no division by zero to guard.
If you keep the NULLs in the join (as the alternative does), rating >= 4 and the other tests are simply unknown for them, so the bucket counts still ignore them; but then the denominator must be the sum of the buckets, and empty departments must be filtered out afterwards. Order by the share, highest first, with the department name breaking ties. One pass plus the join.
Reference solution (MySQL)
SELECT e.department,
SUM(CASE WHEN a.rating >= 4 THEN 1 ELSE 0 END) AS exceeds,
SUM(CASE WHEN a.rating = 3 THEN 1 ELSE 0 END) AS meets,
SUM(CASE WHEN a.rating <= 2 THEN 1 ELSE 0 END) AS below,
ROUND(SUM(CASE WHEN a.rating >= 4 THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) AS exceeds_pct
FROM Appraisal a
JOIN Employee e ON e.emp_id = a.emp_id
WHERE a.cycle = 'FY2024-25' AND a.rating IS NOT NULL
GROUP BY e.department
ORDER BY exceeds_pct DESC, e.departmentAnother way
SELECT department, exceeds, meets, below, ROUND(exceeds * 100 / (exceeds + meets + below), 2) AS exceeds_pct
FROM (
SELECT e.department, COUNT(CASE WHEN a.rating IN (4, 5) THEN 1 END) AS exceeds,
COUNT(CASE WHEN a.rating = 3 THEN 1 END) AS meets, COUNT(CASE WHEN a.rating IN (1, 2) THEN 1 END) AS below
FROM Employee e JOIN Appraisal a ON a.emp_id = e.emp_id AND a.cycle = 'FY2024-25'
GROUP BY e.department
) t
WHERE exceeds + meets + below > 0
ORDER BY exceeds_pct DESC, department← Employees Averaging Less Than Eight Hours a Day · Hiring Funnel for Each Open Requisition →