Special Bonus Calculation

MOD() and text case sensitivity vary by database and collation. These examples assume positive employee IDs and non-NULL names.

Problem Statement

A company wants to give a 100% bonus (a full extra month’s salary) to certain employees, but only if they meet two specific rules.

Business context This is a common pattern in payroll systems: bonuses are not given to everyone equally, they depend on rules set by HR or management. Sometimes those rules look arbitrary from the outside (like an ID being odd), but they still need to be applied exactly and consistently across every employee.

Goal For every employee, decide whether they qualify for a bonus, and if they do, their bonus equals their full salary. If they don’t qualify, their bonus is 0.

Input A table of employees with their ID, name, and monthly salary.

Output Each employee’s ID along with their calculated bonus.

Table Structure

Table: Employees

Column Meaning

• employee_id — a unique number identifying each employee, and also the primary key of this table
• name — the employee’s name
• salary — the employee’s monthly salary, which becomes their bonus amount if they qualify

Important Notes

• employee_id is the primary key, so it is guaranteed to be unique and never NULL.
• There are two conditions that both need to be true at the same time for someone to get a bonus:
  1. The employee_id must be an odd number.
  2. The employee’s name must NOT start with the letter ‘M’.
• If either condition fails, the bonus is 0, not partially reduced. This is a strict all-or-nothing rule, not a sliding scale.
• Name comparisons in SQL can be case-sensitive or case-insensitive depending on the database and its collation settings. Most default MySQL setups treat 'M%' as matching both ‘Michael’ and ‘michael’, but this can vary, so it’s worth being aware of in an interview setting.

Example

Input: Employees table

Expected Output

CREATE TABLE Employees (employee_id INTEGER, name TEXT, salary INTEGER);
INSERT INTO Employees VALUES
  (2, 'Meir', 3000),
  (3, 'Michael', 3800),
  (7, 'Addilyn', 7400),
  (8, 'Juan', 6100),
  (9, 'Kannon', 7700);

Explanation

• ID 2: even number, fails the odd check → bonus 0
• ID 3: odd number, but name “Michael” starts with ‘M’ → fails the second check → bonus 0
• ID 7: odd number, name “Addilyn” does not start with ‘M’ → both checks pass → bonus equals salary, 7400
• ID 8: even number, fails the odd check → bonus 0
• ID 9: odd number, name “Kannon” does not start with ‘M’ → both checks pass → bonus equals salary, 7700

Intuition

Think of this like a club with two entry rules at the door. To get in, you need to satisfy both rules, not just one.

Rule 1: your membership number must be odd. Rule 2: your name must not start with the letter M.

If you fail even one rule, you don’t get in, no exceptions, no partial credit. Once someone actually gets through the door (passes both checks), they get the full reward, their entire salary as a bonus.

This is a good example of combining two very different types of conditions, a number check and a text check, into a single rule using AND. SQL doesn’t care that one condition is about numbers and the other is about text, both just need to evaluate to TRUE for the row to qualify.

Mental Model

Imagine a bouncer standing at a door with a checklist taped to a clipboard. For every person, the checklist just has two boxes to tick:

• Is their number odd? ✅ or ❌
• Does their name avoid starting with M? ✅ or ❌

The bouncer doesn’t let someone in with just one checkmark. Both boxes need to be ticked. If both are ticked, that person gets handed a bonus, equal to something tied to them personally (their salary). If either box is unchecked, they get sent away with nothing, which in this case means a bonus of 0, not an error or a missing value.

Thought Process

Before writing SQL, break this into two separate smaller problems, then combine them.

1. How do I check if a number is odd in SQL? Most databases support the MOD() function, which returns the remainder after division. MOD(employee_id, 2) returns 1 when the ID is odd, and 0 when it’s even. So MOD(employee_id, 2) = 1 becomes your odd check.
2. How do I check if a name does NOT start with a specific letter? SQL’s LIKE operator supports wildcard matching. 'M%' means “starts with M, followed by anything.” To check the opposite (does not start with M), you simply add NOT in front: name NOT LIKE 'M%'.
3. How do I combine both checks into one rule? Since both conditions must be true together, join them with AND inside a single WHEN condition.
4. What happens if the row doesn’t pass both checks? It should return 0, which naturally fits into an ELSE branch.
5. What should the actual bonus value be, if the employee qualifies? Not a fixed number, but their own salary. So the THEN part of the CASE should reference the salary column directly, not a hardcoded number.

