Between the Spaces
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
- L3
- Asked in
Problem
Return each message's ID and its word count from chat_msgs, for an analyst profiling how wordy the chat is who treats every space in the content as a divider between words.
Every pause between words is a vote for length. Count them.
Tables
chat_msgs
Columns: msg_id INTEGER, channel TEXT, sender_id INTEGER, content TEXT, msg_type TEXT, sent_at TEXT, edited INTEGER, reply_to INTEGER
| msg_id | channel | sender_id | content | msg_type | sent_at | edited | reply_to |
|---|---|---|---|---|---|---|---|
| 147 | #engineering | 197 | Anyone seeing increased latency? | file | 2026-02-02 01:07:00 | 0 | NULL |
| 194 | #incidents | 294 | PR #412 ready for review | reaction | 2026-03-03 02:14:00 | 0 | NULL |
| 241 | #random | 391 | Dashboard is down | thread_reply | 2026-04-04 03:21:00 | 0 | NULL |
| 288 | #deployments | 488 | Fixed the flaky test | system | 2026-05-05 04:28:00 | 0 | NULL |
| 335 | #data-team | 585 | Merged the migration | Text | 2026-06-06 05:35:00 | 0 | NULL |
| 382 | #general | 682 | NULL | FILE | 2026-07-07 06:42:00 | 0 | NULL |
| 429 | #engineering | 779 | LGTM | text | 2026-08-08 07:49:00 | 0 | NULL |
| 476 | #incidents | 876 | 👍 | file | 2026-09-09 08:56:00 | 0 | 5 |
| 523 | #random | 973 | reaction | 2026-01-10 09:03:00 | 0 | NULL | |
| 570 | #deployments | 1070 | Can someone check the pipeline? | thread_reply | 2026-02-11 10:10:00 | 0 | NULL |
Expected output
| msg_id | word_count |
|---|---|
| 147 | 4 |
| 194 | 5 |
| 241 | 3 |
| 382 | NULL |
| 429 | 1 |
Worked solution and explanation
The trick hiding under a metric
Strip the costume and this is counting one character. Anyone can measure a string; the skill is turning the spaces in content into words, and the cheapest way is to measure how much the string shrinks when the spaces are stripped out. The real trap is the plus one and the nulls. Forget the plus one and every count is off by a word: 'Dashboard is down' has two spaces and three words. Forget that a missing body makes LENGTH return NULL, or paper over it with a default, and you invent a number where the honest answer is unknown.
Break down the requirements
Step 1: Turn spaces into a word count
Words are separated by spaces, so the number of words is the number of spaces plus one. Count the spaces by measuring how many characters vanish when you strip them: LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) is the space count, and adding 1 turns dividers into words.
Step 2: Return message ID and word count
Select msg_id next to the computed expression aliased as word_count. Because NULL content flows through LENGTH untouched, those rows come back as NULL for free, which is exactly what the expected output shows.
The solution
SELECT
msg_id,
LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1 AS word_count
FROM chat_msgsCommon follow-up questions
- What if `content` has multiple consecutive spaces? (Each extra space inflates the count by one. Collapse repeated spaces to one with `REGEXP_REPLACE` before counting.)
- How would you find the average word count per channel? (`GROUP BY channel` with `AVG` over the same word-count expression.)
- What if `content` is NULL for some messages? (`LENGTH(NULL)` is NULL, so the row returns NULL. Wrap with `COALESCE` if you want a numeric default instead.)