Level 2 · Cancellation rate by channel

To compute a rate you need two counts from the same group: the part and the whole. A WHERE filter gives you one or the other, never both. Conditional aggregation puts the condition inside the aggregate instead:

SELECT
  channel,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY channel;

Each row contributes 1 to cancelled_orders if it is cancelled and 0 otherwise, while COUNT(*) still sees every row. One pass over the table, as many conditional counts as you want.

Three equivalent spellings

SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)   -- works everywhere
COUNT(CASE WHEN status = 'cancelled' THEN 1 END)        -- no ELSE: NULLs are not counted
COUNT(*) FILTER (WHERE status = 'cancelled')            -- Postgres, DuckDB, SQLite

The FILTER clause is SQL-standard and very readable, but MySQL, BigQuery and Snowflake do not support it. BigQuery has COUNTIF(status = 'cancelled'). The SUM(CASE ...) form runs everywhere, which is why it is the one interviewers expect.

Sums, not just counts

The same idea works for amounts:

SUM(CASE WHEN channel = 'app' THEN shipping_fee ELSE 0 END) AS app_shipping

This is also how you pivot in portable SQL: one column per category value, computed with a CASE inside an aggregate. DuckDB has a PIVOT statement, and BigQuery and Snowflake have their own PIVOT syntax, but conditional aggregation works in all of them.

Rates

ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancel_rate_pct

100.0 keeps the division decimal (in Postgres, an integer sum divided by an integer count would truncate to 0). A neat alternative for a rate is the average of a 0/1 flag:

ROUND(100 * AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0 END), 2)

Why it beats several queries

You could run one query per status and glue the numbers together in a spreadsheet. Conditional aggregation keeps the numerator and denominator in the same group, from the same rows, at the same moment. When someone asks “do these add up?”, they do: total minus cancelled minus refunded minus pending equals completed.

Reading the result

Run the solution and compare the three channels. If the cancellation rates are within a fraction of a percentage point of each other, the “marketplace cancels more” story is not supported by the data, and saying so politely, with the numbers, is part of the job.

Common mistakes

• COUNT(CASE WHEN ... THEN 1 ELSE 0 END) counts every row, because 0 is not NULL. With COUNT, drop the ELSE. With SUM, keep it.
• Filtering with WHERE and then dividing by COUNT(*), which makes every rate 100%.