Find Trending Hashtags - Problem
Given a table Tweets containing user tweets with hashtags, find the top 3 trending hashtags in February 2024.
Requirements:
- Each tweet contains exactly one hashtag
- Filter tweets from February 2024 only
- Return top 3 hashtags by count
- Order results by hashtag count (descending), then by hashtag name (descending)
Table Schema
Tweets
| Column Name | Type | Description |
|---|---|---|
user_id
|
int | ID of the user who posted the tweet |
tweet_id
PK
|
int | Primary key, unique identifier for each tweet |
tweet_date
|
date | Date when the tweet was posted |
tweet
PK
|
varchar | Content of the tweet containing one hashtag |
Primary Key: tweet_id
Note: Each tweet contains exactly one hashtag that needs to be extracted and counted
Input & Output
Example 1 — Basic Hashtag Counting
Input Table:
| user_id | tweet_id | tweet_date | tweet |
|---|---|---|---|
| 1 | 101 | 2024-02-01 | Love this #AI trend! |
| 2 | 102 | 2024-02-02 | Working on #AI project |
| 3 | 103 | 2024-02-03 | Excited about #tech |
| 4 | 104 | 2024-02-04 | New #blockchain news |
| 5 | 105 | 2024-02-05 | Learning #AI today |
| 6 | 106 | 2024-01-30 | January #AI post |
Output:
| hashtag | hashtag_count |
|---|---|
| #AI | 3 |
| #tech | 1 |
| #blockchain | 1 |
💡 Note:
From the February 2024 tweets, #AI appears 3 times (most frequent), while #tech and #blockchain each appear once. The January tweet is excluded. Results are ordered by count DESC, then hashtag DESC, so #tech comes before #blockchain alphabetically in descending order.
Example 2 — Tie Breaking by Hashtag Name
Input Table:
| user_id | tweet_id | tweet_date | tweet |
|---|---|---|---|
| 1 | 201 | 2024-02-01 | Love #python coding |
| 2 | 202 | 2024-02-02 | Great #java tutorial |
| 3 | 203 | 2024-02-03 | Amazing #react framework |
| 4 | 204 | 2024-02-04 | Building with #nodejs |
Output:
| hashtag | hashtag_count |
|---|---|
| #react | 1 |
| #python | 1 |
| #nodejs | 1 |
💡 Note:
All hashtags have the same count (1), so they are ordered by hashtag name in descending order: #react, #python, #nodejs, #java. Only the top 3 are returned.
Constraints
-
1 ≤ user_id ≤ 10000 -
tweet_idis unique for each row -
tweet_dateis in YYYY-MM-DD format -
Each
tweetcontains exactly one hashtag starting with# - Hashtag can contain letters, numbers, and underscores
Visualization
Tap to expand
Understanding the Visualization
1
Filter
Select February 2024 tweets only
2
Extract
Extract hashtags from tweet text
3
Aggregate
Group by hashtag and count occurrences
4
Rank
Order by count and get top 3
Key Takeaway
🎯 Key Insight: Use date filtering + string extraction + GROUP BY to analyze social media trends efficiently
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code