Calculate Special Bonus - Problem
🎯 Employee Bonus Calculator
You're tasked with implementing a company's special bonus system! Given an employee database, you need to calculate each employee's bonus based on two specific criteria:
- Employee ID is odd AND
- Employee name does NOT start with 'M'
If both conditions are met, the employee receives a 100% salary bonus (bonus = salary). Otherwise, they receive no bonus (bonus = 0).
Input: A table Employees with columns employee_id (int), name (varchar), and salary (int).
Output: Return employee_id and bonus for each employee, ordered by employee_id.
Example: Employee with ID 3, name "Alice", salary 1000 → gets bonus 1000 (odd ID, name doesn't start with M). Employee with ID 2, name "Bob", salary 2000 → gets bonus 0 (even ID).
Input & Output
example_1.sql — Basic Example
$
Input:
Employees table:
+-------------+-------+--------+
| employee_id | name | salary |
+-------------+-------+--------+
| 2 | Meir | 3000 |
| 3 | Michael| 3800 |
| 7 | Addilyn| 7400 |
| 8 | Juan | 6100 |
| 9 | Kannon | 7700 |
+-------------+-------+--------+
›
Output:
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
+-------------+-------+
💡 Note:
Employee 2: Even ID → bonus = 0. Employee 3: Odd ID but name starts with 'M' → bonus = 0. Employee 7: Odd ID and name doesn't start with 'M' → bonus = salary = 7400. Employee 8: Even ID → bonus = 0. Employee 9: Odd ID and name doesn't start with 'M' → bonus = salary = 7700.
example_2.sql — Edge Cases with Names
$
Input:
Employees table:
+-------------+-------+--------+
| employee_id | name | salary |
+-------------+-------+--------+
| 1 | Mary | 2000 |
| 5 | mike | 1500 |
| 11 | Alice | 3000 |
+-------------+-------+--------+
›
Output:
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 1 | 0 |
| 5 | 1500 |
| 11 | 3000 |
+-------------+-------+
💡 Note:
Employee 1: Odd ID but name starts with 'M' (uppercase) → bonus = 0. Employee 5: Odd ID and name starts with 'm' (lowercase, not 'M') → bonus = 1500. Employee 11: Odd ID and name doesn't start with 'M' → bonus = 3000. Note: Case sensitivity matters - 'M' ≠ 'm'.
example_3.sql — Single Employee
$
Input:
Employees table:
+-------------+-------+--------+
| employee_id | name | salary |
+-------------+-------+--------+
| 4 | Bob | 5000 |
+-------------+-------+--------+
›
Output:
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 4 | 0 |
+-------------+-------+
💡 Note:
Employee 4: Even ID (4 % 2 = 0) → bonus = 0, regardless of the name not starting with 'M'.
Constraints
- 1 ≤ employee_id ≤ 1000
- 1 ≤ name.length ≤ 20
- 1000 ≤ salary ≤ 106
- Name consists of uppercase and lowercase English letters
- Employee_id is unique (primary key)
Visualization
Tap to expand
Understanding the Visualization
1
Gate 1: ID Check
Employee approaches first gate - is their ID number odd?
2
Gate 2: Name Check
If they pass Gate 1, check if their name starts with 'M'
3
Prize Distribution
Only employees who pass both gates receive the full salary bonus
Key Takeaway
🎯 Key Insight: Use conditional SQL logic (CASE/IF) to implement business rules efficiently in a single query pass
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code