# $1241. 每个帖子的评论数
# https://leetcode-cn.com/problems/number-of-comments-per-post/

# SQL架构
Create table If Not Exists Submissions (sub_id int, parent_id int);
Truncate table Submissions;
insert into Submissions (sub_id, parent_id) values ('1', null);
insert into Submissions (sub_id, parent_id) values ('2', null);
insert into Submissions (sub_id, parent_id) values ('1', null);
insert into Submissions (sub_id, parent_id) values ('12', null);
insert into Submissions (sub_id, parent_id) values ('3', '1');
insert into Submissions (sub_id, parent_id) values ('5', '2');
insert into Submissions (sub_id, parent_id) values ('3', '1');
insert into Submissions (sub_id, parent_id) values ('4', '1');
insert into Submissions (sub_id, parent_id) values ('9', '1');
insert into Submissions (sub_id, parent_id) values ('10', '2');
insert into Submissions (sub_id, parent_id) values ('6', '7');

# Write your MySQL query statement below
select
    post_id,
    count(sub_id) as number_of_comments
from
    (
        select
            distinct post.sub_id as post_id,
            sub.sub_id as sub_id
        from
            Submissions post
            left join Submissions sub on post.sub_id = sub.parent_id
        where
            post.parent_id is null
    ) tmp
group by
    post_id
order by
    post_id asc;

# clean-up
drop table Submissions;