Uber: Which Promo Gets Credit for a Rider's First Trip
Growth marketing attributes each rider's first completed trip to the promo that brought them in, using a last-touch rule.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Dates
- Dialect: MySQL
- Problem: #997
Problem statement
Growth marketing attributes each rider's first completed trip to the promo that brought them in, using a last-touch rule.
A rider's first completed trip is the one with the earliest completed_at among their trips with status completed. It is credited to the promo the rider touched most recently at or before that trip's completed_at, provided the touch was no more than 7 days earlier (a touch exactly 7 × 24 hours before still counts). Ties on touched_at go to the larger touch_id. A first trip with no such touch is credited to 'ORGANIC'. Riders with no completed trip are left out.
Return credited_code and first_trips (the number of first trips credited to it), ordered by first_trips descending, then credited_code.
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: Trip
| Column | Type |
|---|---|
| trip_id | int |
| rider_id | int |
| completed_at | datetime |
| status | enum(completed, cancelled) |
Primary key: trip_id.
One row per trip; for a cancelled trip completed_at is the time it was called off.
Table: PromoTouch
| Column | Type |
|---|---|
| touch_id | int |
| rider_id | int |
| promo_code | varchar |
| touched_at | datetime |
Primary key: touch_id.
One row each time a rider opened or applied a promo; codes are upper-case.
Examples
Example 1
Trip
| trip_id | rider_id | completed_at | status |
|---|---|---|---|
| 1 | 1 | 2024-06-10 09:00:00 | completed |
| 2 | 2 | 2024-06-01 08:00:00 | cancelled |
| 3 | 2 | 2024-06-12 18:00:00 | completed |
| 4 | 3 | 2024-06-15 08:00:00 | completed |
| 5 | 4 | 2024-06-20 21:00:00 | completed |
| 6 | 4 | 2024-06-25 21:00:00 | completed |
| 7 | 1 | 2024-06-18 10:00:00 | completed |
PromoTouch
| touch_id | rider_id | promo_code | touched_at |
|---|---|---|---|
| 1 | 1 | RIDE50 | 2024-06-05 10:00:00 |
| 2 | 1 | AIRPORT75 | 2024-06-09 20:00:00 |
| 3 | 2 | RIDE50 | 2024-06-05 18:00:00 |
| 4 | 3 | RIDE50 | 2024-06-15 08:30:00 |
| 5 | 3 | MONSOON25 | 2024-06-01 08:00:00 |
| 6 | 4 | RIDE50 | 2024-06-19 12:00:00 |
| 7 | 4 | AIRPORT75 | 2024-06-24 12:00:00 |
| 8 | 5 | RIDE50 | 2024-06-02 12:00:00 |
Output
| credited_code | first_trips |
|---|---|
| RIDE50 | 2 |
| AIRPORT75 | 1 |
| ORGANIC | 1 |
How to solve Uber: Which Promo Gets Credit for a Rider's First Trip
Attribution questions are multi-step, and each step is a CTE.
First trips. Among completed trips only, ROW_NUMBER() per rider ordered by completed_at (with trip_id to break a tie) picks one first trip per rider. Rider 2's cancelled trip on 1 June is ignored, so their first trip is on 12 June. Riders with no completed trip, like rider 5, have no row and drop out of the whole computation.
Candidate touches. Each first trip is joined to the rider's touches in the window [completed_at − 7 days, completed_at], both ends inclusive: rider 2's RIDE50 touch exactly seven days earlier qualifies; rider 3's touch half an hour after the trip and their two-week-old touch do not. ROW_NUMBER() ordered by touched_at DESC, touch_id DESC ranks the candidates, so rank 1 is the last touch with ties resolved as the statement says — rider 1's AIRPORT75 beats the older RIDE50, and rider 4's later AIRPORT75 touch is after their first trip and plays no part.
Counting. The first trips are LEFT JOINed to the rank-1 touches, so a first trip with no qualifying touch keeps NULL and COALESCE labels it ORGANIC. Grouping by that label gives the counts; the order uses the count and then the code. The alternative finds the first trip with MIN(completed_at) and the winning touch with a correlated ORDER BY … LIMIT 1 subquery — clear, and cheap with an index on (rider_id, touched_at).
Reference solution (MySQL)
WITH first_trip AS (
SELECT rider_id, completed_at
FROM (SELECT rider_id, completed_at,
ROW_NUMBER() OVER (PARTITION BY rider_id ORDER BY completed_at, trip_id) AS rn
FROM Trip WHERE status = 'completed') t
WHERE rn = 1
), touch AS (
SELECT f.rider_id, p.promo_code,
ROW_NUMBER() OVER (PARTITION BY f.rider_id ORDER BY p.touched_at DESC, p.touch_id DESC) AS rn
FROM first_trip f
JOIN PromoTouch p
ON p.rider_id = f.rider_id
AND p.touched_at <= f.completed_at
AND p.touched_at >= DATE_SUB(f.completed_at, INTERVAL 7 DAY)
)
SELECT COALESCE(t.promo_code, 'ORGANIC') AS credited_code, COUNT(*) AS first_trips
FROM first_trip f
LEFT JOIN touch t ON t.rider_id = f.rider_id AND t.rn = 1
GROUP BY COALESCE(t.promo_code, 'ORGANIC')
ORDER BY first_trips DESC, credited_codeAnother way
SELECT credited_code, COUNT(*) AS first_trips FROM (
SELECT f.rider_id,
COALESCE((SELECT p.promo_code FROM PromoTouch p
WHERE p.rider_id = f.rider_id
AND p.touched_at <= f.first_at
AND TIMESTAMPDIFF(SECOND, p.touched_at, f.first_at) <= 7 * 86400
ORDER BY p.touched_at DESC, p.touch_id DESC
LIMIT 1), 'ORGANIC') AS credited_code
FROM (SELECT rider_id, MIN(completed_at) AS first_at FROM Trip WHERE status = 'completed' GROUP BY rider_id) f
) x
GROUP BY credited_code
ORDER BY first_trips DESC, credited_code← Uber: Trip Event Logs That Break the State Machine · Walmart: Active Rollbacks and the Savings on Each →