# 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

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

## Worked solution and explanation

### What this really is

This is a latest-row-per-group problem stapled to a per-group 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 your month count drops to one; count rows instead of months and a table checked twice in March looks like two months of coverage.

> **Trick to solving**
>
> Do the two jobs in two passes, then join. Rank each table's rows by recency and keep rn = 1 as a whole row, so its rule and run_at ride along for free. In parallel, group the raw rows by table and count DISTINCT year-month buckets. Join the two on tbl_name and you have the newest verdict plus true month coverage, side by side.

---

### Walking through it

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

In one CTE, number each table's checks newest-first: ROW_NUMBER() OVER (PARTITION BY tbl_name ORDER BY run_at DESC). Ordering on the full run_at timestamp (not just the date) is what makes recency exact, and ranking the whole row means the winning rule and run_at come along automatically.

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

In a second CTE, work from the RAW rows: GROUP BY tbl_name and COUNT(DISTINCT STRFTIME('%Y-%m', run_at)). STRFTIME buckets each timestamp to its calendar month, and DISTINCT collapses repeat checks in the same month so coverage is measured in months, not rows.

#### Step 3: Join and project the winning row

Join the two CTEs on tbl_name, keep only rn = 1 from the ranked set, and project tbl_name, rule, run_at and the month count. Order by tbl_name so it reads like a roster. Because the count came from a separate scan, collapsing to the newest row never shrinks it.

---

### The solution

**Latest verdict plus month coverage**

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

> **Cost analysis**
>
> Two scans of dq_checks: one feeds the partitioned window, one feeds the grouped distinct-month count. Both are per-table aggregations that stay cheap at 700K rows and 250 tables, and the final join is on the 250-row table key. No self-join back onto the fact rows, no correlated per-row subquery.

> **Interviewers watch for**
>
> 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.

> **Common pitfall**
>
> 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 count has to be taken before the collapse, which is why it lives in its own scan.

**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 ranked CTE.)_
- 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.)_
- 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 count DISTINCT months where passed = 0 in the second CTE.)_
- 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.