Performance Bonus Payout for FY2024-25

Medium
237.

Performance Bonus Payout for FY2024-25

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

ColumnType
emp_idint
full_namevarchar
joined_ondate
annual_ctcint

Primary key: emp_id.

annual_ctc is the current yearly cost to company in rupees.

Table: Appraisal

ColumnType
emp_idint
cyclevarchar
ratingint

Primary key (together): emp_id, cycle.

rating is 1–5, or NULL until the manager submits it.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namejoined_onannual_ctc
1Aarav Sharma2021-05-101800000
2Diya Iyer2023-02-061200000
3Ishaan Gupta2024-10-01900000
4Kavya Nair2024-11-181000000
5Rohan Das2022-08-011500000
6Meera Rao2020-03-022400000

Appraisal

emp_idcyclerating
1FY2024-255
2FY2024-253
3FY2024-254
4FY2024-255
5FY2024-252
6FY2023-245

Output

emp_idratingbonus_amount
15360000
2396000
34108000
450
520
6NULL0
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.

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

ColumnType
emp_idint
full_namevarchar
joined_ondate
annual_ctcint

Primary key: emp_id.

annual_ctc is the current yearly cost to company in rupees.

Table: Appraisal

ColumnType
emp_idint
cyclevarchar
ratingint

Primary key: emp_id, cycle.

rating is 1–5, or NULL until the manager submits it.

Examples

Example 1

Employee

emp_idfull_namejoined_onannual_ctc
1Aarav Sharma2021-05-101800000
2Diya Iyer2023-02-061200000
3Ishaan Gupta2024-10-01900000
4Kavya Nair2024-11-181000000
5Rohan Das2022-08-011500000
6Meera Rao2020-03-022400000

Appraisal

emp_idcyclerating
1FY2024-255
2FY2024-253
3FY2024-254
4FY2024-255
5FY2024-252
6FY2023-245

Output

emp_idratingbonus_amount
15360000
2396000
34108000
450
520
6NULL0

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_id

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