Invalid Tweets II - Problem
Given a table Tweets containing all tweets in a social media app, find all invalid tweets.
A tweet is considered invalid if it meets any of the following criteria:
- It exceeds 140 characters in length
- It has more than 3 mentions (words starting with @)
- It includes more than 3 hashtags (words starting with #)
Return the result table ordered by tweet_id in ascending order.
Table Schema
Tweets
| Column Name | Type | Description |
|---|---|---|
tweet_id
PK
|
int | Primary key, unique identifier for each tweet |
content
|
varchar | The text content of the tweet |
Primary Key: tweet_id
Note: Each row represents a single tweet in the social media app
Input & Output
Example 1 — Multiple Validation Rules
Input Table:
| tweet_id | content |
|---|---|
| 1 | Traveling, exploring, and living my best life @JaneSmith @SaraJohnson @LisaTaylor @MikeBrown #Foodie #Fitness #Learning |
| 2 | Just had the best dinner with friends! #Foodie #Friends #Fun |
| 4 | Working hard on my new project #Work #Goals #Productivity #Fun |
Output:
| tweet_id |
|---|
| 1 |
| 4 |
💡 Note:
Tweet 1 contains 4 mentions (@JaneSmith, @SaraJohnson, @LisaTaylor, @MikeBrown) which exceeds the limit of 3. Tweet 4 contains 4 hashtags (#Work, #Goals, #Productivity, #Fun) which exceeds the limit of 3. Tweet 2 is valid with only 3 hashtags.
Example 2 — Character Length Violation
Input Table:
| tweet_id | content |
|---|---|
| 5 | This is a very long tweet that exceeds the 140 character limit by containing way too much text and going on and on about nothing important just to make it invalid |
| 6 | Short tweet #OK |
Output:
| tweet_id |
|---|
| 5 |
💡 Note:
Tweet 5 exceeds 140 characters in length, making it invalid. Tweet 6 is short and valid with only 1 hashtag.
Example 3 — All Valid Tweets
Input Table:
| tweet_id | content |
|---|---|
| 7 | Good morning! @friend1 @friend2 #beautiful #day |
| 8 | Simple tweet with no violations |
Output:
| tweet_id |
|---|
💡 Note:
Both tweets are valid: Tweet 7 has 2 mentions and 2 hashtags (within limits), and Tweet 8 has no mentions or hashtags. No results returned.
Constraints
-
1 ≤ tweet_id ≤ 1000 -
0 ≤ content.length ≤ 1000 - Content may contain letters, digits, spaces, and special characters
- Mentions start with '@' followed by alphanumeric characters
- Hashtags start with '#' followed by alphanumeric characters
Visualization
Tap to expand
Understanding the Visualization
1
Input Analysis
Examine tweet content for violations
2
String Validation
Apply LENGTH() and character counting
3
Filter Results
Return invalid tweet IDs
Key Takeaway
🎯 Key Insight: Use string manipulation functions to efficiently validate content against multiple criteria in a single query
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code