The Most Recent Word

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

dq_checks

Columns: check_id INTEGER, tbl_name TEXT, col_name TEXT, rule TEXT, passed INTEGER, fail_pct REAL, run_at TEXT, severity TEXT

Expected output

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.

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

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

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