# Completion Rate

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

Canonical URL: <https://datadriven.io/problems/completion_rate>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Operations wants to compare how cleanly each region closes out orders. For each region, compute the percentage of orders whose status is 'Completed', skipping rows where region is NULL. Return the region and that completion percentage.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests conditional aggregation for computing a percentage within groups, combined with NULL filtering. The single-pass approach using CASE WHEN is the expected pattern.

---

### Break down the requirements

#### Step 1: Filter out NULL regions

`WHERE region IS NOT NULL` as specified.

#### Step 2: Compute completion percentage per region

`100.0 * SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) / COUNT(*)` gives the completion rate.

---

### The solution

**Conditional percentage per region**

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

> **Cost Analysis**
>
> Single scan of 50M rows with NULL filter and conditional sum. GROUP BY reduces to the number of distinct regions. Efficient single-pass approach.

> **Interviewers Watch For**
>
> Whether the candidate handles the NULL region filter before aggregation and uses conditional aggregation rather than a subquery or separate filtered count.

> **Common Pitfall**
>
> Using `COUNT(CASE WHEN status = 'Completed' THEN 1 END)` works but is less intuitive than `SUM(CASE ... THEN 1 ELSE 0 END)`. Either is correct, but be consistent.

---

## Common follow-up questions

- What if you needed to compare completion rates before and after a specific date? _(Tests adding a date-based conditional split to the aggregation.)_
- How would you handle case sensitivity in the status column? _(Tests LOWER(status) = 'completed' for safe matching.)_
- What if there were zero orders in a region? _(Division by zero; tests NULLIF or CASE guard in the denominator.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/completion_rate)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.