Referral Bonus Owed to Each Employee — SQL Joins Problem
Employees earn a ₹25,000 referral bonus for every candidate they referred who has joined the company.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #229
Problem statement
Employees earn a ₹25,000 referral bonus for every candidate they referred who has joined the company. Referrals at any other stage — still interviewing, rejected, offer declined — earn nothing yet.
Return every employee who has earned at least one bonus, with the columns emp_id, full_name, joined_referrals and bonus_amount (in rupees). Order the rows by bonus_amount descending, then emp_id ascending.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| department | varchar |
Primary key: emp_id.
One row per current employee.
Table: Referral
| Column | Type |
|---|---|
| referral_id | int |
| referrer_id | int |
| candidate_name | varchar |
| stage | enum(applied, interviewing, offered, joined, rejected, declined) |
Primary key: referral_id.
One row per candidate referred. referrer_id is the emp_id of the employee who referred them.
Examples
Example 1
Employee
| emp_id | full_name | department |
|---|---|---|
| 401 | Neha Verma | Engineering |
| 402 | Karan Singh | Sales |
| 403 | Simran Patel | Engineering |
| 404 | Dev Menon | Finance |
Referral
| referral_id | referrer_id | candidate_name | stage |
|---|---|---|---|
| 1 | 401 | Arun Kumar | joined |
| 2 | 401 | Bhavna Shah | joined |
| 3 | 402 | Chirag Jain | rejected |
| 4 | 403 | Divya Pillai | joined |
| 5 | 403 | Esha Kapoor | declined |
| 6 | 404 | Gaurav Sinha | interviewing |
| 7 | 402 | Hema Rao | joined |
Output
| emp_id | full_name | joined_referrals | bonus_amount |
|---|---|---|---|
| 401 | Neha Verma | 2 | 50000 |
| 402 | Karan Singh | 1 | 25000 |
| 403 | Simran Patel | 1 | 25000 |
How to solve Referral Bonus Owed to Each Employee
Join, filter, group. Each referral row is linked to its referrer through r.referrer_id = e.emp_id; keeping only stage = 'joined' leaves the referrals that pay. Grouping by employee and counting gives joined_referrals, and the bonus is that count times ₹25,000.
Because the filter runs before the grouping, an employee whose referrals are all at other stages forms no group and is left out — exactly the "at least one bonus" rule. The inner join also leaves out employees who referred nobody. Grouping by both emp_id and full_name keeps the query valid under MySQL's ONLY_FULL_GROUP_BY.
You can also aggregate the referrals first in a derived table (one row per referrer) and join that to the employees, which keeps the join small; or count conditionally with SUM(CASE …) and drop zero rows with HAVING. The statement fixes the order: the biggest bonus first, emp_id breaking ties. Cost: one pass over the referrals plus a key lookup per group.
Reference solution (MySQL)
SELECT e.emp_id, e.full_name, COUNT(*) AS joined_referrals, COUNT(*) * 25000 AS bonus_amount
FROM Employee e
JOIN Referral r ON r.referrer_id = e.emp_id
WHERE r.stage = 'joined'
GROUP BY e.emp_id, e.full_name
ORDER BY bonus_amount DESC, e.emp_idAnother way
SELECT e.emp_id, e.full_name, j.joined_referrals, j.joined_referrals * 25000 AS bonus_amount
FROM Employee e
JOIN (SELECT referrer_id, COUNT(*) AS joined_referrals FROM Referral WHERE stage = 'joined' GROUP BY referrer_id) j ON j.referrer_id = e.emp_id
ORDER BY j.joined_referrals DESC, e.emp_idAnother way
SELECT e.emp_id, e.full_name, SUM(CASE WHEN r.stage = 'joined' THEN 1 ELSE 0 END) AS joined_referrals,
SUM(CASE WHEN r.stage = 'joined' THEN 25000 ELSE 0 END) AS bonus_amount
FROM Employee e JOIN Referral r ON r.referrer_id = e.emp_id
GROUP BY e.emp_id, e.full_name
HAVING joined_referrals > 0
ORDER BY bonus_amount DESC, e.emp_id← Earned Leave Lapsing Above the Carry-Forward Cap · Work Anniversaries Coming Up in October 2025 →