Product Sales Analysis III — LeetCode 1070 Python Solution

Table: Sales +-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, year) is the primary key (combination of columns with unique values) of this table. Each row records a sale of a product in a given year.

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
Each row records a sale of a product in a given year.
A product may have multiple sales entries in the same year.
Note that the per-unit price.

import duckdb
import pandas as pd

def solution(sales: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Sales", sales)
    return con.execute("""SELECT
    product_id,
    year AS first_year,
    quantity,
    price
FROM Sales
WHERE
    (product_id, year) IN (
        SELECT
            product_id,
            MIN(year) AS year
        FROM Sales
        GROUP BY product_id
    );""").df()