# Batch Job Performance Tiers

> Every batch job gets a grade.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The data platform team is grading batch job throughput for their quarterly review. Each job's total rows processed determines its tier: 30,000 or more is 'Outstanding', 20,000 to 29,999 is 'Satisfactory', 10,000 to 19,999 is 'Unsatisfactory', and anything below 10,000 is 'Poor'. Show each job's name, total rows, and tier, with the highest-throughput jobs first.

## Worked solution and explanation

### Why this problem exists in real interviews

Data platform teams give you this to see if you'll write four `CASE WHEN` branches without first asking who owns those tier cutoffs. The probe: do you treat 30000/20000/10000 as gospel from the prompt, or do you ask whether thresholds live in a config table the platform PM updates each quarter? They also watch whether you grain on `job_name` versus `job_id`.

---

### Break down the requirements

#### Step 1: Pick the grain

`batch_jobs` has both `job_id` and `job_name`. The prompt says 'each job's name', so collapse to `job_name` with `GROUP BY job_name`. Ask out loud whether multiple `job_id` rows per name are reruns to sum, or you want max.

#### Step 2: Aggregate rows

`SUM(rows_done) AS total_rows`. Sum, not avg, because the tier is about total throughput per quarterly review, not per-run average.

#### Step 3: Bucket with CASE WHEN

Order branches high-to-low so the first true wins: `>= 30000` 'Outstanding', then `>= 20000`, then `>= 10000`, else 'Poor'. Wrapping `SUM(rows_done)` in `CASE` is fine; the planner computes it once.

#### Step 4: Sort the output

`ORDER BY total_rows DESC` so the platform PM reads top throughput first. Don't sort by tier label; alphabetical 'Outstanding' before 'Poor' is meaningless to a reader.

---

### The solution

**TIER BY TOTAL ROWS**

```sql
SELECT
  job_name,
  SUM(rows_done) AS total_rows,
  CASE
    WHEN SUM(rows_done) >= 30000 THEN 'Outstanding'
    WHEN SUM(rows_done) >= 20000 THEN 'Satisfactory'
    WHEN SUM(rows_done) >= 10000 THEN 'Unsatisfactory'
    ELSE 'Poor'
  END AS performance_tier
FROM batch_jobs
GROUP BY job_name
ORDER BY total_rows DESC;
```

> **Cost Analysis**
>
> 500K rows, single hash-aggregate on `job_name`. No joins, no window. The `CASE` is per-group not per-row, so cost is dominated by the aggregate. If `job_name` has high cardinality, the planner picks hash agg over sort agg; index on `job_name` only helps if it's clustered.

> **Interviewers Watch For**
>
> Ask before writing: 'Are 30000 and above inclusive at the boundary, or strictly above?' The prompt says 30000 'or more', which is `>=`, but voicing closed-versus-open intervals shows you've been burned by off-by-one tier bugs. Also ask if failed jobs (`status != 'succeeded'`) should still count their `rows_done`.

> **Common Pitfall**
>
> Writing the `CASE` branches low-to-high: `WHEN SUM(rows_done) >= 10000 THEN 'Unsatisfactory'` first. Every job above 10000 hits that branch and never sees 'Outstanding' or 'Satisfactory'. `CASE` short-circuits on first match, so order matters.

> **In Production**
>
> You'd join a `job_tier_thresholds` config table keyed by quarter and `BETWEEN` the bounds, not hardcode 30000/20000/10000. The PM updates a row, no PR. Mention this aloud even if you write the inline `CASE` for the interview.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you rewrite this so the tier thresholds come from a config table? _(Tests whether you reach for `CROSS JOIN` on a tiny thresholds CTE plus `BETWEEN`, and whether you handle the quarter-over-quarter SCD.)_
- Show the count and percentage of jobs in each tier. _(Probes a second aggregation layer: wrap this query in a CTE and `GROUP BY performance_tier` with `COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()`.)_
- How does the answer change if `rows_done` can be NULL for jobs that crashed mid-run? _(Tests whether you remember `SUM` ignores NULL but `COALESCE(rows_done, 0)` is safer if you ever divide, plus whether crashed runs should be excluded entirely.)_
- The PM wants a 5th tier 'Elite' at 100000 and above. How do you add it without rewriting? _(Reinforces the config-table answer and shows you anticipate threshold churn.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/batch_job_performance_tiers)
- [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.