Number of Comments per Post - Problem

Given a Submissions table that contains posts and comments, find the number of comments per post.

The table structure:

  • sub_id: Unique identifier for each submission
  • parent_id: NULL for posts, references sub_id of the parent post for comments

Requirements:

  • Count only unique comments per post (ignore duplicate comments)
  • Treat duplicate posts as one post
  • Return post_id and number_of_comments
  • Order results by post_id in ascending order

Table Schema

Submissions
Column Name Type Description
sub_id PK int Unique identifier for submission (post or comment)
parent_id int NULL for posts, sub_id of parent post for comments
Primary Key: sub_id
Note: Table may contain duplicate rows. Posts have parent_id = NULL, comments reference parent post via parent_id

Input & Output

Example 1 — Basic Posts and Comments
Input Table:
sub_id parent_id
1
2 1
3 1
4
5 4
Output:
post_id number_of_comments
1 2
4 1
💡 Note:

Post 1 has 2 comments (submissions 2 and 3). Post 4 has 1 comment (submission 5). Results are ordered by post_id ascending.
Example 2 — Duplicate Submissions
Input Table:
sub_id parent_id
1
1
2 1
2 1
3
Output:
post_id number_of_comments
1 1
3 0
💡 Note:

Duplicate posts (1,1) are treated as one post. Duplicate comments (2,2) are counted as one unique comment. Post 3 has no comments so count is 0.
Example 3 — No Comments Case
Input Table:
sub_id parent_id
1
2
Output:
post_id number_of_comments
1 0
2 0
💡 Note:

Both posts have no comments, so number_of_comments is 0 for each post.

Constraints

  • 1 ≤ sub_id ≤ 1000
  • parent_id is NULL for posts or references a valid sub_id
  • Table may contain duplicate rows

Visualization

Tap to expand
SQL Problem: Number of Comments per PostInput: Submissions Tablesub_idparent_id1NULL21314NULL■ Posts■ CommentsSQL JOIN& GROUP BYOutput: Comments Countpost_idnumber_of_comments1240Query Logic:1. Filter posts: WHERE parent_id IS NULL2. LEFT JOIN posts with comments3. GROUP BY post_id, COUNT DISTINCT comments4. ORDER BY post_id ASC
Understanding the Visualization
1
Identify Posts
Filter rows where parent_id IS NULL
2
Join Comments
LEFT JOIN to match comments with posts
3
Count & Group
GROUP BY post and COUNT DISTINCT comments
Key Takeaway
🎯 Key Insight: Use self-join pattern when relating rows within the same table (posts ↔ comments)

Related Problems

Asked in
Facebook 28 Twitter 19 Instagram 15
23.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen