Payable Amount on Surge-Priced Orders

Easy
149.

Payable Amount on Surge-Priced Orders

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

ColumnType
order_idint
customer_idint
placed_atdatetime
item_totalint
delivery_feeint
surge_feeint
discountint

Primary key: order_id.

Amounts are in rupees. surge_fee is 0 when no surge applied; discount is NULL when no coupon was used.

Example

The rows must come back in the order shown.

FoodOrder

order_idcustomer_idplaced_atitem_totaldelivery_feesurge_feediscount
5001112024-07-14 20:05:004503025NULL
5002122024-07-14 20:11:0032030050
5003132024-07-14 20:40:00610040100
5004112024-07-15 13:02:00280251520
5005142024-07-15 21:30:002402510NULL
5006152024-07-16 12:45:00199300NULL

Output

order_idsurge_feepayable
500340550
500125505
500415300
500510275
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.

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

ColumnType
order_idint
customer_idint
placed_atdatetime
item_totalint
delivery_feeint
surge_feeint
discountint

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_idcustomer_idplaced_atitem_totaldelivery_feesurge_feediscount
5001112024-07-14 20:05:004503025NULL
5002122024-07-14 20:11:0032030050
5003132024-07-14 20:40:00610040100
5004112024-07-15 13:02:00280251520
5005142024-07-15 21:30:002402510NULL
5006152024-07-16 12:45:00199300NULL

Output

order_idsurge_feepayable
500340550
500125505
500415300
500510275

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_id

Another 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, 1

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