Count Deliveries in Every Time Band — SQL Medium Problem

Problem statement

A food-delivery app sorts its deliveries into three bands by minutes:

• 'On time' — under 30 minutes;
• 'Late' — 30 to 45 minutes, both ends included;
• 'Very late' — more than 45 minutes.

Return one row per band with the columns band and deliveries (how many deliveries fall in that band). All three bands must appear, with 0 for a band no delivery falls in. Deliveries still on the way (minutes is NULL) belong to no band. Return the rows in any order.

Tables

Table: Delivery

Primary key: delivery_id.

minutes is the time from the order being placed to it reaching the door, in whole minutes, or NULL while the order is still on its way.

Examples

Example 1

Delivery

Output

How to solve Count Deliveries in Every Time Band

Counting per band with GROUP BY over a CASE gives the right numbers for the bands that occur, but a band with no deliveries produces no group at all, so it is missing from the answer instead of showing 0. The bands have to come from the query itself.

The most direct way is one query per band, stacked with UNION ALL: SELECT 'On time' AS band, COUNT(*) AS deliveries FROM Delivery WHERE minutes < 30, then the same for the other two ranges. An aggregate with no GROUP BY always returns exactly one row — COUNT(*) over zero matching rows is 0 — so every band appears. The column names come from the first SELECT.

Alternatively, build a small derived table holding the three band names and LEFT JOIN the classified deliveries to it. Count a column of the joined side, COUNT(d.band), so a band with no match counts 0 rather than 1.

Watch the NULLs. A CASE that ends in ELSE 'Very late' would file every delivery still on the way under the last band; spelling out WHEN minutes > 45 THEN 'Very late' with no ELSE leaves them unclassified, and WHERE minutes < 30 already rejects them. BETWEEN 30 AND 45 includes both ends, which is exactly the "Late" band.

The union version scans the table three times and the join version once; both are linear.

Reference solution (MySQL)

SELECT 'On time' AS band, COUNT(*) AS deliveries FROM Delivery WHERE minutes < 30
UNION ALL
SELECT 'Late', COUNT(*) FROM Delivery WHERE minutes BETWEEN 30 AND 45
UNION ALL
SELECT 'Very late', COUNT(*) FROM Delivery WHERE minutes > 45

Another way

SELECT b.band, COUNT(d.band) AS deliveries
FROM (SELECT 'On time' AS band UNION ALL SELECT 'Late' UNION ALL SELECT 'Very late') b
LEFT JOIN (
  SELECT CASE WHEN minutes < 30 THEN 'On time' WHEN minutes <= 45 THEN 'Late' WHEN minutes > 45 THEN 'Very late' END AS band
  FROM Delivery
) d ON d.band = b.band
GROUP BY b.band

Another way

SELECT 'On time' AS band, IFNULL(SUM(minutes < 30), 0) AS deliveries FROM Delivery
UNION ALL SELECT 'Late', IFNULL(SUM(minutes >= 30 AND minutes <= 45), 0) FROM Delivery
UNION ALL SELECT 'Very late', IFNULL(SUM(minutes > 45), 0) FROM Delivery