Once you see the problem as “two independent checks joined by AND, feeding into a CASE that returns either salary or 0,” writing the SQL becomes direct.

Approach

Step 1: Check if employee_id is odd Use MOD(employee_id, 2) = 1. This works reliably across all rows, since MOD always returns either 0 (even) or 1 (odd) for whole numbers.

Step 2: Check if the name does not start with ‘M’ Use name NOT LIKE 'M%'. The % wildcard means “any characters after this point,” so 'M%' matches any name beginning with M, and NOT LIKE flips that to match everything else.

Step 3: Combine both conditions with AND Both checks must pass together, so join them using AND inside a single WHEN clause.

Step 4: Assign the bonus If both conditions are true, return the employee’s salary as their bonus. Otherwise, return 0 using ELSE.

Step 5: Sort the output Order the final result by employee_id, so the output is easy to read and matches the expected format.

SQL Query

SELECT
    employee_id,
    -- Bonus is granted only if BOTH conditions are true:
    -- 1) employee_id is odd (MOD by 2 leaves remainder 1)
    -- 2) name does NOT start with the letter 'M'
    CASE
        WHEN MOD(employee_id, 2) = 1 AND name NOT LIKE 'M%' THEN salary
        ELSE 0
    END AS bonus
FROM Employees
ORDER BY employee_id;

Query Explanation

• SELECT employee_id — brings back the employee ID unchanged, since the output only needs the ID and the calculated bonus, not the name or salary directly.
• MOD(employee_id, 2) = 1 — checks if dividing the ID by 2 leaves a remainder of 1, which is only true for odd numbers.
• name NOT LIKE ‘M%’ — checks that the name does not begin with the letter M. The % symbol is a wildcard meaning “zero or more of any character,” so 'M%' matches any string starting with M, regardless of length.
• AND — combines both checks. Both must independently evaluate to TRUE for the whole condition to be TRUE.
• THEN salary — if both checks pass, the bonus is set to that employee’s own salary value, not a fixed number.
• ELSE 0 — if either check fails, the bonus defaults to 0.
• ORDER BY employee_id — sorts the final output numerically by employee ID, so rows appear in a predictable order.

Dry Run

Original table

↓

What SQL does first: check both conditions per row

Note: employee 2 fails both checks at once, the ID is even, and the name “Meir” also starts with M. Since AND requires every condition to be true, failing even one (let alone both) is enough to send this row straight to ELSE.

↓

Assigning bonus based on the combined result

↓

Final output (sorted by employee_id)

SQL Execution Flow

FROM Employees
   ↓
Read one row at a time
   ↓
Evaluate MOD(employee_id, 2) = 1
   ↓
Evaluate name NOT LIKE 'M%'
   ↓
Combine both with AND
   ↓
Assign salary (if true) or 0 (if false) via CASE
   ↓
SELECT employee_id, bonus
   ↓
ORDER BY employee_id
   ↓
Return sorted final output

Since there’s no GROUP BY or HAVING, each row is processed independently, and ORDER BY is the very last step, applied only after every row already has its bonus value calculated.

Time Complexity

Time Complexity: O(n log n) Evaluating the CASE condition for each row is O(n), since it’s a constant amount of work per row. However, the ORDER BY at the end requires sorting the result set, and general-purpose sorting typically takes O(n log n) time.

Space Complexity: O(n) Sorting usually requires the database to hold the result set (or a good portion of it) in memory or temporary storage to reorder it, unlike the previous two problems, which only needed constant extra space.

Why: The bonus calculation itself is a simple per-row operation, but the ORDER BY step is what changes the overall complexity, since sorting cannot be done in a single straight pass through the data the way independent per-row checks can.

