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
| Column | Type |
|---|---|
| post_id | int |
| author_id | int |
| is_deleted | bool |
Primary key: post_id.
is_deleted is 1 for a post the author or a moderator removed.
Table: post_hashtags
| Column | Type |
|---|---|
| post_id | int |
| hashtag | varchar |
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_id | author_id | is_deleted |
|---|---|---|
| 1 | 7 | 0 |
| 2 | 8 | 0 |
| 3 | 7 | 1 |
| 4 | 9 | 0 |
| 5 | 8 | 0 |
| 6 | 9 | 0 |
post_hashtags
| post_id | hashtag |
|---|---|
| 1 | ipl |
| 1 | cricket |
| 2 | ipl |
| 2 | foodie |
| 3 | ipl |
| 3 | foodie |
| 4 | foodie |
| 4 | cricket |
| 5 | ipl |
| 5 | cricket |
| 6 | foodie |
Output
| hashtag | post_count |
|---|---|
| cricket | 3 |
| foodie | 3 |
| ipl | 3 |
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.hashtagAnother 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, hashtagAnother 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 →