7. Cancel rate by country
Asked in screens shaped like: Uber, DoorDash, Lyft
Ops wants a cancel rate per country on one row: total orders, cancelled orders, and the ratio. The trap is integer division — cancelled / orders returns 0 in most engines unless you force a float.
Requirements
- Columns: country, orders, cancelled, cancel_rate
- orders is every row for that country, cancelled and completed alike
- cancel_rate is cancelled / orders rounded to 4 decimals
- Order by country
Expected output
country, orders, cancelled, cancel_rate
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
A conditional SUM counts a subset without a second query.
Hint 2
Multiply by 1.0 before dividing so the result is real, not integer.
Hint 3
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)
SELECT country,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
ROUND(1.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 4) AS cancel_rate
FROM orders
GROUP BY country
ORDER BY country;Why it works
Conditional aggregation gets numerator and denominator from a single pass. The 1.0 multiplier is what stops integer division from flooring the rate to zero.
Run your query to see the result set.
Submit to run the checks.
Why it works
Conditional aggregation gets numerator and denominator from a single pass. The 1.0 multiplier is what stops integer division from flooring the rate to zero.
Pro problem
Medium problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems