Generate the Invoice — LeetCode 2362 Python Solution

Table: Products +-------------+------+ | Column Name | Type | +-------------+------+ | product_id | int | | price | int | +-------------+------+ product_id contains unique values. Each row in this table shows the ID of a product and the price of one unit.

+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | int  |
| price       | int  |
+-------------+------+
product_id contains unique values.
Each row in this table shows the ID of a product and the price of one unit.

import duckdb
import pandas as pd

def solution(products: pd.DataFrame, purchases: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Products", products)
    con.register("Purchases", purchases)
    return con.execute("""WITH
    P AS (
        SELECT *
        FROM
            Purchases
            JOIN Products USING (product_id)
    ),
    T AS (
        SELECT invoice_id, SUM(price * quantity) AS amount
        FROM P
        GROUP BY invoice_id
        ORDER BY 2 DESC, 1
        LIMIT 1
    )
SELECT product_id, quantity, (quantity * price) AS price
FROM
    P
    JOIN T USING (invoice_id);""").df()