Swiggy: Instamart Orders Delivered Within 10 Minutes

Easy
948.

Swiggy: Instamart Orders Delivered Within 10 Minutes

EasyBasicsDates

MySQL

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

ColumnTypeValues
order_idint
store_idint
placed_atdatetime
delivered_atdatetime
statusenumdelivered, 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

InstamartOrder

order_idstore_idplaced_atdelivered_atstatus
100112025-03-10 08:00:002025-03-10 08:07:30delivered
100212025-03-10 08:05:002025-03-10 08:15:40delivered
100322025-03-10 09:12:102025-03-10 09:23:10delivered
100422025-03-10 09:30:00NULLcancelled
100532025-03-10 23:55:002025-03-11 00:04:59delivered
100632025-03-10 21:00:002025-03-10 21:10:00delivered

Output

order_idstore_iddelivery_minutes
100117
1002110
100539
1006310
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
store_idint
placed_atdatetime
delivered_atdatetime
statusenum(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_idstore_idplaced_atdelivered_atstatus
100112025-03-10 08:00:002025-03-10 08:07:30delivered
100212025-03-10 08:05:002025-03-10 08:15:40delivered
100322025-03-10 09:12:102025-03-10 09:23:10delivered
100422025-03-10 09:30:00NULLcancelled
100532025-03-10 23:55:002025-03-11 00:04:59delivered
100632025-03-10 21:00:002025-03-10 21:10:00delivered

Output

order_idstore_iddelivery_minutes
100117
1002110
100539
1006310

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) <= 10

Another 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 →