Reported Posts II — LeetCode 1132 Python Solution

Table: Actions +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | post_id | int | | action_date | date | | action | enum | | extra | varchar | +---------------+---------+ This table may have duplicate rows. The action column is an ENUM (category) type of ('view', 'like', 'reaction', 'comment', 'report', 'share').

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    | 
| action        | enum    |
| extra         | varchar |
+---------------+---------+
This table may have duplicate rows.
The action column is an ENUM (category) type of ('view', 'like', 'reaction', 'comment', 'report', 'share').
The extra column has optional information about the action, such as a reason for the report or a type of reaction.

import duckdb
import pandas as pd

def solution(actions: pd.DataFrame, removals: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Actions", actions)
    con.register("Removals", removals)
    return con.execute("""WITH
    T AS (
        SELECT
            COUNT(DISTINCT t2.post_id) / COUNT(DISTINCT t1.post_id) * 100 AS percent
        FROM
            Actions AS t1
            LEFT JOIN Removals AS t2 ON t1.post_id = t2.post_id
        WHERE extra = 'spam'
        GROUP BY action_date
    )
SELECT ROUND(AVG(percent), 2) AS average_daily_percent
FROM T;""").df()