Refund Due on Cancelled Food Orders

Easy
155.

Refund Due on Cancelled Food Orders

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

ColumnTypeValues
order_idint
order_valueint
cancelled_byenumcustomer, restaurant, rider, platform
minutes_after_placingint

Primary key: order_id.

One row per cancelled order, order_value in rupees; minutes_after_placing is whole minutes between paying and cancelling.

Example

The rows must come back in the order shown.

CancelledOrder

order_idorder_valuecancelled_byminutes_after_placing
8101400customer1
8102355customer2
8103355customer3
8104280customer10
8105280customer11
8106520restaurant25
8107199rider40
8108610platform5

Output

order_idcancelled_byrefund
8101customer400
8102customer355
8103customer177
8104customer140
8105customer0
8106restaurant520
8107rider199
8108platform610
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.

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

ColumnType
order_idint
order_valueint
cancelled_byenum(customer, restaurant, rider, platform)
minutes_after_placingint

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_idorder_valuecancelled_byminutes_after_placing
8101400customer1
8102355customer2
8103355customer3
8104280customer10
8105280customer11
8106520restaurant25
8107199rider40
8108610platform5

Output

order_idcancelled_byrefund
8101customer400
8102customer355
8103customer177
8104customer140
8105customer0
8106restaurant520
8107rider199
8108platform610

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_id

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

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