Swiggy: Packaging Charges Above the Per-Item Cap

Easy
953.

Swiggy: Packaging Charges Above the Per-Item Cap

EasyJoinsBasics

MySQL

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

ColumnType
restaurant_idint
restaurant_namevarchar
packaging_cap_per_itemint

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

ColumnType
order_idint
restaurant_idint
item_countint
packaging_chargeint

Primary key: order_id.

One row per order; item_count is the number of items in it, always at least 1.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Restaurant

restaurant_idrestaurant_namepackaging_cap_per_item
1Udupi Upahar10
2Biryani Bowl House15
3Wok ExpressNULL
4Momo Hub8

FoodOrder

order_idrestaurant_iditem_countpackaging_charge
2011330
2021225
2032475
2043590
205418
2064330
2072110

Output

order_idrestaurant_namepackaging_chargeexcess_charge
202Udupi Upahar255
203Biryani Bowl House7515
206Momo Hub306
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.

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

ColumnType
restaurant_idint
restaurant_namevarchar
packaging_cap_per_itemint

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

ColumnType
order_idint
restaurant_idint
item_countint
packaging_chargeint

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_idrestaurant_namepackaging_cap_per_item
1Udupi Upahar10
2Biryani Bowl House15
3Wok ExpressNULL
4Momo Hub8

FoodOrder

order_idrestaurant_iditem_countpackaging_charge
2011330
2021225
2032475
2043590
205418
2064330
2072110

Output

order_idrestaurant_namepackaging_chargeexcess_charge
202Udupi Upahar255
203Biryani Bowl House7515
206Momo Hub306

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_count

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