Hours between scans
mediumHours between scans
Amazon SQL Interview Question
A package sitting four hours between being picked and being packed is a bottleneck at the pack bench. A package sitting four hours before it is picked is a bottleneck in the pick path. They need different fixes and different people, so the delay has to be attributed to the step it happened in.
Return the package_id, the stage scanned, the scanned_at time, and the hours since that package's previous scan as hours_since_previous, rounded to 2 decimal places. A package's first scan has nothing before it, so leave its gap empty. Sort by package_id and then by 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 | stage | scanned_at | hours_since_previous |
|---|---|---|---|
| P001 | received | 2024-11-01 09:05:00 | NULL |
| P001 | picked | 2024-11-01 10:20:00 | 1.25 |
| P001 | packed | 2024-11-01 11:15:00 | 0.92 |
| P001 | shipped | 2024-11-01 13:40:00 | 2.42 |
| P001 | delivered | 2024-11-02 09:30:00 | 19.83 |
| P006 | received | 2024-11-01 07:50:00 | NULL |
| P006 | picked | 2024-11-01 09:40:00 | 1.83 |
Explanation
P001's first scan has nothing before it, so its gap is empty. It was picked 1.25 hours later, packed 0.92 hours after that, and shipped 2.42 hours after packing. The delivery scan is 19.83 hours later, most of that overnight.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
medium
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...