February Hashtag Momentum Leaderboard

Medium

A social analytics team needs a monthly leaderboard of the strongest hashtag momentum in short-form posts. Table: Tweets user_id: author identifier tweet_id: unique post identifier tweet_date: posting date (YYYY-MM-DD) tweet: post text Task: Produce the top 3 hashtags for February 2024. Processing rules: Consider only rows where tweet_date is in February 2024. Extract a hashtag token from tweet text using pattern #[A-Za-z0-9_]+. Count how many February tweets mention each hashtag token. Return at most 3 rows sorted by: hashtag_count DESC hashtag DESC (for tie-breaking) Output requirements: Return exactly these columns: hashtag, hashtag_count Notes: Rows without a valid hashtag token are ignored. Comparison is case-sensitive, so #Data and #data are different hashtags. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                               |
|---------|----------|-------------|-------------------------------------|
| 135     | 13       | 2024-02-01  | Enjoying a great start #HappyDay    |
| 136     | 14       | 2024-02-03  | Another bright morning #HappyDay    |
| 137     | 15       | 2024-02-04  | Deep work session #WorkLife         |
| 138     | 16       | 2024-02-04  | Exploring new tools #TechLife       |
| 139     | 17       | 2024-02-05  | Grateful for progress #HappyDay     |
| 140     | 18       | 2024-02-07  | Building with curiosity #TechLife   |
| 141     | 19       | 2024-02-09  | A quiet walk helps #Nature          |

Output

[
  {"hashtag":"#HappyDay","hashtag_count":3},
  {"hashtag":"#TechLife","hashtag_count":2},
  {"hashtag":"#WorkLife","hashtag_count":1}
]

Explanation

The top three hashtags in February are ranked by mention frequency, then hashtag text descending for ties.

Example 2

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                  |
|---------|----------|-------------|------------------------|
| 10      | 101      | 2024-02-10  | Fast ship #Alpha       |
| 10      | 102      | 2024-02-11  | Fast ship #Alpha       |
| 11      | 103      | 2024-02-10  | Feature push #Zeta     |
| 11      | 104      | 2024-02-12  | Feature push #Zeta     |
| 12      | 105      | 2024-02-12  | Ops sync #Beta         |

Output

[
  {"hashtag":"#Zeta","hashtag_count":2},
  {"hashtag":"#Alpha","hashtag_count":2},
  {"hashtag":"#Beta","hashtag_count":1}
]

Explanation

#Zeta and #Alpha tie on count. Descending hashtag order places #Zeta first.

Example 3

Input

Tweets:
| user_id | tweet_id | tweet_date  | tweet                        |
|---------|----------|-------------|------------------------------|
| 21      | 201      | 2024-02-28  | Release train #ShipIt        |
| 21      | 202      | 2024-02-29  | Leap day celebration #ShipIt |
| 22      | 203      | 2024-02-29  | Tracking quality #Stable     |
| 23      | 204      | 2024-02-14  | No marker in this row        |
| 24      | 205      | 2024-01-31  | Jan data should not count #ShipIt |

Output

[
  {"hashtag":"#ShipIt","hashtag_count":2},
  {"hashtag":"#Stable","hashtag_count":1}
]

Explanation

February 29 is included in 2024. January rows and rows without a valid hashtag token are ignored.

Constraints

1 <= number of rows in Tweets <= 300000
tweet_id values are unique
1 <= user_id, tweet_id <= 2 * 10^9
tweet_date is a valid ISO date string
tweet is a text payload that may or may not contain a hashtag token
A valid hashtag token matches #[A-Za-z0-9_]+
Only February 2024 rows are considered
Output columns must be exactly: hashtag, hashtag_count
Sort by hashtag_count DESC, hashtag DESC
Return at most 3 rows
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [hashtag, hashtag_count]

Tweets: | user_id | tweet_id | tweet_date | tweet | |---------|----------|-------------|-------------------------------------| | 135 | 13 | 2024-02-01 | Enjoying a great start #HappyDay | | 136 | 14 | 2024-02-03 | Another bright morning #HappyDay | | 137 | 15 | 2024-02-04 | Deep work session #WorkLife | | 138 | 16 | 2024-02-04 | Exploring new tools #TechLife | | 139 | 17 | 2024-02-05 | Grateful for progress #HappyDay | | 140 | 18 | 2024-02-07 | Building with curiosity #TechLife | | 141 | 19 | 2024-02-09 | A quiet walk helps #Nature |

Tweets: | user_id | tweet_id | tweet_date | tweet | |---------|----------|-------------|------------------------| | 10 | 101 | 2024-02-10 | Fast ship #Alpha | | 10 | 102 | 2024-02-11 | Fast ship #Alpha | | 11 | 103 | 2024-02-10 | Feature push #Zeta | | 11 | 104 | 2024-02-12 | Feature push #Zeta | | 12 | 105 | 2024-02-12 | Ops sync #Beta |

Tweets: | user_id | tweet_id | tweet_date | tweet | |---------|----------|-------------|------------------------------| | 21 | 201 | 2024-02-28 | Release train #ShipIt | | 21 | 202 | 2024-02-29 | Leap day celebration #ShipIt | | 22 | 203 | 2024-02-29 | Tracking quality #Stable | | 23 | 204 | 2024-02-14 | No marker in this row | | 24 | 205 | 2024-01-31 | Jan data should not count #ShipIt |