Tabla: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+

- (sale_id, year) es la llave primaria (combinación de columnas con valores únicos).
- product_id es una llave foranea (columna de referencia) hacia la tabla Product.
- Cada fila de esta tabla muestra una venta del producto con product_id en un cierto año.
- Nota que el precio es por cada unidad vendida.

Tabla: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+

- product_id es la llave primaria (columna con valores únicos).
- Cada fila de esta tabla indica el nombre y el ID del producto.

El formato del resultado se muestra en el siguiente ejemplo.

Ejemplo 1:

Entrada:

Tabla Sales:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Tabla Product:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Salida:

+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

Solución:

import pandas as pd


def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    # Filter rows, keeping min year for each product
    md = sales.groupby(by='product_id', as_index=False)['year'].min()

    # Join keeping products with min year
    result = md.merge(sales, how='left')

    # Rename and drop columns to match desired output
    result.rename(columns={'year': 'first_year'}, inplace=True)
    result.drop(columns=['sale_id'], inplace=True)

    return result


def test_sales_analysis():
    data = [
        [1, 100, 2008, 10, 5000],
        [2, 100, 2009, 12, 5000],
        [7, 200, 2011, 15, 9000]
    ]
    sales = pd.DataFrame(
        data,
        columns=['sale_id', 'product_id', 'year', 'quantity', 'price']
    ).astype({
        'sale_id': 'Int64',
        'product_id': 'Int64',
        'year': 'Int64',
        'quantity': 'Int64',
        'price': 'Int64'
    })
    data = [
        [100, 'Nokia'],
        [200, 'Apple'],
        [300, 'Samsung']
    ]
    product = pd.DataFrame(
        data,
        columns=['product_id', 'product_name']
    ).astype({'product_id': 'Int64', 'product_name': 'object'})

    data = [
        [100, 2008, 10, 5000],
        [200, 2011, 15, 9000]
    ]
    expected = pd.DataFrame(
        data, columns=['product_id', 'first_year', 'quantity', 'price']
    ).astype({
        'product_id': 'Int64',
        'first_year': 'Int64',
        'quantity': 'Int64',
        'price': 'Int64'
    })

    got = sales_analysis(sales, product)
    pd.testing.assert_frame_equal(got, expected)