Verify Commit ID Uniqueness
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
- Asked in
- Vesta Innovations
Problem
Every row of repo_commits is one commit, and its author name was typed by hand, so the same person can appear with different capitalization or stray spaces around the name. Show the total number of commits next to how many different people authored them.
Duplicate commit IDs. Are there any?
Tables
repo_commits
Columns: commit_id INTEGER, repo_name TEXT, author TEXT, message TEXT, added INTEGER, removed INTEGER, commit_at TEXT
| 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
| total_commits | distinct_authors |
|---|---|
| 200 | 6 |
Worked solution and explanation
What this problem is really asking
Strip the framing and this is a unique headcount over a name column nobody cleaned. Counting commits is the easy half. The author figure is where candidates split: COUNT(author) hands back 200, the commit total again, and even COUNT(DISTINCT author) returns 10, because 'Alice' and 'alice', 'Bob' and 'bob', ' charlie' and 'dana ' each count as two people. The seed holds six authors. Clean the name before you collapse it, or the history looks spread over nearly twice the team that wrote it.
Build the two numbers
Step 1: Count the commits
commit_id is unique and never NULL in repo_commits, so COUNT(commit_id) is the commit total: 200. It equals COUNT(*); naming the key just says what is being counted.
Step 2: Clean the name before counting it
Read the sample rows before you write an aggregate: ' charlie' carries a leading space, 'dana ' a trailing one, and 'Alice' sits beside 'alice'. TRIM(author) strips the padding and LOWER(...) folds the case, so every spelling of one person lands on the same key.
Step 3: Count unique people in the same row
COUNT(DISTINCT LOWER(TRIM(author))) collapses the cleaned names to 6. Both expressions sit in one SELECT with no GROUP BY, so the whole table folds into a single row.
The solution
SELECT
COUNT(commit_id) AS total_commits,
COUNT(DISTINCT LOWER(TRIM(author))) AS distinct_authors
FROM repo_commits- Unique raw spellings
COUNT(DISTINCT author)sees 'Alice', 'alice', 'Bob', 'bob', 'charlie', ' charlie', 'dana', 'dana ', 'eve' and 'frank': 10 authors.- Unique cleaned names
COUNT(DISTINCT LOWER(TRIM(author)))sees 'alice', 'bob', 'charlie', 'dana', 'eve' and 'frank': 6 authors.
Common follow-up questions
- Which author is behind the most commits? (Group on `LOWER(TRIM(author))`, or 'Bob' and 'bob' split one person's total across two rows.)
- How would you show unique authors per repository? (Add `repo_name` to a `GROUP BY` and keep the same cleaned unique count.)
- How would you measure concentration more precisely than a raw ratio? (Compare the top author's share of commits to the total, or count how many authors cover 80% of commits.)