Daily Cancellation Rate Without Suspended Members — SQL Hard Problem

Problem statement

A food-delivery app records each order with the customer who placed it and the delivery partner assigned to it. An order can end delivered or be cancelled by the customer, the partner or the restaurant. Some members have been suspended for fraud, and their orders must be left out of the figures.

For each day from 2025-03-01 to 2025-03-07 (both included), compute the cancellation rate: the number of cancelled orders (every status other than delivered) divided by the number of orders, counting only orders whose customer is not suspended and whose partner is not suspended. An order cancelled before any partner was assigned has partner_id NULL — it still counts, as long as its customer is not suspended. Round the rate to 2 decimal places.

Return the columns order_day and cancellation_rate, one row per day with at least one counted order, ordered by order_day.

Tables

Table: FoodOrder

Primary key: order_id.

customer_id and partner_id are member_ids in Member. partner_id is NULL when the order was cancelled before a partner was assigned.

Table: Member

Primary key: member_id.

suspended is 1 for a suspended member and 0 otherwise.

Examples

Example 1

FoodOrder

Member

Output

How to solve Daily Cancellation Rate Without Suspended Members

The work is in choosing the orders; the arithmetic is one grouped average. An order counts when it falls inside the week, its customer is not suspended, and its partner — if it has one — is not suspended either.

Each order refers to two members, so the reference query joins Member twice under two aliases. The customer side is an inner join with c.suspended = 0 in its condition: every order has a customer, and orders of suspended customers drop out. The partner side must be a LEFT JOIN, because an inner join on partner_id would silently discard every order cancelled before assignment — exactly the orders the statement says still count. The WHERE clause then admits an order if it has no partner or its partner is not suspended.

With the right orders kept, grouping by order_date and averaging a 1-or-0 cancellation flag gives cancelled ÷ total for each day; days with no counted order produce no group and so no row.

The first alternative asks the question per order with NOT EXISTS: no suspended member whose id is the customer's or the partner's. IN (customer_id, NULL) is never true through the NULL, so unassigned orders pass. The second uses NOT IN against a list of suspended ids, which needs the explicit partner_id IS NULL OR … — NULL NOT IN (…) is unknown and would drop the order. Each version is a single pass over the orders with key lookups into Member.

Reference solution (MySQL)

SELECT o.order_date AS order_day,
       ROUND(SUM(CASE WHEN o.status = 'delivered' THEN 0 ELSE 1 END) / COUNT(*), 2) AS cancellation_rate
FROM FoodOrder o
JOIN Member c ON c.member_id = o.customer_id AND c.suspended = 0
LEFT JOIN Member p ON p.member_id = o.partner_id
WHERE o.order_date BETWEEN '2025-03-01' AND '2025-03-07'
  AND (o.partner_id IS NULL OR p.suspended = 0)
GROUP BY o.order_date
ORDER BY o.order_date

Another way

SELECT order_date AS order_day, ROUND(AVG(status <> 'delivered'), 2) AS cancellation_rate
FROM FoodOrder o
WHERE order_date BETWEEN '2025-03-01' AND '2025-03-07'
  AND NOT EXISTS (SELECT 1 FROM Member m WHERE m.suspended = 1 AND m.member_id IN (o.customer_id, o.partner_id))
GROUP BY order_date
ORDER BY order_day

Another way

WITH banned AS (SELECT member_id FROM Member WHERE suspended = 1)
SELECT order_date AS order_day, ROUND(AVG(IF(status = 'delivered', 0, 1)), 2) AS cancellation_rate
FROM FoodOrder
WHERE order_date >= '2025-03-01' AND order_date <= '2025-03-07'
  AND customer_id NOT IN (SELECT member_id FROM banned)
  AND (partner_id IS NULL OR partner_id NOT IN (SELECT member_id FROM banned))
GROUP BY order_date
ORDER BY order_date