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
Number of Comments per Post INPUT: Submissions Table sub_id parent_id 1 NULL (post) 2 NULL (post) 3 1 (comment) 4 1 (comment) 5 2 (comment) 3 1 (dup) Posts: parent_id = NULL Comments: parent_id != NULL Duplicates: same sub_id Post 1 C3 C4 Post 2 C5 ALGORITHM STEPS 1 Identify Posts Filter: parent_id IS NULL SELECT DISTINCT sub_id WHERE parent_id IS NULL 2 Find Comments Filter: parent_id IS NOT NULL WHERE parent_id IS NOT NULL 3 LEFT JOIN Posts with Comments Posts LEFT JOIN Comments ON post.id = comment.parent 4 Count Unique Comments GROUP BY post_id COUNT(DISTINCT sub_id) ORDER BY post_id ASC Posts Comments Result LEFT GROUP FINAL RESULT post_id num_comments 1 2 2 1 Explanation: Post 1 has 2 unique comments (sub_id 3 and 4, dup ignored) Post 2 has 1 comment (sub_id 5) Visual Summary Post 1 2 comments Post 2 1 comment OK - Sorted ASC Key Insight: Use LEFT JOIN to include posts with zero comments. COUNT(DISTINCT sub_id) handles duplicate comments. Posts are identified by parent_id IS NULL, comments by parent_id IS NOT NULL referencing a post's sub_id. DISTINCT on posts ensures duplicates are treated as one post. ORDER BY post_id ASC for required sorting. TutorialsPoint - Number of Comments per Post | Optimal Solution

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