Number of Comments per Post - Practice Coding Problems

Number of Comments per Post - Problem

Database Easy

Imagine you're building a social media analytics dashboard that needs to track engagement metrics! You have a database table called Submissions that contains both posts and comments in a hierarchical structure.

Here's how it works:

Each row represents either a post or a comment
Posts have parent_id = NULL (they're the root of the conversation)
Comments have parent_id pointing to a post's sub_id

Your mission: Calculate how many unique comments each post has received. The table might contain duplicate posts (treat as one) and duplicate comments (count unique ones only).

Expected Output: A result table with post_id and number_of_comments, ordered by post_id ascending.

Input & Output

example_1.sql — Basic Case

$ Input: Submissions table: | sub_id | parent_id | |--------|----------| | 1 | null | | 2 | 1 | | 3 | 1 | | 4 | null | | 5 | 4 | | 6 | 4 |

› Output: | post_id | number_of_comments | |---------|-------------------| | 1 | 2 | | 4 | 2 |

💡 Note: Post 1 has comments 2 and 3 (2 total). Post 4 has comments 5 and 6 (2 total). Results ordered by post_id ascending.

example_2.sql — Duplicate Comments

$ Input: Submissions table: | sub_id | parent_id | |--------|----------| | 1 | null | | 2 | 1 | | 3 | 1 | | 2 | 1 | | 4 | null |

› Output: | post_id | number_of_comments | |---------|-------------------| | 1 | 2 | | 4 | 0 |

💡 Note: Post 1 has duplicate comment 2, but we count only unique comments (2, 3). Post 4 has no comments.

example_3.sql — No Comments

$ Input: Submissions table: | sub_id | parent_id | |--------|----------| | 1 | null | | 2 | null | | 3 | null |

› Output: | post_id | number_of_comments | |---------|-------------------| | 1 | 0 | | 2 | 0 | | 3 | 0 |

💡 Note: All entries are posts with no comments. Each post has 0 comments.

Constraints

1 ≤ Number of submissions ≤ 1000
1 ≤ sub_id ≤ 10⁵
parent_id is null for posts, valid sub_id for comments
The table may contain duplicate rows
Results must be ordered by post_id in ascending order

Visualization

Tap to expand

Understanding the Visualization

Identify Posts

Find all unique posts (entries with parent_id = NULL)

Match Comments

For each post, find all comments that reference it as parent

Count Unique

Count distinct comment IDs to handle duplicates

Generate Report

Present results ordered by post ID for consistent reporting

Key Takeaway

🎯 Key Insight: SQL's aggregation functions (GROUP BY + COUNT DISTINCT) handle complex data relationships efficiently in a single query, making it ideal for analytics reporting where duplicate handling and grouping are essential.

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28

The optimal solution uses a single SQL query with LEFT JOIN and GROUP BY to efficiently count unique comments per post. Key insights: LEFT JOIN connects posts with comments, COUNT(DISTINCT) handles duplicates automatically, and database engines optimize aggregation operations for best performance.

Common Approaches

Approach	Time	Space	Notes
✓ SQL Aggregation (Optimal)	O(n log n)	O(n)	Use GROUP BY and COUNT DISTINCT in a single SQL query
Brute Force (Multiple Scans)	O(n²)	O(n)	Scan all posts, then for each post scan entire table to count comments

SQL Aggregation (Optimal) — Algorithm Steps

LEFT JOIN the table with itself to connect posts with their comments
GROUP BY post_id to aggregate results per post
Use COUNT(DISTINCT comment_sub_id) to count unique comments
ORDER BY post_id for the required output format

Visualization

Tap to expand

Step-by-Step Walkthrough

LEFT JOIN

Join posts with their comments in one operation

GROUP BY

Group results by post_id

COUNT DISTINCT

Count unique comments per group

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_SUBMISSIONS 1000
#define NULL_PARENT -1

typedef struct {
    int sub_id;
    int parent_id;
} Submission;

typedef struct {
    int post_id;
    int comment_count;
} Result;

int compare_results(const void* a, const void* b) {
    return ((Result*)a)->post_id - ((Result*)b)->post_id;
}

int countCommentsOptimal(Submission* submissions, int n, Result* result) {
    // Remove duplicates (simplified approach)
    int unique_count = 0;
    for (int i = 0; i < n; i++) {
        int is_duplicate = 0;
        for (int j = 0; j < unique_count; j++) {
            if (submissions[i].sub_id == submissions[j].sub_id && 
                submissions[i].parent_id == submissions[j].parent_id) {
                is_duplicate = 1;
                break;
            }
        }
        if (!is_duplicate) {
            submissions[unique_count++] = submissions[i];
        }
    }
    
    // Find posts
    int posts[MAX_SUBMISSIONS];
    int post_count = 0;
    
    for (int i = 0; i < unique_count; i++) {
        if (submissions[i].parent_id == NULL_PARENT) {
            int exists = 0;
            for (int j = 0; j < post_count; j++) {
                if (posts[j] == submissions[i].sub_id) {
                    exists = 1;
                    break;
                }
            }
            if (!exists) {
                posts[post_count++] = submissions[i].sub_id;
            }
        }
    }
    
    // Count unique comments for each post
    for (int i = 0; i < post_count; i++) {
        int comments[MAX_SUBMISSIONS];
        int comment_count = 0;
        
        for (int j = 0; j < unique_count; j++) {
            if (submissions[j].parent_id == posts[i]) {
                int exists = 0;
                for (int k = 0; k < comment_count; k++) {
                    if (comments[k] == submissions[j].sub_id) {
                        exists = 1;
                        break;
                    }
                }
                if (!exists) {
                    comments[comment_count++] = submissions[j].sub_id;
                }
            }
        }
        
        result[i].post_id = posts[i];
        result[i].comment_count = comment_count;
    }
    
    qsort(result, post_count, sizeof(Result), compare_results);
    return post_count;
}

/*
SQL Equivalent:
SELECT p.sub_id as post_id, 
       COUNT(DISTINCT c.sub_id) as number_of_comments
FROM Submissions p
LEFT JOIN Submissions c ON p.sub_id = c.parent_id
WHERE p.parent_id IS NULL
GROUP BY p.sub_id
ORDER BY p.sub_id;
*/

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database engine optimizes with indexing and hash aggregation

⚡ Linearithmic

Space Complexity

O(n)

Space for join results and grouping hash tables

⚡ Linearithmic Space

23.4K Views

Medium Frequency

~15 min Avg. Time

856 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

SQL Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler