Swiggy: Instamart Orders Delivered Within 10 Minutes
The Instamart operations team checks every morning which grocery orders actually kept the ten-minute promise made on the app.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #948
Problem statement
The Instamart operations team checks every morning which grocery orders actually kept the ten-minute promise made on the app.
Return every delivered order whose delivery time is at most 10 minutes, with the columns order_id, store_id and delivery_minutes. delivery_minutes is the number of whole minutes between placed_at and delivered_at, rounded down — 10 minutes 40 seconds counts as 10, 11 minutes exactly counts as 11. Cancelled orders have no delivery time and are never in the answer. Return the rows in any order.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: InstamartOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| placed_at | datetime |
| delivered_at | datetime |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per Instamart order. delivered_at is NULL for a cancelled order; an order may be delivered after midnight.
Examples
Example 1
InstamartOrder
| order_id | store_id | placed_at | delivered_at | status |
|---|---|---|---|---|
| 1001 | 1 | 2025-03-10 08:00:00 | 2025-03-10 08:07:30 | delivered |
| 1002 | 1 | 2025-03-10 08:05:00 | 2025-03-10 08:15:40 | delivered |
| 1003 | 2 | 2025-03-10 09:12:10 | 2025-03-10 09:23:10 | delivered |
| 1004 | 2 | 2025-03-10 09:30:00 | NULL | cancelled |
| 1005 | 3 | 2025-03-10 23:55:00 | 2025-03-11 00:04:59 | delivered |
| 1006 | 3 | 2025-03-10 21:00:00 | 2025-03-10 21:10:00 | delivered |
Output
| order_id | store_id | delivery_minutes |
|---|---|---|
| 1001 | 1 | 7 |
| 1002 | 1 | 10 |
| 1005 | 3 | 9 |
| 1006 | 3 | 10 |
How to solve Swiggy: Instamart Orders Delivered Within 10 Minutes
Each order is judged on its own, so this is a filter on one table with one computed column. TIMESTAMPDIFF(MINUTE, placed_at, delivered_at) returns the number of complete minutes between the two timestamps — exactly the rounding the statement asks for, so 10 minutes 40 seconds gives 10 and stays in, while 11 minutes gives 11 and drops out. Working in timestamps rather than clock times also handles the order placed at 23:55 and delivered after midnight: the date part carries the day over.
Cancelled orders have no delivered_at. The difference with NULL is NULL and NULL <= 10 is not true, so they would be dropped anyway, but filtering on status = 'delivered' says what is meant.
The alternative counts seconds and divides with DIV 60, which truncates the same way, and expresses the limit as "delivered before placed_at plus 11 minutes" — anything strictly earlier than the eleventh minute is at most 10 whole minutes. That form can use an index on the timestamps because the column is not wrapped in a function on one side. Either query is a single scan.
Reference solution (MySQL)
SELECT order_id, store_id, TIMESTAMPDIFF(MINUTE, placed_at, delivered_at) AS delivery_minutes
FROM InstamartOrder
WHERE status = 'delivered'
AND TIMESTAMPDIFF(MINUTE, placed_at, delivered_at) <= 10Another way
SELECT order_id, store_id, TIMESTAMPDIFF(SECOND, placed_at, delivered_at) DIV 60 AS delivery_minutes FROM InstamartOrder WHERE status = 'delivered' AND delivered_at < DATE_ADD(placed_at, INTERVAL 11 MINUTE)← Spotify: Playlist Pairs Sharing Half Their Available Tracks · Swiggy: Rain Fee Collected per City →