Skip to content

LeetCode 1683 - Invalid Tweets

Database Language: MySQL

Difficulty: ⭐

Problem Description

Input

Table: Tweets

Column Name Type
tweet_id int
content varchar

tweet_id is the primary key (column with unique VALUES) for this table.

This table contains all the tweets in a social media app.

Requirement

Write a solution to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

Input

Tweets table:

tweet_id content
1 Leetcode is fun
2 Questions in Leetcode are hard.
Output
tweet_id
2
Explanation

Tweet 1 has length = 15. It is a valid tweet.

Tweet 2 has length = 31. It is an invalid tweet.

SQL Schema

CREATE TABLE IF NOT EXISTS Tweets (tweet_id int, content varchar(50));

TRUNCATE TABLE Tweets;

INSERT INTO Tweets (tweet_id, content) VALUES ('1', 'Leetcode is fun');
INSERT INTO Tweets (tweet_id, content) VALUES ('2', 'Questions in Leetcode are hard.');

Solutions

There are two string functions in MySQL that returns the length of a string, namely CHAR_LENGTH and LENGTH:

CHAR_LENGTH(str)

Returns the length of the string str, measured in code points. A multibyte character counts as a single code point. This means that, for a string containing two 3-byte characters, LENGTH() returns 6, whereas CHAR_LENGTH() returns 2.

LENGTH(str)

Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5. Returns NULL if str is NULL.

Since the data type of the content column is defined as VARCHAR, any of these 2 string functions can be used to identify invalid Tweets as these functions will return the same output.

To return the invalid Tweets where the number of characters in the content column is more than 15 characters using the CHAR_LENGTH string function, the query will be as follows:

SELECT tweet_id
FROM Tweets
WHERE CHAR_LENGTH(content) > 15

Here's the fastest time for this query:

  • Runtime: 500ms
  • Beats: 90.49% as of August 5, 2024

Similarly, here's the query that returns invalid Tweets using the LENGTH string function:

SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15

And here's the fastest time for this query:

  • Runtime: 502ms
  • Beats: 89.62% as of August 5, 2024

As can be seen, the fastest time for both queries are near each other. This is because the query execution plan generated by MySQL for both queries are similar as well:

CHAR_LENGTH

-> Filter: (char_length(tweets.content) > 15)  (cost=0.45 rows=2) (actual time=0.028..0.029 rows=1 loops=1)
    -> Table scan on Tweets  (cost=0.45 rows=2) (actual time=0.016..0.020 rows=2 loops=1)

LENGTH

-> Filter: (length(tweets.content) > 15)  (cost=0.45 rows=2) (actual time=0.026..0.027 rows=1 loops=1)
    -> Table scan on Tweets  (cost=0.45 rows=2) (actual time=0.020..0.025 rows=2 loops=1)