Payable Amount on Surge-Priced Orders — SQL Basics Problem
When it rains or the dinner rush outruns the rider fleet, the app adds a surge fee to the bill.
- Difficulty: Easy
- Topics: Basics, Conditional Logic
- Dialect: MySQL
- Problem: #149
Problem statement
When it rains or the dinner rush outruns the rider fleet, the app adds a surge fee to the bill. A coupon, if one was applied, takes discount rupees off; orders without a coupon have discount NULL.
For every order that carried a surge fee (surge_fee greater than 0), return order_id, surge_fee and payable, where payable = item_total + delivery_fee + surge_fee − discount and a missing discount counts as 0. Order the rows by payable descending, then order_id ascending.
Tables
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| item_total | int |
| delivery_fee | int |
| surge_fee | int |
| discount | int |
Primary key: order_id.
Amounts are in rupees. surge_fee is 0 when no surge applied; discount is NULL when no coupon was used.
Examples
Example 1
FoodOrder
| order_id | customer_id | placed_at | item_total | delivery_fee | surge_fee | discount |
|---|---|---|---|---|---|---|
| 5001 | 11 | 2024-07-14 20:05:00 | 450 | 30 | 25 | NULL |
| 5002 | 12 | 2024-07-14 20:11:00 | 320 | 30 | 0 | 50 |
| 5003 | 13 | 2024-07-14 20:40:00 | 610 | 0 | 40 | 100 |
| 5004 | 11 | 2024-07-15 13:02:00 | 280 | 25 | 15 | 20 |
| 5005 | 14 | 2024-07-15 21:30:00 | 240 | 25 | 10 | NULL |
| 5006 | 15 | 2024-07-16 12:45:00 | 199 | 30 | 0 | NULL |
Output
| order_id | surge_fee | payable |
|---|---|---|
| 5003 | 40 | 550 |
| 5001 | 25 | 505 |
| 5004 | 15 | 300 |
| 5005 | 10 | 275 |
How to solve Payable Amount on Surge-Priced Orders
The filter is one comparison, surge_fee > 0. The computed column is where the care goes: in SQL any arithmetic that touches a NULL yields NULL, so 450 + 30 + 25 - NULL is NULL, not 505. The statement says a missing discount counts as zero, which is exactly what COALESCE(discount, 0) (or MySQL's IFNULL) says: use the discount if there is one, else 0.
A CASE WHEN discount IS NULL … spelling is equivalent and sometimes clearer to a reader who has not met COALESCE. The alias payable can be used in ORDER BY because ORDER BY runs after the SELECT list is computed; two orders can owe the same amount, so order_id fixes the order between them.
One pass over the table; nothing is joined or grouped.
Reference solution (MySQL)
SELECT order_id, surge_fee,
item_total + delivery_fee + surge_fee - COALESCE(discount, 0) AS payable
FROM FoodOrder
WHERE surge_fee > 0
ORDER BY payable DESC, order_idAnother way
SELECT order_id, surge_fee, item_total + delivery_fee + surge_fee - IFNULL(discount, 0) AS payable FROM FoodOrder WHERE surge_fee > 0 ORDER BY 3 DESC, 1Another way
SELECT order_id, surge_fee, CASE WHEN discount IS NULL THEN item_total + delivery_fee + surge_fee ELSE item_total + delivery_fee + surge_fee - discount END AS payable FROM FoodOrder WHERE surge_fee <> 0 ORDER BY payable DESC, order_id← Pure-Veg Restaurants Rated 4.2 or Higher · Menu Items Nobody Has Ordered →