1873. Calculate Special Bonus - Solution

The problem

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.

Approach overview

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 1: Approach 1: Using Conditional Logic

This approach involves iterating through each employee, checking if their employee_id is odd and their name does not start with 'M'. If both conditions are met, the bonus is set to their full salary; otherwise, it is set to 0. Loop through each employee and apply these checks systematically.

Python

class Employee:
    def __init__(self, employee_id, name, salary):
        self.employee_id = employee_id
        self.name = name
        self.salary = salary


def calculate_bonus(employees):
    results = []
    for employee in employees:
        bonus = employee.salary if employee.employee_id % 2 == 1 and not employee.name.startswith('M') else 0
        results.append((employee.employee_id, bonus))
    return results


employees = [
    Employee(2, "Meir", 3000),
    Employee(3, "Michael", 3800),
    Employee(7, "Addilyn", 7400),
    Employee(8, "Juan", 6100),
    Employee(9, "Kannon", 7700)
]

results = calculate_bonus(employees)

print("+-------------+-------+")
print("| employee_id | bonus |")
print("+-------------+-------+")
for employee_id, bonus in results:
    print(f"| {employee_id:11} | {bonus:5} |")
print("+-------------+-------+")

Java

import java.util.*;

class Employee {
    int employee_id;
    String name;
    int salary;

    Employee(int employee_id, String name, int salary) {
        this.employee_id = employee_id;
        this.name = name;
        this.salary = salary;
    }
}

class Result {
    int employee_id;
    int bonus;
    
    Result(int employee_id, int bonus) {
        this.employee_id = employee_id;
        this.bonus = bonus;
    }
}

public class CalculateBonus {

    public static List<Result> calculateBonus(List<Employee> employees) {
        List<Result> result = new ArrayList<>();
        for (Employee employee : employees) {
            int bonus = 0;
            if (employee.employee_id % 2 != 0 && !employee.name.startsWith("M")) {
                bonus = employee.salary;
            }
            result.add(new Result(employee.employee_id, bonus));
        }
        return result;
    }
    
    public static void main(String[] args) {
        List<Employee> employees = Arrays.asList(
            new Employee(2, "Meir", 3000),
            new Employee(3, "Michael", 3800),
            new Employee(7, "Addilyn", 7400),
            new Employee(8, "Juan", 6100),
            new Employee(9, "Kannon", 7700)
        );
        
        List<Result> results = calculateBonus(employees);

        System.out.println("+-------------+-------+");
        System.out.println("| employee_id | bonus |");
        System.out.println("+-------------+-------+");
        for (Result res : results) {
            System.out.printf("| %11d | %5d |\n", res.employee_id, res.bonus);
        }
        System.out.println("+-------------+-------+");
    }
}

C++

#include <iostream>
#include <vector>
#include <string>

struct Employee {
    int employee_id;
    std::string name;
    int salary;
};

std::vector<std::pair<int, int>> calculate_bonus(const std::vector<Employee> &employees) {
    std::vector<std::pair<int, int>> result;
    for (const auto &employee : employees) {
        int bonus = 0;
        if (employee.employee_id % 2 == 1 && employee.name[0] != 'M') {
            bonus = employee.salary;
        }
        result.emplace_back(employee.employee_id, bonus);
    }
    return result;
}

int main() {
    std::vector<Employee> employees = {
        {2, "Meir", 3000},
        {3, "Michael", 3800},
        {7, "Addilyn", 7400},
        {8, "Juan", 6100},
        {9, "Kannon", 7700}
    };
    
    auto results = calculate_bonus(employees);

    std::cout << "+-------------+-------+\n";
    std::cout << "| employee_id | bonus |\n";
    std::cout << "+-------------+-------+\n";
    for (const auto &[id, bonus] : results) {
        std::cout << "| " << id << "            | " << bonus << "   |\n";
    }
    std::cout << "+-------------+-------+\n";
    return 0;
}

JavaScript

class Employee {
    constructor(employeeId, name, salary) {
        this.employeeId = employeeId;
        this.name = name;
        this.salary = salary;
    }
}

function calculateBonus(employees) {
    return employees.map(employee => {
        const bonus = (employee.employeeId % 2 !== 0 && !employee.name.startsWith('M')) ? employee.salary : 0;
        return { employee_id: employee.employeeId, bonus: bonus };
    });
}

