Uber: Freight Lane Rates Spiking Over the 28-Day Average

Hard
995.

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.

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

ColumnType
load_idint
lanevarchar
booked_ondate
rate_per_kmint

Primary key: load_id.

One row per booked truckload; rate_per_km is in whole rupees.

Example

The rows must come back in the order shown.

LoadBooking

load_idlanebooked_onrate_per_km
1Mumbai-Pune2024-03-0140
2Mumbai-Pune2024-03-0544
3Mumbai-Pune2024-03-1036
4Mumbai-Pune2024-03-1550
5Mumbai-Pune2024-03-2052
6Mumbai-Pune2024-03-2958
7Delhi-Jaipur2024-03-0230
8Delhi-Jaipur2024-03-0431
9Delhi-Jaipur2024-03-2045
10Delhi-Jaipur2024-04-1060
1 more row

Output

load_idlanebooked_onrate_per_kmprior_loads
11Delhi-Jaipur2024-03-25463
6Mumbai-Pune2024-03-29585
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: 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

ColumnType
load_idint
lanevarchar
booked_ondate
rate_per_kmint

Primary key: load_id.

One row per booked truckload; rate_per_km is in whole rupees.

Examples

Example 1

LoadBooking

load_idlanebooked_onrate_per_km
1Mumbai-Pune2024-03-0140
2Mumbai-Pune2024-03-0544
3Mumbai-Pune2024-03-1036
4Mumbai-Pune2024-03-1550
5Mumbai-Pune2024-03-2052
6Mumbai-Pune2024-03-2958
7Delhi-Jaipur2024-03-0230
8Delhi-Jaipur2024-03-0431
9Delhi-Jaipur2024-03-2045
10Delhi-Jaipur2024-04-1060
11Delhi-Jaipur2024-03-2546

Output

load_idlanebooked_onrate_per_kmprior_loads
11Delhi-Jaipur2024-03-25463
6Mumbai-Pune2024-03-29585

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_id

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