Swiggy: GMV Report With City Subtotals and a Grand Total

Hard
968.

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

ColumnTypeValues
order_idint
cityvarchar
businessenumfood, instamart, genie
order_valueint
statusenumdelivered, 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.

Example

The rows must come back in the order shown.

SwiggyOrder

order_idcitybusinessorder_valuestatus
1Mumbaifood450delivered
2Mumbaiinstamart820delivered
3Mumbaifood300delivered
4Mumbaigenie99cancelled
5Bengaluruinstamart610delivered
6Bengalurugenie120delivered
7Bengalurufood510cancelled
8Punefood380cancelled
9Bengaluruinstamart240delivered

Output

citybusinessordersgmv
Bengalurugenie1120
Bengaluruinstamart2850
BengaluruAll businesses3970
Mumbaifood2750
Mumbaiinstamart1820
MumbaiAll businesses31570
All citiesAll businesses62540
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: 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

ColumnType
order_idint
cityvarchar
businessenum(food, instamart, genie)
order_valueint
statusenum(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_idcitybusinessorder_valuestatus
1Mumbaifood450delivered
2Mumbaiinstamart820delivered
3Mumbaifood300delivered
4Mumbaigenie99cancelled
5Bengaluruinstamart610delivered
6Bengalurugenie120delivered
7Bengalurufood510cancelled
8Punefood380cancelled
9Bengaluruinstamart240delivered

Output

citybusinessordersgmv
Bengalurugenie1120
Bengaluruinstamart2850
BengaluruAll businesses3970
Mumbaifood2750
Mumbaiinstamart1820
MumbaiAll businesses31570
All citiesAll businesses62540

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, business

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