Invalid Tweets

Invalid Tweets

1. What is this problem about?

The Invalid Tweets interview question is a practical database challenge. You are given a Tweets table containing an ID and the text of the tweet. Your task is to identify all tweets that are "invalid," which in this context means the length of the tweet content is strictly greater than 15 characters.

2. Why is this asked in interviews?

Companies like Twitter, Meta, and Microsoft use the Invalid Tweets coding problem to assess a candidate's basic SQL proficiency. It tests your knowledge of the LENGTH() or CHAR_LENGTH() functions and the use of the WHERE clause for filtering. It evaluation whether you can perform simple data validation tasks in a relational database.

3. Algorithmic pattern used

This problem follows the SQL Filtering and String Measurement pattern.

1. Select: Choose the tweet_id column.
2. Filter: Use the WHERE clause.
3. Measure: Apply the string length function to the content column and compare it to the threshold (15).
4. Note: In most modern databases, LENGTH() returns the number of characters, but in some contexts, it might return bytes. Using CHAR_LENGTH() is often safer for UTF-8 characters.

4. Example explanation

Tweets Table:

• ID 1: "Hello World" (11 chars)
• ID 2: "Learning SQL is fun!" (20 chars)

1. First tweet has length 11. $11 \leq 15$. (Valid).
2. Second tweet has length 20. $20 > 15$. (Invalid). Result: tweet_id 2.

5. Common mistakes candidates make

• Wrong Length Function: Using COUNT() instead of a string length function.
• Off-by-one: Using >= 15 instead of > 15.
• Selecting all columns: Using SELECT * when only the ID was requested.

6. Interview preparation tip

Always check the specific SQL dialect (MySQL, PostgreSQL, T-SQL) mentioned in the interview. While LENGTH() is common, knowing when to use LEN() or CHAR_LENGTH() shows you understand the nuances of different Database interview patterns.