Calculate Special Bonus - Problem
Given a table Employees with employee information, calculate the bonus for each employee based on specific rules.
Bonus Rules:
- If employee ID is odd AND name does not start with 'M' → bonus = 100% of salary
- Otherwise → bonus = 0
Return the result ordered by employee_id.
Table Schema
Employees
| Column Name | Type | Description |
|---|---|---|
employee_id
PK
|
int | Primary key, unique employee identifier |
name
|
varchar | Employee name |
salary
|
int | Employee salary |
Primary Key: employee_id
Note: Each row represents one employee with their ID, name, and salary
Input & Output
Example 1 — Mixed Conditions
Input Table:
| employee_id | name | salary |
|---|---|---|
| 2 | Meir | 3000 |
| 3 | Michael | 1000 |
| 1 | Will | 1000 |
Output:
| employee_id | bonus |
|---|---|
| 1 | 1000 |
| 2 | 0 |
| 3 | 0 |
💡 Note:
Employee 1: ID is odd (1) and name 'Will' doesn't start with 'M' → bonus = 1000
Employee 2: ID is even (2) → bonus = 0
Employee 3: ID is odd (3) but name 'Michael' starts with 'M' → bonus = 0
Example 2 — All Eligible
Input Table:
| employee_id | name | salary |
|---|---|---|
| 1 | Alice | 5000 |
| 3 | Bob | 4000 |
Output:
| employee_id | bonus |
|---|---|
| 1 | 5000 |
| 3 | 4000 |
💡 Note:
Both employees have odd IDs and names not starting with 'M', so they get full salary as bonus.
Example 3 — No Eligible
Input Table:
| employee_id | name | salary |
|---|---|---|
| 2 | John | 2000 |
| 4 | Mary | 3000 |
Output:
| employee_id | bonus |
|---|---|
| 2 | 0 |
| 4 | 0 |
💡 Note:
Employee 2 has even ID and Employee 4 has even ID, so both get 0 bonus regardless of names.
Constraints
-
1 ≤ employee_id ≤ 1000 -
nameconsists of uppercase and lowercase letters -
1 ≤ salary ≤ 10^6
Visualization
Tap to expand
Understanding the Visualization
1
Input
Employee table with ID, name, salary
2
Conditions
Check odd ID AND name ≠ 'M'
3
Output
Employee ID with calculated bonus
Key Takeaway
🎯 Key Insight: Use CASE WHEN for conditional bonus calculation based on multiple criteria
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code