Swiggy: Rain Fee Collected per City

Easy
949.

Swiggy: Rain Fee Collected per City

EasyAggregation

MySQL

When it rains, a small rain fee is added to food orders so delivery partners are paid more for riding in bad weather. Finance wants to know how much of it each city actually collected.

For every city with at least one delivered order that carried a rain fee (rain_fee > 0), return city, rain_orders (the number of such orders) and rain_fee_total (the sum of their rain fees). Cancelled orders collect nothing; a rain_fee of 0 or NULL means no fee. Order the rows by rain_fee_total from highest to lowest, then by city alphabetically.

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: FoodOrder

ColumnTypeValues
order_idint
cityvarchar
order_datedate
rain_feeint
statusenumdelivered, cancelled

Primary key: order_id.

One row per food order. rain_fee is in rupees; it is NULL on orders logged before the fee was recorded.

Example

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

FoodOrder

order_idcityorder_daterain_feestatus
1Bengaluru2025-07-0125delivered
2Bengaluru2025-07-0125cancelled
3Mumbai2025-07-0130delivered
4Mumbai2025-07-0220delivered
5Bengaluru2025-07-0225delivered
6Chennai2025-07-020delivered
7Pune2025-07-03NULLdelivered
8Kolkata2025-07-0350delivered

Output

cityrain_ordersrain_fee_total
Bengaluru250
Kolkata150
Mumbai250
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: Rain Fee Collected per City — SQL Problem

When it rains, a small rain fee is added to food orders so delivery partners are paid more for riding in bad weather.

  • Difficulty: Easy
  • Topics: Aggregation
  • Dialect: MySQL
  • Problem: #949

Problem statement

When it rains, a small rain fee is added to food orders so delivery partners are paid more for riding in bad weather. Finance wants to know how much of it each city actually collected.

For every city with at least one delivered order that carried a rain fee (rain_fee > 0), return city, rain_orders (the number of such orders) and rain_fee_total (the sum of their rain fees). Cancelled orders collect nothing; a rain_fee of 0 or NULL means no fee. Order the rows by rain_fee_total from highest to lowest, then by city alphabetically.

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: FoodOrder

ColumnType
order_idint
cityvarchar
order_datedate
rain_feeint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per food order. rain_fee is in rupees; it is NULL on orders logged before the fee was recorded.

Examples

Example 1

FoodOrder

order_idcityorder_daterain_feestatus
1Bengaluru2025-07-0125delivered
2Bengaluru2025-07-0125cancelled
3Mumbai2025-07-0130delivered
4Mumbai2025-07-0220delivered
5Bengaluru2025-07-0225delivered
6Chennai2025-07-020delivered
7Pune2025-07-03NULLdelivered
8Kolkata2025-07-0350delivered

Output

cityrain_ordersrain_fee_total
Bengaluru250
Kolkata150
Mumbai250

How to solve Swiggy: Rain Fee Collected per City

Filter first, then group. The rows that matter are delivered orders with rain_fee > 0; everything else — cancelled orders, orders with a zero fee and orders whose fee is NULL — contributes nothing and should never reach the aggregate. NULL > 0 evaluates to NULL, which WHERE treats as false, so NULL fees need no special handling.

After the filter, GROUP BY city with COUNT(*) and SUM(rain_fee) gives both numbers. A city with no qualifying order has no rows left and simply does not form a group, which is what the statement wants.

The order is fixed: highest total first, and cities tied on the total alphabetically. Without the second key the tied rows could come back in any order. The alternative keeps every row and moves the condition into conditional sums, then removes the empty cities with HAVING — a common pattern when one report needs several differently filtered totals per group. Both are one pass over the orders.

Reference solution (MySQL)

SELECT city, COUNT(*) AS rain_orders, SUM(rain_fee) AS rain_fee_total
FROM FoodOrder
WHERE status = 'delivered' AND rain_fee > 0
GROUP BY city
ORDER BY rain_fee_total DESC, city

Another way

SELECT city,
       SUM(CASE WHEN status = 'delivered' AND rain_fee > 0 THEN 1 ELSE 0 END) AS rain_orders,
       SUM(CASE WHEN status = 'delivered' AND rain_fee > 0 THEN rain_fee ELSE 0 END) AS rain_fee_total
FROM FoodOrder
GROUP BY city
HAVING SUM(CASE WHEN status = 'delivered' AND rain_fee > 0 THEN 1 ELSE 0 END) > 0
ORDER BY rain_fee_total DESC, city

← Swiggy: Instamart Orders Delivered Within 10 Minutes · Swiggy: Genie Fare by Distance Slab →