Description

Table: Submissions

Column NameType
sub_idint
parent_idint
  • This table may have duplicate rows.
  • Each row can be a post or comment on the post.
  • parent_id is null for posts.
  • parent_id for comments is sub_id for another post in the table.

Problem Statement

Write a solution to find the number of comments per post. The result table should contain post_id and its corresponding number_of_comments.

  • The Submissions table may contain duplicate comments. You should count the number of unique comments per post.
  • The Submissions table may contain duplicate posts. You should treat them as one post.

The result table should be ordered by post_id in ascending order.

The result format is in the following example.

Example 1:

Input:

  • Submissions table:
sub_idparent_id
1Null
2Null
1Null
12Null
31
52
31
41
91
102
67

Output:

post_idnumber_of_comments
13
22
120

Explanation:

  • The post with id 1 has three comments in the table with id 3, 4, and 9. The comment with id 3 is repeated in the table, we counted it only once.
  • The post with id 2 has two comments in the table with id 5 and 10.
  • The post with id 12 has no comments in the table.
  • The comment with id 6 is a comment on a deleted post with id 7 so we ignored it.

Solution

First you need to find the posts. For this you can filter the table with rows where parent_id IS NULL.

1WITH posts AS (
2    SELECT DISTINCT sub_id
3    FROM Submissions
4    WHERE parent_id IS NULL
5)

The next step is to find distinct sub_id grouped by post_id from the above posts view. For this you will have to join with Submissions table.

1SELECT p.sub_id AS post_id, COUNT(DISTINCT s.sub_id) AS number_of_comments
2    FROM posts p
3    LEFT JOIN Submissions s ON p.sub_id = s.parent_id
4    GROUP BY p.sub_id

Finally, you need to order the results by sub_id. The final solution looks like this.

1WITH posts AS (
2    SELECT DISTINCT sub_id
3    FROM Submissions
4    WHERE parent_id IS NULL
5) SELECT p.sub_id AS post_id, COUNT(DISTINCT s.sub_id) AS number_of_comments
6    FROM posts p
7    LEFT JOIN Submissions s ON p.sub_id = s.parent_id
8    GROUP BY p.sub_id
9    ORDER By p.sub_id