# Between the Spaces > Every pause between words is a vote for length. Count them. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem A chat analytics team is profiling how wordy each message is, treating every space in the content as a divider between words. Return each message's ID alongside its word count. ## Worked solution and explanation ### The trick hiding under a metric Strip the costume and this is counting a single character. There is no SPLIT, no tokenizer, no array function to lean on: the entire problem is the LENGTH minus LENGTH-of-REPLACE plus one idiom, and either you know it cold or you burn interview minutes reinventing it. Candidates who reach for a fancy string-split stall out on portability; the ones who ship count the spaces and add one. The real trap is the plus one and the nulls. Forget the plus one and every single count is off by a word. Forget that NULL content poisons LENGTH and you either crash or invent a number where the honest answer is simply NULL. --- ### 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 **Space-counting word count** ```sql SELECT msg_id, LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1 AS word_count FROM chat_msgs ``` > **Cost Analysis** > > Full scan of 30M rows. LENGTH and REPLACE are O(n) in the length of each content string, so long messages cost more than short ones, but this is an unavoidable per-row transformation. No index helps a computed column over the whole table; the plan is a single sequential pass and stays cheap per row. > **Interviewers Watch For** > > The LENGTH minus LENGTH(REPLACE(...)) idiom is the canonical way to count occurrences of a character without a split function. A strong candidate writes it without hesitating and immediately names the off-by-one: dividers plus one equals words. > **Common Pitfall** > > This formula returns 1 for an empty string, since LENGTH('') is 0 and 0 minus 0 plus 1 is 1. If an empty message should count as zero words, guard it with a CASE. Also remember that any run of consecutive spaces inflates the count by one per extra space. --- ## Common follow-up questions - What if content has multiple consecutive spaces? _(Each extra space inflates the count by one. Normalize repeated spaces to a single space 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/between_the_spaces) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.