Completion Rate

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

An order with no region recorded counts toward no region at all, while every other row of orders counts toward its region's total whether or not it finished. Give each region's share of orders whose status is 'Completed', as a percentage, with the regions listed alphabetically.

Not every region closes orders cleanly. The percentages tell the story.

Tables

orders

Columns: order_id INTEGER, status TEXT, region TEXT, profit REAL

Expected output

Worked solution and explanation

What this really is

This is a ratio of two counts over the same group, dressed up as an ops scorecard. The numerator is the completed orders in a region and the denominator is every order in that region, and both have to survive into the same `GROUP BY region` row. Anyone can count completed orders. The trap is reaching for WHERE status = 'Completed': that filter deletes the denominator before grouping ever sees it, so every region comes back at 100.00. The quieter trap is dividing two integer counts, which truncates every region under 100% to 0.

Step 1: Drop the orders with no region

18 of the 200 rows in orders have a NULL region. Left in, they form a sixth group keyed on NULL that the scorecard has no use for. WHERE region IS NOT NULL is the only filter that belongs in the WHERE, because it removes those rows from both counts at once.

Step 2: Keep every status in the group

The denominator is COUNT(*) over the whole region: Pending, Shipped, Cancelled and the 14 rows with a NULL status all count. Nothing about status may touch the WHERE.

Step 3: Count completions inside the aggregate

SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) scores each row 1 or 0 and adds them up, so the numerator is built from exactly the rows COUNT(*) sees. A NULL status falls to the ELSE and counts as not completed, which is what ops means.

Step 4: Force decimal math, then round

100.0 * goes first so the division runs in decimals. APAC's 2 / 18 is 0 in integer arithmetic and 11.11 after the multiply. ROUND(..., 2) gives the two-decimal shape the preview shows.

SELECT region,
       ROUND(100.0 * SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) / COUNT(*), 2) AS completion_pct
FROM orders
WHERE region IS NOT NULL
GROUP BY region
ORDER BY region

Filtered count

With WHERE status = 'Completed' and then COUNT(*), the uncompleted rows are gone before grouping, so the only total left is the completed one. Dividing it by itself reports 100.00 for every region.

Conditional count

Every row reaches its group and the CASE decides which rows add to the numerator. APAC keeps all 18 orders in its denominator and reports 11.11.

Common follow-up questions

• How would you compare each region's completion rate before and after a given date? (Adds a second conditional count per period inside the same pass.)
• What if `status` also arrived as 'completed' and 'COMPLETED'? (Normalizing with `LOWER(status)` inside the condition, never in the `WHERE`.)
• What if a region could have zero orders? (Guarding the denominator with `NULLIF(COUNT(*), 0)`, and whether such a region can even come from this table.)