Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA

Medium
661.

Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA

F-Assured sellers must hand an order to Ekart within 24 hours of confirming it. The seller-quality team runs the check with the cut-off 2024-10-10 00:00:00.

An order is late if it was dispatched more than 24 hours after confirmed_at, or if it has not been dispatched and the cut-off is more than 24 hours after confirmed_at. Exactly 24 hours is on time. Report a seller with at least 3 orders of which more than half are late. Return seller_name, orders (all their orders) and late_orders, ordered by late_orders descending, then seller_name.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: Seller

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per F-Assured seller; names are unique.

Table: FulfilmentOrder

ColumnType
order_idint
seller_idint
confirmed_atdatetime
dispatched_atdatetime

Primary key: order_id.

One row per order, confirmed before the cut-off. dispatched_at is NULL until the parcel is handed over.

Example

The rows must come back in the order shown.

Seller

seller_idseller_name
1Metro Gadgets
2Desi Weaves
3Om Sai Traders

FulfilmentOrder

order_idseller_idconfirmed_atdispatched_at
112024-10-01 10:00:002024-10-02 10:00:00
212024-10-02 09:00:002024-10-03 09:01:00
312024-10-07 18:30:00NULL
422024-10-03 11:00:002024-10-03 15:00:00
522024-10-04 11:00:002024-10-06 12:00:00
622024-10-09 20:00:00NULL
732024-10-05 08:00:002024-10-07 08:00:00
832024-10-05 09:00:002024-10-07 09:00:00

Output

seller_nameorderslate_orders
Metro Gadgets32
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.

Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA

F-Assured sellers must hand an order to Ekart within 24 hours of confirming it. The seller-quality team runs the check with the cut-off 2024-10-10 00:00:00.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #661

Problem statement

F-Assured sellers must hand an order to Ekart within 24 hours of confirming it. The seller-quality team runs the check with the cut-off 2024-10-10 00:00:00.

An order is late if it was dispatched more than 24 hours after confirmed_at, or if it has not been dispatched and the cut-off is more than 24 hours after confirmed_at. Exactly 24 hours is on time. Report a seller with at least 3 orders of which more than half are late. Return seller_name, orders (all their orders) and late_orders, ordered by late_orders descending, then seller_name.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: Seller

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per F-Assured seller; names are unique.

Table: FulfilmentOrder

ColumnType
order_idint
seller_idint
confirmed_atdatetime
dispatched_atdatetime

Primary key: order_id.

One row per order, confirmed before the cut-off. dispatched_at is NULL until the parcel is handed over.

Examples

Example 1

Seller

seller_idseller_name
1Metro Gadgets
2Desi Weaves
3Om Sai Traders

FulfilmentOrder

order_idseller_idconfirmed_atdispatched_at
112024-10-01 10:00:002024-10-02 10:00:00
212024-10-02 09:00:002024-10-03 09:01:00
312024-10-07 18:30:00NULL
422024-10-03 11:00:002024-10-03 15:00:00
522024-10-04 11:00:002024-10-06 12:00:00
622024-10-09 20:00:00NULL
732024-10-05 08:00:002024-10-07 08:00:00
832024-10-05 09:00:002024-10-07 09:00:00

Output

seller_nameorderslate_orders
Metro Gadgets32

How to solve Flipkart: F-Assured Sellers Missing the 24-Hour Dispatch SLA

The lateness test has two cases that collapse into one: an order still waiting is late precisely when the cut-off is already past its 24-hour deadline, which is the same as pretending it was dispatched at the cut-off. COALESCE(dispatched_at, '2024-10-10 00:00:00') makes that substitution, and TIMESTAMPDIFF(MINUTE, confirmed_at, …) > 1440 is the test. Measuring in minutes keeps the boundary exact — 24 hours to the minute is on time, 24 hours and one minute is late.

Per seller, COUNT(*) gives all orders and a CASE inside SUM the late ones. The two filters are on aggregates: at least three orders, so one bad day does not flag a seller, and 2 * late > orders for "more than half" without division or rounding.

The alternative compares the deadline DATE_ADD(confirmed_at, INTERVAL 24 HOUR) with the dispatch time (or the cut-off), sums the boolean flag, and compares with COUNT(*) / 2, which is decimal division. Both read each order once.

Reference solution (MySQL)

SELECT s.seller_name,
       COUNT(*) AS orders,
       SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, o.confirmed_at, COALESCE(o.dispatched_at, '2024-10-10 00:00:00')) > 1440
                THEN 1 ELSE 0 END) AS late_orders
FROM Seller s
JOIN FulfilmentOrder o ON o.seller_id = s.seller_id
GROUP BY s.seller_id, s.seller_name
HAVING COUNT(*) >= 3
   AND 2 * SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, o.confirmed_at, COALESCE(o.dispatched_at, '2024-10-10 00:00:00')) > 1440 THEN 1 ELSE 0 END) > COUNT(*)
ORDER BY late_orders DESC, s.seller_name

Another way

WITH flagged AS (
  SELECT seller_id,
         CASE WHEN dispatched_at IS NULL THEN DATE_ADD(confirmed_at, INTERVAL 24 HOUR) < '2024-10-10 00:00:00'
              ELSE DATE_ADD(confirmed_at, INTERVAL 24 HOUR) < dispatched_at END AS is_late
  FROM FulfilmentOrder
)
SELECT s.seller_name, COUNT(*) AS orders, SUM(f.is_late) AS late_orders
FROM flagged f JOIN Seller s ON s.seller_id = f.seller_id
GROUP BY s.seller_id, s.seller_name
HAVING COUNT(*) >= 3 AND SUM(f.is_late) > COUNT(*) / 2
ORDER BY late_orders DESC, s.seller_name

← Flipkart: Certified Buyer Reviews and Their Rating per Product · Flipkart: Sale Shoppers Who Bought From Three Categories →