Number of Comments per Post

Number of Comments per Post

What is this problem about?

The Number of Comments per Post SQL problem asks you to count the number of comments for each post. Posts and comments are in the same Submissions table, where comments have a non-NULL parent_id pointing to their parent post. For each post (parent_id IS NULL), count how many direct comments it has. Posts with no comments should show 0. This is a self-join or GROUP BY with LEFT JOIN problem.

Why is this asked in interviews?

Meta asks this to test hierarchical data querying in SQL — a common pattern in social media and forum databases. It validates knowledge of self-referencing tables, LEFT JOIN for zero-count handling, and proper NULL filtering. The database interview pattern is directly demonstrated.

Algorithmic pattern used

LEFT JOIN + GROUP BY with NULL handling. Join the posts (parent_id IS NULL) with comments (parent_id IS NOT NULL) on post.sub_id = comment.parent_id. Group by post.sub_id and count comments. Use COALESCE to return 0 for posts with no comments.

SELECT p.sub_id AS post_id, COUNT(c.sub_id) AS number_of_comments
FROM Submissions p
LEFT JOIN Submissions c ON p.sub_id = c.parent_id
WHERE p.parent_id IS NULL
GROUP BY p.sub_id
ORDER BY p.sub_id

Example explanation

Submissions: (1, NULL), (2, 1), (3, 1), (4, NULL), (5, 4), (6, 6) (self-ref, skip). Posts: IDs 1, 4. Comments of post 1: IDs 2, 3. Comments of post 4: ID 5. Result: post 1 → 2 comments, post 4 → 1 comment.

Common mistakes candidates make

• Including comments as rows (missing WHERE parent_id IS NULL for posts).
• Counting NULL rows after LEFT JOIN (use COUNT(c.sub_id), not COUNT(*)).
• Missing posts with 0 comments (LEFT JOIN includes them; COUNT(*) would give 1 incorrectly).
• Not handling duplicate post-comment relationships properly.

Interview preparation tip

Hierarchical self-referencing table queries use a self-JOIN pattern: join the same table twice, once for parents and once for children. Always use LEFT JOIN to preserve parent rows with no children, and COUNT(child_column) (not COUNT(*)) to correctly return 0 for empty groups. This pattern appears in org chart queries, thread/reply systems, and category/subcategory hierarchies.