Number of Comments per Post — LeetCode 1241 Python Solution

Table: Submissions +---------------+----------+ | Column Name | Type | +---------------+----------+ | sub_id | int | | parent_id | int | +---------------+----------+ This table may have duplicate rows. Each row can be a post or comment on the post.

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| sub_id        | int      |
| parent_id     | int      |
+---------------+----------+
This table may have duplicate rows.
Each row can be a post or comment on the post.
parent_id is null for posts.
parent_id for comments is sub_id for another post in the table.

import duckdb
import pandas as pd

def solution(submissions: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Submissions", submissions)
    return con.execute("""WITH
    t AS (
        SELECT DISTINCT s1.sub_id AS post_id, s2.sub_id AS sub_id
        FROM
            Submissions AS s1
            LEFT JOIN Submissions AS s2 ON s1.sub_id = s2.parent_id
        WHERE s1.parent_id IS NULL
    )
SELECT post_id, COUNT(sub_id) AS number_of_comments
FROM t
GROUP BY post_id
ORDER BY post_id;""").df()