Warehouse Transfers Still in Transit

Medium
389.

Warehouse Transfers Still in Transit

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

ColumnType
warehouse_idint
cityvarchar

Primary key: warehouse_id.

One warehouse per city.

Table: StockTransfer

ColumnType
transfer_idint
from_whint
to_whint
skuvarchar
qtyint
dispatched_atdatetime
received_atdatetime

Primary key: transfer_id.

from_wh and to_wh are warehouse ids (never equal); every transfer was dispatched before the check time.

Example

The rows must come back in the order shown.

Warehouse

warehouse_idcity
1Bengaluru
2Chennai
3Hyderabad

StockTransfer

transfer_idfrom_whto_whskuqtydispatched_atreceived_at
80112GRO-10011202025-04-07 09:30:00NULL
80223SNK-60014002025-04-08 18:00:00NULL
80331DAI-2001602025-04-08 17:59:00NULL
80413HOM-5001902025-04-06 11:00:002025-04-08 10:00:00
80521BEV-3001752025-04-10 06:45:00NULL

Output

transfer_idfrom_cityto_cityskuhours_in_transittransit_status
801BengaluruChennaiGRO-100180DELAYED
802ChennaiHyderabadSNK-600148ON_TIME
803HyderabadBengaluruDAI-200148ON_TIME
805ChennaiBengaluruBEV-300111ON_TIME
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.

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

ColumnType
warehouse_idint
cityvarchar

Primary key: warehouse_id.

One warehouse per city.

Table: StockTransfer

ColumnType
transfer_idint
from_whint
to_whint
skuvarchar
qtyint
dispatched_atdatetime
received_atdatetime

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_idcity
1Bengaluru
2Chennai
3Hyderabad

StockTransfer

transfer_idfrom_whto_whskuqtydispatched_atreceived_at
80112GRO-10011202025-04-07 09:30:00NULL
80223SNK-60014002025-04-08 18:00:00NULL
80331DAI-2001602025-04-08 17:59:00NULL
80413HOM-5001902025-04-06 11:00:002025-04-08 10:00:00
80521BEV-3001752025-04-10 06:45:00NULL

Output

transfer_idfrom_cityto_cityskuhours_in_transittransit_status
801BengaluruChennaiGRO-100180DELAYED
802ChennaiHyderabadSNK-600148ON_TIME
803HyderabadBengaluruDAI-200148ON_TIME
805ChennaiBengaluruBEV-300111ON_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_id

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