The Most Frequently Ordered Products for Each Customer — LeetCode 1596 Python Solution

Table: Customers +---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id is the column with unique values for this table. This table contains information about the customers.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
This table contains information about the customers.

import duckdb
import pandas as pd

def solution(customers: pd.DataFrame, orders: pd.DataFrame, products: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Customers", customers)
    con.register("Orders", orders)
    con.register("Products", products)
    return con.execute("""WITH
    T AS (
        SELECT
            customer_id,
            product_id,
            RANK() OVER (
                PARTITION BY customer_id
                ORDER BY COUNT(1) DESC
            ) AS rk
        FROM Orders
        GROUP BY 1, 2
    )
SELECT customer_id, product_id, product_name
FROM
    T
    JOIN Products USING (product_id)
WHERE rk = 1;""").df()