Hashtags Used on Three or More Live Posts

Easy
426.

Hashtags Used on Three or More Live Posts

The explore page only lists a hashtag once it appears on at least three posts that are not deleted. Deleted posts keep their hashtag rows but must not be counted.

Return the columns hashtag and post_count (the number of live posts carrying it) for the qualifying hashtags, ordered by post_count descending, then by hashtag ascending.

Tables

Table: posts

ColumnType
post_idint
author_idint
is_deletedbool

Primary key: post_id.

is_deleted is 1 for a post the author or a moderator removed.

Table: post_hashtags

ColumnType
post_idint
hashtagvarchar

Primary key (together): post_id, hashtag.

One row per hashtag on a post, stored lower-case without the #. post_id is always a row of posts.

Example

The rows must come back in the order shown.

posts

post_idauthor_idis_deleted
170
280
371
490
580
690

post_hashtags

post_idhashtag
1ipl
1cricket
2ipl
2foodie
3ipl
3foodie
4foodie
4cricket
5ipl
5cricket
1 more row

Output

hashtagpost_count
cricket3
foodie3
ipl3
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.

Hashtags Used on Three or More Live Posts — SQL Problem

The explore page only lists a hashtag once it appears on at least three posts that are not deleted.

  • Difficulty: Easy
  • Topics: Aggregation, Joins
  • Dialect: MySQL
  • Problem: #426

Problem statement

The explore page only lists a hashtag once it appears on at least three posts that are not deleted. Deleted posts keep their hashtag rows but must not be counted.

Return the columns hashtag and post_count (the number of live posts carrying it) for the qualifying hashtags, ordered by post_count descending, then by hashtag ascending.

Tables

Table: posts

ColumnType
post_idint
author_idint
is_deletedbool

Primary key: post_id.

is_deleted is 1 for a post the author or a moderator removed.

Table: post_hashtags

ColumnType
post_idint
hashtagvarchar

Primary key: post_id, hashtag.

One row per hashtag on a post, stored lower-case without the #. post_id is always a row of posts.

Examples

Example 1

posts

post_idauthor_idis_deleted
170
280
371
490
580
690

post_hashtags

post_idhashtag
1ipl
1cricket
2ipl
2foodie
3ipl
3foodie
4foodie
4cricket
5ipl
5cricket
6foodie

Output

hashtagpost_count
cricket3
foodie3
ipl3

How to solve Hashtags Used on Three or More Live Posts

Each hashtag row needs its post's is_deleted flag, so join post_hashtags to posts and drop deleted posts in WHERE. Grouping the surviving rows by hashtag gives one row per tag, and since (post_id, hashtag) is the primary key, COUNT(*) is the number of distinct live posts carrying it.

The threshold is a condition on the group, not on a row, so it belongs in HAVING: COUNT(*) >= 3 — exactly three qualifies. Filtering deleted posts in HAVING instead (with conditional SUM, as one alternative does) gives the same answer, but WHERE is cheaper because it discards rows before they are grouped.

Ties on post_count are broken by the hashtag's name so the order is fixed. The query is one join plus one grouping; an IN (SELECT …) semi join is an equivalent way to apply the live-post filter.

Reference solution (MySQL)

SELECT h.hashtag, COUNT(*) AS post_count
FROM post_hashtags h
JOIN posts p ON p.post_id = h.post_id
WHERE p.is_deleted = 0
GROUP BY h.hashtag
HAVING COUNT(*) >= 3
ORDER BY post_count DESC, h.hashtag

Another way

SELECT hashtag, post_count FROM (SELECT hashtag, COUNT(*) AS post_count FROM post_hashtags WHERE post_id IN (SELECT post_id FROM posts WHERE is_deleted = 0) GROUP BY hashtag) t WHERE post_count >= 3 ORDER BY post_count DESC, hashtag

Another way

SELECT h.hashtag, SUM(CASE WHEN p.is_deleted = 0 THEN 1 ELSE 0 END) AS post_count FROM post_hashtags h JOIN posts p ON p.post_id = h.post_id GROUP BY h.hashtag HAVING SUM(CASE WHEN p.is_deleted = 0 THEN 1 ELSE 0 END) >= 3 ORDER BY post_count DESC, h.hashtag

← Stories Posted and Total Story Views per User · Handles Breaking the Username Policy →