Walmart: DC Trailers Dispatched Under 80% Full — SQL Problem
The transportation team at a Walmart distribution centre tracks trailer fill, because a trailer that leaves light costs nearly as much to drive as a full one.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #1004
Problem statement
The transportation team at a Walmart distribution centre tracks trailer fill, because a trailer that leaves light costs nearly as much to drive as a full one.
For trailers dispatched in April 2025, return the loads whose loaded_pallets is strictly below 80% of capacity_pallets. Return load_id, dc_id and empty_pallets (capacity minus loaded), ordered by empty_pallets descending, then load_id ascending.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: TrailerLoad
| Column | Type |
|---|---|
| load_id | int |
| dc_id | int |
| dispatch_date | date |
| capacity_pallets | int |
| loaded_pallets | int |
Primary key: load_id.
One row per outbound trailer from a distribution centre. loaded_pallets never exceeds capacity_pallets.
Examples
Example 1
TrailerLoad
| load_id | dc_id | dispatch_date | capacity_pallets | loaded_pallets |
|---|---|---|---|---|
| 9001 | 6011 | 2025-04-01 | 26 | 20 |
| 9002 | 6011 | 2025-04-03 | 26 | 21 |
| 9003 | 6011 | 2025-04-07 | 30 | 24 |
| 9004 | 6094 | 2025-04-12 | 30 | 23 |
| 9005 | 6094 | 2025-04-30 | 30 | 30 |
| 9006 | 6094 | 2025-03-31 | 26 | 8 |
| 9007 | 6011 | 2025-04-18 | 26 | 10 |
| 9008 | 6094 | 2025-04-22 | 53 | 46 |
Output
| load_id | dc_id | empty_pallets |
|---|---|---|
| 9007 | 6011 | 16 |
| 9004 | 6094 | 7 |
| 9001 | 6011 | 6 |
How to solve Walmart: DC Trailers Dispatched Under 80% Full
Each trailer is judged on its own row, so the query is a filter plus a computed column. The fill test compares a count with a percentage of another count, and the cleanest way to do that is to keep it in integers: loaded_pallets * 100 < capacity_pallets * 80 is the same inequality as loaded / capacity < 0.8, with no division and no rounding.
The boundary is strict. A 30-pallet trailer with 24 pallets is exactly 80% full and stays off the list, while a 26-pallet trailer with 20 pallets (76.9%) is on it and one with 21 (80.8%) is not. Rounding the percentage first would blur exactly these cases.
The month filter keeps 1 and 30 April and drops 31 March. The order — most empty slots first, then load_id — is total, so ties come out the same every time. The alternative divides (decimal division, so 24/30 is exactly 0.8) and reads the month with YEAR/MONTH. One scan either way.
Reference solution (MySQL)
SELECT load_id, dc_id, capacity_pallets - loaded_pallets AS empty_pallets
FROM TrailerLoad
WHERE dispatch_date BETWEEN '2025-04-01' AND '2025-04-30'
AND loaded_pallets * 100 < capacity_pallets * 80
ORDER BY empty_pallets DESC, load_idAnother way
SELECT load_id, dc_id, capacity_pallets - loaded_pallets AS empty_pallets
FROM TrailerLoad
WHERE YEAR(dispatch_date) = 2025 AND MONTH(dispatch_date) = 4
AND loaded_pallets / capacity_pallets < 0.8
ORDER BY capacity_pallets - loaded_pallets DESC, load_id ASC← Walmart: Supercenters per State From Store Codes · Walmart: Sam's Club Members Who Shopped Away From Home →