1241. Number of Comments per Post
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 67.8%
Problem Breakdown
The problem requires us to determine the number of comments per post from a given database schema. The inputs are two tables: Posts and Comments. The Posts table contains columns like post_id, title, etc., while the Comments table contains columns like comment_id, post_id, content, etc. The output should be a list of posts with their corresponding number of comments.
Inputs:
Poststable with at leastpost_id.Commentstable with at leastcomment_idandpost_id.
Outputs:
- A result set with each post’s ID and the count of its comments.
Implicit Constraints/Edge Cases:
- There might be posts without any comments.
- There might be duplicate entries in the
Commentstable for the same post. - The tables could be empty.
Intuition
A brute-force approach would involve iterating through each post and counting its comments individually. This would result in a time complexity of O(n * m), where n is the number of posts and m is the average number of comments per post, which is inefficient for large datasets.
The key insight is to leverage SQL’s aggregation capabilities to efficiently count comments per post in a single pass through the data. By using a JOIN operation followed by a GROUP BY, we can achieve this with better time complexity.
Approach
- Join Operation: Perform an inner join between the
PostsandCommentstables on thepost_idfield to link each comment to its corresponding post. - Group By: Group the results by
post_idto aggregate comments per post. - Count Comments: Use the
COUNT(*)function to count the number of comments for each post. - Select and Return: Select the
post_idand the count of comments, ensuring that posts with zero comments are included by using a left join instead.
Complexity Analysis
- Time Complexity: O(n + m), where n is the number of rows in the
Poststable and m is the number of rows in theCommentstable. This is because we perform a single pass through each table. - Space Complexity: O(1) for the query execution, assuming the database can handle the join operation efficiently.
Solution
# SQL Query to solve the problem
SELECT p.post_id, COUNT(c.comment_id) AS num_comments
FROM Posts p
LEFT JOIN Comments c ON p.post_id = c.post_id
GROUP BY p.post_id;
Edge Cases Handled
- Empty Tables: If either
PostsorCommentsis empty, the query will return an empty result set. - No Comments for a Post: Using a left join ensures that posts without comments are included in the result with a count of zero.
- Duplicate Comments: The
COUNT(*)function inherently handles duplicates by counting each comment individually.
The proposed solution is correct and efficient. It uses SQL’s aggregation capabilities to count comments per post, ensuring that all edge cases are handled appropriately.