easy Find Popular Posts

Each row in Posts is one like-event — a single user liking a single post — so the number of likes a post has is simply the number of rows that carry its post_id, which GROUP BY post_id + COUNT(...) computes by bucketing rows and counting each bucket.

Problem Statement

Table: Posts

Each row records that one user liked one post. There is no precomputed likes column — the likes are the rows. The schema is exactly two columns:

+-------------+------+
| Column Name | Type |
+-------------+------+
| post_id     | int  |
| user_id     | int  |
+-------------+------+

• (post_id, user_id) together form the primary key: a given user can like a given post at most once, but the same post_id appears on many rows (once per distinct liker).
• user_id is the user who liked the post identified by post_id.

Task: return the number of likes for each post, as a column named post_likes, ordered by post_id ascending.

(Note: an earlier version of this page listed a post_likes column in the schema and said the table "contains the number of likes received." That is wrong and self-contradictory — if a stored count existed there would be nothing to COUNT. The whole point of the problem is that likes are stored one-row-per-like and must be aggregated. The schema above is the real one the example data matches.)

Worked example

Input — 8 like-events:

Posts:
+---------+---------+
| post_id | user_id |
+---------+---------+
|    1    |    2    |
|    2    |    1    |
|    1    |    3    |
|    3    |    1    |
|    1    |    4    |
|    4    |    2    |
|    2    |    2    |
|    4    |    3    |
+---------+---------+

Trace. The engine scans the 8 rows and drops each into a bucket keyed by post_id, accumulating a running count:

After the scan, each bucket emits one output row: post 1 → 3, post 2 → 2, post 3 → 1, post 4 → 2. ORDER BY post_id ASC then sorts those four rows.

Output:

+---------+------------+
| post_id | post_likes |
+---------+------------+
|    1    |     3      |
|    2    |     2      |
|    3    |     1      |
|    4    |     2      |
+---------+------------+

The query

SELECT
    post_id,
    COUNT(*) AS post_likes
FROM Posts
GROUP BY post_id
ORDER BY post_id ASC;

Reading it the way the engine evaluates it (not the way it is written): FROM Posts produces the rows → GROUP BY post_id partitions them into one group per distinct post_id → COUNT(*) collapses each group to a single number → SELECT shapes the output columns → ORDER BY sorts the result. This is why you may put post_id in SELECT without an aggregate: it is the grouping key, so it is constant within each group.

COUNT(*) vs COUNT(user_id) — the distinction that actually bites

The original solution used COUNT(user_id). On this exact data it gives the right answer, but it is the wrong instinct, and knowing why is the real lesson here:

• COUNT(*) counts rows in the group, unconditionally.
• COUNT(user_id) counts rows where user_id IS NOT NULL. NULLs are skipped.
• COUNT(DISTINCT user_id) counts distinct non-NULL values.

Concretely, add one row (5, NULL) — a post that exists but whose liker wasn't recorded:

Now the two diverge. If "likes" means "like-events," COUNT(*) is correct (1). If it means "users who liked," COUNT(user_id) (0, here) or COUNT(DISTINCT user_id) is what you want. Because (post_id, user_id) is the primary key, user_id can never be NULL and can never repeat within a post, so all three agree on this table — which is exactly why the bug is invisible until the schema relaxes a constraint.

Why the naive version is not wrong, but is fragile

COUNT(user_id) here is correct only because the PK guarantees user_id is non-null and unique per post. Prefer COUNT(*) when you mean "how many rows"; it states the intent, costs no NULL check, and doesn't silently change answers if someone later drops the NOT NULL or uniqueness guarantee.

Pitfalls

• Counting a nullable column by reflex. COUNT(some_col) quietly drops NULL rows. Engineers reach for it out of habit and then can't explain why a count is lower than the row total. Use COUNT(*) for "rows," and reach for a named column only when you specifically want "rows where that column is present."
• Posts with zero likes vanish. This query only ever reports posts that appear in Posts. A post that exists in some other table but has no like-rows produces no group, so it is absent from the output — not shown as 0. To include zero-like posts you must LEFT JOIN from the post catalog and then COUNT(Posts.user_id) (so the unmatched side counts as 0, which is the one place COUNT(*) would be wrong — it would count the single null-padded join row as 1).
• Selecting a non-grouped, non-aggregated column. Adding user_id to the SELECT list is an error in standard SQL and Postgres; MySQL with ONLY_FULL_GROUP_BY disabled silently returns an arbitrary user_id per group. Only the grouping key(s) and aggregates belong in SELECT.
• Assuming GROUP BY sorts. Some engines happen to return groups in key order; the SQL standard does not promise it. The output is unordered until you add ORDER BY post_id — never rely on incidental ordering.

Takeaways

• When each row is one event, "how many X per Y" is GROUP BY Y + COUNT(*) — no stored count column needed; the rows are the count.
• COUNT(*) = rows in the group; COUNT(col) = non-NULL values of col; COUNT(DISTINCT col) = distinct non-NULL values. They diverge the moment NULLs or duplicates are possible.
• A primary key on (post_id, user_id) is what makes the three counts agree here — relax that constraint and the choice suddenly matters. Pick the one that states your intent, not the one that happens to work.
• GROUP BY does not sort; add ORDER BY when order is part of the spec.

Based on the LeetCode-style "Find Popular Posts" practice problem and the SQL semantics of GROUP BY and the COUNT aggregate as specified in the SQL standard and documented in the PostgreSQL and MySQL manuals (aggregate functions, NULL handling, ONLY_FULL_GROUP_BY). Re-authored and deepened for this guide: corrected the self-contradictory schema (removed the phantom post_likes column and the conflicting primary-key bullets), fixed the "Step 3" mislabel on the GROUP BY intermediate output, switched the solution to COUNT(*), and added the COUNT(*) vs COUNT(col) NULL distinction, a row-by-row trace, the bucketing diagram, and the zero-like-posts / LEFT JOIN pitfall.