Appraisal Rating Mix by Department

Medium
233.

Appraisal Rating Mix by Department

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

ColumnType
emp_idint
full_namevarchar
departmentvarchar

Primary key: emp_id.

Table: Appraisal

ColumnType
emp_idint
cyclevarchar
ratingint

Primary key (together): 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.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namedepartment
1Aarav SharmaEngineering
2Diya IyerEngineering
3Ishaan GuptaEngineering
4Kavya NairSales
5Rohan DasSales
6Meera RaoFinance
7Zara KhanLegal

Appraisal

emp_idcyclerating
1FY2024-255
2FY2024-253
3FY2024-254
3FY2023-242
4FY2024-252
5FY2024-254
6FY2024-25NULL
7FY2024-251

Output

departmentexceedsmeetsbelowexceeds_pct
Engineering21066.67
Sales10150
Legal0010
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
full_namevarchar
departmentvarchar

Primary key: emp_id.

Table: Appraisal

ColumnType
emp_idint
cyclevarchar
ratingint

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_idfull_namedepartment
1Aarav SharmaEngineering
2Diya IyerEngineering
3Ishaan GuptaEngineering
4Kavya NairSales
5Rohan DasSales
6Meera RaoFinance
7Zara KhanLegal

Appraisal

emp_idcyclerating
1FY2024-255
2FY2024-253
3FY2024-254
3FY2023-242
4FY2024-252
5FY2024-254
6FY2024-25NULL
7FY2024-251

Output

departmentexceedsmeetsbelowexceeds_pct
Engineering21066.67
Sales10150
Legal0010

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.department

Another 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 →