Net Lines

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Each row of repo_commits is one commit: the repository it landed in, the lines it added and the lines it removed, across several years of history. What is each repository's net line growth over that whole history, everything added minus everything removed?

Some authors build. Others trim. The net tells the truth.

Tables

repo_commits

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

Expected output

Worked solution and explanation

What this really is

Without the dashboard framing, this is a grain question. The log has one row per commit and the answer has one row per repository, so the whole job is collapsing to that grain before you net anything. Everyone knows the net is added minus removed. What separates candidates is where that subtraction lives. Do it on each row and stop, and you return 200 commit-level differences. Let author or commit_at into the GROUP BY and each repository breaks into fragments. Either way, no row tells you how much a repository grew.

Step 1: Group on repo_name only

The question names one entity, the repository, so repo_name is the only grouping key. Nothing in it is per author or per period, and the commits span several years with no cutoff, so every row of repo_commits belongs in its repository's total.

Step 2: Total each side, then subtract

SUM(added) is everything the repository gained and SUM(removed) is everything it lost; their difference is the net. Name it net_lines so the result reads as an answer, not as an expression.

SELECT
  repo_name,
  SUM(added) - SUM(removed) AS net_lines
FROM repo_commits
GROUP BY repo_name

Net per commit, then total

SUM(added - removed) nets every row first. If either column is ever NULL on a commit, that difference is NULL, and SUM drops the whole commit.

Total each side, then net

SUM(added) - SUM(removed) lets each total skip only its own missing values. Both columns are always filled here, so the two forms agree, but this one stays correct if that changes.

SELECT
  repo_name,
  added - removed AS net_lines
FROM repo_commits

Common follow-up questions

• How would you report the same net per author, given the spellings in `author`? (Tests whether the candidate cleans the key (trims spaces, folds case) before grouping, and asks whether 'Alice' and 'alice' are really one person.)
• Leadership wants only the last 90 days. Where does the cutoff go? (A `WHERE` on `commit_at` before grouping, so the totals only see commits in range.)
• How would you show each repository's net per year? (Adds the year of `commit_at` as a second grouping key; the grain changes from repository to repository-year.)