Find Trending Hashtags II - Problem
Given a Tweets table containing user tweets from February 2024, find the top 3 trending hashtags.
Each tweet may contain multiple hashtags (words starting with #). You need to:
- Extract all hashtags from tweet text
- Count frequency of each hashtag
- Return top 3 hashtags ordered by count (descending), then by hashtag name (descending)
Table: Tweets
| Column Name | Type |
|---|---|
| user_id | int |
| tweet_id | int |
| tweet_date | date |
| tweet | varchar |
tweet_id is the primary key. All tweet_date values are valid dates in February 2024.
Table Schema
Tweets
| Column Name | Type | Description |
|---|---|---|
user_id
|
int | User identifier |
tweet_id
PK
|
int | Unique tweet identifier (primary key) |
tweet_date
|
date | Date when tweet was posted (February 2024) |
tweet
PK
|
varchar | Tweet content text that may contain hashtags |
Primary Key: tweet_id
Note: All tweet_date values are guaranteed to be in February 2024
Input & Output
Example 1 — Multiple Hashtags
Input Table:
| user_id | tweet_id | tweet_date | tweet |
|---|---|---|---|
| 1 | 1 | 2024-02-01 | Loving the new #AI features! #tech #innovation |
| 2 | 2 | 2024-02-02 | Just tried #AI for coding. Mind blown! #tech |
| 3 | 3 | 2024-02-03 | The future is here with #AI technology #future |
| 4 | 4 | 2024-02-04 | Working on #innovation projects today |
Output:
| hashtag | hashtag_count |
|---|---|
| #AI | 3 |
| #tech | 2 |
| #innovation | 2 |
💡 Note:
#AI appears 3 times (tweets 1,2,3), #tech appears 2 times (tweets 1,2), and #innovation appears 2 times (tweets 1,4). Since #tech and #innovation both have count 2, they're ordered by hashtag name descending, so #tech comes before #innovation.
Example 2 — Equal Counts Ordering
Input Table:
| user_id | tweet_id | tweet_date | tweet |
|---|---|---|---|
| 1 | 1 | 2024-02-01 | Love #python programming #coding |
| 2 | 2 | 2024-02-02 | Working with #java today #coding |
| 3 | 3 | 2024-02-03 | Learning #javascript fundamentals |
Output:
| hashtag | hashtag_count |
|---|---|
| #coding | 2 |
| #python | 1 |
| #javascript | 1 |
💡 Note:
#coding appears 2 times. The other hashtags appear once each. Among equal counts, we order by hashtag name descending: #python > #javascript > #java alphabetically reversed.
Constraints
-
1 ≤ user_id ≤ 1000 -
1 ≤ tweet_id ≤ 10000 -
tweet_dateis a valid date in February 2024 -
1 ≤ tweet.length ≤ 280 -
Hashtags start with
#and contain alphanumeric characters
Visualization
Tap to expand
Understanding the Visualization
1
Input
Tweets table with text content
2
Extract
Parse hashtags from tweet text
3
Count
Group and count hashtag frequencies
Key Takeaway
🎯 Key Insight: Use regex string splitting with GROUP BY aggregation for text pattern extraction and counting
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code