The Opening Run
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
- L4
- Asked in
- Meta
Problem
The data platform team wants to see how many rows each batch job processed on its very first run, as a baseline for measuring throughput improvements over time. Reading down the roster alphabetically by job name, show each job alongside the rows done from that first execution.
Before the tuning, before the records. What the first pass carried home.
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_name | rows_done |
|---|---|
| backup_db | 9506 |
| cache_warm | 4365 |
| daily_report | NULL |
| data_cleanup | 194 |
| email_blast | 1261 |
Worked solution and explanation
Why this problem exists in real interviews
Interviewers use the batch_jobs table here to probe row numbering within partitions combined with nested subqueries. The columns job_name, status, rows_done force candidates to reason about the correct grain before writing any aggregation.
Break down the requirements
Step 1: Partition by job_name
Number each job's runs chronologically with ROW_NUMBER() partitioned by job_name and ordered by started ascending, so the earliest run gets rn = 1.
Step 2: Filter to rank 1
Keep only the first run per job by filtering rn = 1.
The solution
SELECT job_name, rows_done
FROM (
SELECT job_name, rows_done,
ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY started ASC) AS rn
FROM batch_jobs
) ranked
WHERE rn = 1
ORDER BY job_nameCommon follow-up questions
- The `ended` column in `batch_jobs` has roughly 2% NULLs. How does your query handle those rows, and would the result change if NULLs were replaced with zeros? (Tests whether the candidate understands how NULLs propagate through aggregation functions and whether their WHERE/JOIN conditions implicitly filter them out.)
- Your window function uses a default frame. What is the implicit frame, and would switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change anything? (Tests knowledge of default window frames (RANGE vs ROWS) and when the distinction matters.)
- The `job_name` column in `batch_jobs` has a zipf distribution, meaning a few values dominate. How does that skew affect your query plan and parallelism? (Tests understanding of data skew: the optimizer may choose a bad plan when histogram statistics are stale.)
- If the business definition of `status` changed mid-quarter (e.g., a status value was renamed), how would you handle historical consistency? (Tests awareness of slowly changing dimensions and backward-compatible query design.)