Last Five Batch Jobs
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- Visa
Problem
Recency here is judged by job ID, not by start time: the highest IDs are the newest jobs. Pull the five newest records from batch_jobs in full for the data platform team's tail check, newest to oldest.
The last five. A quick tail check.
Tables
batch_jobs
Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER
| 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
| job_id | job_name | status | rows_done | started | ended | priority | retries |
|---|---|---|---|---|---|---|---|
| 10005800 | daily_report | failed | 9700 | 2026-01-17 04:00:00 | NULL | 0 | 0 |
| 10005799 | sync_elastic | running | 9603 | 2025-03-16 03:00:00 | 2025-03-16 05:09:00 | 7 | 5 |
| 10005798 | backup_db | completed | 9506 | 2024-02-03 02:00:00 | 2024-02-03 04:58:00 | 4 | 0 |
| 10005797 | metric_agg | FAILED | 9409 | 2023-01-14 01:00:00 | 2023-01-14 03:47:00 | 1 | 0 |
| 10005796 | log_archive | Completed | NULL | 2022-12-13 00:00:00 | NULL | 8 | 0 |
Worked solution and explanation
This is a top-N on a stated key, dressed up as an ops tail check. Everyone writes the cap at five. What separates candidates is reading 'most recent by job ID' literally and sorting on job_id, not on started, which looks like the natural recency column. On this table the two disagree: the five highest IDs started anywhere from 2022 to 2026. A sort on started descending returns five different jobs from September 2026, and the answer misses on every row.
Walking it
Step 1: Take the key the business named
'Most recent by job ID' is the ordering contract. job_id is unique, so sorting it descending yields one unambiguous newest-to-oldest sequence with no tie to settle.
Step 2: Sort before you cap
ORDER BY job_id DESC decides which five rows survive; LIMIT 5 only counts them off. Without the sort the engine hands back whatever it scans first, here the oldest IDs starting at 1047, and nothing promises even that.
Step 3: Project the record column by column
Name all eight columns instead of SELECT *. The result keeps the preview's column order, and it does not silently change shape when someone adds a column to batch_jobs.
SELECT job_id, job_name, status, rows_done, started, ended, priority, retries
FROM batch_jobs
ORDER BY job_id DESC
LIMIT 5- Sort by start time
ORDER BY started DESC LIMIT 5returns the five latest starts, all in September 2026. It is a reasonable query for a question nobody asked.- Sort by job ID
ORDER BY job_id DESC LIMIT 5returns IDs 10005800 down to 10005796, whose starts run from 2026 back to 2022. That is what the task defined as newest.
Common follow-up questions
- Now the team wants the five most recently started jobs, and two jobs share a `started` value at the cut. What changes? (Tests whether the candidate adds a stated tie rule before the cap.)
- How would you return the latest five runs for each `job_name`? (Tests moving from a global top-N to one scoped per group.)
- Job IDs now come from several workers and are no longer issued in order. How do you define newest? (Tests whether the candidate questions the recency key instead of trusting it.)