const employees = [
    new Employee(2, "Meir", 3000),
    new Employee(3, "Michael", 3800),
    new Employee(7, "Addilyn", 7400),
    new Employee(8, "Juan", 6100),
    new Employee(9, "Kannon", 7700)
];

const results = calculateBonus(employees);

console.log("+-------------+-------+");
console.log("| employee_id | bonus |");
console.log("+-------------+-------+");
results.forEach(({ employee_id, bonus }) => {
    console.log(`| ${employee_id.toString().padStart(11)} | ${bonus.toString().padStart(5)} |`);
});
console.log("+-------------+-------+");

#include <stdio.h>
#include <string.h>

struct Employee {
    int employee_id;
    char name[100];
    int salary;
};

struct Result {
    int employee_id;
    int bonus;
};

void calculate_bonus(struct Employee employees[], int n, struct Result results[]) {
    for (int i = 0; i < n; i++) {
        results[i].employee_id = employees[i].employee_id;
        if (employees[i].employee_id % 2 == 1 && employees[i].name[0] != 'M') {
            results[i].bonus = employees[i].salary;
        } else {
            results[i].bonus = 0;
        }
    }
}

int main(void) {
    struct Employee employees[] = {
        {2, "Meir", 3000},
        {3, "Michael", 3800},
        {7, "Addilyn", 7400},
        {8, "Juan", 6100},
        {9, "Kannon", 7700}
    };
    int n = sizeof(employees) / sizeof(employees[0]);
    struct Result results[n];
    
    calculate_bonus(employees, n, results);

    printf("+-------------+-------+\n");
    printf("| employee_id | bonus |\n");
    printf("+-------------+-------+\n");
    for (int i = 0; i < n; i++) {
        printf("| %11d | %5d |\n", results[i].employee_id, results[i].bonus);
    }
    printf("+-------------+-------+\n");
    return 0;
}

using System;
using System.Collections.Generic;

public class Employee {
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public int Salary { get; set; }
}

public class Result {
    public int EmployeeId { get; set; }
    public int Bonus { get; set; }
}

public class CalculateBonus {
    public static List<Result> GetBonuses(List<Employee> employees) {
        List<Result> results = new List<Result>();
        foreach (var employee in employees) {
            int bonus = 0;
            if (employee.EmployeeId % 2 != 0 && !employee.Name.StartsWith("M")) {
                bonus = employee.Salary;
            }
            results.Add(new Result { EmployeeId = employee.EmployeeId, Bonus = bonus });
        }
        return results;
    }
    
    public static void Main() {
        List<Employee> employees = new List<Employee> {
            new Employee { EmployeeId = 2, Name = "Meir", Salary = 3000 },
            new Employee { EmployeeId = 3, Name = "Michael", Salary = 3800 },
            new Employee { EmployeeId = 7, Name = "Addilyn", Salary = 7400 },
            new Employee { EmployeeId = 8, Name = "Juan", Salary = 6100 },
            new Employee { EmployeeId = 9, Name = "Kannon", Salary = 7700 }
        };
        
        List<Result> results = GetBonuses(employees);

        Console.WriteLine("+-------------+-------+");
        Console.WriteLine("| employee_id | bonus |");
        Console.WriteLine("+-------------+-------+");
        foreach (var result in results) {
            Console.WriteLine($"| {result.EmployeeId,11} | {result.Bonus,5} |");
        }
        Console.WriteLine("+-------------+-------+");
    }
}

This Python code defines an Employee class. It iterates through each employee and checks the bonus logic using a conditional expression. Results are stored in a list of tuples, which are printed at the end.

Complexity

Time Complexity: O(n), where n is the number of employees.
Space Complexity: O(n), for storing the results.

Try this approach in the editor

Approach 2: Approach 2: Functional Programming Style

This approach utilizes functional programming paradigms such as map/filter. It maps through each row of data, filters the rows based on the bonus eligibility criteria, and transforms them with the mapped values.

Python

class Employee:
    def __init__(self, employee_id, name, salary):
        self.employee_id = employee_id
        self.name = name
        self.salary = salary


employees = [
    Employee(2, "Meir", 3000),
    Employee(3, "Michael", 3800),
    Employee(7, "Addilyn", 7400),
    Employee(8, "Juan", 6100),
    Employee(9, "Kannon", 7700)
]

results = list(map(lambda e: (e.employee_id, e.salary) if e.employee_id % 2 != 0 and not e.name.startswith('M') else (e.employee_id, 0), employees))

