# Fresh Ink > Recent activity is the only activity that counts. Name the busiest hands. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem We keep a table of repository commits, each stamped with its author and commit date. Counting only commits from the three most recent calendar years present in the data (the year of the latest commit and the two years before it), find the ten authors with the most commits. Return each author with their commit count, most active first. ## Worked solution and explanation ### Why this problem exists in real interviews Engineering productivity dashboards constantly ask 'who shipped the most recently?' but anchored to the data, not to wall-clock time, because backfills and stale snapshots make `CURRENT_DATE` lie. Interviewers use this prompt to test whether you can build a dynamic cutoff from `MAX(commit_at)` inside a scalar subquery, then apply it as a WHERE filter and finish with the standard top-N aggregation. --- ### Break down the requirements #### Step 1: Anchor the window to the data, not to today `commit_at` is TEXT, so use `strftime('%Y', commit_at)` to extract the year and `CAST(... AS INTEGER)` to compare arithmetically. The cutoff is the latest year minus 2, computed in a scalar subquery against `repo_commits` itself, which makes the window slide with the data instead of breaking on a stale snapshot. #### Step 2: Filter rows whose year is in the window `WHERE CAST(strftime('%Y', commit_at) AS INTEGER) >= (cutoff)` keeps every commit whose year falls in the latest-year-and-two-before-it span. Because the comparison wraps `commit_at` in `strftime`, the planner cannot use a B-tree index on `commit_at` directly; that is acceptable here because the prompt is correctness-first, not throughput-tuned. #### Step 3: Group, count, sort, limit `GROUP BY author` collapses to one row per author across the roughly 200 distinct authors, with the count of commits in the window. `ORDER BY commit_count DESC` puts the most active first, and `LIMIT 10` returns the top ten. Two columns out: `author` and `commit_count`. --- ### The solution **Dynamic year cutoff via scalar subquery, then top-10 by author** ```sql SELECT author, COUNT(*) AS commit_count FROM repo_commits WHERE CAST(strftime('%Y', commit_at) AS INTEGER) >= (SELECT CAST(strftime('%Y', MAX(commit_at)) AS INTEGER) - 2 FROM repo_commits) GROUP BY author ORDER BY commit_count DESC LIMIT 10 ``` > **Cost Analysis** > > `repo_commits` is 5M rows with no partitioning. The scalar subquery runs once: a single MAX scan over `commit_at` (which the engine may shortcut with an index, otherwise a full scan). The outer query then does a second full scan applying the year filter, hash-aggregates over 200 distinct `author` values, and returns 10 rows. Two scans of a 5M-row table are acceptable; in production you would either materialize the cutoff or partition by year. > **Interviewers Watch For** > > Interviewers note whether you anchor the cutoff to `MAX(commit_at)` from the data (correct) versus `CURRENT_DATE - INTERVAL '2 years'` (which silently breaks on stale dumps). They also watch whether you cast the year to INTEGER before subtracting, and whether you remember `LIMIT 10` rather than returning all 200 authors and assuming the caller will trim. > **Common Pitfall** > > Comparing year strings (`strftime('%Y', commit_at) >= '2022'`) accidentally works for four-digit years but breaks the moment the engine decides to coerce one side to INTEGER. The deeper trap is using `date('now', '-2 years')`: the dataset's latest commit may be from 2024 while `now` is 2026, so the filter excludes everything and the result is empty. --- ## Common follow-up questions - How would you change the query so the window is the last 24 months relative to `MAX(commit_at)` rather than calendar years? _(Tests whether the candidate switches from `strftime('%Y', ...)` arithmetic to a date-shift on `commit_at` itself, e.g. `commit_at >= date(MAX(commit_at), '-24 months')`, and recognizes the difference in boundary semantics.)_ - How would you tie-break two authors with the same `commit_count` so the top-10 ordering is reproducible? _(Tests deterministic ordering. Add `, author ASC` to the `ORDER BY` so collisions break alphabetically; otherwise repeat runs may swap rows 9 and 10.)_ - How would you also report each author's `repo_name` count to surface contributors who span many repos versus those locked to one? _(Tests whether the candidate adds `COUNT(DISTINCT repo_name) AS repo_breadth` alongside `COUNT(*)` without breaking the GROUP BY, and reasons about the 70 distinct `repo_name` values.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/fresh_ink) - [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.