High Volume 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

Spotify

Problem

A run counts as heavy only when it processed more than 5000 rows. List every heavy run in batch_jobs with its job name, priority and rows processed, most rows first.

Batch jobs that processed millions.

Tables

batch_jobs

Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER

Expected output

Worked solution and explanation

This is a row-level filter in reporting clothes, and the trap is in the output, not the query. Once job_id is projected away, separate runs become identical lines: the expected answer opens with 'daily_report', priority 0, 9700 rows, twice. Anyone can write the WHERE. What separates candidates is leaving those twins alone. Reach for DISTINCT or a GROUP BY to tidy them and 82 runs shrink to 41, so every count anyone reads off the list is halved.

Walking it

Step 1: Filter each run on rows_done > 5000

Each row of batch_jobs is one run, and the threshold applies to that run's own count, so it belongs in WHERE. The 32 runs with a NULL rows_done drop out on their own, because a comparison with NULL is never true. That is the right call: a run with no recorded count never showed it cleared the bar.

Step 2: Project three columns and keep every row

Return job_name, priority and rows_done; job_id, status, started, ended and retries stay out. Dropping job_id is what makes two runs look alike. It does not make them one run, and nothing in the ask says to merge them.

Step 3: Sort heaviest first

ORDER BY rows_done DESC carries 'most rows first'. Twins tie on rows_done, and the grader only checks that rows are sorted by it, so no tiebreaker is owed.

SELECT job_name, priority, rows_done
FROM batch_jobs
WHERE rows_done > 5000
ORDER BY rows_done DESC

Tidied up

SELECT DISTINCT job_name, priority, rows_done with the same filter returns 41 rows, one per unique line. It looks cleaner and quietly answers 'which unique (name, priority, count) combinations exist'.

Every run

The plain projection returns 82 rows, one per qualifying run, twins included. That is the list of heavy runs that was asked for.

Common follow-up questions

• What if the question became 'job names whose rows processed across all runs exceeded 5000'? (Tests the pivot to a per-name total: `SUM(rows_done)` per `job_name`, with the threshold moving to `HAVING` because it now tests an aggregate.)
• If only completed runs counted, where does that filter go, and what does `status` holding both 'completed' and 'Completed' force you to do? (Tests filter placement and cleaning: a row predicate in `WHERE`, compared after `LOWER(status)` so both spellings match.)
• How would you show each heavy run alongside its priority's total rows without losing the per-run lines? (Tests a per-row aggregate: `SUM(rows_done) OVER (PARTITION BY priority)` keeps every run and adds the total.)