Uber: Freight Lane Rates Spiking Over the 28-Day Average
Uber Freight's pricing desk watches for loads booked far above what the same lane has been costing lately, a sign of a capacity crunch on that lane.
- Difficulty: Hard
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #995
Problem statement
Uber Freight's pricing desk watches for loads booked far above what the same lane has been costing lately, a sign of a capacity crunch on that lane.
A load is a spike when its rate_per_km is more than 25% above the average rate_per_km of the loads on the same lane booked in the 28 days before it — booked_on from 28 days earlier up to the day before, inclusive; loads on the same day do not count — and there were at least 3 such earlier loads. Return load_id, lane, booked_on, rate_per_km and prior_loads (the number of loads in its window), ordered by booked_on, then load_id.
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: LoadBooking
| Column | Type |
|---|---|
| load_id | int |
| lane | varchar |
| booked_on | date |
| rate_per_km | int |
Primary key: load_id.
One row per booked truckload; rate_per_km is in whole rupees.
Examples
Example 1
LoadBooking
| load_id | lane | booked_on | rate_per_km |
|---|---|---|---|
| 1 | Mumbai-Pune | 2024-03-01 | 40 |
| 2 | Mumbai-Pune | 2024-03-05 | 44 |
| 3 | Mumbai-Pune | 2024-03-10 | 36 |
| 4 | Mumbai-Pune | 2024-03-15 | 50 |
| 5 | Mumbai-Pune | 2024-03-20 | 52 |
| 6 | Mumbai-Pune | 2024-03-29 | 58 |
| 7 | Delhi-Jaipur | 2024-03-02 | 30 |
| 8 | Delhi-Jaipur | 2024-03-04 | 31 |
| 9 | Delhi-Jaipur | 2024-03-20 | 45 |
| 10 | Delhi-Jaipur | 2024-04-10 | 60 |
| 11 | Delhi-Jaipur | 2024-03-25 | 46 |
Output
| load_id | lane | booked_on | rate_per_km | prior_loads |
|---|---|---|---|---|
| 11 | Delhi-Jaipur | 2024-03-25 | 46 | 3 |
| 6 | Mumbai-Pune | 2024-03-29 | 58 | 5 |
How to solve Uber: Freight Lane Rates Spiking Over the 28-Day Average
Every load is compared with a trailing window of its own lane's history, so the table is joined to itself: l is the load being judged and p ranges over the same lane's loads booked from 28 days before up to the day before. The lower edge is inclusive — load 6 on 29 March still sees load 1 from 1 March, exactly 28 days earlier — and the upper edge is strict, so loads booked the same day are not part of the baseline.
Grouping by the judged load gives COUNT(*) (the prior loads) and SUM(p.rate_per_km) for its window. The inner join means a load with no history has no group at all, and HAVING COUNT(*) >= 3 drops thin windows such as Delhi-Jaipur's 20 March load, which had only two predecessors.
The spike test rate > 1.25 × SUM / COUNT is rewritten as 4 × rate × COUNT > 5 × SUM. That keeps the comparison in exact integers: averages like 42.5 or 35.33… then never pass through decimal or floating-point rounding, and a boundary case such as load 4 (rate 50 against an average of 40 — exactly 25% above, not more) is decided correctly.
The alternative computes the window's count and total with two correlated subqueries using DATEDIFF … BETWEEN 1 AND 28. Window functions with a date-based RANGE frame would also work in engines that support them; the self join is the portable choice, and with an index on (lane, booked_on) each window is a short range scan.
Reference solution (MySQL)
SELECT l.load_id, l.lane, l.booked_on, l.rate_per_km, COUNT(*) AS prior_loads
FROM LoadBooking l
JOIN LoadBooking p
ON p.lane = l.lane
AND p.booked_on >= DATE_SUB(l.booked_on, INTERVAL 28 DAY)
AND p.booked_on < l.booked_on
GROUP BY l.load_id, l.lane, l.booked_on, l.rate_per_km
HAVING COUNT(*) >= 3 AND 4 * l.rate_per_km * COUNT(*) > 5 * SUM(p.rate_per_km)
ORDER BY l.booked_on, l.load_idAnother way
SELECT load_id, lane, booked_on, rate_per_km, prior_loads FROM (
SELECT l.*,
(SELECT COUNT(*) FROM LoadBooking p WHERE p.lane = l.lane AND DATEDIFF(l.booked_on, p.booked_on) BETWEEN 1 AND 28) AS prior_loads,
(SELECT SUM(p.rate_per_km) FROM LoadBooking p WHERE p.lane = l.lane AND DATEDIFF(l.booked_on, p.booked_on) BETWEEN 1 AND 28) AS prior_total
FROM LoadBooking l
) x
WHERE prior_loads >= 3 AND rate_per_km * prior_loads * 4 > prior_total * 5
ORDER BY booked_on, load_id← Uber: Split Fare Shares to the Paisa · Uber: Trip Event Logs That Break the State Machine →