Uber: Which Promo Gets Credit for a Rider's First Trip

Hard
997.

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.

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

ColumnTypeValues
trip_idint
rider_idint
completed_atdatetime
statusenumcompleted, cancelled

Primary key: trip_id.

One row per trip; for a cancelled trip completed_at is the time it was called off.

Table: PromoTouch

ColumnType
touch_idint
rider_idint
promo_codevarchar
touched_atdatetime

Primary key: touch_id.

One row each time a rider opened or applied a promo; codes are upper-case.

Example

The rows must come back in the order shown.

Trip

trip_idrider_idcompleted_atstatus
112024-06-10 09:00:00completed
222024-06-01 08:00:00cancelled
322024-06-12 18:00:00completed
432024-06-15 08:00:00completed
542024-06-20 21:00:00completed
642024-06-25 21:00:00completed
712024-06-18 10:00:00completed

PromoTouch

touch_idrider_idpromo_codetouched_at
11RIDE502024-06-05 10:00:00
21AIRPORT752024-06-09 20:00:00
32RIDE502024-06-05 18:00:00
43RIDE502024-06-15 08:30:00
53MONSOON252024-06-01 08:00:00
64RIDE502024-06-19 12:00:00
74AIRPORT752024-06-24 12:00:00
85RIDE502024-06-02 12:00:00

Output

credited_codefirst_trips
RIDE502
AIRPORT751
ORGANIC1
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: 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

ColumnType
trip_idint
rider_idint
completed_atdatetime
statusenum(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

ColumnType
touch_idint
rider_idint
promo_codevarchar
touched_atdatetime

Primary key: touch_id.

One row each time a rider opened or applied a promo; codes are upper-case.

Examples

Example 1

Trip

trip_idrider_idcompleted_atstatus
112024-06-10 09:00:00completed
222024-06-01 08:00:00cancelled
322024-06-12 18:00:00completed
432024-06-15 08:00:00completed
542024-06-20 21:00:00completed
642024-06-25 21:00:00completed
712024-06-18 10:00:00completed

PromoTouch

touch_idrider_idpromo_codetouched_at
11RIDE502024-06-05 10:00:00
21AIRPORT752024-06-09 20:00:00
32RIDE502024-06-05 18:00:00
43RIDE502024-06-15 08:30:00
53MONSOON252024-06-01 08:00:00
64RIDE502024-06-19 12:00:00
74AIRPORT752024-06-24 12:00:00
85RIDE502024-06-02 12:00:00

Output

credited_codefirst_trips
RIDE502
AIRPORT751
ORGANIC1

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_code

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