print("+-------------+-------+")
print("| employee_id | bonus |")
print("+-------------+-------+")
for employee_id, bonus in results:
    print(f"| {employee_id:11} | {bonus:5} |")
print("+-------------+-------+")

JavaScript

class Employee {
    constructor(employeeId, name, salary) {
        this.employeeId = employeeId;
        this.name = name;
        this.salary = salary;
    }
}

const employees = [
    new Employee(2, "Meir", 3000),
    new Employee(3, "Michael", 3800),
    new Employee(7, "Addilyn", 7400),
    new Employee(8, "Juan", 6100),
    new Employee(9, "Kannon", 7700)
];

const results = employees.map(({employeeId, name, salary}) => {
    const bonus = (employeeId % 2 !== 0 && !name.startsWith('M')) ? salary : 0;
    return { employee_id: employeeId, bonus };
});

console.log("+-------------+-------+");
console.log("| employee_id | bonus |");
console.log("+-------------+-------+");
results.forEach(({ employee_id, bonus }) => {
    console.log(`| ${employee_id.toString().padStart(11)} | ${bonus.toString().padStart(5)} |`);
});
console.log("+-------------+-------+");

This Python solution uses lambda functions and 'map' to apply the bonus logic across all employees in a functional style approach without using an explicit loop. It's concise and leverages built-in Python capabilities.

Complexity

Time Complexity: O(n), where n is the number of employees.
Space Complexity: O(n), for storing the results.

Try this approach in the editor

Approach 3: IF Statement + ORDER BY Clause

We can use the IF statement to determine the calculation method of the bonus, and then use ORDER BY to sort the results by employee_id.

MySQL

# Write your MySQL query statement below
SELECT
    employee_id,
    IF(
        employee_id % 2 = 0
        OR LEFT(name, 1) = 'M',
        0,
        salary
    ) AS bonus
FROM
    employees
ORDER BY 1;

Try this approach in the editor

Complexity comparison

Approach	Complexity
Approach 1: Using Conditional Logic	Time Complexity: O(n), where n is the number of employees. Space Complexity: O(n), for storing the results.
Approach 2: Functional Programming Style	Time Complexity: O(n), where n is the number of employees. Space Complexity: O(n), for storing the results.
IF Statement + ORDER BY Clause	–

Complexity analysis

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.

Frequently asked questions

Is Calculate Special Bonus easy or hard?

Calculate Special Bonus is classified as an Easy problem. It mainly tests understanding of SQL filtering and conditional expressions rather than advanced algorithms or complex query optimization.

Calculate Special Bonus Python/Java solution

When implemented outside SQL, iterate through employee records and compute the bonus using a conditional check: if employee_id is odd and the name does not start with 'M', set bonus to salary; otherwise set it to 0. The algorithm runs in O(n) time with constant extra space.

How to solve Calculate Special Bonus in O(n)?

Run a single SQL query that computes the bonus using conditional logic. Use CASE WHEN employee_id % 2 = 1 AND name NOT LIKE 'M%' THEN salary ELSE 0 END and order the result by employee_id. Since the query processes each row once, it runs in O(n) time.

What is the best approach for Calculate Special Bonus?

The best approach uses a SQL CASE expression to evaluate the bonus condition for each row. If employee_id is odd and the name does not start with 'M', return the salary; otherwise return 0. The query scans the table once, giving O(n) time complexity and O(1) extra space.

Is Calculate Special Bonus asked at Google/Amazon/Meta?

This problem represents a common SQL interview pattern used by companies that test database fundamentals, including Google, Amazon, and Meta. The focus is not the difficulty but the ability to translate business rules into a clean SQL query.

What data structure is used in Calculate Special Bonus?

The problem operates directly on a relational database table. No additional data structures like arrays or hash maps are required because the calculation is done per row using SQL conditional expressions.

What is the time complexity of Calculate Special Bonus?

The time complexity is O(n) because the database scans each row in the Employees table exactly once to evaluate the condition. No joins, subqueries, or additional data structures are required, so the space complexity remains O(1).

Calculate Special Bonus solution and explanation

The problem

Approach overview

Approach 1: Approach 1: Using Conditional Logic

Approach 2: Approach 2: Functional Programming Style

Approach 3: IF Statement + ORDER BY Clause

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Calculate Special Bonus solution and explanation

The problem

Approach overview

Approach 1: Approach 1: Using Conditional Logic

Approach 2: Approach 2: Functional Programming Style

Approach 3: IF Statement + ORDER BY Clause

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems