by tweet_id in ascending order.

Solution

Method 1 – String Functions and Aggregation

Intuition

We need to check three conditions for each tweet: length, number of mentions, and number of hashtags. We can use string functions to count mentions and hashtags, and filter accordingly.

Approach

For each tweet, check if the length of content is greater than 140.
Count the number of mentions (occurrences of '@') in content.
Count the number of hashtags (occurrences of '#') in content.
Select tweets where any of the above conditions is true.
Return the result ordered by tweet_id ascending.

Code

[{"language":"MySQL","code":"SELECT tweet_id\nFROM Tweets\nWHERE CHAR_LENGTH(content) > 140\n   OR LENGTH(content) - LENGTH(REPLACE(content, '@', '')) > 3\n   OR LENGTH(content) - LENGTH(REPLACE(content, '#', '')) > 3\nORDER BY tweet_id ASC;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">SELECT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> tweet_id</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">FROM</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> Tweets</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">WHERE</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> CHAR_LENGTH(content) </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">></span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\"> 140</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">   OR</span><span style=\"color:#D73A49;--shiki-dark:#F97583\"> LENGTH</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">(content) </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">-</span><span style=\"color:#D73A49;--shiki-dark:#F97583\"> LENGTH</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">(</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">REPLACE</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">(content, </span><span style=\"color:#032F62;--shiki-dark:#9ECBFF\">'@'</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">,

Related Tags