Problem Description

You have a table called Tweets that contains information about tweets posted in February 2024. Each row in the table has:

• user_id: the ID of the user who posted the tweet
• tweet_id: a unique identifier for each tweet (primary key)
• tweet_date: the date when the tweet was posted
• tweet: the actual text content of the tweet

Your task is to find the top 3 trending hashtags from all tweets posted in February 2024.

A hashtag is any word that starts with the # symbol (like #coding, #leetcode, etc.). Each tweet can contain zero, one, or multiple hashtags within its text.

To determine the trending hashtags, you need to:

1. Extract all hashtags from tweets posted in February 2024
2. Count how many times each hashtag appears across all tweets
3. Return the 3 most frequently used hashtags

The results should be ordered by:

• First, by the count of each hashtag in descending order (most frequent first)
• If two hashtags have the same count, order them by the hashtag name in descending alphabetical order

The output should include two columns:

• hashtag: the hashtag text (including the # symbol)
• count: the number of times this hashtag appeared

For example, if the tweets contain hashtags like #python appearing 10 times, #sql appearing 8 times, and #data appearing 8 times, the result would show these three hashtags ordered by their counts, with #sql appearing before #data due to alphabetical ordering (descending).

Intuition

The key insight here is that we need to process unstructured text data (tweets) to extract structured information (hashtags and their counts). Since hashtags follow a specific pattern - they always start with # followed by word characters - we can use pattern matching to identify them.

Think of this problem as a three-step pipeline:

First, we need to filter our data to only February 2024 tweets since we're interested in trends for that specific month. This is straightforward date filtering.

Second, we need to extract hashtags from the tweet text. Regular expressions are perfect for this because hashtags have a well-defined pattern: # followed by one or more word characters (letters, numbers, or underscores). The regex pattern #\w+ captures exactly this - the # symbol followed by \w+ (one or more word characters).

Third, once we have all hashtags extracted, we face a counting problem. Each tweet might contribute multiple hashtags, so we need to flatten our data structure from "tweets with lists of hashtags" to "a single list of all hashtags". Then counting becomes simple - just count how many times each unique hashtag appears.

The sorting requirement gives us our final step. Since we want trending hashtags, we sort by frequency (count) first. The secondary sort by hashtag name in descending order handles ties - ensuring consistent ordering when multiple hashtags have the same count.

The beauty of this approach is that it breaks down a complex text analysis problem into simple, manageable steps: filter → extract → flatten → count → sort. Each step has a clear purpose and uses the right tool for the job (date filtering, regex matching, aggregation, and sorting).

Solution Approach

Following the approach of using regular expression matching, let's walk through the implementation step by step:

Step 1: Filter for February 2024 Tweets

tweets_feb_2024 = tweets[tweets["tweet_date"].between("2024-02-01", "2024-02-29")]

We use pandas' between() method to filter rows where the tweet_date falls within February 2024. This gives us only the relevant tweets for our analysis.

Step 2: Extract Hashtags Using Regular Expression

hashtags = tweets_feb_2024["tweet"].str.findall(r"#\w+")

The regular expression pattern #\w+ matches:

• # - the literal hashtag symbol
• \w+ - one or more word characters (letters, digits, or underscores)

The str.findall() method returns a list of all hashtags found in each tweet. For example, if a tweet contains "#python #coding", it returns ['#python', '#coding'].

Step 3: Flatten the List of Hashtags

all_hashtags = [tag for sublist in hashtags for tag in sublist]

Since findall() returns a list for each tweet, we have a Series of lists. We use list comprehension to flatten this into a single list containing all hashtags from all tweets. This transforms our data from nested structure to a flat list that's easier to count.

Step 4: Count Hashtag Occurrences

hashtag_counts = pd.Series(all_hashtags).value_counts().reset_index()
hashtag_counts.columns = ["hashtag", "count"]

We convert the flat list to a pandas Series and use value_counts() to count occurrences of each unique hashtag. The reset_index() converts this to a DataFrame with two columns: the hashtag and its count.

Step 5: Sort by Count and Hashtag Name

hashtag_counts = hashtag_counts.sort_values(
    by=["count", "hashtag"], ascending=[False, False]
)

We sort by two criteria:

• Primary: count in descending order (most frequent first)
• Secondary: hashtag name in descending order (to break ties consistently)

Step 6: Return Top 3 Trending Hashtags

top_3_hashtags = hashtag_counts.head(3)

Finally, we use head(3) to select only the top 3 rows from our sorted DataFrame.

The complete solution elegantly combines pandas operations with regular expression pattern matching to transform unstructured tweet text into structured trending hashtag data.

Example Walkthrough

Let's walk through the solution with a small example dataset:

Sample Tweets Table:

Step 1: Filter for February 2024 After filtering dates between "2024-02-01" and "2024-02-29", we exclude tweet_id 105 (March date):

• Remaining tweets: 101, 102, 103, 104

Step 2: Extract Hashtags Using regex pattern #\w+ on each tweet:

• Tweet 101: ['#python', '#sql', '#coding']
• Tweet 102: ['#sql']
• Tweet 103: ['#python']
• Tweet 104: ['#coding', '#python']

Step 3: Flatten the Lists Combine all hashtags into one list: ['#python', '#sql', '#coding', '#sql', '#python', '#coding', '#python']

Step 4: Count Occurrences

• #python: 3 times
• #sql: 2 times
• #coding: 2 times

Step 5: Sort by Count and Name First sort by count (descending), then by name (descending) for ties:

1. #python - count: 3
2. #sql - count: 2 (alphabetically 's' > 'c' in descending order)
3. #coding - count: 2

Step 6: Return Top 3 Since we only have 3 unique hashtags, all are returned:

The key insight is how the secondary sort breaks the tie between #sql and #coding - both have count 2, but #sql comes first because 's' > 'c' when sorting in descending alphabetical order.

Solution Implementation

Time and Space Complexity

Time Complexity: O(n * m + h * log(h))

• O(n) for filtering tweets where n is the total number of tweets in the DataFrame
• O(n * m) for extracting hashtags using regex, where m is the average length of each tweet string
• O(h) for flattening the list of hashtags, where h is the total number of hashtags extracted
• O(h) for counting hashtag occurrences using value_counts()
• O(h * log(h)) for sorting hashtags by count and then by hashtag name
• O(1) for selecting the top 3 hashtags

The dominant operation is the sorting step, making the overall time complexity O(n * m + h * log(h)).

Space Complexity: O(n + h)

• O(n) for storing the filtered tweets from February 2024
• O(h) for storing the extracted hashtags list
• O(h) for storing the flattened hashtags list
• O(u) for storing unique hashtag counts, where u ≤ h is the number of unique hashtags
• O(1) for storing the top 3 hashtags result

The overall space complexity is O(n + h) as we need to store the filtered tweets and all extracted hashtags.

Learn more about how to find time and space complexity quickly.

Common Pitfalls

1. Case Sensitivity in Hashtag Counting

A critical issue that often occurs is treating hashtags with different cases as separate entities. For example, #Python, #python, and #PYTHON would be counted as three different hashtags, even though they likely represent the same topic.

Why this happens: The default string operations and value_counts() are case-sensitive, treating each variation as unique.

Solution: Convert all hashtags to lowercase before counting:

# Extract and normalize hashtags to lowercase
hashtags_series = tweets_feb_2024["tweet"].str.findall(r"#\w+")
all_hashtags = []
for hashtag_list in hashtags_series:
    for hashtag in hashtag_list:
        all_hashtags.append(hashtag.lower())

2. Handling Empty or Null Tweet Content

If the tweet column contains null values or empty strings, the str.findall() operation will fail or produce unexpected results.

Why this happens: Null values in the tweet column will cause str.findall() to return NaN values, which can't be iterated over in the flattening step.

Solution: Filter out null or empty tweets before processing:

# Remove rows where tweet is null or empty
tweets_feb_2024 = tweets_feb_2024[tweets_feb_2024["tweet"].notna()]
tweets_feb_2024 = tweets_feb_2024[tweets_feb_2024["tweet"].str.strip() != ""]

3. Incorrect Leap Year Handling for February

The code assumes February 2024 has 29 days, but if applied to a non-leap year, using February 29th as the end date could cause issues or miss data if the database strictly validates dates.

Why this happens: Not all years have February 29th, and some SQL databases might throw an error or handle the invalid date differently.

Solution: Use a more robust date filtering approach:

# Use month and year extraction for more reliable filtering
tweets_feb_2024 = tweets[
    (pd.to_datetime(tweets["tweet_date"]).dt.year == 2024) & 
    (pd.to_datetime(tweets["tweet_date"]).dt.month == 2)
]

4. Regex Pattern Limitations

The pattern #\w+ only matches word characters (letters, numbers, underscore) but real-world hashtags might contain other valid characters that get truncated.

Why this happens: Hashtags on platforms like Twitter can include characters beyond \w, and the current pattern would split #COVID-19 into just #COVID.

Solution: Use a more comprehensive regex pattern:

# Pattern that captures more realistic hashtag formats
hashtag_pattern = r"#[a-zA-Z0-9_]+"
# Or if you want to match until whitespace or punctuation
hashtag_pattern = r"#[^\s\.\,\!\?\;\:]+(?=\s|$|[\.,:;!?])"

5. Performance Issues with Large Datasets

The list comprehension approach for flattening can be memory-intensive with millions of tweets.

Why this happens: Creating intermediate lists and iterating multiple times consumes significant memory and processing time.

Solution: Use pandas' built-in explode() method for better performance:

# More efficient flattening using explode
hashtags_series = tweets_feb_2024["tweet"].str.findall(r"#\w+")
hashtags_df = hashtags_series.explode().reset_index(drop=True)
hashtag_counts = hashtags_df.value_counts().reset_index()