Watch 10 video solutions for Calculate Special Bonus, a easy level problem involving Database. This walkthrough by Everyday Data Science has 6,418 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Employees
+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | | salary | int | +-------------+---------+ employee_id is the primary key (column with unique values) for this table. Each row of this table indicates the employee ID, employee name, and salary.
Write a solution to calculate the bonus of each employee. The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee's name does not start with the character 'M'. The bonus of an employee is 0 otherwise.
Return the result table ordered by employee_id.
The result format is in the following example.
Example 1:
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 | +-------------+-------+ Explanation: The employees with IDs 2 and 8 get 0 bonus because they have an even employee_id. The employee with ID 3 gets 0 bonus because their name starts with 'M'. The rest of the employees get a 100% bonus.
Problem Overview: The Employees table contains employee_id, name, and salary. You must compute a bonus column where an employee receives their full salary as a bonus only if the employee_id is odd and the name does not start with 'M'. Otherwise the bonus is 0. The result should return employee_id and bonus, ordered by employee_id.
Approach 1: Using Conditional Logic (O(n) time, O(1) space)
This approach uses SQL conditional expressions such as CASE WHEN to compute the bonus during a single table scan. For each row, check two conditions: employee_id % 2 = 1 (odd ID) and name NOT LIKE 'M%' (name does not start with M). If both are true, return the salary; otherwise return 0. The database evaluates this logic row-by-row while scanning the table once, making the time complexity O(n) with constant extra space O(1). This is the most common SQL solution and works consistently across MySQL, PostgreSQL, and SQL Server. The core concept relies on database queries and SQL conditional expressions.
Approach 2: Functional Programming Style (O(n) time, O(1) space)
Instead of explicit branching, treat the condition as a boolean expression and convert it directly into a numeric result. For example, multiply salary by a condition such as (employee_id % 2 = 1 AND name NOT LIKE 'M%'). In some SQL dialects and in languages like Python or JavaScript, boolean values evaluate to 1 or 0, effectively toggling the bonus without a full CASE statement. This style is concise and mirrors a functional mapping operation where each row is transformed into a new value. It still scans the table once, giving O(n) time and O(1) space complexity. This pattern appears frequently when working with database result transformations and lightweight SQL expressions.
Recommended for interviews: The Conditional Logic (CASE) approach is what interviewers typically expect. It clearly shows that you can translate problem rules into SQL conditions and structure a query that computes derived values. The functional style is shorter and elegant, but the explicit CASE version is easier to read and widely supported across database systems.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Conditional Logic with CASE | O(n) | O(1) | Best general solution. Clear SQL logic and compatible with most databases. |
| Functional Boolean Expression | O(n) | O(1) | Useful for concise queries when the SQL dialect supports boolean-to-integer evaluation. |
