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
| Column | Type |
|---|---|
| order_id | int |
| city | varchar |
| order_date | date |
| rain_fee | int |
| status | enum(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_id | city | order_date | rain_fee | status |
|---|---|---|---|---|
| 1 | Bengaluru | 2025-07-01 | 25 | delivered |
| 2 | Bengaluru | 2025-07-01 | 25 | cancelled |
| 3 | Mumbai | 2025-07-01 | 30 | delivered |
| 4 | Mumbai | 2025-07-02 | 20 | delivered |
| 5 | Bengaluru | 2025-07-02 | 25 | delivered |
| 6 | Chennai | 2025-07-02 | 0 | delivered |
| 7 | Pune | 2025-07-03 | NULL | delivered |
| 8 | Kolkata | 2025-07-03 | 50 | delivered |
Output
| city | rain_orders | rain_fee_total |
|---|---|---|
| Bengaluru | 2 | 50 |
| Kolkata | 1 | 50 |
| Mumbai | 2 | 50 |
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, cityAnother 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 →