Cart Shipping Fee and Amount Payable — SQL Problem
At checkout the app shows a shipping fee. Shipping is free for Plus members and for any cart whose subtotal is ₹499 or more; every other cart pays ₹40.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #104
Problem statement
At checkout the app shows a shipping fee. Shipping is free for Plus members and for any cart whose subtotal is ₹499 or more; every other cart pays ₹40.
Return every cart with the columns cart_id, shipping_fee and payable (the subtotal plus the shipping fee), ordered by cart_id. is_plus_member is 1 for a Plus member and 0 otherwise.
Tables
Table: carts
| Column | Type |
|---|---|
| cart_id | int |
| customer_id | int |
| subtotal | int |
| is_plus_member | bool |
Primary key: cart_id.
One row per cart at checkout; subtotal in rupees after item discounts.
Examples
Example 1
carts
| cart_id | customer_id | subtotal | is_plus_member |
|---|---|---|---|
| 301 | 41 | 1249 | 0 |
| 302 | 42 | 499 | 0 |
| 303 | 43 | 498 | 0 |
| 304 | 44 | 120 | 1 |
| 305 | 45 | 260 | 0 |
Output
| cart_id | shipping_fee | payable |
|---|---|---|
| 301 | 0 | 1249 |
| 302 | 0 | 499 |
| 303 | 40 | 538 |
| 304 | 0 | 120 |
| 305 | 40 | 300 |
How to solve Cart Shipping Fee and Amount Payable
The fee is a per-row decision, so it is a CASE (or MySQL's IF) in the select list: when the customer is a Plus member or the subtotal is at least 499, the fee is 0, otherwise 40. The boundary matters — cart 302 at exactly ₹499 ships free, cart 303 at ₹498 pays.
payable needs the same fee. SQL does not let a select-list alias be used in another expression of the same list, so either repeat the CASE (as the reference does) or compute the fee once in a derived table and add it outside — the alternative flips the condition, charging 40 only when the customer is not a member and the subtotal is below 499, which is De Morgan's law applied to the free-shipping rule.
Nothing is aggregated or joined; the query reads each cart once and sorts by the key.
Reference solution (MySQL)
SELECT cart_id,
CASE WHEN is_plus_member = 1 OR subtotal >= 499 THEN 0 ELSE 40 END AS shipping_fee,
subtotal + CASE WHEN is_plus_member = 1 OR subtotal >= 499 THEN 0 ELSE 40 END AS payable
FROM carts
ORDER BY cart_idAnother way
SELECT cart_id, fee AS shipping_fee, subtotal + fee AS payable FROM (SELECT cart_id, subtotal, IF(is_plus_member = 0 AND subtotal < 499, 40, 0) AS fee FROM carts) t ORDER BY cart_id← Marketplace Orders per Month in 2024 · Masked Reviewer Names for Top Reviews →