Walmart: DC Trailers Dispatched Under 80% Full

Easy
1004.

Walmart: DC Trailers Dispatched Under 80% Full

EasyBasicsDates

MySQL

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

ColumnType
load_idint
dc_idint
dispatch_datedate
capacity_palletsint
loaded_palletsint

Primary key: load_id.

One row per outbound trailer from a distribution centre. loaded_pallets never exceeds capacity_pallets.

Example

The rows must come back in the order shown.

TrailerLoad

load_iddc_iddispatch_datecapacity_palletsloaded_pallets
900160112025-04-012620
900260112025-04-032621
900360112025-04-073024
900460942025-04-123023
900560942025-04-303030
900660942025-03-31268
900760112025-04-182610
900860942025-04-225346

Output

load_iddc_idempty_pallets
9007601116
900460947
900160116
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.

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

ColumnType
load_idint
dc_idint
dispatch_datedate
capacity_palletsint
loaded_palletsint

Primary key: load_id.

One row per outbound trailer from a distribution centre. loaded_pallets never exceeds capacity_pallets.

Examples

Example 1

TrailerLoad

load_iddc_iddispatch_datecapacity_palletsloaded_pallets
900160112025-04-012620
900260112025-04-032621
900360112025-04-073024
900460942025-04-123023
900560942025-04-303030
900660942025-03-31268
900760112025-04-182610
900860942025-04-225346

Output

load_iddc_idempty_pallets
9007601116
900460947
900160116

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_id

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