Uber: Freight Loads Booked Below the Posted Rate
On Uber Freight a shipper's load is posted at a rate, and a carrier books it — sometimes after negotiating the price down.
- Difficulty: Easy
- Topics: Joins, Basics
- Dialect: MySQL
- Problem: #975
Problem statement
On Uber Freight a shipper's load is posted at a rate, and a carrier books it — sometimes after negotiating the price down. The pricing team wants to see where carriers accepted less than the posted rate.
Return every load that has been booked at a booked_rate strictly lower than its posted_rate, with the columns load_id, carrier_name and saving (posted_rate - booked_rate). Loads not yet booked have a NULL booked_rate and carrier_id and are never in the answer. Return the rows 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: Carrier
| Column | Type |
|---|---|
| carrier_id | int |
| carrier_name | varchar |
Primary key: carrier_id.
One row per trucking company on the platform.
Table: FreightLoad
| Column | Type |
|---|---|
| load_id | int |
| lane | varchar |
| posted_rate | int |
| booked_rate | int |
| carrier_id | int |
Primary key: load_id.
Rates are in rupees for the whole load. A booked load always names an existing carrier; an unbooked one has NULL booked_rate and carrier_id.
Examples
Example 1
Carrier
| carrier_id | carrier_name |
|---|---|
| 1 | Bharat Roadlines |
| 2 | Deccan Haulers |
| 3 | Sahyadri Freight |
FreightLoad
| load_id | lane | posted_rate | booked_rate | carrier_id |
|---|---|---|---|---|
| 501 | Mumbai-Pune | 42000 | 39500 | 1 |
| 502 | Delhi-Jaipur | 56000 | 56000 | 2 |
| 503 | Chennai-Bengaluru | 61000 | NULL | NULL |
| 504 | Ahmedabad-Mumbai | 48000 | 51000 | 3 |
| 505 | Hyderabad-Bengaluru | 73000 | 68000 | 2 |
| 506 | Kolkata-Patna | 30000 | 27500 | 1 |
Output
| load_id | carrier_name | saving |
|---|---|---|
| 501 | Bharat Roadlines | 2500 |
| 505 | Deccan Haulers | 5000 |
| 506 | Bharat Roadlines | 2500 |
How to solve Uber: Freight Loads Booked Below the Posted Rate
Each qualifying load needs one fact from FreightLoad (the two rates) and one from Carrier (the name), so the query joins the two on carrier_id and filters on the rates.
The condition booked_rate < posted_rate is strict, so load 502, booked at exactly its posted price, is out, and so is load 504, where the carrier negotiated the price up. The saving is simply the difference of the two rates, computed in the select list.
Unbooked loads need no special handling: their booked_rate is NULL, the comparison with NULL is unknown, and WHERE keeps only rows where it is true; their NULL carrier_id would also fail the inner join. The alternative fetches the name with a correlated scalar subquery instead of a join and writes the test as a positive difference — the same rows, since a booked load always names a carrier. Either way it is one pass over the loads with a primary-key lookup per row.
Reference solution (MySQL)
SELECT l.load_id, c.carrier_name, l.posted_rate - l.booked_rate AS saving
FROM FreightLoad l
JOIN Carrier c ON c.carrier_id = l.carrier_id
WHERE l.booked_rate < l.posted_rateAnother way
SELECT load_id, (SELECT carrier_name FROM Carrier c WHERE c.carrier_id = l.carrier_id) AS carrier_name, posted_rate - booked_rate AS saving FROM FreightLoad l WHERE posted_rate - booked_rate > 0← Uber: Rider Cancellations That Earn a Late-Cancel Fee · Uber: Promo Codes Nobody Has Redeemed →