Invalid Tweets II - Solution & Explanation

Problem Overview: Each row in the Tweets table contains a tweet's text. A tweet becomes invalid if it includes more than three @ mentions. The task is to return the tweet_id values for tweets where the number of @ characters exceeds three.

Approach 1: LENGTH() + REPLACE() Character Counting (O(n * m) time, O(1) space)

The key observation: the number of occurrences of a character can be computed using string length differences. Calculate LENGTH(content), then remove all @ characters with REPLACE(content, '@', ''). The difference between these lengths equals the number of mentions. If the difference is greater than three, the tweet is invalid. This works because REPLACE removes every matching character, so the length shrink directly reveals the count.

In SQL, the query filters rows using LENGTH(content) - LENGTH(REPLACE(content, '@', '')) > 3. The database scans each tweet string once to compute both lengths. No additional data structures are required, making the approach efficient and easy to implement directly in a query.

The same logic translates cleanly to Python. Compute the original string length and subtract the length after replacing @ with an empty string. The difference gives the mention count. This pattern is common when solving string counting problems where built‑in counting utilities are unavailable.

This approach is preferred in database problems because it avoids loops or joins and keeps the entire computation inside a single query. It leverages native SQL string functions, which are highly optimized inside database engines.

Recommended for interviews: The LENGTH() - LENGTH(REPLACE()) pattern is the expected solution. It demonstrates that you understand how to count character occurrences using SQL string functions without procedural logic. Interviewers often look for this trick because it converts a seemingly iterative problem into a single declarative filter condition.