Calculate Special Bonus

Calculate Special Bonus

What is this problem about?

The Calculate Special Bonus interview question is a SQL task that requires conditional logic. You are given an Employees table with columns employee_id, name, and salary. A special bonus is awarded to an employee if:

1. Their employee_id is an odd number.
2. Their name does not start with the character 'M'. If both conditions are met, the bonus is 100% of the salary; otherwise, the bonus is 0. This Calculate Special Bonus coding problem is a test of SQL's conditional expressions.

Why is this asked in interviews?

Google uses this to check a candidate's familiarity with SQL's CASE statement or IF function. It also tests your knowledge of string pattern matching (LIKE) and basic arithmetic operators (% for modulo). It's a fundamental test of whether you can translate business logic into a database query.

Algorithmic pattern used

This follows the Database interview pattern. You use a SELECT statement with a CASE WHEN clause to evaluate the conditions.

• employee_id % 2 != 0 checks for odd IDs.
• name NOT LIKE 'M%' checks that the name doesn't start with 'M'.

Example explanation

Employees:

1. ID: 1, Name: "Alice", Salary: 1000. (Odd ID, doesn't start with M). Bonus = 1000.
2. ID: 2, Name: "Bob", Salary: 2000. (Even ID). Bonus = 0.
3. ID: 3, Name: "Mark", Salary: 3000. (Odd ID, but starts with M). Bonus = 0.
4. ID: 4, Name: "John", Salary: 4000. (Even ID). Bonus = 0. Result table will show each ID and their calculated bonus.

Common mistakes candidates make

• Logical OR instead of AND: Giving a bonus if either condition is met, when the rules state both must be true.
• Case sensitivity: Not accounting for the fact that SQL's LIKE operator might be case-insensitive depending on the collation, though usually 'M' is specific.
• Missing Sort Order: Most SQL problems require the output to be sorted by employee_id unless stated otherwise.

Interview preparation tip

Practice using CASE WHEN ... THEN ... ELSE ... END. It is the most versatile way to implement complex branching logic within a SQL query. Also, remember that name NOT LIKE 'M%' is equivalent to LEFT(name, 1) != 'M'.