Top Batch Job Under Priority 1
A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- medium
- Seniority
- L3
- Asked in
- Salesforce
Problem
The data platform team is profiling batch_jobs for throughput leaders: return every job that processed at least 95% as many rows as the single busiest job managed.
Priority one. Top performer.
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 | priority | rows_done |
|---|---|---|---|
| 5465 | cache_warm | 5 | 9215 |
| 5559 | metric_agg | 1 | 9409 |
| 5606 | backup_db | 4 | 9506 |
| 5653 | sync_elastic | 7 | 9603 |
| 5700 | daily_report | 0 | 9700 |
Worked solution and explanation
What this is really testing
Strip off the throughput story and this is a compare-every-row-against-a-table-wide-peak problem. The costume is 'throughput leaders'; the skill is re-deriving the busiest job's row count on the fly and keeping the whole band that runs close to it, not just the single winner. Reach for ORDER BY rows_done DESC LIMIT 1 and you hand back one job; paste a fixed number for the peak and your cutoff rots the moment the data moves.
Working through it
Step 1: Re-derive the peak, do not hardcode it
Compute the busiest job's row count with an uncorrelated scalar subquery: (SELECT MAX(b2.rows_done) FROM batch_jobs b2). It returns one number for the entire table, and because it is MAX, a NULL rows_done can never be the peak. Scale it by 0.95 to get the 5% band, so the cutoff tracks the data instead of a literal like 9215.
Step 2: Keep the whole pack with an inequality
Filter the outer rows with rows_done >= 0.95 * peak rather than sorting and taking the first. The inequality admits every job inside the band, so co-leaders and near-peers all survive. It also drops the null jobs for free: NULL >= anything is never true, so a job with no recorded row count silently falls out.
The solution
SELECT b.job_id, b.job_name, b.priority, b.rows_done
FROM batch_jobs b
WHERE b.rows_done >= 0.95 * (
SELECT MAX(b2.rows_done)
FROM batch_jobs b2
)One uncorrelated scalar subquery sets the bar; the inequality keeps the pack.
Common follow-up questions
- How would you rewrite this with a window function instead of a scalar subquery? (Tests MAX() OVER () to broadcast the peak onto every row, then filter on it.)
- What if the band had to be computed per priority tier instead of across the whole table? (Tests correlating the peak to each row's own group: a per-tier maximum.)
- What changes if 'leaders' meant the top 5% by percentile rather than within 5% of the peak? (Tests `PERCENT_RANK` or NTILE versus a max-relative threshold.)