# Third Largest Batch Job

> Bronze medal in the batch job rankings.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Meta

## Problem

During the weekly platform review, the data engineering lead asked for the third-largest batch job by total rows processed. If multiple jobs tie for that position, list them alphabetically by name.

## Tables

### batch_jobs

- job_id INTEGER
- job_name TEXT
- status TEXT
- rows_done INTEGER
- started TEXT
- ended TEXT
- priority INTEGER
- retries INTEGER

Sample rows (10 of 200):

| job_id | job_name | status | rows_done | started | ended | priority | retries |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1047 | user_export | running | 97 | 2026-02-02 01:00:00 | 2026-02-02 03:11:00 | 3 | 0 |
| 1094 | data_cleanup | failed | 194 | 2026-03-03 02:00:00 | 2026-03-03 04:22:00 | 6 | 0 |
| 1141 | email_blast | queued | 291 | 2026-04-04 03:00:00 | 2026-04-04 05:33:00 | 9 | 0 |
| 1188 | index_rebuild | canceled | 388 | 2026-05-05 04:00:00 | NULL | 2 | 0 |
| 1235 | cache_warm | Completed | 485 | 2026-06-06 05:00:00 | 2026-06-06 07:55:00 | 5 | 0 |
| 1282 | log_archive | FAILED | NULL | 2026-07-07 06:00:00 | 2026-07-07 08:06:00 | 8 | 0 |
| 1329 | metric_agg | completed | 679 | 2026-08-08 07:00:00 | 2026-08-08 09:17:00 | 1 | 0 |
| 1376 | backup_db | running | 776 | 2026-09-09 08:00:00 | NULL | 4 | 0 |
| 1423 | sync_elastic | failed | 873 | 2026-01-10 09:00:00 | 2026-01-10 11:39:00 | 7 | 5 |
| 1470 | daily_report | queued | 970 | 2026-02-11 10:00:00 | 2026-02-11 12:50:00 | 0 | 0 |

## Expected output

First 1 of 1 rows:

| job_name | total_rows |
| --- | --- |
| cache_warm | 97000 |

## Worked solution and explanation

### Why this problem exists in real interviews

This is a variant of the Nth-value problem applied to batch job sizes. It tests the same ranking fundamentals as other top-N problems.

---

### Break down the requirements

#### Step 1: Identify the ranking metric

Aggregate total rows processed per job: SUM(rows_done) GROUP BY job_name (a job_name can appear across many job_id runs).

#### Step 2: Apply DENSE_RANK

Rank the per-job totals with DENSE_RANK() OVER (ORDER BY SUM(rows_done) DESC) so jobs tied on the same total share a rank and the third distinct total is rank 3 (not pushed past by ties, which ROW_NUMBER would do).

#### Step 3: Filter to rank 3

Filter the ranked set to rnk = 3 to keep exactly the third-largest total, including every job tied at that total.

---

### The solution

**Nth-value extraction with DENSE_RANK**

```sql
SELECT job_name, total_rows
FROM (
    SELECT
        job_name,
        SUM(rows_done) AS total_rows,
        DENSE_RANK() OVER (ORDER BY SUM(rows_done) DESC) AS rnk
    FROM batch_jobs
    GROUP BY job_name
) ranked
WHERE rnk = 3
ORDER BY job_name;
```

> **Cost Analysis**
>
> The window function sorts all rows by `output_bytes`. An index on `output_bytes DESC` avoids the sort. Since we only need rank 3, a top-N optimization can stop early.

> **Interviewers Watch For**
>
> Interviewers verify you know the difference between DENSE_RANK and LIMIT/OFFSET for Nth-value queries.

> **Common Pitfall**
>
> Using `LIMIT 1 OFFSET 2` does not handle ties. If two jobs share the largest size, offset 2 gives what is tied for first, not the third distinct value.

---

## Common follow-up questions

- What if you needed the third largest per job category? _(Tests PARTITION BY in the window function.)_
- How would you find the top 3 largest jobs instead of just the third? _(Tests changing the filter to rnk <= 3.)_
- What if output_bytes can be NULL? _(Tests NULL handling in ORDER BY.)_

## Related

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