Most Common Leading Hashtags in Captions

Medium
438.

Most Common Leading Hashtags in Captions

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

ColumnType
post_idint
author_idint
captionvarchar

Primary key: post_id.

One row per legacy post; caption may be NULL.

Example

The rows must come back in the order shown.

posts

post_idauthor_idcaption
13Sunday brunch at Bandra #foodie #mumbaidiaries
24#ipl what a finish tonight #cricket
33Rain again #mumbaidiaries
45no tags on this one
56#foodie
64NULL
77Last over drama #ipl #memes
85Street food crawl #foodie #travel

Output

hashtagpost_count
foodie3
ipl2
mumbaidiaries1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
post_idint
author_idint
captionvarchar

Primary key: post_id.

One row per legacy post; caption may be NULL.

Examples

Example 1

posts

post_idauthor_idcaption
13Sunday brunch at Bandra #foodie #mumbaidiaries
24#ipl what a finish tonight #cricket
33Rain again #mumbaidiaries
45no tags on this one
56#foodie
64NULL
77Last over drama #ipl #memes
85Street food crawl #foodie #travel

Output

hashtagpost_count
foodie3
ipl2
mumbaidiaries1

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, hashtag

Another 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 →