add 1241

fishercoder1534 · fishercoder1534 · commit 3b2c2eac516f · 2020-02-01T11:32:41.000-08:00
diff --git a/README.md b/README.md
@@ -848,7 +848,8 @@ _If you like this project, please leave me a star._ &#9733;
 |-----|----------------|---------------|---------------|---------------|-------------
 |1327|[List the Products Ordered in a Period](https://leetcode.com/problems/list-the-products-ordered-in-a-period/)|[Solution](../master/database/_1327.sql) || Easy |
 |1280|[Students and Examinations](https://leetcode.com/problems/students-and-examinations/)|[Solution](../master/database/_1280.sql) | [:tv:](https://www.youtube.com/watch?v=ThbkV4Fs7iE)| Easy |
-|1251|[Average Selling Price](https://leetcode.com/problems/average-selling-price//)|[Solution](../master/database/_1251.sql) | | Easy |
+|1251|[Average Selling Price](https://leetcode.com/problems/average-selling-price/)|[Solution](../master/database/_1251.sql) | | Easy |
+|1241|[Number of Comments per Post](https://leetcode.com/problems/number-of-comments-per-post/)|[Solution](../master/database/_1241.sql) | | Easy |
 |1179|[Reformat Department Table](https://leetcode.com/problems/reformat-department-table/)|[Solution](../master/database/_1179.sql) |  | Easy |
 |1173|[Immediate Food Delivery I](https://leetcode.com/problems/immediate-food-delivery-i/)|[Solution](../master/database/_1173.sql) |  | Easy |
 |1148|[Article Views I](https://leetcode.com/problems/article-views-i/)|[Solution](../master/database/_1148.sql) |  | Easy |
diff --git a/database/_1241.sql b/database/_1241.sql
@@ -0,0 +1,64 @@
+--1241. Number of Comments per Post
+--
+--Table: Submissions
+--
+--+---------------+----------+
+--| Column Name   | Type     |
+--+---------------+----------+
+--| sub_id        | int      |
+--| parent_id     | int      |
+--+---------------+----------+
+--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 query result format is in the following example:
+--
+--Submissions table:
+--+---------+------------+
+--| sub_id  | parent_id  |
+--+---------+------------+
+--| 1       | Null       |
+--| 2       | Null       |
+--| 1       | Null       |
+--| 12      | Null       |
+--| 3       | 1          |
+--| 5       | 2          |
+--| 3       | 1          |
+--| 4       | 1          |
+--| 9       | 1          |
+--| 10      | 2          |
+--| 6       | 7          |
+--+---------+------------+
+--
+--Result table:
+--+---------+--------------------+
+--| post_id | number_of_comments |
+--+---------+--------------------+
+--| 1       | 3                  |
+--| 2       | 2                  |
+--| 12      | 0                  |
+--+---------+--------------------+
+--
+--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.
+--
+--# Write your MySQL query statement below
+
+select s.sub_id as post_id,
+(select count(distinct(s1.sub_id)) from Submissions s1 where s1.parent_id = s.sub_id) as number_of_comments
+from Submissions s
+where s.parent_id is null
+group by s.sub_id;
