Data Repo Fix Commits

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

Our fix tally lists some engineers twice, once per way their name was capitalized, and misses commits written as 'Fix' instead of 'fix'. Rebuild it from repo_commits: per repository and author, the number of commits whose message mentions 'fix', with each person under a single lowercase name.

How many commits start with 'fix'?

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

This is a case-insensitive pattern count with a dirty grouping key, dressed up as a bug-fix tally. Anyone can write the COUNT(*) and the GROUP BY. The trick is that capitalization is noise on BOTH sides: every fix message in repo_commits starts with a capital ('Fix login bug', 'Fix flaky test'), and the same engineers were typed as 'alice' and 'Alice', 'bob' and 'Bob'. Fold only one side and the tally either comes back empty or shows two people where there is one, with their fixes split between the rows.

Step 1: Fold the message before matching

LOWER(message) LIKE '%fix%' turns 'Fix' and 'FIX' into 'fix' before the comparison. The wildcards on both sides are what 'mentions fix' means, so 'hotfix' or 'fixed' would count too. A NULL or empty message never matches and drops out without a guard.

Step 2: Fold the author before counting

Group on LOWER(author), not author, and return that folded value as author. On the seed this merges frontend's 'Alice' (4 fixes) with 'alice' (2) into one row of 6, and data-platform's 'bob' and 'Bob' the same way: 15 raw groups become 13 people.

Step 3: Count rows per repository and person

COUNT(*) per (repo_name, LOWER(author)) is the whole metric: every row that survives the filter is one fix commit. No order is asked for, so none is imposed.

The solution

SELECT repo_name,
       LOWER(author) AS author,
       COUNT(*) AS fix_count
FROM repo_commits
WHERE LOWER(message) LIKE '%fix%'
GROUP BY repo_name, LOWER(author)

Raw author

GROUP BY repo_name, author returns 'Alice' with 4 and 'alice' with 2 on frontend as two rows. The manager reads two half-busy engineers.

Folded author

GROUP BY repo_name, LOWER(author) returns one 'alice' row with 6. One person, one row, the full count.

Common follow-up questions

• What if 'fix' appears inside a word like 'prefix' or 'suffix'? ('%fix%' matches those too; anchoring the match to a word boundary with a regular-expression function narrows it.)
• Two author values carry stray spaces (' charlie', 'dana '). Would `TRIM` change this answer? (Not on this data, since neither is on a fix commit, but folding with `LOWER(TRIM(author))` is the safer habit.)
• How would you show each repository's fix commits as a share of all its commits? (A conditional `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` over an unfiltered scan, divided by `COUNT(*)`.)