Referral Bonus Owed to Each Employee

Easy
229.

Referral Bonus Owed to Each Employee

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

ColumnType
emp_idint
full_namevarchar
departmentvarchar

Primary key: emp_id.

One row per current employee.

Table: Referral

ColumnTypeValues
referral_idint
referrer_idint
candidate_namevarchar
stageenumapplied, 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.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namedepartment
401Neha VermaEngineering
402Karan SinghSales
403Simran PatelEngineering
404Dev MenonFinance

Referral

referral_idreferrer_idcandidate_namestage
1401Arun Kumarjoined
2401Bhavna Shahjoined
3402Chirag Jainrejected
4403Divya Pillaijoined
5403Esha Kapoordeclined
6404Gaurav Sinhainterviewing
7402Hema Raojoined

Output

emp_idfull_namejoined_referralsbonus_amount
401Neha Verma250000
402Karan Singh125000
403Simran Patel125000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
full_namevarchar
departmentvarchar

Primary key: emp_id.

One row per current employee.

Table: Referral

ColumnType
referral_idint
referrer_idint
candidate_namevarchar
stageenum(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_idfull_namedepartment
401Neha VermaEngineering
402Karan SinghSales
403Simran PatelEngineering
404Dev MenonFinance

Referral

referral_idreferrer_idcandidate_namestage
1401Arun Kumarjoined
2401Bhavna Shahjoined
3402Chirag Jainrejected
4403Divya Pillaijoined
5403Esha Kapoordeclined
6404Gaurav Sinhainterviewing
7402Hema Raojoined

Output

emp_idfull_namejoined_referralsbonus_amount
401Neha Verma250000
402Karan Singh125000
403Simran Patel125000

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_id

Another 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_id

Another 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 →