00:00:00

Description

Editorial

Unique Comment Count by Post

EASY10 pts

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:
1. post_id
2. number_of_comments
Output rows ordered by post_id ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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}.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

submissions =

[{"sub_id":1,"parent_id":null},{"sub_id":2,"parent_id":null},{"sub_id":1,"parent_id":null},{"sub_id":12,"parent_id":null},{"sub_id":3,"parent_id":1},{"sub_id":5,"parent_id":2},{"sub_id":3,"parent_id":1},{"sub_id":4,"parent_id":1},{"sub_id":9,"parent_id":1},{"sub_id":10,"parent_id":2},{"sub_id":6,"parent_id":7}]

Loading problem...

101

00:00:00

Description

Editorial

Unique Comment Count by Post

EASY10 pts

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:
1. post_id
2. number_of_comments
Output rows ordered by post_id ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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}.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

submissions =

Unique Comment Count by Post

Hints

Unique Comment Count by Post

Hints