# The Weight of Words > Verbose commits. Risky changes? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem An engineering manager wants to test a hunch that authors who write longer commit messages tend to ship riskier changes. For each author, find the average commit message length, the number of commits, and the average lines added per commit, ignoring commits that have no message. Keep only authors with more than two commits, longest average message first. ## Worked solution and explanation ### What this problem really is This is a per-author average dressed up as a code-risk study. The skill being probed: can you tell 'no message' from a zero-length message, and fold one person's case-variant names into a single bucket, before you aggregate? Anyone can write AVG and GROUP BY. The seed hides two traps: some rows carry an empty string ('') next to the true NULLs, and the same author shows up as both 'bob' and 'Bob'. Filter only the NULLs and the empty commits count as length 0, dragging every average down. Group by the raw author and 'alice' and 'Alice' split into two people who each miss the two-commit threshold, so both vanish from the result. --- ### Break it down #### Step 1: Drop the missing and the empty 'Skip any commits that have no message' means two kinds of row, not one. WHERE message IS NOT NULL AND LENGTH(message) > 0 drops both the NULL author (Bob, commit 3427) and the empty string (bob, commit 3549). Filtering only IS NOT NULL leaves the empty row in, and AVG(LENGTH(message)) then averages in a spurious 0. #### Step 2: One bucket per person The prose says 'for each author', but the data stores case-variant duplicates: bob/Bob, alice/Alice. GROUP BY LOWER(author) collapses each person into one group, so their commits count together and clear the HAVING COUNT(*) > 2 gate. Group by the raw string and you get four half-populated groups that the threshold silently deletes. #### Step 3: Aggregate and sort Now aggregate the surviving rows per author: AVG(LENGTH(message)) for average length, COUNT(*) for total commits, AVG(added) for average lines added. ROUND each to two decimals, then ORDER BY avg_msg_len DESC with author as the tie-breaker so equal averages sort predictably. --- ### The solution **Per-author aggregate with a NULL-and-empty guard** ```sql SELECT LOWER(author) AS author, ROUND(AVG(LENGTH(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 AND LENGTH(message) > 0 GROUP BY LOWER(author) HAVING COUNT(*) > 2 ORDER BY avg_msg_len DESC, author; ``` > **The empty string is not NULL** > > The single most common miss here is treating 'no message' as 'message IS NULL' and stopping there. An empty string is not NULL: it passes the IS NOT NULL check but has LENGTH 0, so it inflates COUNT(*) and pulls AVG(LENGTH(message)) toward zero. You need both conditions in the WHERE, before the GROUP BY runs. > **Interviewers watch for** > > Naming the output grain (one row per person, case-folded) before writing the GROUP BY is the tell that separates a candidate who reasons about data from one who pattern-matches syntax. Spotting the alice/Alice duplication in the sample rows without being told is exactly the instinct the interviewer is looking for. > **Why the plan stays cheap** > > Across 10M rows this is one full scan feeding a hash aggregate, with no join to blow up. The WHERE runs before grouping, so the NULL and empty-message rows never reach the aggregate. author has roughly 5000 distinct values, so the grouped output is tiny and the final ORDER BY sorts only a handful of rows. An index on author would not help a full-table aggregate: the plan is scan-bound by design. --- ## Common follow-up questions - If you had filtered only message IS NOT NULL, which authors' averages would shift, and by how much? _(Tests whether the candidate knows AVG ignores NULLs but still averages in zero-length strings.)_ - How would you handle authors who differ only by trailing whitespace or accented characters, not just letter case? _(Tests data-quality awareness around case-variant and whitespace-variant author names.)_ - Why does the two-commit minimum go in HAVING rather than WHERE, and what would break if you moved it? _(Tests understanding of where a threshold belongs relative to aggregation.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_weight_of_words) - [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.