Uber: Rider Referrals That Qualified for the Bonus
Under the rider referral programme a rider earns ₹100 for each friend they invited who completed a trip within 14 days of signing up — a trip on the sign-up…
- Difficulty: Medium
- Topics: Joins, Subqueries, Dates
- Dialect: MySQL
- Problem: #987
Problem statement
Under the rider referral programme a rider earns ₹100 for each friend they invited who completed a trip within 14 days of signing up — a trip on the sign-up day itself counts, and so does one exactly 14 days later. Cancelled trips never count, and a friend with several qualifying trips earns the bonus once.
Return referrer_id, referrer_name, qualified (the number of friends who qualified) and bonus (100 × qualified) for every rider with at least one qualified referral, in any order.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: Rider
| Column | Type |
|---|---|
| rider_id | int |
| rider_name | varchar |
| signed_up_on | date |
| referred_by | int |
Primary key: rider_id.
referred_by is the rider_id of the rider whose invite they used, or NULL.
Table: Trip
| Column | Type |
|---|---|
| trip_id | int |
| rider_id | int |
| trip_date | date |
| status | enum(completed, cancelled) |
Primary key: trip_id.
One row per trip. A trip is never dated before its rider signed up.
Examples
Example 1
Rider
| rider_id | rider_name | signed_up_on | referred_by |
|---|---|---|---|
| 1 | Aarav | 2024-01-05 | NULL |
| 2 | Diya | 2024-02-01 | NULL |
| 3 | Kabir | 2024-03-01 | 1 |
| 4 | Meera | 2024-03-03 | 1 |
| 5 | Rohan | 2024-03-10 | 2 |
| 6 | Zara | 2024-03-12 | 2 |
| 7 | Ishaan | 2024-03-15 | 3 |
Trip
| trip_id | rider_id | trip_date | status |
|---|---|---|---|
| 101 | 3 | 2024-03-15 | completed |
| 102 | 4 | 2024-03-17 | completed |
| 103 | 4 | 2024-03-10 | cancelled |
| 104 | 5 | 2024-03-10 | completed |
| 105 | 5 | 2024-03-11 | completed |
| 106 | 6 | 2024-03-20 | cancelled |
| 107 | 7 | 2024-03-20 | completed |
| 108 | 6 | 2024-03-27 | completed |
Output
| referrer_id | referrer_name | qualified | bonus |
|---|---|---|---|
| 1 | Aarav | 2 | 200 |
| 2 | Diya | 1 | 100 |
| 3 | Kabir | 1 | 100 |
How to solve Uber: Rider Referrals That Qualified for the Bonus
Referrer and friend live in the same table, linked by referred_by, so the query self-joins Rider: r is the referrer, f each friend they brought in. Riders who referred nobody never join and so never appear.
A friend qualifies when at least one of their trips is completed and dated within 14 days of their sign-up. EXISTS expresses "at least one" exactly and counts each friend once however many trips they took — Rohan's two early trips earn Diya one bonus. DATEDIFF(t.trip_date, f.signed_up_on) <= 14 puts both edges in: a trip on the sign-up day gives 0, one fourteen days later gives 14 (Meera), and fifteen days (Zara's completed trip) is too late. Cancelled trips are excluded by the status test.
After the filter each surviving row is one qualified friend, so COUNT(*) per referrer is qualified and the bonus is a multiple of it. The alternative joins the trips directly and uses COUNT(DISTINCT f.rider_id) to undo the duplication the join introduces. With indexes on referred_by and Trip.rider_id both run in roughly linear time.
Reference solution (MySQL)
SELECT r.rider_id AS referrer_id, r.rider_name AS referrer_name,
COUNT(*) AS qualified, 100 * COUNT(*) AS bonus
FROM Rider r
JOIN Rider f ON f.referred_by = r.rider_id
WHERE EXISTS (
SELECT 1 FROM Trip t
WHERE t.rider_id = f.rider_id
AND t.status = 'completed'
AND DATEDIFF(t.trip_date, f.signed_up_on) <= 14
)
GROUP BY r.rider_id, r.rider_nameAnother way
SELECT r.rider_id AS referrer_id, r.rider_name AS referrer_name,
COUNT(DISTINCT f.rider_id) AS qualified, 100 * COUNT(DISTINCT f.rider_id) AS bonus
FROM Rider r
JOIN Rider f ON f.referred_by = r.rider_id
JOIN Trip t ON t.rider_id = f.rider_id AND t.status = 'completed'
AND t.trip_date <= DATE_ADD(f.signed_up_on, INTERVAL 14 DAY)
GROUP BY r.rider_id, r.rider_name← Uber: Airport Queue Position When Each Driver Arrived · Uber: Uber Pro Leaderboard Around Driver 107 in Pune →