Minutes from order to doorstep
hardMinutes from order to doorstep
Instacart SQL Interview Question
Instacart logs a status event each time an order moves forward, from placed to delivered. The operations team wants to know how long delivered orders took.
For every order that was delivered, return the order_id and the minutes between its placed event and its delivered event as minutes_to_deliver, rounded to 1 decimal place. Sort by order_id.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
eventsTable12 rows
| Column Name | Type |
|---|---|
| event_id | BIGINT |
| order_id | BIGINT |
| status | VARCHAR |
| event_time | TIMESTAMP |
eventsExample Input
| event_id | order_id | status | event_time |
|---|---|---|---|
| 1 | 5001 | placed | 2024-09-10 09:00:12 |
| 2 | 5002 | placed | 2024-09-10 09:04:40 |
| 3 | 5001 | shopping | 2024-09-10 09:12:05 |
| 4 | 5003 | placed | 2024-09-10 09:15:30 |
| 5 | 5002 | shopping | 2024-09-10 09:21:18 |
| 6 | 5001 | delivered | 2024-09-10 10:02:47 |
| 7 | 5004 | placed | 2024-09-10 10:05:00 |
| 8 | 5003 | canceled | 2024-09-10 10:07:33 |
| 9 | 5002 | out for delivery | 2024-09-10 10:15:09 |
| 10 | 5004 | shopping | 2024-09-10 10:31:52 |
| 11 | 5005 | placed | 2024-09-10 10:40:26 |
| 12 | 5002 | delivered | 2024-09-10 11:01:14 |
Example Output
| order_id | minutes_to_deliver |
|---|---|
| 5001 | 62.6 |
| 5002 | 116.6 |
Explanation
Order 5001 was placed at 9:00:12 and delivered at 10:02:47, 62 minutes and 35 seconds later, which is 62.6 minutes. Order 5003 was canceled and orders 5004 and 5005 have not been delivered, so they are left out.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Instacart
Difficulty
hard
Topic
dates
Language
SQL
Your query
Starting the SQL engine...