Hashtag Trends
ASKED IN INTERVIEWTwitter
Problem Statement
Twitter analytics team needs to display the Trending Now section on the user dashboard. A hashtag is considered trending based on how many times it has been used in tweets within a rolling 6-hour window.
Write an SQL query to find the top 3 hashtags used in the last 6 hours.
Rules:
- The time window is exactly 6 hours prior to the latest tweet timestamp in the dataset.
- If two hashtags have the same count, order them alphabetically.
- Output columns: hashtag_name, usage_count.
- Return the results in descending order of usage_count.
Table Schema:
- Tweets: Contains tweet metadata.
- Hashtags: Contains the hashtag strings.
- Tweet_Hashtags: A mapping table linking tweets to hashtags.
Tests your understanding of
Aggregation, Window Functions, Filtering and DateTime
Input Tables
Tweets
| tweet_id(INTEGER) | user_id(INTEGER) | tweet_timestamp(TIMESTAMP) |
|---|---|---|
| 1 | 10 | 2026-02-10 08:00:00 |
| 2 | 11 | 2026-02-10 09:30:00 |
| 3 | 12 | 2026-02-10 10:15:00 |
| 4 | 13 | 2026-02-10 12:00:00 |
| 5 | 14 | 2026-02-10 13:45:00 |
| 6 | 15 | 2026-02-10 13:55:00 |
Hashtags
| hashtag_id(INTEGER) | hashtag_name(VARCHAR) |
|---|---|
| 1 | #SQL |
| 2 | #Tech |
| 3 | |
| 4 | #Data |
Tweet_Hashtags
| tweet_id(INTEGER) | hashtag_id(INTEGER) |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 2 |
| 6 | 3 |
Expected Output
| hashtag_name(VARCHAR) | usage_count(INTEGER) |
|---|---|
| #SQL | 3 |
| #Tech | 2 |
| 1 |
Tags
MediumASKED IN INTERVIEWAggregationWindow FunctionsFilteringDateTime
15-20 min
54%