Employee Bonus - Practice Coding Problems

Employee Bonus - Problem

Database Easy

You're managing a company's HR system with two important tables: Employee and Bonus. Your task is to find all employees who either received a small bonus (less than $1000) or no bonus at all.

This is a classic SQL problem that tests your understanding of LEFT JOIN operations and handling NULL values - essential skills for any database developer!

Given:

Employee Table: Contains employee details (ID, name, supervisor, salary)
Bonus Table: Contains bonus information (employee ID and bonus amount)

Goal: Return the name and bonus of employees who have bonus < 1000 OR no bonus entry.

Note: Some employees might not have any entry in the Bonus table, which should be treated as having no bonus.

Input & Output

example_1.sql — Basic Case

$ Input: Employee: [(1,'Alice',null,70000), (2,'Bob',1,80000), (3,'Charlie',2,90000)] Bonus: [(1,800), (3,1500)]

› Output: [('Alice',800), ('Bob',null)]

💡 Note: Alice has bonus 800 < 1000 ✓, Bob has no bonus entry ✓, Charlie has bonus 1500 ≥ 1000 ✗

example_2.sql — All Have Bonuses

$ Input: Employee: [(1,'John',null,60000), (2,'Jane',1,75000)] Bonus: [(1,500), (2,1200)]

› Output: [('John',500)]

💡 Note: John has bonus 500 < 1000 ✓, Jane has bonus 1200 ≥ 1000 ✗

example_3.sql — No Bonuses

$ Input: Employee: [(1,'Mary',null,55000), (2,'Tom',1,65000)] Bonus: []

› Output: [('Mary',null), ('Tom',null)]

💡 Note: Both employees have no bonus entries, so both should be included with null bonuses

Visualization

Tap to expand

Understanding the Visualization

Setup Tables

We have Employee records (everyone) and Bonus records (selective)

LEFT JOIN Magic

Merge tables keeping ALL employees, bonus becomes NULL if missing

Apply Business Logic

Filter for small bonuses (< 1000) OR no bonuses (NULL)

Present Results

Return employee names with their bonus status

Key Takeaway

🎯 Key Insight: LEFT JOIN is perfect for "optional" relationships - it keeps all records from the left table while gracefully handling missing matches as NULL values.

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through both tables with efficient JOIN operation

✓ Linear Growth

Space Complexity

O(k)

Space for result set only, database handles JOIN efficiently

✓ Linear Space

Constraints

1 ≤ Employee.empId ≤ 10⁴
1 ≤ Employee.name.length ≤ 20
0 ≤ Bonus.bonus ≤ 10⁶
Employee.empId is unique
Some employees may not have bonus entries

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 18

The optimal solution uses a LEFT JOIN approach to merge Employee and Bonus tables while preserving all employees. The key insight is that LEFT JOIN naturally handles missing bonus records by setting them to NULL, then we simply filter for bonus < 1000 OR bonus IS NULL. This gives us O(n+m) time complexity in a single, efficient database operation.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Query Approach	O(n²)	O(1)	Use subqueries to check bonus conditions for each employee
LEFT JOIN Approach (Optimal)	O(n + m)	O(k)	Use LEFT JOIN to merge tables and filter in one efficient operation

Nested Query Approach — Algorithm Steps

For each employee, check if they have a bonus record
If they have a bonus, check if it's less than 1000
If no bonus record exists, include them in results
Combine results using UNION or complex WHERE conditions

Visualization

Tap to expand

Step-by-Step Walkthrough

Start with Employee 1

Take first employee and search entire bonus table

Check Every Bonus

Scan through all bonus records to find match

Apply Filter

If found and < 1000, or not found, include in result

Repeat Process

Move to next employee and repeat entire search

Code -

solution.c — C

// C simulation with arrays
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int empId;
    char name[50];
    int supervisor;
    int salary;
} Employee;

typedef struct {
    int empId;
    int bonus;
} Bonus;

typedef struct {
    char name[50];
    int bonus;
    int hasBonus;
} Result;

int solution(Employee* employees, int empCount, Bonus* bonuses, int bonusCount, Result* result) {
    int resultCount = 0;
    
    for (int i = 0; i < empCount; i++) {
        int found = 0;
        int bonusAmount = 0;
        
        // Search for bonus (nested loop simulation)
        for (int j = 0; j < bonusCount; j++) {
            if (bonuses[j].empId == employees[i].empId) {
                found = 1;
                bonusAmount = bonuses[j].bonus;
                break;
            }
        }
        
        if (found && bonusAmount < 1000) {
            strcpy(result[resultCount].name, employees[i].name);
            result[resultCount].bonus = bonusAmount;
            result[resultCount].hasBonus = 1;
            resultCount++;
        } else if (!found) {
            strcpy(result[resultCount].name, employees[i].name);
            result[resultCount].bonus = 0;
            result[resultCount].hasBonus = 0;
            resultCount++;
        }
    }
    
    return resultCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each employee, we scan the bonus table, leading to nested loop behavior

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

Constraints

1 ≤ Employee.empId ≤ 10⁴
1 ≤ Employee.name.length ≤ 20
0 ≤ Bonus.bonus ≤ 10⁶
Employee.empId is unique
Some employees may not have bonus entries

42.8K Views

High Frequency

~8 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Nested Query Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler