# Where the Exports Land

> Every export run ends somewhere. Tally the endings, loudest first.

Canonical URL: <https://datadriven.io/problems/where_the_exports_land>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Export jobs have been flaky lately, so for every batch job whose name contains 'export', count how many landed in each completion status, most common first.

## 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

**Filtered frequency count, most common first**

```sql
SELECT status, COUNT(*) AS occurrences
FROM batch_jobs
WHERE job_name LIKE '%export%'
GROUP BY status
ORDER BY occurrences DESC, status
```

> **Mixed-case status values**
>
> In real tables a status column often mixes 'failed', 'FAILED', and 'Failed'. GROUP BY treats those as three separate groups and the counts fragment without any error. When the data is dirty, normalize with LOWER(status) before grouping so the counts collapse back together.

> **Interviewers Watch For**
>
> The strong signal is a candidate who states the output shape out loud before typing: one row per status, a count column, sorted most-common-first, and who asks how ties should break. Naming the tie-break unprompted reads as production experience.

> **Cost Analysis**
>
> At 300K rows this is a single sequential scan plus a hash aggregate over a handful of distinct statuses. The LIKE '%export%' has a leading wildcard, so no index can serve the filter and the cost is dominated by the scan, which is unavoidable for a substring search. There is no sort blow-up, because the group cardinality is tiny.

---

## Common 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/where_the_exports_land)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.