Interview drills: reports, pivots, percentages

Reporting questions ask for the table a manager would read: one row per city, a column per payment method, each category’s share of revenue. The SQL is mostly GROUP BY and CASE; what interviewers check is the grain and the denominators. Write the grain first, “one row per city”, because a join can quietly change it: an order paid in two parts becomes two rows. Name each denominator, a share of what, and say what happens when it is zero. Keep every group the report needs, also one without rows, by starting from the table that lists the groups. Round only at the end, because rounding early makes totals drift, and formatting turns numbers into text.

Two problems for one month of orders

Problem A: the payment mix by city. An invented grocery delivery service has cities(id, name), orders(id, city_id, status, placed_on, fee_paise) and payments(id, order_id, method, amount_paise); an order paid in two parts has two payments. For one month, return one row per city, every city included: its number of delivered orders and the share of their paid amount that came by upi, card and cash, in per cent with one decimal. Full size: 300 cities, 4 million orders and 4.6 million payments a month.

Problem B: shares of revenue. From categories(id, name) and order_items(order_id, category_id, qty, unit_paise), return each category’s revenue and its share of the total with one decimal, every category included. The shares as shown must add up to 100.0.

The brute force, and why it fails at full size

The literal translation computes every cell of the report with a subquery of its own:

-- An invented grocery delivery service: its cities, orders and payments. Amounts are in paise; an order
-- can be paid in two parts, so it can have two payments.
CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO cities VALUES (1, 'Indore'), (2, 'Kochi'), (3, 'Nagpur'), (4, 'Surat');
CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  city_id   INTEGER NOT NULL REFERENCES cities (id),
  status    TEXT    NOT NULL,                 -- 'delivered' or 'cancelled'
  placed_on TEXT    NOT NULL,                 -- 'YYYY-MM-DD'
  fee_paise INTEGER NOT NULL                  -- the delivery fee, one per order
);
INSERT INTO orders VALUES
  (101, 1, 'delivered', '2026-09-03', 2500), (102, 1, 'delivered', '2026-09-07', 1500),
  (103, 1, 'delivered', '2026-09-12', 2500), (104, 1, 'cancelled', '2026-09-15', 2500),
  (105, 1, 'delivered', '2026-09-28', 1500), (201, 2, 'delivered', '2026-09-02', 2000),
  (202, 2, 'delivered', '2026-09-19', 2000), (203, 2, 'delivered', '2026-09-30', 0),
  (301, 3, 'delivered', '2026-08-30', 2000), (401, 4, 'delivered', '2026-09-05', 1500),
  (402, 4, 'delivered', '2026-09-21', 1500);
CREATE TABLE payments (
  id           INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders (id),
  method       TEXT    NOT NULL,              -- 'upi', 'card' or 'cash'
  amount_paise INTEGER NOT NULL
);
INSERT INTO payments (order_id, method, amount_paise) VALUES
  (101, 'upi', 45000), (102, 'card', 120000), (103, 'upi', 30000), (103, 'card', 50000), (105, 'cash', 25000),
  (201, 'upi', 60000), (202, 'upi', 15000), (203, 'card', 90000),
  (301, 'upi', 40000),
  (401, 'cash', 70000), (402, 'card', 35000);

-- The literal translation: one subquery per cell of the report, cities in rows, methods in columns.
SELECT c.name AS city,
       (SELECT count(*) FROM orders AS o
        WHERE o.city_id = c.id AND o.status = 'delivered' AND o.placed_on LIKE '2026-09-%') AS orders,
       (SELECT 100 * sum(p.amount_paise) FROM payments AS p JOIN orders AS o ON o.id = p.order_id
        WHERE o.city_id = c.id AND o.status = 'delivered' AND o.placed_on LIKE '2026-09-%' AND p.method = 'upi')
       / (SELECT sum(p.amount_paise) FROM payments AS p JOIN orders AS o ON o.id = p.order_id
          WHERE o.city_id = c.id AND o.status = 'delivered' AND o.placed_on LIKE '2026-09-%') AS upi_pct
FROM cities AS c
ORDER BY c.name;

┌────────┬────────┬─────────┐
│  city  │ orders │ upi_pct │
├────────┼────────┼─────────┤
│ Indore │ 4      │ 27      │
│ Kochi  │ 3      │ 45      │
│ Nagpur │ 0      │         │
│ Surat  │ 2      │         │
└────────┴────────┴─────────┘

The upi column alone needs two subqueries per city, each joining payments to orders, so the whole report runs seven for each of 300 cities, and without a suitable index each reads 4.6 million payments: about 10¹⁰ rows. The result is also wrong twice. 100 * sum(...) / sum(...) divides integers, so Indore shows 27 instead of 27.8, and Surat, which had no upi payment, shows NULL where its share is 0.

What to notice

• Write the grain first: one row per city.
• Count orders at the order grain and sum payments at the payment grain, then join the summaries.
• Conditional aggregation pivots the methods into columns.
• Multiply by 100.0 before dividing, and wrap the denominator in nullif(..., 0).
• Start from cities with a LEFT JOIN, so that a city without orders keeps its row.
• Round in the last step: shares rounded one by one need not add up to 100.

The report in four steps, grain first Pro: opens with a Pro, Premium or Ultimate pass