# First Light > Every repo has a scrappy stretch before its first green build. See who was shipping then, and how big their changes were. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L6 ## Problem We're profiling who was shipping code in each repo's scrappy early days, before it ever logged a successful CI build. For each of those authors, report their average lines added and average lines removed per commit, biggest contributions first. ## Worked solution and explanation ### What this problem really is Strip the costume and this is a per-repo anchored filter with one sharp edge: the anchor is not the repo's earliest build, it is the repo's first SUCCESSFUL build. So the anchor is a MIN scoped to status = 'success', joined back onto the commits, and you keep only what landed before it. Anyone can write the CTE and the join. The trick is that filtered MIN. Anchor on the earliest build of any status and you quietly change every repo's cutoff, because most repos log a failed or canceled build first, and you drop exactly the early committers this question is about. > **Trick to Solving** > > The anchor is each repo's **first successful build**, not its first build. > > 1. Anchor: `MIN(built_at)` per `repo_name`, filtered to `status = 'success'` > 2. Window: commits whose `commit_at` is strictly before that anchor > 3. Output: per author, two AVGs (lines added, lines removed) --- ### Break down the requirements #### Step 1: Anchor on each repo's first green build Build a CTE `first_success` that filters `ci_builds` to `status = 'success'`, groups by `repo_name`, and takes `MIN(built_at)` as `first_success_at`. The WHERE has to sit inside this CTE so the MIN only sees successful builds. One row per repo that ever went green; repos that never succeeded produce no row. #### Step 2: Keep commits that landed before it Join `repo_commits` to `first_success` on `repo_name`. The inner join is doing double duty here: it attaches the anchor AND silently discards commits to repos that never succeeded (no anchor row to match). Then keep only the early commits with `commit_at < fs.first_success_at`. Compare `commit_at` (the timestamp), never `message` (the commit text). #### Step 3: Average per author, biggest first Group by `author` and project `AVG(rc.added)` and `AVG(rc.removed)`, then order by `avg_lines_added` descending so the biggest contributors surface first. Because the timestamps are stored as ISO text, every comparison here is a plain string comparison; no date functions are needed at all. --- ### The solution **Filtered MIN anchor, before-window, two AVGs** ```sql WITH first_success AS ( SELECT repo_name, MIN(built_at) AS first_success_at FROM ci_builds WHERE status = 'success' GROUP BY repo_name ) SELECT rc.author, AVG(rc.added) AS avg_lines_added, AVG(rc.removed) AS avg_lines_removed FROM repo_commits rc JOIN first_success fs ON rc.repo_name = fs.repo_name WHERE rc.commit_at < fs.first_success_at GROUP BY rc.author ORDER BY avg_lines_added DESC ``` > **Time and Space Complexity** > > **Time:** O(b + c) where b is `ci_builds` rows and c is `repo_commits` rows. The CTE hash-aggregates the success builds down to about one row per repo (~400), then a hash join on `repo_name` against `repo_commits` plus the range filter is a single streaming pass. > > **Space:** O(r) for the per-repo anchor table where r is the number of repos that have gone green. > **Interviewers Watch For** > > Whether the anchor is the first build or the first SUCCESSFUL build. It is the whole problem. A strong candidate scopes the MIN with the status filter without being told twice, reads the columns so they compare `built_at` (the timestamp) rather than `status` or `trigger`, and notices that the inner join is what excludes never-green repos. > **Common Pitfall** > > Anchoring on `MIN(built_at)` over all builds instead of only successful ones. Most repos log a failed or canceled build before their first green run, so the unfiltered MIN sits earlier in time. Early commits that came after the first failure but before the first success then fall outside your window and their authors vanish from the result, even though they are exactly the people the question asks about. --- ## Common follow-up questions - How would you also list authors on repos that never logged a successful build? _(Tests handling repos that never reached a successful build, and whether the candidate switches from an inner join to a left join to surface them.)_ - What changes if early meant between the first successful build and the second successful build? _(Tests turning a strict before-anchor filter into a bounded window without reaching for interval math.)_ - What does this query do for an author who committed early to two different repos? _(Tests whether the candidate sees that one author committing to several repos blends their separate pre-green windows into one average.)_ - Which indexes on repo_commits and ci_builds would let the planner avoid full scans here? _(Tests indexing for the join plus the status-filtered aggregate and the range comparison.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/first_light) - [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.