# Data Repo Fix Commits

> How many commits start with 'fix'?

Canonical URL: <https://datadriven.io/problems/data_repo_fix_commits>

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

A data engineering manager wants to see who is closing bugs across the codebase. For every repository, count the commits whose message mentions 'fix' in any capitalization, broken down by author.

## Tables

### repo_commits

- commit_id INTEGER
- repo_name TEXT
- author TEXT
- message TEXT
- added INTEGER
- removed INTEGER
- commit_at TEXT

Sample rows (10 of 200):

| commit_id | repo_name | author | message | added | removed | commit_at |
| --- | --- | --- | --- | --- | --- | --- |
| 3061 | backend-api | bob | Add unit tests | 17 | 7 | 2026-02-02 01:09:00 |
| 3122 | infra | charlie | Refactor auth module | 34 | 14 | 2026-03-03 02:18:00 |
| 3183 | ml-pipeline |  charlie | Update deps | 51 | 21 | 2026-04-04 03:27:00 |
| 3244 | mobile-app | dana  | Improve perf | 68 | 28 | 2026-05-05 04:36:00 |
| 3305 | data-platform | frank |  Add caching layer  | 85 | 35 | 2026-06-06 05:45:00 |
| 3366 | frontend | Alice | Fix typo in readme | 102 | 42 | 2026-07-07 06:54:00 |
| 3427 | backend-api | Bob | NULL | 119 | 49 | 2026-08-08 07:03:00 |
| 3488 | infra | alice | WIP | 136 | 56 | 2026-09-09 08:12:00 |
| 3549 | ml-pipeline | bob |  | 153 | 63 | 2026-01-10 09:21:00 |
| 3610 | mobile-app | charlie | Add search endpoint | 170 | 70 | 2026-02-11 10:30:00 |

## Expected output

First 5 of 15 rows:

| repo_name | author | fix_count |
| --- | --- | --- |
| backend-api | bob | 2 |
| backend-api | dana | 4 |
| backend-api | frank | 4 |
| data-platform | Bob | 2 |
| data-platform | dana | 2 |

## Worked solution and explanation

### What this is really testing

Beneath the audit framing this is a case-insensitive substring count: keep only the commits whose message carries `fix`, then tally them per repo and author. Anyone can write `COUNT(*)` with a `GROUP BY`. The two things that separate people are matching `fix` no matter how it is capitalized, and NOT quietly merging `bob` and `Bob` into one person. Miss the first and you undercount every `Fix` and `FIX`; miss the second and you hand the manager a name that does not exist.

---

### Match 'fix' regardless of case

#### Step 1: Normalize the message, not the author

`LOWER(message) LIKE '%fix%'` folds `Fix`, `FIX` and `hotfix` down to one comparison. The wildcards on both sides are what 'mentions fix' means: `fixed`, `hotfix` and `bugfix` all count. A null or empty `message` yields no match and drops out on its own.

#### Step 2: Count per repo and author as stored

`GROUP BY repo_name, author` with `COUNT(*) AS fix_count`. Leave `author` exactly as it sits in the table: `bob` and `Bob` are different keys, so they stay different rows in the answer.

### The solution

**Case-insensitive fix count per repo and author**

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

> **Lowercasing the author erases people**
>
> `GROUP BY repo_name, LOWER(author)` looks tidy and is wrong here: it fuses `bob` and `Bob` into one row and inflates someone's apparent output. Only normalize the thing you are MATCHING (`message`), never the thing you are REPORTING (`author`).

> **Case handling is the tell**
>
> The tell is whether you reach for `LOWER(...)` (or `ILIKE`) on the message. A bare `message LIKE '%fix%'` silently drops every capitalized `Fix` the moment the engine treats `LIKE` as case-sensitive, and the count comes back low without any error to warn you.

> **One scan, small grouping**
>
> This is a single pass over `repo_commits` applying one string test per row, then a grouping that collapses to (repo x author). `LOWER(message)` blocks any index on `message`, but a leading-wildcard `%fix%` was never index-friendly anyway, so a full scan is the expected plan.

---

## Common follow-up questions

- What if 'fix' appears inside a word like 'prefix' or 'suffix'? _('%fix%' matches those too; if that is unwanted you would anchor the pattern with spaces or word boundaries.)_
- How would you show each repo's fix commits as a share of its total commits? _(A window `COUNT` over the repo partition, or a conditional `SUM(CASE WHEN ... )` over an unfiltered scan.)_
- What if the manager actually wants a case-insensitive author tally? _(Then GROUP BY LOWER(author), but confirm the definition first; it changes who the 'people' are.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/data_repo_fix_commits)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.