# Radio Silence > Some codebases stop speaking for too long. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem An engineering manager is auditing repositories that 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. Surface the repositories whose longest silence runs past 5 days, ordered from the most silent down, and number the standings as you go. ## Worked solution and explanation ### What this really is Strip the audit framing and this is a per-repository maximum of consecutive differences wearing a business costume. Within each repo, line up every commit with the one before it, difference the two timestamps into days, then keep the largest of those gaps. Here is the tell: the average of consecutive gaps quietly telescopes to (last minus first) divided by the count, so a plain aggregate can fake the average without ever pairing rows. The maximum gap has no such shortcut. You are forced to materialize every consecutive difference, and that is exactly the skill being probed. > **The move that cracks it** > > LAG shifts each repo's commit stream back by one row so every commit sits next to its predecessor. Difference the timestamps and you have a per-commit gap. Do that inside a CTE, then take MAX per repo on top. --- ### Why a plain aggregate won't save you **Average gap** AVG of the consecutive gaps collapses: the interior timestamps cancel, leaving (max(commit_at) minus min(commit_at)) divided by (count minus 1). You can compute it with no ordered offset at all, which is why averaging would not truly test the skill. **Longest silence (MAX gap)** MAX of the consecutive gaps has no closed form from min, max, and count. A repo that commits daily for weeks then goes dark for 30 days has the same span as one that drifts evenly, but a very different longest silence. You must compute each adjacent difference and keep the biggest. --- ### 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) minus 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 and drop out with WHERE gap_days IS NOT NULL. A repo with a single commit has only that NULL row, so it disappears entirely and never reaches the standings. #### Step 3: Take the max, filter, position Group by repo_name, take MAX(gap_days) as the longest silence, keep only repos whose longest silence exceeds 5 days with HAVING, and attach a dense position over those maxima ordered widest first. --- ### The solution **LAG-based gap analysis with ranking** ```sql 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 MAX(gap_days) > 5 ORDER BY longest_silence DESC, repo_name ``` > **Match the date math to the engine** > > commit_at is stored as TEXT and this runs on SQLite, so julianday(commit_at) is the clean way to get numeric days. The Postgres reflex, casting with commit_at::timestamp and pulling EXTRACT(EPOCH FROM diff) / 86400, is a portability trap: those constructs are not valid here and the query comes back empty or errors. Read the column type before you reach for date functions. > **Interviewers watch for** > > Two tells separate candidates. First, reaching for the telescoping shortcut: (max minus min) over (count minus 1) is correct for the average but silently wrong for the longest gap, and a candidate who applies it here has not understood the metric. Second, excluding the first commit per repo and recognizing that a single-commit repo has no measurable silence at all. Leave the NULL gaps in and even MAX ignores them, but the explicit WHERE states the intent. > **Where the time goes** > > Over 5M rows the cost is dominated by the window sort: partition by repo_name, order by commit_at is an O(n log n) sort per partition. The MAX aggregation collapses to the number of repos, about 80, so it is cheap. There is no join and no self-join, which is why an ordered offset beats pairing each commit to its predecessor by hand. --- ## Common follow-up questions - What happens to a repo with only one commit, and is that the behavior you want? _(All of its gaps are NULL, so it drops out before aggregating. 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.)_ - If commit_at were a DATE instead of a TEXT timestamp, how would the gap calculation change? _(With a true date column the difference is already an integer number of days, so julianday is unnecessary; tests awareness of the type-to-function mapping.)_ - How would you position two repos with the same longest silence? _(Identical maxima force a decision on tie behavior and whether ORDER BY needs a stable tiebreaker.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/radio_silence) - [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.