Find Trending Hashtags II - Practice Coding Problems

Find Trending Hashtags II - Problem

Database Hard

🔥 Find Trending Hashtags II

You're tasked with analyzing Twitter data to discover the most popular hashtags trending in February 2024! Given a table of tweets, you need to extract all hashtags from the tweet content and identify the top 3 trending hashtags based on their frequency.

Table: Tweets

Column Name	Type
user_id	int
tweet_id	int
tweet_date	date
tweet	varchar

Where tweet_id is the primary key and each row represents a unique tweet posted in February 2024.

Your Mission:

Extract all hashtags (words starting with #) from tweet content
Count the frequency of each hashtag
Return the top 3 most frequent hashtags
Order results by count (descending), then by hashtag name (descending)

Note: A single tweet can contain multiple hashtags, and each occurrence should be counted separately!

Input & Output

example_1.sql — Basic Hashtag Counting

$ Input: Tweets table: | user_id | tweet_id | tweet_date | tweet | |---------|----------|------------|---------| | 1 | 101 | 2024-02-15 | "Loving #AI and #MachineLearning today!" | | 2 | 102 | 2024-02-16 | "#AI is the future! #TechNews rocks" | | 3 | 103 | 2024-02-17 | "Great day for #TechNews and #Innovation" |

› Output: | hashtag | count | |---------|-------| | #ai | 2 | | #technews | 2 | | #machinelearning | 1 |

💡 Note: The hashtag #AI appears in 2 tweets (converted to lowercase), #TechNews appears 2 times, and #MachineLearning appears once. Results are ordered by count DESC, then hashtag name DESC.

example_2.sql — Multiple Hashtags Per Tweet

$ Input: Tweets table: | user_id | tweet_id | tweet_date | tweet | |---------|----------|------------|---------| | 1 | 201 | 2024-02-10 | "#SuperBowl #Sports #NFL what a game!" | | 2 | 202 | 2024-02-11 | "Love #Sports and #Football #SuperBowl" | | 3 | 203 | 2024-02-12 | "#SuperBowl was amazing! #Sports" |

› Output: | hashtag | count | |---------|-------| | #superbowl | 3 | | #sports | 3 | | #nfl | 1 |

💡 Note: Both #SuperBowl and #Sports appear 3 times each. Since counts are equal, we order by hashtag name DESC, so #superbowl comes before #sports. #NFL appears once.

example_3.sql — Edge Case with Punctuation

$ Input: Tweets table: | user_id | tweet_id | tweet_date | tweet | |---------|----------|------------|---------| | 1 | 301 | 2024-02-20 | "Check out #AI! It's #Amazing, truly #Revolutionary." | | 2 | 302 | 2024-02-21 | "#AI and #Amazing tech everywhere!" | | 3 | 303 | 2024-02-22 | "The #Future is #AI powered" |

› Output: | hashtag | count | |---------|-------| | #ai | 3 | | #amazing | 2 | | #revolutionary | 1 |

💡 Note: Hashtags followed by punctuation (!, ., ,) are correctly extracted. #AI appears 3 times, #Amazing 2 times, and other hashtags once each.

Visualization

Tap to expand

Understanding the Visualization

Data Ingestion

Collect all tweets from February 2024

Hashtag Extraction

Use regex to find all #hashtags in tweet content

Frequency Analysis

Count occurrences of each unique hashtag

Trend Ranking

Sort by frequency and select top 3 trending topics

Key Takeaway

🎯 Key Insight: The optimal solution leverages database-optimized regular expressions and Common Table Expressions to extract and count hashtags in a single efficient pass, making it perfect for real-time social media analytics at scale.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through tweets with efficient regex matching and aggregation

✓ Linear Growth

Space Complexity

O(h)

Space proportional to number of unique hashtags found

✓ Linear Space

Constraints

1 ≤ number of tweets ≤ 10⁴
1 ≤ tweet length ≤ 280 characters
All tweet_date values are valid dates in February 2024
Hashtags are case-insensitive and must start with # followed by alphanumeric characters
Each tweet may contain 0 to 10 hashtags
Hashtag names can contain letters, numbers, and underscores only

Asked in

f Meta 45 🐦 Twitter 38 G Google 32 in LinkedIn 28

The optimal solution uses regular expressions with REGEXP_EXTRACT_ALL to efficiently extract hashtags in a single database scan. We leverage Common Table Expressions (CTEs) to create a clean pipeline: extract hashtags → flatten arrays → count occurrences → sort and limit results. This approach achieves O(n) time complexity with database-optimized functions.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(n)	Extract hashtags using multiple nested queries and manual parsing
Optimized SQL with REGEXP (Optimal)	O(n)	O(h)	Use regular expressions and CTEs to extract and count hashtags efficiently

Brute Force (Multiple Subqueries) — Algorithm Steps

Split each tweet text into individual words
Filter words that start with '#' character
Use multiple subqueries to count each hashtag occurrence
Manually order and limit results to top 3

Visualization

Tap to expand

Step-by-Step Walkthrough

Split Tweets

Break each tweet into individual words using STRING_SPLIT

Filter Hashtags

Keep only words starting with '#' character

Count Occurrences

Group and count each unique hashtag

Order Results

Sort by count DESC, hashtag DESC and limit to 3

Code -

solution.c — C

// Brute Force SQL Solution
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sql[] = "WITH hashtag_words AS (\n"
                       "    SELECT tweet_id, TRIM(value) as word\n"
                       "    FROM Tweets t\n"
                       "    CROSS APPLY STRING_SPLIT(t.tweet, ' ')\n"
                       "    WHERE TRIM(value) LIKE '#%'\n"
                       "      AND tweet_date >= '2024-02-01'\n"
                       "      AND tweet_date < '2024-03-01'\n"
                       "), hashtag_counts AS (\n"
                       "    SELECT word as hashtag, COUNT(*) as count\n"
                       "    FROM hashtag_words\n"
                       "    WHERE word != '#'\n"
                       "    GROUP BY word\n"
                       ")\n"
                       "SELECT hashtag, count\n"
                       "FROM hashtag_counts\n"
                       "ORDER BY count DESC, hashtag DESC\n"
                       "LIMIT 3;";
    return sql;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes through the data for each hashtag extraction and counting

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for intermediate results in subqueries

⚡ Linearithmic Space

Constraints

1 ≤ number of tweets ≤ 10⁴
1 ≤ tweet length ≤ 280 characters
All tweet_date values are valid dates in February 2024
Hashtags are case-insensitive and must start with # followed by alphanumeric characters
Each tweet may contain 0 to 10 hashtags
Hashtag names can contain letters, numbers, and underscores only

38.5K Views

High Frequency

~25 min Avg. Time

1.5K 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

🔥 Find Trending Hashtags II

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler