Welcome to Subscribe On Youtube

3103. Find Trending Hashtags II 🔒

Description

Table: Tweets

+-------------+---------+
\| Column Name \| Type    \|
+-------------+---------+
\| user_id     \| int     \|
\| tweet_id    \| int     \|
\| tweet_date  \| date    \|
\| tweet       \| varchar \|
+-------------+---------+
tweet_id is the primary key (column with unique values) for this table.
Each row of this table contains user_id, tweet_id, tweet_date and tweet.

Write a solution to find the top 3 trending hashtags in February 2024. Every tweet may contain several hashtags.

Return the result table orderd by count of hashtag, hashtag in descending order.

The result format is in the following example.

 

Example 1:

Input:

Tweets table:

+---------+----------+------------------------------------------------------------+------------+
\| user_id \| tweet_id \| tweet                                                      \| tweet_date \|
+---------+----------+------------------------------------------------------------+------------+
\| 135     \| 13       \| Enjoying a great start to the day. #HappyDay #MorningVibes \| 2024-02-01 \|
\| 136     \| 14       \| Another #HappyDay with good vibes! #FeelGood               \| 2024-02-03 \|
\| 137     \| 15       \| Productivity peaks! #WorkLife #ProductiveDay               \| 2024-02-04 \|
\| 138     \| 16       \| Exploring new tech frontiers. #TechLife #Innovation        \| 2024-02-04 \|
\| 139     \| 17       \| Gratitude for today's moments. #HappyDay #Thankful         \| 2024-02-05 \|
\| 140     \| 18       \| Innovation drives us. #TechLife #FutureTech                \| 2024-02-07 \|
\| 141     \| 19       \| Connecting with nature's serenity. #Nature #Peaceful       \| 2024-02-09 \|
+---------+----------+------------------------------------------------------------+------------+
 

Output:

+-----------+-------+
\| hashtag   \| count \|
+-----------+-------+
\| #HappyDay \| 3     \|
\| #TechLife \| 2     \|
\| #WorkLife \| 1     \|
+-----------+-------+

Explanation:

• #HappyDay: Appeared in tweet IDs 13, 14, and 17, with a total count of 3 mentions.
• #TechLife: Appeared in tweet IDs 16 and 18, with a total count of 2 mentions.
• #WorkLife: Appeared in tweet ID 15, with a total count of 1 mention.

Note: Output table is sorted in descending order by count and hashtag respectively.

Solutions

Solution 1: Regular Expression Matching

We can use regular expressions to match all tags in each tweet, and then count the occurrence of each tag. Finally, we can sort the tags in descending order by the number of occurrences. If the number of occurrences is the same, we sort them in descending order by the tag name, and return the top three tags.

• Python

• import pandas as pd
  
  
  def find_trending_hashtags(tweets: pd.DataFrame) -> pd.DataFrame:
      # Filter tweets for February 2024
      tweets_feb_2024 = tweets[tweets["tweet_date"].between("2024-02-01", "2024-02-29")]
  
      # Extract hashtags from tweets
      hashtags = tweets_feb_2024["tweet"].str.findall(r"#\w+")
  
      # Flatten list of hashtags
      all_hashtags = [tag for sublist in hashtags for tag in sublist]
  
      # Count occurrences of each hashtag
      hashtag_counts = pd.Series(all_hashtags).value_counts().reset_index()
      hashtag_counts.columns = ["hashtag", "count"]
  
      # Sort by count of hashtag in descending order
      hashtag_counts = hashtag_counts.sort_values(
          by=["count", "hashtag"], ascending=[False, False]
      )
  
      # Get top 3 trending hashtags
      top_3_hashtags = hashtag_counts.head(3)
  
      return top_3_hashtags
  
  

All Problems

All Solutions