Product Sales Analysis IV — LeetCode 2324 Python Solution

Table: Sales +-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | user_id | int | | quantity | int | +-------------+-------+ sale_id contains unique values. product_id is a foreign key (reference column) to Product table.

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| user_id     | int   |
| quantity    | int   |
+-------------+-------+
sale_id contains unique values.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows the ID of the product and the quantity purchased by a user.

import duckdb
import pandas as pd

def solution(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Sales", sales)
    con.register("Product", product)
    return con.execute("""WITH
    T AS (
        SELECT
            user_id,
            product_id,
            RANK() OVER (
                PARTITION BY user_id
                ORDER BY SUM(quantity * price) DESC
            ) AS rk
        FROM
            Sales
            JOIN Product USING (product_id)
        GROUP BY 1, 2
    )
SELECT user_id, product_id
FROM T
WHERE rk = 1;""").df()