Common Mistakes to Avoid

Mistake 1: Using LIKE ‘M%’ instead of NOT LIKE ‘M%’

Why it happens: It’s easy to misread the requirement as “name starts with M gets a bonus” instead of the actual rule, which is the opposite.

Wrong:

CASE WHEN MOD(employee_id, 2) = 1 AND name LIKE 'M%' THEN salary ELSE 0 END

This gives bonuses to the wrong group of employees entirely, rewarding names starting with M instead of excluding them.

Correct:

CASE WHEN MOD(employee_id, 2) = 1 AND name NOT LIKE 'M%' THEN salary ELSE 0 END

Mistake 2: Checking for odd numbers using employee_id % 2 != 0 inconsistently

Why it happens: Different databases support slightly different syntax for modulo, and mixing them up can cause syntax errors or unexpected results.

Wrong (in databases that don’t support this operator):

WHEN employee_id % 2 = 1 THEN salary

This works in MySQL and PostgreSQL, but not every SQL dialect supports the % operator the same way.

Correct: Use MOD(employee_id, 2) = 1, which works consistently across most major databases, or check your specific database’s supported syntax.

Mistake 3: Forgetting that ELSE is required here for correctness, even though it’s technically optional

Why it happens: Since CASE technically works without ELSE (defaulting to NULL), it’s tempting to skip it.

Wrong:

CASE WHEN MOD(employee_id, 2) = 1 AND name NOT LIKE 'M%' THEN salary END

Without ELSE, every employee who doesn’t qualify gets a bonus of NULL instead of 0, which does not match the expected output format at all.

Correct: Always include ELSE 0, since the problem explicitly states non-qualifying employees should show a bonus of 0, not a missing value.

Mistake 4: Hardcoding a bonus amount instead of referencing salary

Why it happens: Beginners sometimes assume “100% bonus” means some fixed number, rather than realizing it means “equal to whatever that employee’s salary already is.”

Wrong:

CASE WHEN MOD(employee_id, 2) = 1 AND name NOT LIKE 'M%' THEN 100 ELSE 0 END

This gives every qualifying employee the same flat bonus of 100, ignoring their actual salary completely.

Correct: Reference the salary column directly in THEN, since the bonus is defined as 100% of that person’s own salary.

Mistake 5: Forgetting ORDER BY entirely

Why it happens: The bonus logic is the main focus, so the sorting requirement at the end can get overlooked.

Why it matters: Without ORDER BY, the database is free to return rows in any order it wants, which might not match the expected output shown in the problem, especially in interview settings where output order can matter for validation.

Correct: Add ORDER BY employee_id at the end of the query if the expected output is shown in a specific sorted order.

Further Questions

• “How would you modify this to give a 50% bonus instead of a full 100% bonus?”
• “How would this query behave if the name column contained NULL values?”
• “Can you rewrite the odd-number check using the modulo operator (%) instead of MOD()? Which databases support which syntax?”
• “How would you make the name check case-insensitive if the database’s default collation was case-sensitive?”
• “How would you extend this to apply three different bonus tiers instead of just two (bonus vs no bonus)?”

Similar Problems

• Range and pattern-based classification problems, similar to the price categorization example from the CASE Expression concept notes
• Problems combining multiple unrelated condition types (numeric plus text) using AND inside a single CASE
• Problems requiring pattern matching with LIKE and wildcards (%, _) for filtering or classifying text data

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to check if a number is odd or even → MOD(column, 2) = 1 (odd) or = 0 (even)
• Need to check if text starts with, ends with, or contains a specific pattern → LIKE with % wildcards, or NOT LIKE to exclude a pattern
• Need to apply a reward or value only when multiple unrelated conditions are all true → combine them with AND inside a single CASE WHEN
• Need the “reward” itself to depend on existing data (like salary) rather than being a fixed number → reference the actual column name in the THEN clause, not a hardcoded value
• Output needs to be in a specific readable order → don’t forget ORDER BY as the final step