The Weight of Words

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

In repo_commits a missing message is an export gap rather than a commit, so those rows stay out, while a blank message is a real commit that simply said nothing. An engineering manager wants, for each author in each repository with more than two commits, the average message length, the commit count and the average lines added, both averages rounded to two decimals, longest average message first. Letter case or stray spaces around a name never make a different person, and spaces around a message are not part of its length.

Verbose commits. Risky changes?

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

This is a per-group average dressed up as a code-risk study, and the real question is which rows every measure is computed over. Anyone can write AVG and GROUP BY. The trap is that the measures disagree about missing data: AVG(LENGTH(message)) quietly skips the 16 rows whose message is NULL, while COUNT(*) and AVG(added) happily include them. Skip the filter and your length average describes one set of commits while your count and lines-added average describe another, and a pair sitting near the two-commit bar can survive on commits nobody measured.

The second trap is identity. The author column holds 'alice' and 'Alice', 'bob' and 'Bob', plus ' charlie' and 'dana ' with stray spaces. Group on the raw string and one person becomes several partial authors, each with its own short count.

Break it down

Step 1: Remove the rows that are not commits

A missing message is an export gap, so WHERE message IS NOT NULL drops it before anything is counted. A blank message stays: it is a real commit with length 0, and it counts. Filtering on message <> '' here would wrongly throw away real commits.

Step 2: Fold each person into one name

LOWER(TRIM(author)) makes 'Alice', 'alice' and ' alice' the same key. Group on that expression together with repo_name, so every commit by one person in one repository lands in one bucket before the HAVING COUNT(*) > 2 bar judges it.

Step 3: Measure what the message actually says

' Add caching layer ' carries padding that is not part of the message, so the length is LENGTH(TRIM(message)). Then ROUND(AVG(...), 2) for both averages and COUNT(*) for commits, sorted by avg_msg_len descending. Pairs tied on that average may come back in any order.

The solution

SELECT
    LOWER(TRIM(author)) AS author,
    repo_name,
    ROUND(AVG(LENGTH(TRIM(message))), 2) AS avg_msg_len,
    COUNT(*) AS commit_count,
    ROUND(AVG(added), 2) AS avg_lines_added
FROM repo_commits
WHERE message IS NOT NULL
GROUP BY LOWER(TRIM(author)), repo_name
HAVING COUNT(*) > 2
ORDER BY avg_msg_len DESC;

No filter

AVG(LENGTH(message)) averages over messaged rows only, but COUNT(*) and AVG(added) include the missing-message rows. Three columns, two different populations.

Filter first

WHERE message IS NOT NULL removes the gap rows once, so the length average, the count and the lines-added average all describe the same commits.

Common follow-up questions

• If you dropped the `WHERE`, which columns would change and which would not? (Tests knowing that `AVG` skips `NULL` while `COUNT(*)` does not.)
• How would you report how many commits per author had a blank message? (Tests conditional counting alongside the main aggregate.)
• Why does the two-commit bar sit in `HAVING` and not `WHERE`? (Tests where a threshold on a group size belongs.)