Find Trending Hashtags - Problem
Find Trending Hashtags
You're analyzing social media data to identify trending hashtags during a specific time period. Given a table of tweets, your goal is to find the top 3 most popular hashtags that were tweeted in February 2024.
Table Schema:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| tweet_id | int |
| tweet_date | date |
| tweet | varchar |
+-------------+---------+
Key Details:
•
• Each tweet contains exactly one hashtag
• You need to count hashtag occurrences in February 2024 only
• Return results ordered by: count (descending), then hashtag name (descending)
• Show only the top 3 trending hashtags
You're analyzing social media data to identify trending hashtags during a specific time period. Given a table of tweets, your goal is to find the top 3 most popular hashtags that were tweeted in February 2024.
Table Schema:
Tweets+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| tweet_id | int |
| tweet_date | date |
| tweet | varchar |
+-------------+---------+
Key Details:
•
tweet_id is the primary key• Each tweet contains exactly one hashtag
• You need to count hashtag occurrences in February 2024 only
• Return results ordered by: count (descending), then hashtag name (descending)
• Show only the top 3 trending hashtags
Input & Output
example_1.sql — Basic Case
$
Input:
Tweets table:
+--------+----------+------------+---------------------------+
| user_id| tweet_id | tweet_date | tweet |
+--------+----------+------------+---------------------------+
| 1 | 101 | 2024-02-01 | Beautiful weather! #sunny |
| 2 | 102 | 2024-02-02 | Working from home #remote |
| 3 | 103 | 2024-02-03 | Another sunny day #sunny |
| 4 | 104 | 2024-02-04 | Remote work rocks #remote |
| 5 | 105 | 2024-02-05 | Valentine's Day! #love |
+--------+----------+------------+---------------------------+
›
Output:
+----------+-------+
| hashtag | count |
+----------+-------+
| #sunny | 2 |
| #remote | 2 |
| #love | 1 |
+----------+-------+
💡 Note:
#sunny and #remote each appear twice, #love appears once. When counts are equal, we sort by hashtag name in descending order, so #sunny comes before #remote.
example_2.sql — Edge Case with Dates
$
Input:
Tweets table:
+--------+----------+------------+---------------------------+
| user_id| tweet_id | tweet_date | tweet |
+--------+----------+------------+---------------------------+
| 1 | 201 | 2024-01-31 | January tweet #winter |
| 2 | 202 | 2024-02-01 | February starts #spring |
| 3 | 203 | 2024-02-29 | Last day of Feb #spring |
| 4 | 204 | 2024-03-01 | March begins #flowers |
+--------+----------+------------+---------------------------+
›
Output:
+----------+-------+
| hashtag | count |
+----------+-------+
| #spring | 2 |
+----------+-------+
💡 Note:
Only tweets from February 2024 are counted. #winter (January) and #flowers (March) are excluded. #spring appears twice in February.
example_3.sql — More Than 3 Hashtags
$
Input:
Tweets table:
+--------+----------+------------+---------------------------+
| user_id| tweet_id | tweet_date | tweet |
+--------+----------+------------+---------------------------+
| 1 | 301 | 2024-02-14 | Happy Valentine #love |
| 2 | 302 | 2024-02-14 | Valentine dinner #food |
| 3 | 303 | 2024-02-15 | Working hard #work |
| 4 | 304 | 2024-02-16 | Beautiful sunset #photo |
| 5 | 305 | 2024-02-17 | Another work day #work |
+--------+----------+------------+---------------------------+
›
Output:
+----------+-------+
| hashtag | count |
+----------+-------+
| #work | 2 |
| #photo | 1 |
| #love | 1 |
+----------+-------+
💡 Note:
#work appears twice (highest count). Among single-count hashtags, we show #photo and #love (alphabetically descending). #food is excluded as we only show top 3.
Visualization
Tap to expand
Understanding the Visualization
1
Data Ingestion
Millions of tweets flow into the system daily, stored with timestamps
2
Time Filtering
Filter tweets to February 2024 using indexed date columns
3
Hashtag Extraction
Parse tweet text to identify hashtags using pattern matching
4
Popularity Counting
Aggregate hashtags and count their occurrences
5
Trend Ranking
Sort by popularity and select top 3 trending topics
Key Takeaway
🎯 Key Insight: Database aggregation functions (GROUP BY + COUNT) automatically handle the complex logic of filtering, extracting, counting, and sorting hashtags in a single optimized operation, making this approach scalable for real-world social media analytics.
Time & Space Complexity
Time Complexity
O(n log n)
Database filtering is O(n), sorting is O(k log k) where k is unique hashtags (typically k << n)
⚡ Linearithmic
Space Complexity
O(k)
Only stores unique hashtags and their counts, not all tweets
✓ Linear Space
Constraints
- 1 ≤ Number of tweets ≤ 106
- Each tweet contains exactly one hashtag
- Hashtag format: #followed by alphanumeric characters
- tweet_date is in YYYY-MM-DD format
- Only consider tweets from February 2024
- Return results ordered by count DESC, hashtag DESC
- Show only top 3 trending hashtags
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code