Warehouse Transfers Still in Transit — SQL Joins Problem
Stock moves between the chain's warehouses by truck. A transfer is in transit while received_at is NULL.
- Difficulty: Medium
- Topics: Joins, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #389
Problem statement
Stock moves between the chain's warehouses by truck. A transfer is in transit while received_at is NULL. The control tower checks the board at 2025-04-10 18:00:00: a transfer that has been on the road more than 48 full hours is DELAYED, otherwise it is ON_TIME.
For every transfer in transit, return transfer_id, from_city, to_city, sku, hours_in_transit (whole hours from dispatched_at to the check time, partial hours dropped) and transit_status. Order the rows by hours_in_transit highest first, then by transfer_id.
Tables
Table: Warehouse
| Column | Type |
|---|---|
| warehouse_id | int |
| city | varchar |
Primary key: warehouse_id.
One warehouse per city.
Table: StockTransfer
| Column | Type |
|---|---|
| transfer_id | int |
| from_wh | int |
| to_wh | int |
| sku | varchar |
| qty | int |
| dispatched_at | datetime |
| received_at | datetime |
Primary key: transfer_id.
from_wh and to_wh are warehouse ids (never equal); every transfer was dispatched before the check time.
Examples
Example 1
Warehouse
| warehouse_id | city |
|---|---|
| 1 | Bengaluru |
| 2 | Chennai |
| 3 | Hyderabad |
StockTransfer
| transfer_id | from_wh | to_wh | sku | qty | dispatched_at | received_at |
|---|---|---|---|---|---|---|
| 801 | 1 | 2 | GRO-1001 | 120 | 2025-04-07 09:30:00 | NULL |
| 802 | 2 | 3 | SNK-6001 | 400 | 2025-04-08 18:00:00 | NULL |
| 803 | 3 | 1 | DAI-2001 | 60 | 2025-04-08 17:59:00 | NULL |
| 804 | 1 | 3 | HOM-5001 | 90 | 2025-04-06 11:00:00 | 2025-04-08 10:00:00 |
| 805 | 2 | 1 | BEV-3001 | 75 | 2025-04-10 06:45:00 | NULL |
Output
| transfer_id | from_city | to_city | sku | hours_in_transit | transit_status |
|---|---|---|---|---|---|
| 801 | Bengaluru | Chennai | GRO-1001 | 80 | DELAYED |
| 802 | Chennai | Hyderabad | SNK-6001 | 48 | ON_TIME |
| 803 | Hyderabad | Bengaluru | DAI-2001 | 48 | ON_TIME |
| 805 | Chennai | Bengaluru | BEV-3001 | 11 | ON_TIME |
How to solve Warehouse Transfers Still in Transit
A transfer row carries two foreign keys into the same table, so the city names come from two joins to Warehouse under different aliases — one matched on from_wh, the other on to_wh. Joining once with warehouse_id IN (from_wh, to_wh) would instead produce two rows per transfer.
The time on the road is TIMESTAMPDIFF(HOUR, dispatched_at, '2025-04-10 18:00:00'), which counts complete hours: a truck dispatched at 17:59 two days earlier has been out 48 hours and one minute, which is 48 whole hours and therefore still ON_TIME; the status flips only at 49. A CASE (or IF) turns the number into the label. Received transfers are excluded with received_at IS NULL.
The alternative looks the city names up with scalar subqueries and computes the hours once in a derived table so the label does not repeat the expression. Both read each transfer once and probe the warehouses by primary key.
Reference solution (MySQL)
SELECT t.transfer_id, f.city AS from_city, d.city AS to_city, t.sku,
TIMESTAMPDIFF(HOUR, t.dispatched_at, '2025-04-10 18:00:00') AS hours_in_transit,
CASE WHEN TIMESTAMPDIFF(HOUR, t.dispatched_at, '2025-04-10 18:00:00') > 48 THEN 'DELAYED' ELSE 'ON_TIME' END AS transit_status
FROM StockTransfer t
JOIN Warehouse f ON f.warehouse_id = t.from_wh
JOIN Warehouse d ON d.warehouse_id = t.to_wh
WHERE t.received_at IS NULL
ORDER BY hours_in_transit DESC, t.transfer_idAnother way
SELECT transfer_id, from_city, to_city, sku, hrs AS hours_in_transit, IF(hrs > 48, 'DELAYED', 'ON_TIME') AS transit_status FROM (
SELECT t.transfer_id, t.sku, TIMESTAMPDIFF(HOUR, t.dispatched_at, '2025-04-10 18:00:00') AS hrs,
(SELECT w.city FROM Warehouse w WHERE w.warehouse_id = t.from_wh) AS from_city,
(SELECT w.city FROM Warehouse w WHERE w.warehouse_id = t.to_wh) AS to_city
FROM StockTransfer t WHERE t.received_at IS NULL) x
ORDER BY hrs DESC, transfer_id← Duplicate Vendors After Cleaning Up Their Names · Stores Beating Their City's Average in Q1 →