Performance Bonus Payout for FY2024-25 — SQL Problem
The annual bonus is a share of annual_ctc decided by the FY2024-25 rating: 5 → 20%, 4 → 12%, 3 → 8%, anything lower → 0.
- Difficulty: Medium
- Topics: Conditional Logic, Joins
- Dialect: MySQL
- Problem: #237
Problem statement
The annual bonus is a share of annual_ctc decided by the FY2024-25 rating: 5 → 20%, 4 → 12%, 3 → 8%, anything lower → 0. Two more rules: an employee who joined after 2024-10-01 (strictly after) is not eligible this year and gets 0, and an employee with no rating for the cycle (no row, or NULL) gets 0.
Return every employee with emp_id, rating (NULL when there is none) and bonus_amount in rupees. Order by emp_id.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| joined_on | date |
| annual_ctc | int |
Primary key: emp_id.
annual_ctc is the current yearly cost to company in rupees.
Table: Appraisal
| Column | Type |
|---|---|
| emp_id | int |
| cycle | varchar |
| rating | int |
Primary key: emp_id, cycle.
rating is 1–5, or NULL until the manager submits it.
Examples
Example 1
Employee
| emp_id | full_name | joined_on | annual_ctc |
|---|---|---|---|
| 1 | Aarav Sharma | 2021-05-10 | 1800000 |
| 2 | Diya Iyer | 2023-02-06 | 1200000 |
| 3 | Ishaan Gupta | 2024-10-01 | 900000 |
| 4 | Kavya Nair | 2024-11-18 | 1000000 |
| 5 | Rohan Das | 2022-08-01 | 1500000 |
| 6 | Meera Rao | 2020-03-02 | 2400000 |
Appraisal
| emp_id | cycle | rating |
|---|---|---|
| 1 | FY2024-25 | 5 |
| 2 | FY2024-25 | 3 |
| 3 | FY2024-25 | 4 |
| 4 | FY2024-25 | 5 |
| 5 | FY2024-25 | 2 |
| 6 | FY2023-24 | 5 |
Output
| emp_id | rating | bonus_amount |
|---|---|---|
| 1 | 5 | 360000 |
| 2 | 3 | 96000 |
| 3 | 4 | 108000 |
| 4 | 5 | 0 |
| 5 | 2 | 0 |
| 6 | NULL | 0 |
How to solve Performance Bonus Payout for FY2024-25
One row per employee, a payout decided by rules — a LEFT JOIN plus a CASE.
The LEFT JOIN to Appraisal must carry a.cycle = 'FY2024-25' in its ON clause. That way an employee with only an older appraisal, or none, still comes through once with rating NULL; moving the condition to WHERE would delete exactly those employees. The cycle in the key also guarantees at most one matching row, so nobody is duplicated.
CASE branches are tried in order, so the eligibility rule goes first: joined_on > '2024-10-01' gives 0 whatever the rating (joining on 1 October itself is still eligible). Then the three bands, and ELSE 0 for ratings 1–2 and for NULL — NULL = 5 is unknown, so a NULL rating falls through every band to the ELSE.
The alternative reads the rating with scalar subqueries, maps it to a percentage with a simple CASE rating WHEN … and turns the no-rating case into 0 with IFNULL. Both are one indexed lookup per employee.
Reference solution (MySQL)
SELECT e.emp_id, a.rating,
CASE
WHEN e.joined_on > '2024-10-01' THEN 0
WHEN a.rating = 5 THEN e.annual_ctc * 20 / 100
WHEN a.rating = 4 THEN e.annual_ctc * 12 / 100
WHEN a.rating = 3 THEN e.annual_ctc * 8 / 100
ELSE 0
END AS bonus_amount
FROM Employee e
LEFT JOIN Appraisal a ON a.emp_id = e.emp_id AND a.cycle = 'FY2024-25'
ORDER BY e.emp_idAnother way
SELECT e.emp_id,
(SELECT rating FROM Appraisal a WHERE a.emp_id = e.emp_id AND a.cycle = 'FY2024-25') AS rating,
IF(e.joined_on <= '2024-10-01',
e.annual_ctc * IFNULL((SELECT CASE rating WHEN 5 THEN 20 WHEN 4 THEN 12 WHEN 3 THEN 8 ELSE 0 END
FROM Appraisal a WHERE a.emp_id = e.emp_id AND a.cycle = 'FY2024-25'), 0) / 100,
0) AS bonus_amount
FROM Employee e
ORDER BY e.emp_id← High Performers Due for a Promotion Review · Appraisal Ratings Falling Two Cycles Running →