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:
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
📱 Social Media Trend AnalysisTweet StreamMillions of tweets24/7 ingestionDate FilterFebruary 2024WHERE clauseExtract #tagsPattern matchingREGEX/SUBSTRCount & GroupGROUP BYCOUNT(*)Top 3 TrendsORDER BYLIMIT 3📊 Sample Results#sunny (25)#remote (18)#love (12)🎯 Key InsightSQL's GROUP BY and aggregate functions eliminate the needfor manual loops, leveraging database optimization forefficient processing of millions of tweets
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)

n
2n
Linearithmic
Space Complexity
O(k)

Only stores unique hashtags and their counts, not all tweets

n
2n
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
Asked in
Meta 45 Twitter 38 Instagram 32 LinkedIn 28 TikTok 24 Snapchat 20
89.6K Views
High Frequency
~15 min Avg. Time
2.3K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen