Low or Missing Bonus

A hotel group pays a quarterly bonus to some of its staff. HR wants to follow up with everyone who received a bonus below 300 dollars or no bonus at all (no row in the bonus table).

Return staff_id, full_name and bonus_amount for those people, in any order. For staff with no bonus row, bonus_amount must be NULL. A bonus of exactly 300 is not below 300. Each person appears at most once.

Tables

hotel_staff — One row per employee. Names are not unique.

quarterly_bonus — At most one row per employee who was awarded a bonus this quarter.

Example

hotel_staff

quarterly_bonus

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 22 of 70 · SQLite

The idea

The answer must include people who have no row in quarterly_bonus, so start from hotel_staff and LEFT JOIN the bonuses. Every employee survives the join. Those without a bonus get NULL in bonus_amount.

Then filter in WHERE: keep rows where the amount is below 300 or is NULL. The IS NULL branch is essential. NULL < 300 evaluates to unknown, not true, so without it the missing-bonus staff are dropped again.

Where the filter goes matters. In WHERE it removes rows after the join. In the ON clause it only decides which bonus rows attach, and every employee is still returned. Because staff_id is the key of the bonus table, the join cannot duplicate anyone.

The alternative turns the question around: keep staff for whom no bonus of 300 or more exists (NOT EXISTS), and fetch the amount with a scalar subquery.

Solution

SELECT s.staff_id,
       s.full_name,
       b.bonus_amount
FROM hotel_staff AS s
LEFT JOIN quarterly_bonus AS b
  ON b.staff_id = s.staff_id
WHERE b.bonus_amount < 300
   OR b.bonus_amount IS NULL;

Another way

SELECT s.staff_id,
       s.full_name,
       (SELECT b.bonus_amount
        FROM quarterly_bonus AS b
        WHERE b.staff_id = s.staff_id) AS bonus_amount
FROM hotel_staff AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM quarterly_bonus AS b
  WHERE b.staff_id = s.staff_id
    AND b.bonus_amount >= 300
);

Why common answers fail

An inner join drops everyone without a bonus row, which is half of what was asked.

SELECT s.staff_id, s.full_name, b.bonus_amount
FROM hotel_staff AS s
JOIN quarterly_bonus AS b ON b.staff_id = s.staff_id
WHERE b.bonus_amount < 300;

The LEFT JOIN keeps people with no bonus, but the WHERE test on NULL is not true, so they are filtered out again.

SELECT s.staff_id, s.full_name, b.bonus_amount
FROM hotel_staff AS s
LEFT JOIN quarterly_bonus AS b ON b.staff_id = s.staff_id
WHERE b.bonus_amount < 300;

Putting the filter in the ON clause keeps every employee; well-paid staff just show a NULL bonus instead of being removed.

SELECT s.staff_id, s.full_name, b.bonus_amount
FROM hotel_staff AS s
LEFT JOIN quarterly_bonus AS b
  ON b.staff_id = s.staff_id AND b.bonus_amount < 300;

Uses <= so a bonus of exactly 300 is wrongly included.

SELECT s.staff_id, s.full_name, b.bonus_amount
FROM hotel_staff AS s
LEFT JOIN quarterly_bonus AS b ON b.staff_id = s.staff_id
WHERE b.bonus_amount <= 300 OR b.bonus_amount IS NULL;

What the interviewer is checking

A quick check on LEFT JOIN semantics and three-valued logic: does the candidate know that a NULL fails every comparison, and the difference between filtering in ON and in WHERE?

Mistakes to avoid

• Using an inner join and losing everyone without a bonus.
• Writing only bonus_amount < 300 after a LEFT JOIN, which silently removes the NULL rows.
• Moving the condition into ON, which returns the whole staff list.

Follow-ups to expect

• Show 0 instead of NULL for missing bonuses (COALESCE(b.bonus_amount, 0)).
• Count such people per department (GROUP BY department on the same join).
• What if an employee could have several bonus rows? (Aggregate bonuses per person first, then join.)

In real work

Finding customers below a spend threshold or with no orders at all, or devices with low or missing heartbeat counts, is the same LEFT JOIN plus IS NULL check.

Practice next