Exceeding Tweet Length

Problem Statement

A social media platform enforces a strict character limit on tweets. Any tweet whose content goes over a specific limit is considered invalid and needs to be flagged.

Business context Character limits are a core feature of many social platforms (think of the classic Twitter 280-character rule). Before a post can go live, the system needs a quick, reliable way to check if it breaks the rule, so it can either reject it or flag it for review.

Goal Find every tweet whose content is strictly longer than 15 characters, and return just their tweet_id.

Input A table storing every tweet, along with its text content.

Output A list of tweet_id values, one for every tweet that breaks the 15-character limit.

Table Structure

Table: Tweet

Column Meaning

• tweet_id — a unique identifier for each tweet, and the primary key of this table
• content — the actual text of the tweet. It's restricted to only alphanumeric characters, spaces, and exclamation marks, no other special characters are allowed in this content

Important Notes

• tweet_id is the primary key, so it's guaranteed to be unique.
• The limit is strictly greater than 15, not "15 or more." A tweet with exactly 15 characters is still valid, it only becomes invalid once it crosses past 15.
• Since content is restricted to plain alphanumeric characters, spaces, and exclamation marks, there's no need to worry about accented letters, emojis, or multi-byte characters affecting the character count here, unlike the LENGTH() vs CHAR_LENGTH() distinction covered in the String Functions notes. Still, it's good practice to use CHAR_LENGTH() anyway, since it counts actual characters rather than storage bytes, and behaves consistently regardless of what character set the column ends up using later.
• Spaces and exclamation marks both count toward the character total, they are not ignored or trimmed before measuring length.

Example

Input: Tweet table

Expected Output

CREATE TABLE Tweet (tweet_id INTEGER, content TEXT);
INSERT INTO Tweet VALUES
  (1, 'Hello World!'),
  (2, 'This tweet has more than fifteen characters!');

Explanation

• Tweet 1: "Hello World!" has 12 characters (H-e-l-l-o, space, W-o-r-l-d, !), which is 12, well within the 15-character limit, so it's valid and does not appear in the output.
• Tweet 2: "This tweet has more than fifteen characters!" has 44 characters, far beyond the 15-character limit, so it's invalid and its tweet_id is returned.

Intuition

Think of this like a form field that shows "12/15 characters" as you type, and turns red once you go past the limit. The platform isn't judging what the tweet says, it's purely measuring how long the text is, character by character, and comparing that number against a fixed threshold.

This is one of the simplest possible filtering problems: measure something about each row, then keep only the rows where that measurement crosses a line. There's no need to group rows together, join other tables, or calculate anything complicated, it's a single measurement per row, compared against a single fixed number.

Mental Model

Imagine a bouncer at a club door with a measuring tape, except instead of measuring height, they're measuring the length of a piece of paper each guest is holding.

If the paper is 15 inches long or shorter, the guest gets in, no problem. If the paper is even slightly longer than 15 inches, say 15.001 inches, the bouncer turns them away.

That's exactly what CHAR_LENGTH(content) > 15 does for every single tweet: measure it, and let through only the ones that go over the line.

Thought Process

Before writing SQL, break the problem into its smallest possible pieces:

1. What do I need to measure? The length of the content column, for every tweet.
2. What function measures text length? CHAR_LENGTH() (or LENGTH(), though CHAR_LENGTH is generally the safer, more explicit choice for counting actual characters).
3. What's the actual rule? Strictly greater than 15, so the comparison operator needs to be >, not >=.
4. Do I need any grouping, joining, or ordering? No. This is a simple row-by-row filter, so a plain WHERE clause is all that's needed.
5. What should the output contain? Just tweet_id, nothing else, since that's specifically what the problem asks for.

Once you see it this way, this becomes one of the most direct problems to translate into SQL: measure a column, filter by a threshold, select one column.

Approach

Step 1: Identify what needs to be measured The content column's length is the only thing that matters for this decision.

Step 2: Choose the right length function Use CHAR_LENGTH(content) to count actual characters rather than raw storage bytes.

Step 3: Apply the threshold Filter using CHAR_LENGTH(content) > 15, making sure to use strictly greater than, not greater than or equal to.

Step 4: Select only tweet_id The expected output only needs the tweet_id column, not the content itself.

SQL Query

SELECT tweet_id
FROM Tweet
-- Only keep tweets where the content is strictly longer than 15 characters
WHERE CHAR_LENGTH(content) > 15;

Query Explanation

• SELECT tweet_id — brings back only the tweet's unique ID, since the expected output doesn't need the content itself, just which tweets broke the rule.
• CHAR_LENGTH(content) — counts the number of actual characters in the content column for each row, treating spaces and exclamation marks as regular characters that count toward the total.
• > 15 — the actual filtering condition. Only rows where the character count is strictly more than 15 pass through this WHERE clause. A tweet with exactly 15 characters would not match this condition, and would correctly be excluded from the output.
• FROM Tweet — reads from the Tweet table, checking every row independently, since this decision only depends on a single row's own content, nothing else in the table matters for this check.

Dry Run

Original table

↓

What SQL does first: measure each row's content length

↓

Apply the filter: keep only rows where length > 15

↓

Final output

SQL Execution Flow

FROM Tweet
   ↓
Read one row at a time
   ↓
Calculate CHAR_LENGTH(content) for the row
   ↓
WHERE CHAR_LENGTH(content) > 15
   ↓
Keep the row only if the condition is TRUE
   ↓
