Special Bonus Logic
ASKED IN INTERVIEWApple
Problem Statement
Apple is rewarding employees with a special bonus. You need to calculate the bonus amount for each employee.
The bonus logic is as follows:
- The bonus is 100% of the salary if the employee_id is an odd number AND the employee name does NOT start with the letter M.
- Otherwise, the bonus is 0.
Write an SQL query to return the employee_id and the calculated bonus.
Rules:
- Output columns: employee_id, bonus.
- Results must be sorted by employee_id in ascending order.
Tests your understanding of
Logic, Strings and Conditional Aggregation
Input Tables
employees
| employee_id(INTEGER) | name(VARCHAR) | salary(INTEGER) |
|---|---|---|
| 2 | Meir | 3000 |
| 3 | Michael | 3800 |
| 7 | Addison | 7400 |
| 8 | Juan | 6100 |
| 9 | Kiki | 7700 |
departments
| dept_id(INTEGER) | dept_name(VARCHAR) |
|---|---|
| 1 | Engineering |
| 2 | Sales |
employee_dept_mapping
| employee_id(INTEGER) | dept_id(INTEGER) |
|---|---|
| 2 | 1 |
| 3 | 1 |
| 7 | 2 |
| 8 | 2 |
| 9 | 1 |
Expected Output
| employee_id(INTEGER) | bonus(INTEGER) |
|---|---|
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
Tags
EasyASKED IN INTERVIEWLogicStringsConditional Aggregation
10-15 min
65%