First Light

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

Domain

SQL

Difficulty

medium

Seniority

L6

Asked in

Samsara

Problem

A repo's early days last until its first successful CI build, and every repo reached that point at its own moment. Using repo_commits and the build history in ci_builds, report each author's average lines added and average lines removed per commit over the commits from those early days, biggest contributions first.

Every repo has a scrappy stretch before its first green build. See who was shipping then, and how big their changes were.

Tables

repo_commits

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

ci_builds

Columns: build_id INTEGER, repo_name TEXT, branch TEXT, status TEXT, dur_secs INTEGER, trigger TEXT, built_at TEXT

Expected output

Worked solution and explanation

What this problem really is

Strip the costume and this is a per-repo cutoff. Every repo has its own boundary, the moment of its first status = 'success' build, and each commit has to be judged against its own repo's boundary before anything is averaged. Anyone can write the MIN. What separates candidates is refusing to collapse it into one number. A scalar (SELECT MIN(built_at) FROM ci_builds WHERE status = 'success') returns the earliest green build anywhere, which on this seed belongs to 'frontend', and every repo that went green later loses the commits it made in between. 'infra' reached its first green build about six weeks after 'frontend', and an early 'infra' commit from that stretch silently drops out of its author's averages.

Step 1: Find each repo's first green build

Build first_success from ci_builds: filter to status = 'success', group by repo_name, and take MIN(built_at) as first_success_at. The filter sits inside the CTE so the MIN only ever sees green builds. The result is one row per repo, six on this seed.

Step 2: Hand every commit its own repo's cutoff

Join repo_commits to first_success on repo_name, then keep rc.commit_at < fs.first_success_at. The join key is what makes the cutoff move from repo to repo. The comparison is strict, so a commit at the exact instant of the green build is not early. Both timestamps are ISO text, so string order is time order.

Step 3: Average per author, biggest first

Group the surviving commits by rc.author, take AVG(rc.added) and AVG(rc.removed), and sort by avg_lines_added descending. Authors are grouped as stored, so 'Alice' and 'alice' come out as two rows.

The solution

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

One global cutoff

WHERE rc.commit_at < (SELECT MIN(built_at) FROM ci_builds WHERE status = 'success') measures every commit against the first green build of 'frontend', the earliest on the seed. Early commits to 'infra' and 'ml-pipeline' that came after it vanish.

Each repo's own cutoff

Joining on repo_name gives each commit its own repo's first_success_at. A commit counts exactly when its repo had not yet gone green, which is what early days means.

Common follow-up questions

• How would you include authors on repos that never logged a successful build? (Tests switching to an outer join and deciding what cutoff a never-green repo gets.)
• What if early meant between a repo's first and second successful builds? (Tests picking the second green build per repo without hardcoding dates.)
• An author committed early to two repos. Should their averages blend both windows? (Tests noticing the grain is the author, not the author per repo.)
• What changes if 'Alice' and 'alice' are confirmed to be the same person? (Tests normalizing the grouping key and what that does to the averages.)
• Which indexes on `repo_commits` and `ci_builds` let the planner skip full scans? (Tests indexing for the status-filtered aggregate and for the join plus range filter.)