Latest Commit Build Cost

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

L5

Asked in

Tredence

Problem

Our heaviest CI spend sits on repositories built by outside webhooks, so every engineer who has committed to a webhook-built repository shares that bill. For each of those engineers, find the repository in repo_commits they most recently committed to and return its total build seconds across every build recorded in ci_builds.

The latest commit came with a build cost.

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 is really about

Under the billing story this is a membership-gated latest-touch rollup. The cost lives on the repository, not the person, so the work is choosing the RIGHT people and the RIGHT repo for each: only engineers who have committed to a webhook-built repository are on the hook, and each of them is billed the repository they most recently touched. The subtle part is that the webhook test decides WHO gets billed, not WHICH builds are counted.

Why the obvious query is wrong

The reflex is GROUP BY author with MAX(commit_at), then a re-join to repo_commits to recover repo_name. That loses the repo the max belongs to and fans out on ties. Two more traps hide here: skip the roster subquery and you bill every engineer, including those who never touched a webhook-built repo; and if you read the webhook clause as a filter on ci_builds and sum only trigger = 'webhook' builds, the chosen repo's real cost collapses.

Building it step by step

Step 1: Pin down who is billed

Build the roster with WHERE author IN (SELECT rc.author FROM repo_commits rc JOIN ci_builds cb ON rc.repo_name = cb.repo_name WHERE cb.trigger = 'webhook'). That keeps only engineers who committed to a repository that runs webhook builds. Drop it and every author flows through, inflating the bill.

Step 2: Pick each engineer's most recent repo

ROW_NUMBER() OVER (PARTITION BY author ORDER BY commit_at DESC, commit_id DESC), then keep rn = 1. The window carries repo_name on the winning row, which a GROUP BY author with MAX(commit_at) cannot, and commit_id DESC settles a same-timestamp tie deterministically.

Step 3: Roll up the chosen repo's cost

Join the winning (author, repo_name) back to ci_builds on repo_name, take SUM(dur_secs), and GROUP BY author. Every build for that repo counts, whatever its status.

The solution

WITH latest_repo AS (
  SELECT author, repo_name,
         ROW_NUMBER() OVER (PARTITION BY author ORDER BY commit_at DESC, commit_id DESC) AS rn
  FROM repo_commits
  WHERE author IN (
    SELECT rc.author
    FROM repo_commits rc
    JOIN ci_builds cb ON rc.repo_name = cb.repo_name
    WHERE cb.trigger = 'webhook'
  )
)
SELECT lr.author, SUM(cb.dur_secs) AS total_build_seconds
FROM latest_repo lr
JOIN ci_builds cb ON lr.repo_name = cb.repo_name
WHERE lr.rn = 1
GROUP BY lr.author;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you break ties when an engineer has two commits at the exact same `commit_at` on different repos? (`ROW_NUMBER` needs a deterministic secondary key; `commit_id DESC` in the window `ORDER BY` settles a same-timestamp tie.)
• What if we only want to bill builds that ran after that latest commit? (Carry `commit_at` through the CTE and add `cb.built_at >= lc.commit_at` to the join predicate so only builds after the latest commit are summed.)
• How would you also bill the engineers who never touched a webhook-built repo? (Drop the roster subquery, or UNION a second bucket that rolls the non-webhook engineers up separately so both groups appear.)