Employee Bonus

Employee Bonus

1. What is this problem about?

The Employee Bonus coding problem is a fundamental database query challenge. You are given two tables: an Employee table containing names and IDs, and a Bonus table containing IDs and bonus amounts. The task is to report the name and bonus amount of each employee with a bonus less than 1000. Crucially, if an employee does not have a bonus at all (i.e., they are missing from the Bonus table), they should still be included in the results, as their effective bonus is less than 1000.

2. Why is this asked in interviews?

This is a classic Database interview question used by companies like Uber and Microsoft to test a candidate's understanding of SQL Joins, specifically the difference between an INNER JOIN and a LEFT JOIN. It also checks for the ability to handle NULL values correctly. In real-world software engineering, data is often fragmented across tables, and knowing how to retrieve "missing" or "optional" relationships is a daily requirement for backend and data engineers.

3. Algorithmic pattern used

The core pattern here is the Left Outer Join. By performing a LEFT JOIN from the Employee table to the Bonus table on the employee ID, you ensure that every employee is represented in the resulting set. If no matching bonus exists, the bonus column will contain NULL. You then use a WHERE clause to filter for cases where the bonus is either less than 1000 or is NULL.

4. Example explanation

Imagine these two tables: Employees:

• ID 1: Alice
• ID 2: Bob
• ID 3: Charlie

Bonus:

• ID 1: 500
• ID 2: 2000

When we join them:

• Alice has 500 (Matches bonus < 1000).
• Bob has 2000 (Does not match).
• Charlie has NULL (Matches the criteria because no bonus is technically less than 1000). The output would show Alice and Charlie.

5. Common mistakes candidates make

• Using INNER JOIN: This is the most common error. An inner join would exclude Charlie entirely because he doesn't have a record in the Bonus table.
• Ignoring NULLs: Simply writing WHERE bonus < 1000 often fails in SQL because NULL < 1000 evaluates to UNKNOWN, not TRUE. You must explicitly check OR bonus IS NULL.
• Misidentifying the "Driver" Table: Joining from Bonus to Employee using a left join would only show employees who have a bonus record, which is the opposite of what's needed.

6. Interview preparation tip

Always clarify the requirements regarding missing data. Whenever a problem mentions "employees who did/did not receive X," your mind should immediately go to LEFT JOIN and IS NULL. Practice writing these queries and remember that in many SQL dialects, the order of tables in a LEFT JOIN matters significantly.