Swiggy hard Derived TableSubquery in FROMCASE WHENHAVING

Using a subquery in the FROM clause, first compute for each kitchen_name in swiggy_kitchen_orders: cancelled_count (orders with order_status = 'cancelled'), total_resolved (orders where order_status is not NULL), and cancellation_rate as cancelled_count divided by total_resolved times 100, rounded to 2 decimal places. Then, in the outer query, return only the kitchens where cancellation_rate is greater than 20, with columns kitchen_name, cancelled_count, total_resolved, and cancellation_rate, sorted by cancellation_rate in descending order.

Swiggy · Kitchen Cancellation Rates — practice this real-world SQL scenario live in your browser.

📖 Story Swiggy · Kitchen Cancellation Rates

Wednesday afternoon at Swiggy's Kitchen Health team. swiggy_kitchen_orders logs every order placed at a dark kitchen along with its order_status. A kitchen gets flagged for review when more than 20% of its resolved orders end up cancelled. Some rows have order_status = NULL — the order is still out for delivery or being prepared, with no final outcome yet, so it hasn't actually resolved as either a delivery or a cancellation. The team wants a clean list of every kitchen currently over the 20% cancellation threshold, and an in-transit order must not be counted as if it were a completed, non-cancelled order.

🎯 Your Mission

📋 Table Structure

🗂 swiggy_kitchen_orders

id INTEGER 1

kitchen_name TEXT Andheri Kitchen

order_status TEXT cancelled

⚡ Step-by-Step Walkthrough

Look at the raw orders, including the ones still in transit

query.sql

SELECT *
FROM swiggy_kitchen_orders
ORDER BY kitchen_name, id;

💡 Explanation

swiggy_kitchen_orders has one row per order, not one row per kitchen — kitchen_name repeats across every order, and all three kitchens (Andheri, Bandra, Powai) have exactly 10 orders logged each.
Two kitchens have order_status = NULL rows: Andheri has 2, Powai has 2. That NULL isn't a fourth status value sitting alongside 'delivered' and 'cancelled' — it means the order hasn't reached a final outcome yet, it's still in transit.
Bandra Kitchen has no NULL rows at all — every one of its 10 orders has already resolved to either 'delivered' or 'cancelled', which makes it a useful control: its numbers won't change no matter how the NULL handling is written.
Nothing is aggregated yet — this step only confirms which rows represent a genuine final outcome and which ones are still pending, a distinction the next two steps handle very differently.

See the trap: HAVING with COUNT(*) hides a kitchen that should be flagged

query.sql

SELECT kitchen_name,
       SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
       COUNT(*) AS total_orders,
       ROUND(SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS cancellation_rate_wrong
FROM swiggy_kitchen_orders
GROUP BY kitchen_name
HAVING cancellation_rate_wrong > 20
ORDER BY cancellation_rate_wrong DESC;

💡 Explanation

COUNT(*) counts every row in the group, in-transit orders included — so Powai Kitchen's denominator here is 10 (2 cancelled + 6 delivered + 2 still in transit), not the 8 orders that have actually resolved one way or the other.
That inflated denominator dilutes cancellation_rate_wrong for Powai down to exactly 20.00 — and since the filter is cancellation_rate_wrong > 20, a rate of exactly 20 doesn't qualify, so Powai silently disappears from the result entirely.
Only Bandra Kitchen shows up here, at 30%. Bandra happens to have zero in-transit orders, so COUNT(*) and a resolved-only count give it the identical answer — this trap is invisible on any kitchen without pending orders, which is exactly why it can sit unnoticed for weeks.
HAVING is the right clause here, not WHERE — cancellation_rate_wrong is built from SUM and COUNT, and WHERE isn't allowed to see aggregate results at all, so wrong denominator aside, this query is at least filtering at the correct stage of execution.

Fix it: pre-aggregate resolved orders in a derived table, then filter cleanly

query.sql

SELECT kitchen_name, cancelled_count, total_resolved, cancellation_rate
FROM (
  SELECT kitchen_name,
         SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
         COUNT(order_status) AS total_resolved,
         ROUND(SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) * 100.0 / COUNT(order_status), 2) AS cancellation_rate
  FROM swiggy_kitchen_orders
  GROUP BY kitchen_name
) AS kitchen_stats
WHERE cancellation_rate > 20
ORDER BY cancellation_rate DESC;

💡 Explanation

COUNT(order_status) — not COUNT(*) — is the fix at its core: COUNT() skips NULL automatically, so Powai's total_resolved comes out to 8, not 10, correctly leaving out its 2 in-transit orders.
That single change moves Powai's cancellation_rate from a diluted 20.00 to its real 25.00 — clearly above the 20% threshold now, and clearly a kitchen that deserves review.
The inner query — everything inside FROM (...) AS kitchen_stats — runs to completion first, producing a small, already-aggregated 3-row result with plain ordinary columns; the outer WHERE cancellation_rate > 20 then filters that like any normal table, no aggregate functions or GROUP BY-stage restrictions to work around.
This is the real reason to reach for a derived table over repeating a HAVING expression: kitchen_stats.cancellation_rate is a genuine output column of the subquery, filterable and sortable the same way on any SQL engine — unlike leaning on a SELECT-list alias inside HAVING, which SQLite happens to allow but isn't something every engine guarantees.

▶ Practice this live ← Browse all questions