Unique Comment Count by Post

Easy

A social discussion platform stores both posts and comments in a single event table. Table: submissions sub_id: submission identifier parent_id: null for posts; non-null for comments Data semantics: A row with parent_id = NULL is a post. A row with parent_id != NULL is a comment whose parent_id points to a post id. Duplicate rows may appear for both posts and comments. Some comment rows may reference missing posts; those comments must be ignored. Task: For every distinct post, return how many unique comments it has. Counting rules: Treat duplicate post rows as one post. Count unique comment ids per post (duplicate comment rows count once). Include posts with zero comments. Exclude comments whose parent_id is not a valid post id. Output requirements: Return exactly these columns in this order: post_id number_of_comments Output rows ordered by post_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

submissions:
| 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         |

Output

[
  {"post_id":1,"number_of_comments":3},
  {"post_id":2,"number_of_comments":2},
  {"post_id":12,"number_of_comments":0}
]

Explanation

Posts are {1, 2, 12}. Post 1 has unique comments {3, 4, 9}. Post 2 has unique comments {5, 10}. Comment (6 -> 7) is ignored because post 7 does not exist.

Example 2

Input

submissions:
| sub_id | parent_id |
|--------|-----------|
| 100    | null      |
| 200    | null      |
| 301    | 100       |
| 302    | 100       |
| 302    | 100       |
| 401    | 301       |
| 500    | 900       |

Output

[
  {"post_id":100,"number_of_comments":2},
  {"post_id":200,"number_of_comments":0}
]

Explanation

Duplicate comment row (302 -> 100) counts once. Row (401 -> 301) is ignored because 301 is not a post row. Row (500 -> 900) is also ignored because post 900 does not exist.

Example 3

Input

submissions:
| sub_id | parent_id |
|--------|-----------|
| 50     | null      |
| 75     | null      |
| 50     | null      |
| 800    | 75        |
| 801    | 75        |
| 802    | 75        |
| 802    | 75        |

Output

[
  {"post_id":50,"number_of_comments":0},
  {"post_id":75,"number_of_comments":3}
]

Explanation

Post 50 appears more than once but is treated as one post with zero comments. Post 75 has unique comments {800, 801, 802}.

Constraints

1 <= number of rows in submissions <= 10,000
1 <= sub_id <= 1,000,000,000
parent_id is NULL or in [1, 1,000,000,000]
Duplicate rows may exist
A post is identified by parent_id IS NULL
Comments referencing missing posts must not be counted
Count unique comment sub_id values per post
Include every distinct post exactly once in output
Output must be sorted by post_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: post_id, number_of_comments

submissions: | 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 |

submissions: | sub_id | parent_id | |--------|-----------| | 100 | null | | 200 | null | | 301 | 100 | | 302 | 100 | | 302 | 100 | | 401 | 301 | | 500 | 900 |

submissions: | sub_id | parent_id | |--------|-----------| | 50 | null | | 75 | null | | 50 | null | | 800 | 75 | | 801 | 75 | | 802 | 75 | | 802 | 75 |