Market Analysis III — LeetCode 2922 Python Solution

Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | seller_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ seller_id is column of unique values for this table. This table contains seller id, join date, and favorite brand of sellers.

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| seller_id      | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
seller_id is column of unique values for this table.
This table contains seller id, join date, and favorite brand of sellers.

import duckdb
import pandas as pd

def solution(users: pd.DataFrame, items: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Users", users)
    con.register("Items", items)
    con.register("Orders", orders)
    return con.execute("""WITH
    T AS (
        SELECT seller_id, COUNT(DISTINCT item_id) AS num_items
        FROM
            Orders
            JOIN Users USING (seller_id)
            JOIN Items USING (item_id)
        WHERE item_brand != favorite_brand
        GROUP BY 1
    )
SELECT seller_id, num_items
FROM T
WHERE num_items = (SELECT MAX(num_items) FROM T)
ORDER BY 1;""").df()