Tabla: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+

- (product_id, change_date) es la llave primaria (combinación de columnas con valores únicos).
- Cada fila indica que el precio del producto ha cambiado a un nuevo precio el día especificado.

El formato del resultado se muestra en el siguiente ejemplo.

Ejemplo 1:

Entrada:

Tabla Products:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+

Salida:

+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

Solución

import numpy as np
import pandas as pd


def price_at_given_date(products: pd.DataFrame) -> pd.DataFrame:
    given_date = '2019-08-16'
    default_price = 10

    df = products[
        products['change_date'] <= given_date
    ]
    max_change_date = df.groupby(
        'product_id'
    )['change_date'].max().reset_index()
    df = pd.merge(df,
                  max_change_date,
                  how='right',
                  on=['product_id', 'change_date'])

    df['price'] = np.where(df['change_date'] <= given_date,
                           df['new_price'],
                           default_price)

    missing_prods = products[
        ~products['product_id'].isin(df['product_id'])
    ]

    result = df[['product_id', 'price']]
    if not missing_prods.empty:
        missing_prods.insert(loc=0,
                             column='price',
                             value=[10]*len(missing_prods))

        result = pd.concat([
            result,
            missing_prods[['product_id', 'price']]
        ], ignore_index=True).drop_duplicates()

    return result


def test_price_at_given_date():
    data = [
        [1, 20, '2019-08-14'],
        [2, 50, '2019-08-14'],
        [1, 30, '2019-08-15'],
        [1, 35, '2019-08-16'],
        [2, 65, '2019-08-17'],
        [3, 20, '2019-08-18']
    ]
    columns = {
        'product_id': 'Int64',
        'new_price': 'Int64',
        'change_date': 'datetime64[ns]'
    }
    products = pd.DataFrame(
        data, columns=columns.keys()
    ).astype(columns)

    got = price_at_given_date(products)
    expected = pd.DataFrame({
        'product_id': [1, 2, 3],
        'price': [35, 50, 10],
    })
    pd.testing.assert_frame_equal(got, expected, check_dtype=False)