Packages still moving
mediumPackages still moving
Amazon SQL Interview Question
Every hour, an Amazon fulfillment center needs the packages that are somewhere in the building and not yet out of it, so a supervisor can walk the floor and find them. A package is still in play when its most recent scan is anything other than the delivery scan, and the stage it stopped at says where to look.
Return the package_id, the facility holding it, the last stage it reached as last_stage, and when that happened as last_scanned_at.
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 | facility | last_stage | last_scanned_at |
|---|---|---|---|
| P006 | HYD3 | picked | 2024-11-01 09:40:00 |
Explanation
The most recent scan on P006 is the pick, so it is still somewhere inside HYD3 and the supervisor needs to find it. P001 was last scanned as delivered, so it has left the building and is not listed.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...