February Hashtag Trend Leaderboard

Hard

A social insights team wants to publish a weekly trend panel showing the most-mentioned hashtags in February 2024. Table: Tweets user_id: author identifier tweet_id: unique tweet identifier tweet_date: post date tweet: raw tweet text Task: Compute the top 3 hashtags by total mention volume during February 2024. Extraction rules: A hashtag starts with '#'. It must be followed by at least one character in [A-Za-z0-9_]. The hashtag ends at the first character not in [A-Za-z0-9_]. Matching is case-sensitive (#AI and #ai are different hashtags). Every hashtag occurrence counts, including multiple mentions in the same tweet. Date rule: Count only tweets whose tweet_date is in [2024-02-01, 2024-02-29]. Output requirements: Return exactly columns: hashtag, count Sort by count DESC, hashtag DESC Return only the top 3 rows Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                                                |
|---------|----------|-------------|------------------------------------------------------|
| 101     | 9001     | 2024-02-01  | Kickoff! #Pulse #Daily                               |
| 102     | 9002     | 2024-02-03  | Another win for #Pulse and #Launch                   |
| 103     | 9003     | 2024-02-04  | Team sync #Launch #Build                             |
| 104     | 9004     | 2024-02-07  | Product update #Pulse                                |
| 105     | 9005     | 2024-02-10  | Deep dive #Build #Launch                             |
| 106     | 9006     | 2024-02-12  | Community thread #Daily                              |

Output

[
  {"hashtag":"#Pulse","count":3},
  {"hashtag":"#Launch","count":3},
  {"hashtag":"#Daily","count":2}
]

Explanation

#Pulse and #Launch tie on count 3, so hashtag descending puts #Pulse before #Launch.

Example 2

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                                              |
|---------|----------|-------------|----------------------------------------------------|
| 201     | 9101     | 2024-02-11  | #Edge! #Edge, #Edge?                               |
| 202     | 9102     | 2024-02-11  | Prefixabc#Core and ##Skip then #Core              |
| 203     | 9103     | 2024-02-12  | #A_B #A_B #A_B                                     |
| 204     | 9104     | 2024-02-13  | noise # #! #-bad                                   |

Output

[
  {"hashtag":"#Edge","count":3},
  {"hashtag":"#A_B","count":3},
  {"hashtag":"#Core","count":2}
]

Explanation

Punctuation terminates hashtags, invalid bare markers are ignored, and repeated mentions in one tweet all count.

Example 3

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                         |
|---------|----------|-------------|-------------------------------|
| 301     | 9201     | 2024-01-31  | #IgnoreJan #IgnoreJan         |
| 302     | 9202     | 2024-03-01  | #IgnoreMar                    |
| 303     | 9203     | 2024-02-20  | no hashtag text here          |

Output

[]

Explanation

Only February rows are in scope, and the one February tweet has no valid hashtag.

Constraints

1 <= number of rows in Tweets <= 250000
tweet_id values are unique
1 <= user_id, tweet_id <= 2000000000
tweet_date is a valid date string
tweet is a UTF-8 string with practical social-post length (<= 280 chars recommended)
A valid hashtag matches #[A-Za-z0-9_]+
Hashtag matching is case-sensitive
Count every occurrence, including multiple occurrences within one tweet
Only rows from 2024-02-01 to 2024-02-29 are considered
Return exactly columns [hashtag, count]
Sort by count DESC, hashtag DESC
Return at most 3 rows
SQL submissions must use SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with columns [hashtag, count]

Preparing your editor. You can read the problem while it loads.

February Hashtag Trend Leaderboard

Hard

A social insights team wants to publish a weekly trend panel showing the most-mentioned hashtags in February 2024. Table: Tweets user_id: author identifier tweet_id: unique tweet identifier tweet_date: post date tweet: raw tweet text Task: Compute the top 3 hashtags by total mention volume during February 2024. Extraction rules: A hashtag starts with '#'. It must be followed by at least one character in [A-Za-z0-9_]. The hashtag ends at the first character not in [A-Za-z0-9_]. Matching is case-sensitive (#AI and #ai are different hashtags). Every hashtag occurrence counts, including multiple mentions in the same tweet. Date rule: Count only tweets whose tweet_date is in [2024-02-01, 2024-02-29]. Output requirements: Return exactly columns: hashtag, count Sort by count DESC, hashtag DESC Return only the top 3 rows Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                                                |
|---------|----------|-------------|------------------------------------------------------|
| 101     | 9001     | 2024-02-01  | Kickoff! #Pulse #Daily                               |
| 102     | 9002     | 2024-02-03  | Another win for #Pulse and #Launch                   |
| 103     | 9003     | 2024-02-04  | Team sync #Launch #Build                             |
| 104     | 9004     | 2024-02-07  | Product update #Pulse                                |
| 105     | 9005     | 2024-02-10  | Deep dive #Build #Launch                             |
| 106     | 9006     | 2024-02-12  | Community thread #Daily                              |

Output

[
  {"hashtag":"#Pulse","count":3},
  {"hashtag":"#Launch","count":3},
  {"hashtag":"#Daily","count":2}
]

Explanation

#Pulse and #Launch tie on count 3, so hashtag descending puts #Pulse before #Launch.

Example 2

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                                              |
|---------|----------|-------------|----------------------------------------------------|
| 201     | 9101     | 2024-02-11  | #Edge! #Edge, #Edge?                               |
| 202     | 9102     | 2024-02-11  | Prefixabc#Core and ##Skip then #Core              |
| 203     | 9103     | 2024-02-12  | #A_B #A_B #A_B                                     |
| 204     | 9104     | 2024-02-13  | noise # #! #-bad                                   |

Output

[
  {"hashtag":"#Edge","count":3},
  {"hashtag":"#A_B","count":3},
  {"hashtag":"#Core","count":2}
]

Explanation

Punctuation terminates hashtags, invalid bare markers are ignored, and repeated mentions in one tweet all count.

Example 3

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                         |
|---------|----------|-------------|-------------------------------|
| 301     | 9201     | 2024-01-31  | #IgnoreJan #IgnoreJan         |
| 302     | 9202     | 2024-03-01  | #IgnoreMar                    |
| 303     | 9203     | 2024-02-20  | no hashtag text here          |

Output

[]

Explanation

Only February rows are in scope, and the one February tweet has no valid hashtag.

Constraints

1 <= number of rows in Tweets <= 250000
tweet_id values are unique
1 <= user_id, tweet_id <= 2000000000
tweet_date is a valid date string
tweet is a UTF-8 string with practical social-post length (<= 280 chars recommended)
A valid hashtag matches #[A-Za-z0-9_]+
Hashtag matching is case-sensitive
Count every occurrence, including multiple occurrences within one tweet
Only rows from 2024-02-01 to 2024-02-29 are considered
Return exactly columns [hashtag, count]
Sort by count DESC, hashtag DESC
Return at most 3 rows
SQL submissions must use SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with columns [hashtag, count]