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

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, and alphabetically by name whenever two authors match on that.

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 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.

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

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, author

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.)