Stage times per package
mediumStage times per package
Amazon SQL Interview Question
Amazon runs a fulfillment center on stage times: if packages sit waiting to be packed the answer is more packers, and if the pick walk is slow the answer is where the stock is kept. Every scan is written as its own event as a package moves through, and a package that has not reached a stage has no scan for it yet.
Return the package_id, and the time of each scan as received_at, picked_at, packed_at, shipped_at and delivered_at. A stage a package has not reached yet stays empty.
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
| package_id | received_at | picked_at | packed_at | shipped_at | delivered_at |
|---|---|---|---|---|---|
| P001 | 2024-11-01 09:05:00 | 2024-11-01 10:20:00 | 2024-11-01 11:15:00 | 2024-11-01 13:40:00 | 2024-11-02 09:30:00 |
| P006 | 2024-11-01 07:50:00 | 2024-11-01 09:40:00 | NULL | NULL | NULL |
Explanation
P001 reached every stage, so all five times are filled in. P006 has only been received and picked, so its three later columns are empty rather than the package dropping out of the report.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
medium
Topic
reshaping
Language
SQL
Your query
Starting the SQL engine...