Where the Exports Land
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
- Vesta Innovations
Problem
Export jobs have been flaky lately, so for every batch job whose name contains 'export', count how many landed in each completion status.
Every export run ends somewhere. Tally the endings, loudest first.
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
| status | occurrences |
|---|---|
| Completed | 2 |
| FAILED | 2 |
| canceled | 4 |
| completed | 4 |
| failed | 2 |
Worked solution and explanation
What this problem is really testing
Strip the business framing and this is a filtered frequency distribution: count how often each status appears, but only among the rows whose job_name carries the 'export' substring. Anyone can write COUNT(*) with a GROUP BY. What separates candidates is getting the filter in the right place and the right shape: a substring LIKE '%export%' in the WHERE clause, evaluated before the grouping. Push that predicate into a HAVING, or match on equality instead of a substring, and the counts quietly include the wrong rows or drop the export jobs entirely.
Build it in three moves
Step 1: Filter before you group
Put the substring match in WHERE: job_name LIKE '%export%'. The percent signs on both sides matter, because names like user_export and nightly_export only match with a leading wildcard. Filtering here shrinks the set before any counting happens, so the aggregate never sees a non-export row.
Step 2: Count per status
GROUP BY status with COUNT(*) gives one row per distinct status and its occurrence count. COUNT(*) is right here because you want every matching row, not just the non-null ones, and status never goes null in this data.
Step 3: Order so the most common leads
ORDER BY occurrences DESC puts the most common status on top. Add status as a secondary sort so that two statuses with equal counts come back in a stable order instead of whatever the engine happens to emit.
The solution
SELECT status, COUNT(*) AS occurrences
FROM batch_jobs
WHERE job_name LIKE '%export%'
GROUP BY statusCommon follow-up questions
- The status column has mixed casing in production. How do you keep the counts from fragmenting across 'failed' and 'FAILED'? (Tests robustness to dirty categorical data.)
- If `job_name` were indexed, why would LIKE '%export%' still not use that index, and what would you do to speed up the substring search? (Tests understanding of leading-wildcard search and indexing.)
- How would the query change if you needed the most common status per `job_name` prefix rather than across all export jobs at once? (Tests extending the aggregation grain.)