# Below the Line

> Low severity. High volume.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The data quality team is sizing the volume of check runs from 2026 that came in at the 'low' severity tier. Return the count as a single number.

## Worked solution and explanation

### What they are really testing

This looks like a one-line filtered count, and the COUNT plus WHERE is the easy half. The real probe is the severity column: it is a dirty categorical field where the same tier arrives as 'low', 'Low', and 'LOW'. Match it too literally and your count comes back silently short. The second half of the trap is run_at: it is a timestamp, so isolating a single calendar year means reaching for a date function, not eyeballing a range.

> **LIKE folds case for you in SQLite**
>
> severity LIKE 'low%' catches low, Low, LOW, and any value that starts with those letters in one stroke, because SQLite's LIKE is case-insensitive for ASCII by default. That single operator absorbs both the casing mess and any trailing variant like 'low-priority' without a pile of OR clauses.

> **The two ways this count goes wrong**
>
> WHERE severity = 'low' passes the lowercase sample rows and then quietly drops every 'Low' and 'LOW' the full table holds. And WHERE run_at = '2026' compares a full timestamp to a bare year string and matches zero rows. Both are the kind of bug that returns a plausible-looking number, which is exactly why it survives to production.

#### Step 1: Pin the low tier without trusting the casing

Do not assume the data is clean. Use a case-insensitive match so every spelling of the low tier lands in the same bucket. LIKE 'low%' is the shortest path here; LOWER(severity) = 'low' is the equivalent explicit form if you want to signal intent.

#### Step 2: Extract the year from the timestamp

run_at is a timestamp, so strftime('%Y', run_at) turns each row into its four-digit year string, which you compare against the target year. This is the piece that separates people who know a timestamp is not a date from people who compare the whole thing to a year and get nothing back.

#### Step 3: Count the survivors

With both filters in the WHERE clause, COUNT(*) over what remains is the answer. Every qualifying run counts, including repeated runs of the same rule, so there is no DISTINCT here.

**Low-tier check runs for the target year**

```sql
SELECT COUNT(*) AS low_severity_count
FROM dq_checks
WHERE severity LIKE 'low%'
  AND strftime('%Y', run_at) = '2026';
```

*Case-insensitive LIKE handles the messy severity labels; strftime isolates the year from the timestamp.*

**Looks right, undercounts**

WHERE severity = 'low' AND run_at = '2026'. The equality on severity only catches exact-case rows, and comparing a timestamp to '2026' matches nothing at all, so this often returns 0 and looks like a data problem rather than a query bug.

**Robust**

WHERE severity LIKE 'low%' AND strftime('%Y', run_at) = '2026'. Case-insensitive matching gathers every spelling of the tier, and the year is pulled out of the timestamp explicitly, so the count reflects the true volume.

> **What signals seniority here**
>
> The tell is that you treat severity as untrusted the moment you see mixed casing in the sample, without being told the column is dirty. Strong candidates also name the timezone assumption out loud: strftime reads the stored timestamp as-is, so if run_at were UTC and the business reports in local time, the year boundary could shift a handful of rows.

> **Dirty enum columns are the norm**
>
> Severity, status, and country fields written by many upstream services almost never agree on casing or spelling. In production you either normalize them at ingest or defend against them at read time. This query is the read-time defense: assume the label is inconsistent and match accordingly.

## Common follow-up questions

- Now break the count down by year instead of filtering to one, so the team can see the low-tier trend over time. _(Moves from a scalar filter to grouping on the extracted year.)_
- Return the count per severity tier, normalizing the casing so each tier appears once. _(Tests grouping on a cleaned categorical rather than a hard-coded literal.)_
- How would your answer change if run_at were stored in UTC but the report is defined in the team's local timezone? _(Probes awareness that year extraction is timezone-sensitive.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/below_the_line)
- [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.