Prepaid High-Value Orders Awaiting Dispatch

Easy
98.

Prepaid High-Value Orders Awaiting Dispatch

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

ColumnTypeValues
order_idint
customer_idint
payment_modeenumUPI, Card, NetBanking, COD
order_totaldecimal
statusenumplaced, packed, shipped, delivered, cancelled
placed_atdatetime

Primary key: order_id.

One row per order. order_total is in rupees, GST included.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

orders

order_idcustomer_idpayment_modeorder_totalstatusplaced_at
900111UPI7499placed2024-11-02 10:14:00
900212COD12999placed2024-11-02 10:20:00
900313Card5000packed2024-11-02 11:05:00
900414NetBanking4999placed2024-11-02 11:40:00
900515UPI18990shipped2024-11-02 12:02:00
900616Card25499packed2024-11-02 12:30:00
900717UPI6200cancelled2024-11-02 13:10:00

Output

order_idpayment_modeorder_total
9001UPI7499
9003Card5000
9006Card25499
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
order_idint
customer_idint
payment_modeenum(UPI, Card, NetBanking, COD)
order_totaldecimal
statusenum(placed, packed, shipped, delivered, cancelled)
placed_atdatetime

Primary key: order_id.

One row per order. order_total is in rupees, GST included.

Examples

Example 1

orders

order_idcustomer_idpayment_modeorder_totalstatusplaced_at
900111UPI7499placed2024-11-02 10:14:00
900212COD12999placed2024-11-02 10:20:00
900313Card5000packed2024-11-02 11:05:00
900414NetBanking4999placed2024-11-02 11:40:00
900515UPI18990shipped2024-11-02 12:02:00
900616Card25499packed2024-11-02 12:30:00
900717UPI6200cancelled2024-11-02 13:10:00

Output

order_idpayment_modeorder_total
9001UPI7499
9003Card5000
9006Card25499

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 >= 5000

Another 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 < 5000

Another 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 →