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]

Loading problem...

Preparing your editor. You can read the problem while it loads.

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]