Swiggy: Packaging Charges Above the Per-Item Cap
Restaurants may add a packaging charge to an order, but each partner restaurant agreed to a cap per item in its contract.
- Difficulty: Easy
- Topics: Joins, Basics
- Dialect: MySQL
- Problem: #953
Problem statement
Restaurants may add a packaging charge to an order, but each partner restaurant agreed to a cap per item in its contract. The restaurant-ops team wants every order that went over.
An order's allowed charge is packaging_cap_per_item × item_count. Return order_id, restaurant_name, packaging_charge and excess_charge (the charge minus the allowed amount) for every order whose packaging charge is strictly greater than the allowed amount. Restaurants with no agreed cap (NULL) are never flagged. Return the rows in any order.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| packaging_cap_per_item | int |
Primary key: restaurant_id.
One row per partner restaurant. packaging_cap_per_item is in rupees, or NULL while the contract is being renegotiated.
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| item_count | int |
| packaging_charge | int |
Primary key: order_id.
One row per order; item_count is the number of items in it, always at least 1.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name | packaging_cap_per_item |
|---|---|---|
| 1 | Udupi Upahar | 10 |
| 2 | Biryani Bowl House | 15 |
| 3 | Wok Express | NULL |
| 4 | Momo Hub | 8 |
FoodOrder
| order_id | restaurant_id | item_count | packaging_charge |
|---|---|---|---|
| 201 | 1 | 3 | 30 |
| 202 | 1 | 2 | 25 |
| 203 | 2 | 4 | 75 |
| 204 | 3 | 5 | 90 |
| 205 | 4 | 1 | 8 |
| 206 | 4 | 3 | 30 |
| 207 | 2 | 1 | 10 |
Output
| order_id | restaurant_name | packaging_charge | excess_charge |
|---|---|---|---|
| 202 | Udupi Upahar | 25 | 5 |
| 203 | Biryani Bowl House | 75 | 15 |
| 206 | Momo Hub | 30 | 6 |
How to solve Swiggy: Packaging Charges Above the Per-Item Cap
Each order needs one value from its restaurant — the per-item cap — so an inner join on restaurant_id puts the two side by side. The allowed charge is the cap times item_count, and an order is flagged only when its packaging charge is strictly greater; charging exactly the allowed amount is within the contract.
Restaurants with a NULL cap need no special case. NULL * item_count is NULL, packaging_charge > NULL is NULL, and WHERE keeps only true rows, so their orders are never flagged — the behaviour the statement asks for. Writing COALESCE(cap, 0) would be a bug here: it would flag every order of those restaurants.
The excess is the same difference in the SELECT list. The alternative computes it once in a derived table and filters on excess_charge > 0, which is equivalent because charge - allowed > 0 exactly when charge > allowed, and NULL excesses fail the test the same way. Both are a join through the restaurant's primary key, linear in the number of orders.
Reference solution (MySQL)
SELECT o.order_id, r.restaurant_name, o.packaging_charge,
o.packaging_charge - r.packaging_cap_per_item * o.item_count AS excess_charge
FROM FoodOrder o
JOIN Restaurant r ON r.restaurant_id = o.restaurant_id
WHERE o.packaging_charge > r.packaging_cap_per_item * o.item_countAnother way
SELECT order_id, restaurant_name, packaging_charge, excess_charge FROM (
SELECT o.order_id, r.restaurant_name, o.packaging_charge,
o.packaging_charge - r.packaging_cap_per_item * o.item_count AS excess_charge
FROM FoodOrder o, Restaurant r WHERE r.restaurant_id = o.restaurant_id
) t WHERE excess_charge > 0← Swiggy: Swiggy One Plans With No Order Placed · Swiggy: Dineout Restaurants With Repeat No-Shows →