# The Most Recent Word

> Every table keeps a record. Only the latest verdict still speaks.

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

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: City and County of San Francisco

## Problem

Our data quality system appends a new row every time it runs a check against a table. For each table, return the rule from its most recent check, when that check ran, and the number of different calendar months in which the table has been checked.

## Tables

### dq_checks

- check_id INTEGER
- tbl_name TEXT
- col_name TEXT
- rule TEXT
- passed INTEGER
- fail_pct REAL
- run_at TEXT
- severity TEXT

Sample rows (10 of 200):

| check_id | tbl_name | col_name | rule | passed | fail_pct | run_at | severity |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 131 | users | email | unique | 1 | NULL | 2026-02-02 01:00:00 | high |
| 162 | products | amount | range | 1 | NULL | 2026-03-03 02:00:00 | medium |
| 193 | transactions | status | format | 1 | NULL | 2026-04-04 03:00:00 | low |
| 224 | events | created_at | referential | 1 | NULL | 2026-05-05 04:00:00 | Critical |
| 255 | sessions | price | freshness | 0 | 15 | 2026-06-06 05:00:00 | HIGH |
| 286 | ad_impressions | region | Not_Null | 1 | NULL | 2026-07-07 06:00:00 | critical |
| 317 | orders | user_id | UNIQUE | 1 | 0 | 2026-08-08 07:00:00 | high |
| 348 | users | email | not_null | 1 | NULL | 2026-09-09 08:00:00 | medium |
| 379 | products | amount | unique | 1 | NULL | 2026-01-10 09:00:00 | low |
| 410 | transactions | status | range | 0 | 30 | 2026-02-11 10:00:00 | Critical |

## Expected output

First 5 of 7 rows:

| tbl_name | rule | run_at | months_checked |
| --- | --- | --- | --- |
| ad_impressions | range | 2026-09-07 18:00:00 | 20 |
| events | freshness | 2026-09-02 05:00:00 | 20 |
| orders | format | 2026-09-08 11:00:00 | 21 |
| products | referential | 2026-09-05 20:00:00 | 21 |
| sessions | unique | 2026-09-06 17:00:00 | 20 |

## Worked solution and explanation

### What this really is

This is a latest-row-per-table pick stapled to a per-table coverage count, both wearing a data-quality badge. Two skills are probed at once: for each table pull the single newest row and carry its OWN `rule` and `run_at` along, and separately count how many different calendar months that table appears in. Anyone can find the newest timestamp per table. The trap is keeping the `rule` that belongs to that timestamp while ALSO counting months across every row of the table, not just the one that survived. Collapse the rows first and the month count drops to one; count rows instead of months and a table checked twice in March looks like two months of coverage.

> **The two-invariant split**
>
> Number each table's rows newest-first with `ROW_NUMBER()` and keep `rn = 1` as a WHOLE row, so its `rule` and `run_at` ride along for free. For the coverage, hang a correlated `(SELECT COUNT(DISTINCT STRFTIME('%Y-%m', run_at)) ... GROUP BY tbl_name)` off the surviving row, scoped by `tbl_name`, so it counts distinct months over the table's full history rather than the one row you kept.

---

### Walking through it

#### Step 1: Rank each table's checks by recency

Number each table's checks newest-first inside a derived table: `ROW_NUMBER() OVER (PARTITION BY tbl_name ORDER BY run_at DESC)`. Ordering on the full `run_at` timestamp (not just the date) makes recency exact, and ranking the whole row means the winning `rule` and `run_at` come along automatically.

#### Step 2: Keep only the newest check

Filter the derived table to `rn = 1`, so exactly one row per table survives: the most recent check with its own `rule` and `run_at` intact.

#### Step 3: Count distinct months per table

For each surviving row, compute month coverage with a correlated `(SELECT COUNT(DISTINCT STRFTIME('%Y-%m', c.run_at)) FROM dq_checks c WHERE c.tbl_name = latest.tbl_name GROUP BY c.tbl_name)`. Scoping by `tbl_name` and grouping on it rolls the table's whole history into one bucket; `STRFTIME` maps each timestamp to its calendar month and `DISTINCT` collapses repeat checks in the same month, so coverage is measured in months, not rows. Because this subquery reads the table's RAW rows, collapsing to the newest row never shrinks it.

---

### The solution

**Latest verdict plus month coverage**

```sql
SELECT
    latest.tbl_name,
    latest.rule,
    latest.run_at,
    (
        SELECT COUNT(DISTINCT STRFTIME('%Y-%m', c.run_at))
        FROM dq_checks c
        WHERE c.tbl_name = latest.tbl_name
        GROUP BY c.tbl_name
    ) AS months_checked
FROM (
    SELECT
        tbl_name,
        rule,
        run_at,
        ROW_NUMBER() OVER (
            PARTITION BY tbl_name
            ORDER BY run_at DESC
        ) AS rn
    FROM dq_checks
) latest
WHERE latest.rn = 1
```

> **Cost analysis**
>
> The window pass ranks every row of `dq_checks` once; the correlated month count then fires once per surviving table (250 of them), each scan scoped to that table's rows on `tbl_name` and rolled into one group. Both stay cheap at 700K rows, and there is no self-join back onto the fact rows and no top-level sort.

> **The rule must match the timestamp**
>
> Whether the returned `rule` actually belongs to the newest check, and whether the month count survived the collapse. A candidate who reports the right timestamp but a `rule` from another row, or whose month count silently became 1 because they counted after deduping to the newest row, has broken exactly the two invariants this shape protects.

> **Counting after the collapse**
>
> Computing the month count from the already-collapsed newest-row set instead of the raw rows. Once you filter to `rn = 1` there is exactly one row per table, so any count over it returns 1 for everyone. The correlated subquery scopes and groups by `tbl_name` and reads the full table, which is why it survives the collapse.

**COUNT(*) over the rows**

Counts raw check rows, so a table checked twice in the same calendar month reports two months of coverage. It conflates how OFTEN a table was checked with how many months it was checked in.

**COUNT(DISTINCT month)**

Buckets each `run_at` to its year-month with `STRFTIME` and dedupes, so two checks in March count as one month. This is true month coverage, independent of check frequency.

---

## Common follow-up questions

- How would you show each table's three most recent checks alongside its month coverage? _(Change WHERE rn = 1 to WHERE rn <= 3 in the derived table.)_
- What if two checks for a table share the exact same `run_at`? _(Add a deterministic tiebreak, e.g. ORDER BY `run_at` DESC, `check_id` DESC in the window.)_
- How would you restrict this to each table's most recent FAILED check and its count of failing months? _(Filter passed = 0 before the window for the latest-failed row, and add AND c.passed = 0 inside the correlated month count.)_
- How would you report distinct years of coverage instead of months? _(Swap STRFTIME('%Y-%m', `run_at`) for STRFTIME('%Y', `run_at`) in the distinct count.)_

## Related

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