Radio Silence

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

L5

Problem

An engineering manager is auditing which repositories have gone quiet, where a repository's longest silence is the widest stretch of days between two back-to-back commits. Commit timestamps are stored as text and the queries run on SQLite, so the day count has to come out of the stored timestamps. Limit the audit to repositories with a long track record, more than 31 stretches between consecutive commits, and for each surface its longest silence from most silent down, numbering the standings as you go.

Some codebases stop speaking for too long.

Tables

repo_commits

Columns: commit_id INTEGER, repo_name TEXT, author TEXT, message TEXT, added INTEGER, removed INTEGER, commit_at TEXT

Expected output

Worked solution and explanation

What this really is

Strip the audit framing and this is a per-repository maximum of consecutive differences, gated by a track-record filter. Within each repo, line up every commit with the one before it, difference the two timestamps into days, and keep the largest gap. Two tells hide here. First, the average of consecutive gaps quietly telescopes to (last minus first) over the count, so a plain aggregate could fake an average with no ordered offset, but the maximum gap has no such shortcut and forces you to materialize every adjacent difference. Second, the filter counts stretches between commits, not commit rows, and the first commit of each repo opens no stretch.

Why the first commit is not a silence

Count commit rows

COUNT(*) over every commit row gives 34 and 32 for the two history sizes here. Filter > 31 and both clear the bar, so a 32-commit repo with only 31 real stretches leaks into the standings.

Count the stretches

Drop the first-commit NULL gap first, and the counts become 33 and 31. Now > 31 keeps the deep-history repos and drops the thin ones. The undefined first gap is the whole difference between the two.

Break down the requirements

Step 1: Pair each commit with its predecessor

Partition by repo_name and order by commit_at, then LAG(commit_at) hands each row the previous commit's timestamp. The earliest commit in each repo has nothing behind it, so LAG returns NULL there.

Step 2: Turn the pair into a day count

Compute julianday(commit_at) - julianday(previous commit_at). julianday turns a text timestamp into a fractional day count, so the subtraction is already a number of days. The first-commit rows carry a NULL gap; WHERE gap_days IS NOT NULL drops them so they count as neither a stretch nor a silence.

Step 3: Take the max, filter on history, position

Group by repo_name, take MAX(gap_days) as the longest silence, keep only repos with more than 31 surviving stretches via HAVING COUNT(*) > 31, and attach a dense position over those maxima ordered widest first.

The solution

WITH gaps AS (
    SELECT
        repo_name,
        julianday(commit_at) - julianday(LAG(commit_at) OVER (PARTITION BY repo_name ORDER BY commit_at)) AS gap_days
    FROM repo_commits
)
SELECT
    repo_name,
    MAX(gap_days) AS longest_silence,
    DENSE_RANK() OVER (ORDER BY MAX(gap_days) DESC) AS silence_rank
FROM gaps
WHERE gap_days IS NOT NULL
GROUP BY repo_name
HAVING COUNT(*) > 31

Common follow-up questions

• What happens to a repo with only one commit, and does it belong in this audit? (Its only gap is NULL, so it has zero real stretches and fails the track-record filter outright. Tests whether the candidate anticipates the single-commit edge case.)
• Why can you not compute the longest silence from just the first and last commit dates? (Forces the candidate to articulate that MAX of consecutive gaps has no telescoping shortcut, unlike the average.)
• How would the track-record count change if you counted commit rows instead of the gaps between them? (Surfaces the off-by-one: every repo gains a phantom first-commit stretch, shifting the count by one and leaking thin-history repos past the filter.)
• How would you position two repos with the same longest silence? (Identical maxima force a decision on tie behavior: dense numbering lets them share a standing without skipping the next number.)