Product's Worth Over Invoices — LeetCode 1677 Python Solution

Table: Product +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | name | varchar | +-------------+---------+ product_id is the column with unique values for this table. This table contains the ID and the name of the product.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| name        | varchar |
+-------------+---------+
product_id is the column with unique values for this table.
This table contains the ID and the name of the product. The name consists of only lowercase English letters. No two products have the same name.

import duckdb
import pandas as pd

def solution(product: pd.DataFrame, invoice: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Product", product)
    con.register("Invoice", invoice)
    return con.execute("""SELECT
    name,
    IFNULL(SUM(rest), 0) AS rest,
    IFNULL(SUM(paid), 0) AS paid,
    IFNULL(SUM(canceled), 0) AS canceled,
    IFNULL(SUM(refunded), 0) AS refunded
FROM
    Product
    LEFT JOIN Invoice USING (product_id)
GROUP BY product_id
ORDER BY name;""").df()