Merit Rewards

Merit Rewards is one of the five questions in the HackerRank SQL (Basic) certification test. It needs one join and two conditions, and both conditions have a small trap.

What the question asks

A company wants to reward strong performers in its HR division. Return the employee_ID and name of every employee who works in HR and earned a bonus of 5000 or more in the last quarter.

“5000 or more” includes 5000 itself.

Tables

Both tables use employee_ID as the primary key.

Solution

SELECT e.employee_ID, e.name
FROM employee_information e
JOIN last_quarter_bonus b ON b.employee_ID = e.employee_ID
WHERE e.division = 'HR'
  AND b.bonus >= 5000;

How it works

1. The division is in employee_information and the bonus is in last_quarter_bonus. Joining on employee_ID puts them on one row.
2. e.division = 'HR' keeps only HR staff.
3. b.bonus >= 5000 keeps bonuses of 5000 and above.
4. Both conditions are joined with AND, because an employee needs to pass both.

My GitHub repo has WHERE ei.division LIKE 'HR'. Without a % or _, LIKE behaves the same as =, so it passes. But = says what you mean, so that’s what I use now.

Mistakes that fail the hidden tests

• Using > 5000. An employee with a bonus of exactly 5000 must be in the list. In my test, > dropped Neha, who had exactly 5000.
• Using LIKE 'HR%'. This also matches divisions that start with HR, such as “HR Ops”. In my test it added Ravi from HR Ops. If you want an exact match, use =.
• Forgetting the division filter. At least one public solution filters only on the bonus. That returns high earners from every division.
• Using a LEFT JOIN and keeping NULL bonuses. An HR employee with no bonus row did not earn 5000 or more. A plain JOIN drops them, which is what we want.

One small thing about case: MySQL compares text without caring about case by default, so division = 'hr' also matches “HR”. SQLite and PostgreSQL do care. Write 'HR' exactly and you’re safe everywhere.

Try it on sample data

These are my own rows. They include a bonus of exactly 5000, a bonus of 4999, an HR employee with no bonus row, a missing (NULL) bonus and a division called “HR Ops”.

employee_information

last_quarter_bonus

Output

Neha is in with exactly 5000. Vikram misses by 1. Arjun has a big bonus but works in Sales. Kavya has no bonus row. Ravi is in HR Ops, not HR. Simran’s bonus is NULL, and a NULL is never “5000 or more”. I ran this in MySQL and SQLite and got the same two rows.