Swiggy: GMV Report With City Subtotals and a Grand Total
The weekly business review opens with one table: delivered orders and GMV for every city and business line (food, Instamart, Genie), each city's subtotal…
- Difficulty: Hard
- Topics: Aggregation, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #968
Problem statement
The weekly business review opens with one table: delivered orders and GMV for every city and business line (food, Instamart, Genie), each city's subtotal under its lines, and one grand-total row at the bottom — the way finance lays it out in a spreadsheet.
Using delivered orders only, return the columns city, business, orders (count) and gmv (sum of order_value) as three kinds of rows: one per city and business that had delivered orders; one subtotal per city with business = 'All businesses'; and one grand total with city = 'All cities' and business = 'All businesses'. Order the rows by city alphabetically, within a city its business rows alphabetically and then its subtotal, and the grand total last.
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: SwiggyOrder
| Column | Type |
|---|---|
| order_id | int |
| city | varchar |
| business | enum(food, instamart, genie) |
| order_value | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order of the week; order_value is the basket value in rupees. Every week has at least one delivered order.
Examples
Example 1
SwiggyOrder
| order_id | city | business | order_value | status |
|---|---|---|---|---|
| 1 | Mumbai | food | 450 | delivered |
| 2 | Mumbai | instamart | 820 | delivered |
| 3 | Mumbai | food | 300 | delivered |
| 4 | Mumbai | genie | 99 | cancelled |
| 5 | Bengaluru | instamart | 610 | delivered |
| 6 | Bengaluru | genie | 120 | delivered |
| 7 | Bengaluru | food | 510 | cancelled |
| 8 | Pune | food | 380 | cancelled |
| 9 | Bengaluru | instamart | 240 | delivered |
Output
| city | business | orders | gmv |
|---|---|---|---|
| Bengaluru | genie | 1 | 120 |
| Bengaluru | instamart | 2 | 850 |
| Bengaluru | All businesses | 3 | 970 |
| Mumbai | food | 2 | 750 |
| Mumbai | instamart | 1 | 820 |
| Mumbai | All businesses | 3 | 1570 |
| All cities | All businesses | 6 | 2540 |
How to solve Swiggy: GMV Report With City Subtotals and a Grand Total
A report with subtotals is several aggregations of the same rows, stacked. The detail rows are GROUP BY city, business; the city subtotals are GROUP BY city with the business column filled by the label 'All businesses'; the grand total is an aggregate with no GROUP BY at all, both columns labelled. UNION ALL stacks them — UNION would also work but pays for a pointless duplicate check. Each branch repeats the status = 'delivered' filter, so every level counts the same orders and the subtotals add up to the detail rows.
The order is the subtle part. Sorting by city alone would put 'All cities' before Bengaluru, and within a city 'All businesses' before food. So each branch carries two hidden level columns — city_level is 1 only for the grand total, row_level is 1 for any total row — and the outer query sorts by city_level, city, row_level, business, which places every city's lines, then its subtotal, then the grand total at the very end. The union sits in a derived table so the outer SELECT can drop the sort columns.
MySQL's GROUP BY … WITH ROLLUP produces such rows natively (with NULLs that GROUPING() identifies). The alternative builds the same rollup in one pass: it cross-joins the orders with three level numbers and groups by the level and by expressions that blank out the columns the level rolls up — each order is counted once per level.
Reference solution (MySQL)
SELECT city, business, orders, gmv FROM (
SELECT city, business, COUNT(*) AS orders, SUM(order_value) AS gmv, 0 AS city_level, 0 AS row_level
FROM SwiggyOrder WHERE status = 'delivered'
GROUP BY city, business
UNION ALL
SELECT city, 'All businesses', COUNT(*), SUM(order_value), 0, 1
FROM SwiggyOrder WHERE status = 'delivered'
GROUP BY city
UNION ALL
SELECT 'All cities', 'All businesses', COUNT(*), SUM(order_value), 1, 1
FROM SwiggyOrder WHERE status = 'delivered'
) report
ORDER BY city_level, city, row_level, businessAnother way
SELECT city, business, orders, gmv FROM (
SELECT CASE WHEN lv.n = 3 THEN 'All cities' ELSE o.city END AS city,
CASE WHEN lv.n >= 2 THEN 'All businesses' ELSE o.business END AS business,
COUNT(*) AS orders, SUM(o.order_value) AS gmv,
MAX(CASE WHEN lv.n = 3 THEN 1 ELSE 0 END) AS city_level,
MAX(CASE WHEN lv.n >= 2 THEN 1 ELSE 0 END) AS row_level
FROM SwiggyOrder o
CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) lv
WHERE o.status = 'delivered'
GROUP BY lv.n, CASE WHEN lv.n = 3 THEN 'All cities' ELSE o.city END,
CASE WHEN lv.n >= 2 THEN 'All businesses' ELSE o.business END
) report
ORDER BY city_level, city, row_level, business← Swiggy: Restaurant Offline Minutes Within Opening Hours · Swiggy: Units Sold per Item From Instamart Combo Codes →