SELECT tweet_id
   ↓
Return final output (order does not matter)

Since there's no GROUP BY, JOIN, or ORDER BY in this query, each row is evaluated completely independently, and the WHERE clause runs before SELECT ever picks which columns to show, meaning the length calculation happens on every row first, before the final column list is applied.

Time Complexity

Time Complexity: O(n) Every row needs to be scanned exactly once to calculate its content length and compare it against the threshold. There's no sorting, grouping, or joining involved, so the work grows in a straight line with the number of rows.

Space Complexity: O(1) extra space, beyond the output itself No temporary tables, sorting buffers, or grouping structures are needed. Each row's length is calculated and compared on the fly, without needing to remember anything about other rows.

Why: This is about as simple as a query can get, a single-column measurement compared against a single fixed number, applied independently to every row. There is no dependency between rows at all.

Common Mistakes to Avoid

Mistake 1: Using >= instead of >

Why it happens: "Exceeding the limit" can sometimes be misread as "reaching the limit," causing people to write the condition as greater-than-or-equal instead of strictly greater-than.

Wrong:

WHERE CHAR_LENGTH(content) >= 15

This would incorrectly flag a tweet with exactly 15 characters as invalid, when the problem explicitly says the limit is only broken when length is "strictly greater than" 15.

Correct:

WHERE CHAR_LENGTH(content) > 15

Mistake 2: Using LENGTH() when accented or multi-byte characters might be involved

Why it happens: LENGTH() and CHAR_LENGTH() often return the same result for plain English text, so it's easy to use them interchangeably without noticing a difference.

Why it matters: As covered in the String Functions notes, LENGTH() counts bytes, not visible characters. While this specific problem restricts content to plain alphanumeric characters, spaces, and exclamation marks (which are all single-byte in most encodings), it's still safer to default to CHAR_LENGTH() whenever measuring "how many characters" rather than "how many bytes," especially since real-world systems often loosen these kinds of restrictions over time.

Correct: Default to CHAR_LENGTH() for counting visible characters, reserving LENGTH() specifically for cases where byte size actually matters (like storage or encoding checks).

Mistake 3: Selecting extra columns that weren't asked for

Why it happens: It can feel incomplete to return just an ID without any context about why it was flagged.

Wrong:

SELECT tweet_id, content
FROM Tweet
WHERE CHAR_LENGTH(content) > 15;

While this isn't "wrong" from a business perspective, it does not match the expected output format, which only asks for tweet_id.

Correct: Stick strictly to what the expected output columns are, SELECT tweet_id only, unless the problem explicitly asks for more.

Mistake 4: Forgetting that spaces and punctuation count toward the length

Why it happens: It's tempting to assume only "letters" count, mentally skipping over spaces or the exclamation mark when estimating a tweet's length by eye.

Why it matters: A tweet like "Hello World!" looks short, but every character, including the space between "Hello" and "World," and the exclamation mark, counts toward the total of 12. Miscounting by eye during manual testing can lead to false assumptions about whether the query is working correctly.

Correct: Trust the CHAR_LENGTH() function's count rather than eyeballing the text, especially when writing test cases to verify the query.

Mistake 5: Adding unnecessary complexity, like a CASE expression, when a simple WHERE clause is enough

Why it happens: After working through problems that used CASE extensively (like the earlier Level 1 problems), it can become a reflex to reach for CASE even when it isn't needed.

Unnecessary:

SELECT tweet_id
FROM Tweet
WHERE CASE WHEN CHAR_LENGTH(content) > 15 THEN 1 ELSE 0 END = 1;

This works, but it's needlessly complicated. CASE is meant for producing a labeled value, not simply for filtering rows, a plain boolean condition in WHERE already does that job directly.

Correct: When the task is purely "keep rows that match a condition," and no new column value needs to be created, a direct WHERE clause is always simpler and clearer than wrapping the same logic inside a CASE expression.

Further Questions

• "How would you modify this query to also return the actual content length alongside tweet_id?"
• "How would this query behave differently if the character limit rule changed to 'greater than or equal to' instead of 'strictly greater than'?"
• "What is the difference between LENGTH() and CHAR_LENGTH(), and would it matter for this specific problem?"
• "How would you count how many invalid tweets exist in total, instead of listing each one individually?"
• "How would you extend this to check multiple rules at once, like a character limit AND a minimum word count?"

Similar Problems

• Problems involving CHAR_LENGTH() or LENGTH() for validating data against a character limit, similar to validating usernames, passwords, or form fields
• Simple threshold-based filtering problems, similar in spirit to Example 2 from the CASE Expression concept notes (Boolean Evaluation with CASE), except this one only needs a plain WHERE clause since no labeled output column is required
• Problems combining COUNT() with a WHERE clause to report how many rows break a rule, instead of listing every individual row

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to filter rows based on how long a piece of text is → CHAR_LENGTH(column) inside a WHERE clause
• Rule uses words like "exceeds," "more than," or "over" a limit → use strictly greater than (>), not greater than or equal to (>=)
• Rule uses words like "at least," "minimum," or "reaches" a limit → that's when greater than or equal to (>=) becomes the correct operator instead
• Task is a straightforward row-by-row filter, with no need to create a new labeled column → a plain WHERE clause is enough, no need for CASE
• Text might include non-English characters, accents, or emojis → default to CHAR_LENGTH() over LENGTH() to count actual visible characters rather than storage bytes