Most Common Leading Hashtags in Captions — SQL Problem
Older posts stored hashtags only inside the caption text. To backfill the topic of each post, take the first hashtag in its caption: the text after the…
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #438
Problem statement
Older posts stored hashtags only inside the caption text. To backfill the topic of each post, take the first hashtag in its caption: the text after the first # up to the next space (or the end of the caption). Hashtags are lower-case letters and digits and are always followed by a space or the end of the caption.
Return the columns hashtag (without the #) and post_count — how many posts have it as their first hashtag — ordered by post_count descending, then hashtag. Posts whose caption is NULL or has no # are ignored.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| author_id | int |
| caption | varchar |
Primary key: post_id.
One row per legacy post; caption may be NULL.
Examples
Example 1
posts
| post_id | author_id | caption |
|---|---|---|
| 1 | 3 | Sunday brunch at Bandra #foodie #mumbaidiaries |
| 2 | 4 | #ipl what a finish tonight #cricket |
| 3 | 3 | Rain again #mumbaidiaries |
| 4 | 5 | no tags on this one |
| 5 | 6 | #foodie |
| 6 | 4 | NULL |
| 7 | 7 | Last over drama #ipl #memes |
| 8 | 5 | Street food crawl #foodie #travel |
Output
| hashtag | post_count |
|---|---|
| foodie | 3 |
| ipl | 2 |
| mumbaidiaries | 1 |
How to solve Most Common Leading Hashtags in Captions
Two string steps isolate the tag. First, get the text after the first #: SUBSTRING(caption, LOCATE('#', caption) + 1) does it directly, and the solution's SUBSTRING_INDEX(SUBSTRING_INDEX(caption, '#', 2), '#', -1) takes the piece between the first and second # instead. Second, cut that text at its first space with SUBSTRING_INDEX(…, ' ', 1); when the tag ends the caption there is no space and the whole remainder is the tag.
Captions with no # must be removed first — SUBSTRING_INDEX would otherwise return the caption's first word as if it were a tag. LIKE '%#%' and LOCATE('#', caption) > 0 both reject NULL captions as well, because a NULL makes the predicate unknown.
Then group by the extracted tag and count. Ties on the count are sorted by the tag. String functions on every row make it a full scan — fine for a one-time backfill, after which the tags belong in their own table.
Reference solution (MySQL)
SELECT hashtag, COUNT(*) AS post_count
FROM (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(caption, '#', 2), '#', -1), ' ', 1) AS hashtag
FROM posts
WHERE caption LIKE '%#%'
) t
GROUP BY hashtag
ORDER BY post_count DESC, hashtagAnother way
SELECT SUBSTRING_INDEX(SUBSTRING(caption, LOCATE('#', caption) + 1), ' ', 1) AS hashtag, COUNT(*) AS post_count
FROM posts WHERE LOCATE('#', caption) > 0
GROUP BY SUBSTRING_INDEX(SUBSTRING(caption, LOCATE('#', caption) + 1), ' ', 1)
ORDER BY post_count DESC, hashtag← Sign-Ups by Email Provider · Creators Whose Latest Post Underperformed →