Cart Shipping Fee and Amount Payable

Easy
104.

Cart Shipping Fee and Amount Payable

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

ColumnType
cart_idint
customer_idint
subtotalint
is_plus_memberbool

Primary key: cart_id.

One row per cart at checkout; subtotal in rupees after item discounts.

Example

The rows must come back in the order shown.

carts

cart_idcustomer_idsubtotalis_plus_member
3014112490
302424990
303434980
304441201
305452600

Output

cart_idshipping_feepayable
30101249
3020499
30340538
3040120
30540300
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.

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

ColumnType
cart_idint
customer_idint
subtotalint
is_plus_memberbool

Primary key: cart_id.

One row per cart at checkout; subtotal in rupees after item discounts.

Examples

Example 1

carts

cart_idcustomer_idsubtotalis_plus_member
3014112490
302424990
303434980
304441201
305452600

Output

cart_idshipping_feepayable
30101249
3020499
30340538
3040120
30540300

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_id

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