Long Messages

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Problem

The code quality team wants to spotlight commits whose messages carry real detail, measured against the typical message on the repo. Among commits that actually have a message, ignoring any surrounding spaces, find those longer than the average message length and return the author, the message, and its length, longest first.

Some commit messages tell a novel.

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 really tests

This is a data-derived baseline comparison wearing a length-filter costume. 'Long' is not a number you pick: it means longer than the typical real message, so the bar comes from the data and every commit is held up against it. Anyone can write the length filter. The trap is the baseline. A commit with no message shows up two ways in repo_commits: 16 rows hold NULL and 16 hold an empty string. AVG skips the NULLs on its own, but an empty string has length 0 and walks straight into the average, dragging the bar from 14.46 to about 13.2. That is just low enough for the thirty 14-character messages like 'Add unit tests' to sneak over it, and the list grows from 76 commits to 106.

Walking the requirements

Step 1: Trim once, at the source

Build one row per commit in commit_lengths, with TRIM(message) as the message and LENGTH(TRIM(message)) as message_length. The statement says surrounding spaces do not count, and the seed means it: commit 3305 stores ' Add caching layer ' with a space on each side. Left raw, it measures 19 instead of 17 and comes back as a padded string that no other 'Add caching layer' row matches.

Step 2: Average only the real messages

The bar is AVG(message_length) over the rows where NULLIF(message, '') IS NOT NULL. Because message is already trimmed in the CTE, an all-spaces message is empty here too, so one predicate removes both kinds of missing. Over the 168 real messages the average is 14.46.

Step 3: Compare each commit to the bar

Keep the rows whose message_length beats the scalar subquery. With the bar at 14.46, the 14-character messages stay out and the 17, 18, 19 and 20-character ones come through. Missing messages drop out by themselves: NULL > 14.46 is not true, and neither is 0 > 14.46.

Step 4: Sort by length, nothing more

ORDER BY message_length DESC is the whole order the question asks for. Plenty of commits share a length (sixteen messages are 20 characters), and the grader only checks that lengths never go up as you read down, so commits tied on length may come back in any order. A tiebreaker answers a question nobody asked.

The solution

WITH commit_lengths AS (
    SELECT
        author,
        TRIM(message) AS message,
        LENGTH(TRIM(message)) AS message_length
    FROM repo_commits
)
SELECT
    author,
    message,
    message_length
FROM commit_lengths
WHERE message_length > (
        SELECT AVG(message_length)
        FROM commit_lengths
        WHERE NULLIF(message, '') IS NOT NULL
    )
ORDER BY message_length DESC;

Naive: average over the raw column

AVG(LENGTH(message)) over every row. The 16 empty strings enter at length 0, the bar drops from 14.46 to about 13.2, and the 14-character messages clear it: 106 rows instead of 76.

Correct: average over real content

The average of LENGTH(TRIM(message)) over rows where NULLIF(TRIM(message), '') IS NOT NULL. Only the 168 real messages set the bar, and exactly 76 commits beat it.

Common follow-up questions

• A message is 'a b' with ten spaces in the middle. How does it count under your query, and should it? (Tests whether they know `TRIM` strips only the ends, not interior whitespace.)
• How would you report, per author, how many above-average messages they wrote, given that one person appears as 'Alice' and 'alice'? (Tests aggregating on top of the filtered set and normalizing a dirty grouping key.)
• At 3M rows the subquery scans the whole table and `LENGTH(TRIM(message))` cannot use an index. How would you make this cheaper? (Tests plan intuition: a persisted computed length, or a baseline computed once per load.)