Domain 1EasyCORE

Number of Comments per Post

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.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 1

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:

  • Posts table with at least post_id.
  • Comments table with at least comment_id and post_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 Comments table 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

  1. Join Operation: Perform an inner join between the Posts and Comments tables on the post_id field to link each comment to its corresponding post.
  2. Group By: Group the results by post_id to aggregate comments per post.
  3. Count Comments: Use the COUNT(*) function to count the number of comments for each post.
  4. Select and Return: Select the post_id and 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 Posts table and m is the number of rows in the Comments table. 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 Posts or Comments is 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.