Slowest package per facility
hardSlowest package per facility
Amazon SQL Interview Question
Amazon reviews fulfillment centers on their worst case rather than their average, because the average is carried by thousands of ordinary packages while the complaint comes from the one that took a day. Each center brings its slowest package of the day to the morning stand-up.
For each facility, return the facility, the package_id that took longest, and the hours between its first and last scan as hours_in_flight, rounded to 2 decimal places.
Asked of
- Data Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
amazon_fulfillment_scansTable24 rows
| Column Name | Type |
|---|---|
| scan_id | BIGINT |
| package_id | VARCHAR |
| facility | VARCHAR |
| scanned_at | TIMESTAMP |
| stage | VARCHAR |
amazon_fulfillment_scansExample Input
| scan_id | package_id | facility | scanned_at | stage |
|---|---|---|---|---|
| 101 | P001 | BLR1 | 2024-11-01 09:05:00 | received |
| 102 | P001 | BLR1 | 2024-11-01 10:20:00 | picked |
| 103 | P001 | BLR1 | 2024-11-01 11:15:00 | packed |
| 104 | P001 | BLR1 | 2024-11-01 13:40:00 | shipped |
| 105 | P001 | BLR1 | 2024-11-02 09:30:00 | delivered |
| 123 | P006 | HYD3 | 2024-11-01 07:50:00 | received |
| 124 | P006 | HYD3 | 2024-11-01 09:40:00 | picked |
Example Output
| facility | package_id | hours_in_flight |
|---|---|---|
| BLR1 | P001 | 24.42 |
| HYD3 | P006 | 1.83 |
Explanation
P001 took 24.42 hours from its first scan to its last, the slowest in BLR1. HYD3 has only P006 in the example, at 1.83 hours, so that is its slowest by default.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
hard
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...