Logistics: Cross-Docking
Fed Ex
Problem Statement
Cross-docking is a logistics practice where products from an incoming truck are unloaded and directly loaded into outbound trucks with little to no storage in between. This minimizes handling and storage costs.
Identify all packages that successfully cross-docked at a specific hub. A successful cross-dock is defined as a package arriving at a hub and departing on a different truck within 60 minutes of arrival.
Rules:
- Output columns: package_id, hub_id, arrival_truck, departure_truck, processing_time_minutes.
- arrival_truck is the truck_id of the incoming scan.
- departure_truck is the truck_id of the outgoing scan.
- processing_time_minutes must be strictly less than 60.
- The arrival_truck and departure_truck must be different.
- Result must be sorted by hub_id ascending, then by processing_time_minutes ascending.
Tests your understanding of
Self Join, Time Difference and Operations Analytics
Input Tables
hub_scans
| scan_id(INTEGER) | package_id(VARCHAR) | hub_id(INTEGER) | truck_id(INTEGER) | scan_type(VARCHAR) | scan_timestamp(TIMESTAMP) |
|---|---|---|---|---|---|
| 1 | P100 | 10 | 501 | arrival | 2024-07-01 08:00:00 |
| 2 | P100 | 10 | 502 | departure | 2024-07-01 08:45:00 |
| 3 | P200 | 10 | 501 | arrival | 2024-07-01 09:00:00 |
| 4 | P200 | 10 | 503 | departure | 2024-07-01 11:00:00 |
| 5 | P300 | 11 | 600 | arrival | 2024-07-01 10:00:00 |
| 6 | P300 | 11 | 600 | departure | 2024-07-01 10:20:00 |
| 7 | P400 | 12 | 701 | arrival | 2024-07-01 12:00:00 |
| 8 | P400 | 12 | 705 | departure | 2024-07-01 12:59:00 |
Expected Output
| package_id(VARCHAR) | hub_id(INTEGER) | arrival_truck(INTEGER) | departure_truck(INTEGER) | processing_time_minutes(INTEGER) |
|---|---|---|---|---|
| P100 | 10 | 501 | 502 | 45 |
| P400 | 12 | 701 | 705 | 59 |
Tags
HardSelf JoinTime DifferenceOperations Analytics
30-40 min
35%