Prepaid High-Value Orders Awaiting Dispatch — SQL Problem
The warehouse prioritises prepaid orders of high value: the money is already collected, so a delay costs a refund.
- Difficulty: Easy
- Topics: Basics, Conditional Logic
- Dialect: MySQL
- Problem: #98
Problem statement
The warehouse prioritises prepaid orders of high value: the money is already collected, so a delay costs a refund. An order is prepaid when its payment_mode is anything but COD, and it is awaiting dispatch while its status is placed or packed.
Return every prepaid order awaiting dispatch whose order_total is at least ₹5,000, with the columns order_id, payment_mode and order_total. Return the rows in any order.
Tables
Table: orders
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| payment_mode | enum(UPI, Card, NetBanking, COD) |
| order_total | decimal |
| status | enum(placed, packed, shipped, delivered, cancelled) |
| placed_at | datetime |
Primary key: order_id.
One row per order. order_total is in rupees, GST included.
Examples
Example 1
orders
| order_id | customer_id | payment_mode | order_total | status | placed_at |
|---|---|---|---|---|---|
| 9001 | 11 | UPI | 7499 | placed | 2024-11-02 10:14:00 |
| 9002 | 12 | COD | 12999 | placed | 2024-11-02 10:20:00 |
| 9003 | 13 | Card | 5000 | packed | 2024-11-02 11:05:00 |
| 9004 | 14 | NetBanking | 4999 | placed | 2024-11-02 11:40:00 |
| 9005 | 15 | UPI | 18990 | shipped | 2024-11-02 12:02:00 |
| 9006 | 16 | Card | 25499 | packed | 2024-11-02 12:30:00 |
| 9007 | 17 | UPI | 6200 | cancelled | 2024-11-02 13:10:00 |
Output
| order_id | payment_mode | order_total |
|---|---|---|
| 9001 | UPI | 7499 |
| 9003 | Card | 5000 |
| 9006 | Card | 25499 |
How to solve Prepaid High-Value Orders Awaiting Dispatch
Each condition in the statement is a filter on one column of the same row, so the whole answer is one WHERE clause with three conditions joined by AND.
Prepaid is the complement of cash on delivery, so payment_mode <> 'COD' covers UPI, cards and net banking — and keeps working if the payments team adds a new mode tomorrow. Listing the modes with IN (...) is equivalent on this data. Awaiting dispatch is a set of two statuses, which IN ('placed', 'packed') states directly; status = 'placed' OR status = 'packed' is the same thing, but parenthesise the OR or it binds wrongly against the ANDs around it.
The value condition is inclusive: >= 5000 keeps order 9003 of exactly ₹5,000, while ₹4,999 is out. A shipped or cancelled order is out however large it is. The query reads the table once; on a real orders table an index on (status, order_total) makes it a range scan.
Reference solution (MySQL)
SELECT order_id, payment_mode, order_total
FROM orders
WHERE payment_mode <> 'COD'
AND status IN ('placed', 'packed')
AND order_total >= 5000Another way
SELECT order_id, payment_mode, order_total FROM orders WHERE payment_mode IN ('UPI', 'Card', 'NetBanking') AND (status = 'placed' OR status = 'packed') AND NOT order_total < 5000Another way
SELECT order_id, payment_mode, order_total FROM orders WHERE CASE WHEN payment_mode = 'COD' THEN 0 WHEN status NOT IN ('placed', 'packed') THEN 0 WHEN order_total < 5000 THEN 0 ELSE 1 END = 1← Auditorium Bookings That Clash Once Changeover Is Counted · Catalogue Products Nobody Has Wishlisted →