Uber: Rider Referrals That Qualified for the Bonus

Medium
987.

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

ColumnType
rider_idint
rider_namevarchar
signed_up_ondate
referred_byint

Primary key: rider_id.

referred_by is the rider_id of the rider whose invite they used, or NULL.

Table: Trip

ColumnTypeValues
trip_idint
rider_idint
trip_datedate
statusenumcompleted, cancelled

Primary key: trip_id.

One row per trip. A trip is never dated before its rider signed up.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Rider

rider_idrider_namesigned_up_onreferred_by
1Aarav2024-01-05NULL
2Diya2024-02-01NULL
3Kabir2024-03-011
4Meera2024-03-031
5Rohan2024-03-102
6Zara2024-03-122
7Ishaan2024-03-153

Trip

trip_idrider_idtrip_datestatus
10132024-03-15completed
10242024-03-17completed
10342024-03-10cancelled
10452024-03-10completed
10552024-03-11completed
10662024-03-20cancelled
10772024-03-20completed
10862024-03-27completed

Output

referrer_idreferrer_namequalifiedbonus
1Aarav2200
2Diya1100
3Kabir1100
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.

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

ColumnType
rider_idint
rider_namevarchar
signed_up_ondate
referred_byint

Primary key: rider_id.

referred_by is the rider_id of the rider whose invite they used, or NULL.

Table: Trip

ColumnType
trip_idint
rider_idint
trip_datedate
statusenum(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_idrider_namesigned_up_onreferred_by
1Aarav2024-01-05NULL
2Diya2024-02-01NULL
3Kabir2024-03-011
4Meera2024-03-031
5Rohan2024-03-102
6Zara2024-03-122
7Ishaan2024-03-153

Trip

trip_idrider_idtrip_datestatus
10132024-03-15completed
10242024-03-17completed
10342024-03-10cancelled
10452024-03-10completed
10552024-03-11completed
10662024-03-20cancelled
10772024-03-20completed
10862024-03-27completed

Output

referrer_idreferrer_namequalifiedbonus
1Aarav2200
2Diya1100
3Kabir1100

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_name

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