Refund Due on Cancelled Food Orders — SQL Problem
The refund policy for a cancelled order depends on who cancelled and when.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #155
Problem statement
The refund policy for a cancelled order depends on who cancelled and when. If the restaurant, the rider or the platform cancelled, the customer gets the full order_value back. If the customer cancelled, they get the full value within 2 minutes of placing (2 minutes included), half the value rounded down to the rupee from 3 to 10 minutes, and nothing after 10 minutes.
Return order_id, cancelled_by and refund for every cancelled order, ordered by order_id.
Tables
Table: CancelledOrder
| Column | Type |
|---|---|
| order_id | int |
| order_value | int |
| cancelled_by | enum(customer, restaurant, rider, platform) |
| minutes_after_placing | int |
Primary key: order_id.
One row per cancelled order, order_value in rupees; minutes_after_placing is whole minutes between paying and cancelling.
Examples
Example 1
CancelledOrder
| order_id | order_value | cancelled_by | minutes_after_placing |
|---|---|---|---|
| 8101 | 400 | customer | 1 |
| 8102 | 355 | customer | 2 |
| 8103 | 355 | customer | 3 |
| 8104 | 280 | customer | 10 |
| 8105 | 280 | customer | 11 |
| 8106 | 520 | restaurant | 25 |
| 8107 | 199 | rider | 40 |
| 8108 | 610 | platform | 5 |
Output
| order_id | cancelled_by | refund |
|---|---|---|
| 8101 | customer | 400 |
| 8102 | customer | 355 |
| 8103 | customer | 177 |
| 8104 | customer | 140 |
| 8105 | customer | 0 |
| 8106 | restaurant | 520 |
| 8107 | rider | 199 |
| 8108 | platform | 610 |
How to solve Refund Due on Cancelled Food Orders
The policy is a decision table, and CASE evaluates its branches in order and stops at the first true one — so the order of the branches carries part of the logic. Put the cancelled-by rule first: anyone other than the customer means a full refund whatever the time. Every later branch then knows the customer cancelled, and the minutes decide: <= 2 full, <= 10 half (the first branch already took 0–2, so this one means 3–10), anything else 0.
"Half rounded down to the rupee" matters for odd values: 355 / 2 is 177.5 under MySQL's decimal division, and FLOOR gives 177. Integer division (DIV) gives the same, and so does subtracting the remainder before halving. ROUND would give 178 — the wrong rupee.
Nested IF reads the same rules inside out. Every form is one pass over the table.
Reference solution (MySQL)
SELECT order_id, cancelled_by,
CASE
WHEN cancelled_by <> 'customer' THEN order_value
WHEN minutes_after_placing <= 2 THEN order_value
WHEN minutes_after_placing <= 10 THEN FLOOR(order_value / 2)
ELSE 0
END AS refund
FROM CancelledOrder
ORDER BY order_idAnother way
SELECT order_id, cancelled_by, IF(cancelled_by = 'customer', IF(minutes_after_placing <= 2, order_value, IF(minutes_after_placing <= 10, order_value DIV 2, 0)), order_value) AS refund FROM CancelledOrder ORDER BY order_idAnother way
SELECT order_id, cancelled_by, CASE WHEN cancelled_by IN ('restaurant', 'rider', 'platform') OR minutes_after_placing BETWEEN 0 AND 2 THEN order_value WHEN minutes_after_placing BETWEEN 3 AND 10 THEN (order_value - MOD(order_value, 2)) / 2 ELSE 0 END AS refund FROM CancelledOrder ORDER BY order_id← Restaurants Averaging Under 3.5 Stars · Riders Missing the Delivery SLA in June 2024 →