This Leetcode problem Number of Comments per Post LeetCode Solution is done in SQL.

List of all LeetCode Solution

Number of Comments per Post LeetCode Solution

Problem Statement

There is no primary key for this table, it 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.

Write an SQL query to find number of comments per each post. Result table should contain post_id and its corresponding number_of_comments, and must be sorted by post_id in ascending order.

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

The result format is in the following example.

Example 1:
Input:

Output:

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.

Number of Comments per Post LeetCode Solution MySQL

select 
  t.post_id, 
  count(distinct s.sub_id) as number_of_comments 
from 
  (
    select 
      distinct sub_id as post_id 
    from 
      Submissions 
    where 
      parent_id is null
  ) as t 
  left join Submissions as s on t.post_id = s.parent_id 
group by 
  t.post_id 
order by 
  t.post_id;Code language: SQL (Structured Query Language) (sql)

Number of Comments per Post LeetCode Solution MySQL (Another approach)

select 
  post_id, 
  ifnull(number_of_comments, 0) as number_of_comments 
from 
  (
    select 
      distinct sub_id as post_id 
    from 
      Submissions 
    where 
      parent_id is null
  ) as s1 
  left join (
    select 
      parent_id, 
      count(*) as number_of_comments 
    from 
      (
        select 
          distinct sub_id, 
          parent_id 
        from 
          Submissions
      ) as ds 
    where 
      parent_id is not null 
    group by 
      parent_id
  ) as s2 on s1.post_id = s2.parent_id 
order by 
  post_id;Code language: SQL (Structured Query Language) (sql)