# Median Failure Rate by Table

> Half the tables fail more than this.

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

Domain: SQL · Difficulty: hard · Seniority: L5 · Asked in: Walmart

## Problem

A data-quality team is triaging which of its monitored tables misfire most often across the checks that run against them. Compute the median failure percentage for each table, rounded to the nearest whole number, and list the tables from the worst median down to the cleanest, settling ties alphabetically by table name.

## Tables

### dq_checks

- check_id INTEGER
- tbl_name TEXT
- col_name TEXT
- rule TEXT
- passed INTEGER
- fail_pct REAL
- run_at TEXT
- severity TEXT

Sample rows (10 of 200):

| check_id | tbl_name | col_name | rule | passed | fail_pct | run_at | severity |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 131 | users | email | unique | 1 | NULL | 2026-02-02 01:00:00 | high |
| 162 | products | amount | range | 1 | NULL | 2026-03-03 02:00:00 | medium |
| 193 | transactions | status | format | 1 | NULL | 2026-04-04 03:00:00 | low |
| 224 | events | created_at | referential | 1 | NULL | 2026-05-05 04:00:00 | Critical |
| 255 | sessions | price | freshness | 0 | 15 | 2026-06-06 05:00:00 | HIGH |
| 286 | ad_impressions | region | Not_Null | 1 | NULL | 2026-07-07 06:00:00 | critical |
| 317 | orders | user_id | UNIQUE | 1 | 0 | 2026-08-08 07:00:00 | high |
| 348 | users | email | not_null | 1 | NULL | 2026-09-09 08:00:00 | medium |
| 379 | products | amount | unique | 1 | NULL | 2026-01-10 09:00:00 | low |
| 410 | transactions | status | range | 0 | 30 | 2026-02-11 10:00:00 | Critical |

## Expected output

First 5 of 7 rows:

| tbl_name | median_fail_pct |
| --- | --- |
| ad_impressions | 65 |
| events | 80 |
| orders | 0 |
| products | 90 |
| sessions | 20 |

## Worked solution and explanation

### Why this problem exists in real interviews

Querying `dq_checks` for `tbl_name` data using percentile calculation and grouping tests whether you can translate a business requirement into the right column references and filter sequence. It shows up in senior-level rounds to verify practical fluency.

> **Trick to Solving**
>
> Median in SQL has no built-in aggregate in most engines. The trick is using `PERCENTILE_CONT(0.5)` or a row-counting approach with `ROW_NUMBER`.
> 
> 1. Recognize that `AVG` will not give you the median
> 2. Use `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...)` if available
> 3. Alternatively, rank rows and pick the middle value(s)

---

### Break down the requirements

#### Step 1: Compute the median with `PERCENTILE_CONT`

Per table, rank the non-null `fail_pct` values with `ROW_NUMBER`() OVER (PARTITION BY `tbl_name` ORDER BY `fail_pct`) and capture the group size with COUNT(*) OVER (PARTITION BY `tbl_name`).

#### Step 2: Aggregate with the aggregate

Select the middle row(s): for odd counts both expressions ((cnt+1)/2 and (cnt+2)/2) collapse to the single center rank; for even counts they pick the two central ranks (integer division).

#### Step 3: Order the final output

AVG over the selected middle row(s) yields the true statistical median, then ROUND() gives the nearest whole number per the prose.

---

### The solution

**Rounded `PERCENTILE_CONT` per monitoring target**

```sql
SELECT tbl_name,
       ROUND(AVG(fail_pct)) AS median_fail_pct
FROM (
  SELECT tbl_name,
         fail_pct,
         ROW_NUMBER() OVER (PARTITION BY tbl_name ORDER BY fail_pct) AS rn,
         COUNT(*)    OVER (PARTITION BY tbl_name)                    AS cnt
  FROM dq_checks
  WHERE fail_pct IS NOT NULL
)
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2)
GROUP BY tbl_name
```

> **Cost Analysis**
>
> The query scans 700K rows from `dq_checks`. The window function requires a sort, which is O(n log n). Pre-aggregating reduces the sort input.

> **Interviewers Watch For**
>
> Strong candidates explain their choice of window function (`ROW_NUMBER` vs `RANK` vs `DENSE_RANK`) and why it matches the tie semantics. Familiarity with statistical functions beyond COUNT/SUM/AVG distinguishes senior candidates.

> **Common Pitfall**
>
> Using `ROWS` vs `RANGE` in the window frame produces different results when ties exist. Default to `ROWS` unless you specifically need tie grouping.

---

## Common follow-up questions

- What happens to your result if `dq_checks`.severity contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on severity.)_
- If a group in `dq_checks` has only one row, what does the percentile function return? _(Tests understanding of percentile behavior with minimal data points.)_
- With millions of distinct values in `dq_checks`.check_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like `check_id`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/median_failure_rate_by_table)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.