1251. Average Selling Price

Problem¶

Given price intervals and sold units, compute each product's weighted average selling price:

\[ \text{average_price} = \frac{\sum \text{price at purchase date} \times \text{units}}{\sum \text{units}} \]

Return 0 for products with no sold units, rounded to 2 decimals.

Input:

# Prices
-----------+------------+------------+------
product_id | start_date | end_date   | price
-----------+------------+------------+------
1          | 2019-02-17 | 2019-02-28 | 5
1          | 2019-03-01 | 2019-03-22 | 20
2          | 2019-02-01 | 2019-02-20 | 15
2          | 2019-02-21 | 2019-03-31 | 30

# UnitsSold
-----------+---------------+------
product_id | purchase_date | units
-----------+---------------+------
1          | 2019-02-25    | 100
1          | 2019-03-01    | 15
2          | 2019-02-10    | 200
2          | 2019-03-22    | 30

Output:

-----------+--------------
product_id | average_price
-----------+--------------
1          | 6.96
2          | 16.96

Key trick¶

Join sales to the price interval using an inclusive date range, then compute a weighted average, not a simple average of prices.

Trap¶

Putting the date filter in WHERE can accidentally turn a LEFT JOIN into an INNER JOIN.
Products with no sales must still appear with 0.
The denominator is SUM(units), not the number of rows or price periods.
Date boundaries are inclusive: start_date <= purchase_date <= end_date.

Why is it interesting?¶

This tests whether you understand range joins, weighted aggregation, null handling, and preserving unmatched rows with LEFT JOIN.

SQL solution¶

SELECT
    p.product_id,

    -- Weighted average:
    -- numerator   = total revenue = SUM(units * price)
    -- denominator = total units   = SUM(units)
    -- NULLIF avoids division by zero.
    -- COALESCE returns 0 for products with no sales.
    COALESCE(
        ROUND(
            CAST(SUM(u.units * p.price) AS NUMERIC) / NULLIF(SUM(u.units), 0),
            2
        ),
        0
    ) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
   AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

This works in SQLite; no separate PostgreSQL version is needed here.

Pandas solution¶

import pandas as pd


def average_selling_price(
    prices: pd.DataFrame,
    units_sold: pd.DataFrame,
) -> pd.DataFrame:
    # Output must include every product appearing in Prices.
    products = prices[["product_id"]].drop_duplicates()

    # Join by product first, then keep rows where the sale date falls in the price interval.
    merged = prices.merge(units_sold, on="product_id", how="left")

    valid_sale = merged["purchase_date"].between(
        merged["start_date"],
        merged["end_date"],
        inclusive="both",
    )

    matched = merged.loc[valid_sale].copy()

    # Weighted average = total revenue / total units.
    matched["revenue"] = matched["price"] * matched["units"]

    agg = (
        matched.groupby("product_id", as_index=False)
        .agg(
            revenue=("revenue", "sum"),
            units=("units", "sum"),
        )
    )

    agg["average_price"] = (agg["revenue"] / agg["units"]).round(2)

    # Products without matched sales get 0.
    result = products.merge(
        agg[["product_id", "average_price"]],
        on="product_id",
        how="left",
    )

    result["average_price"] = result["average_price"].fillna(0.0)

    return result[["product_id", "average_price"]]

Pytest test¶

import sqlite3

import pandas as pd
import pytest
from pandas.testing import assert_frame_equal


SQL_QUERY = """
SELECT
    p.product_id,
    COALESCE(
        ROUND(
            CAST(SUM(u.units * p.price) AS NUMERIC) / NULLIF(SUM(u.units), 0),
            2
        ),
        0
    ) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
   AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;
"""


def pandas_solution(prices: pd.DataFrame, units_sold: pd.DataFrame) -> pd.DataFrame:
    products = prices[["product_id"]].drop_duplicates()

    merged = prices.merge(units_sold, on="product_id", how="left")

    valid_sale = merged["purchase_date"].between(
        merged["start_date"],
        merged["end_date"],
        inclusive="both",
    )

    matched = merged.loc[valid_sale].copy()
    matched["revenue"] = matched["price"] * matched["units"]

    agg = (
        matched.groupby("product_id", as_index=False)
        .agg(
            revenue=("revenue", "sum"),
            units=("units", "sum"),
        )
    )

    agg["average_price"] = (agg["revenue"] / agg["units"]).round(2)

    result = products.merge(
        agg[["product_id", "average_price"]],
        on="product_id",
        how="left",
    )

    result["average_price"] = result["average_price"].fillna(0.0)

    return result[["product_id", "average_price"]]


def run_sql(prices_rows, units_rows) -> pd.DataFrame:
    conn = sqlite3.connect(":memory:")

    conn.execute(
        """
        CREATE TABLE Prices (
            product_id INTEGER,
            start_date TEXT,
            end_date TEXT,
            price INTEGER
        )
        """
    )

    conn.execute(
        """
        CREATE TABLE UnitsSold (
            product_id INTEGER,
            purchase_date TEXT,
            units INTEGER
        )
        """
    )

    conn.executemany(
        """
        INSERT INTO Prices (product_id, start_date, end_date, price)
        VALUES (?, ?, ?, ?)
        """,
        prices_rows,
    )

    conn.executemany(
        """
        INSERT INTO UnitsSold (product_id, purchase_date, units)
        VALUES (?, ?, ?)
        """,
        units_rows,
    )

    return pd.read_sql_query(SQL_QUERY, conn)


def make_pandas_inputs(prices_rows, units_rows):
    prices = pd.DataFrame(
        prices_rows,
        columns=["product_id", "start_date", "end_date", "price"],
    )

    units_sold = pd.DataFrame(
        units_rows,
        columns=["product_id", "purchase_date", "units"],
    )

    prices["start_date"] = pd.to_datetime(prices["start_date"])
    prices["end_date"] = pd.to_datetime(prices["end_date"])

    if not units_sold.empty:
        units_sold["purchase_date"] = pd.to_datetime(units_sold["purchase_date"])
    else:
        units_sold = units_sold.astype(
            {
                "product_id": "int64",
                "purchase_date": "datetime64[ns]",
                "units": "int64",
            }
        )

    return prices, units_sold


def normalize(df: pd.DataFrame) -> pd.DataFrame:
    result = df.copy()
    result["product_id"] = result["product_id"].astype("int64")
    result["average_price"] = result["average_price"].astype("float64").round(2)

    return (
        result[["product_id", "average_price"]]
        .sort_values("product_id")
        .reset_index(drop=True)
    )


@pytest.mark.parametrize(
    "prices_rows, units_rows, expected_rows",
    [
        (
            [
                (1, "2019-02-17", "2019-02-28", 5),
                (1, "2019-03-01", "2019-03-22", 20),
                (2, "2019-02-01", "2019-02-20", 15),
                (2, "2019-02-21", "2019-03-31", 30),
            ],
            [
                (1, "2019-02-25", 100),
                (1, "2019-03-01", 15),
                (2, "2019-02-10", 200),
                (2, "2019-03-22", 30),
            ],
            [
                (1, 6.96),
                (2, 16.96),
            ],
        ),
        (
            [
                (1, "2020-01-01", "2020-01-31", 10),
            ],
            [],
            [
                (1, 0.0),
            ],
        ),
        (
            [
                (1, "2020-01-01", "2020-01-10", 10),
                (1, "2020-01-11", "2020-01-20", 20),
                (2, "2020-01-01", "2020-01-31", 50),
            ],
            [
                (1, "2020-01-01", 1),
                (1, "2020-01-10", 2),
                (1, "2020-01-11", 3),
                (1, "2020-01-11", 2),
            ],
            [
                (1, 16.25),
                (2, 0.0),
            ],
        ),
    ],
)
def test_average_selling_price_sql_and_pandas(
    prices_rows,
    units_rows,
    expected_rows,
):
    expected = normalize(
        pd.DataFrame(
            expected_rows,
            columns=["product_id", "average_price"],
        )
    )

    sql_result = normalize(run_sql(prices_rows, units_rows))

    prices, units_sold = make_pandas_inputs(prices_rows, units_rows)
    pandas_result = normalize(pandas_solution(prices, units_sold))

    assert_frame_equal(sql_result, expected)
    assert_frame_equal(pandas_result, expected)

Comment on my solution¶

Your SQL idea is correct: join prices to sales, filter by date interval, then aggregate.
The date condition is better placed inside the ON clause, otherwise products with no matching sales can be lost.
SUM(COALESCE(u.units, 1)) is conceptually wrong; use NULLIF(SUM(u.units), 0) and COALESCE the final result to 0.
In Pandas, chained comparisons do not work element-wise, so use between or combine boolean masks with &.
There is a typo: merge["end_date"] should refer to merged.
For Pandas aggregation, compute a revenue column first, then group by product_id.

-- product_1 - (price_1 * units_1 + price_2 * units_2) / (units_1 + units_2)
-- product_2 - 0 (product_2 doesn't appear in UnitsSold)
--
-- - left join Prices x UnitsSold
-- - keep row with start_date < purchase_date < end_date
-- - group by product_id
-- - compute average on aggregated product_id group
--
-- Works
SELECT
    p.product_id,
    ROUND(CAST(SUM(COALESCE(u.units, 0) * p.price) AS DECIMAL(10,2)) / SUM(COALESCE(u.units, 1)), 2) AS average_price
FROM Prices AS p
LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
WHERE (p.start_date <= u.purchase_date AND u.purchase_date <= p.end_date)
   OR u.purchase_date IS NULL
GROUP BY p.product_id;

import pandas as pd

# I don't see how to write in Pandas!
def average_selling_price(prices: pd.DataFrame, units_sold: pd.DataFrame) -> pd.DataFrame:
    merged = prices.merge(units_sold, on="product_id", how="left")
    mask = (merged["start_date"] <= merged["purchase_date"] <= merge["end_date"]) | merged["purchase_date"].isna()
    return (
        merged.loc[mask]
        .groupby("product_id")
        .agg(average_price=...)
        [["product_id", "average_price"]]
    )

Extra¶

GROUP BY rule¶

In SQL, once you use GROUP BY, every selected column must be either:

in the GROUP BY
or wrapped in an aggregate like SUM, MAX, MIN, etc.

Use CAST to force decimal devision¶

Integer division behavior depends on the SQL dialect.

SELECT numerator / denominator AS result;

To force decimal division, cast at least one operand:

SELECT CAST(numerator AS DECIMAL) / denominator AS result;
SELECT CAST(numerator AS DECIMAL(10,2)) / denominator AS result;

NULLIF¶

What does NULLIF do in that SQL snippet?
SUM(u.units * p.price) / NULLIF(SUM(u.units), 0)

NULLIF(SUM(u.units), 0) returns NULL if SUM(u.units) is 0; otherwise it returns SUM(u.units).

In the above snippet, it prevents division by zero. If total units are 0, the denominator becomes NULL, so the whole division returns NULL instead of causing a divide-by-zero error.

Does this means that any SQL operations like /, SUM, ROUND, etc. when filled with a NULL argument will return NULL?

No. Many SQL operations are NULL-propagating, but not all.

Common behavior:

Arithmetic operators usually return NULL if any operand is NULL:

10 / NULL -- NULL

Functions like ROUND(NULL) usually return NULL:

ROUND(NULL, 2) -- NULL

Aggregate functions usually ignore NULL values. The following sums only non-NULL values:

SUM(column_with_nulls)

If all values are NULL, SUM returns NULL:

SUM(NULL) -- NULL

COUNT is different:

COUNT(column_name) -- Counts non-`NULL` values.
COUNT(*) -- Counts rows, including rows with `NULL`.

Another Pandas solution¶

In the pandas solution, why do we have to merge again to produce result like this:
result = products.merge(
    agg[["product_id", "average_price"]],
    on="product_id",
    how="left",
)
Can't we just do this?
agg["average_price"] = agg["average_price"].fillna(0.0)
return agg[["product_id", "average_price"]]
Aren't NaN cells already present in agg DataFrame?

Is it because of this mask which removes NaN row?
valid_sale = merged["purchase_date"].between(
        merged["start_date"],
        merged["end_date"],
        inclusive="both",
    )
But if this is the reason, do we exclude NaN because this would cause a problem when doing multiplication and division after? Is there NULLIF king of thing in pandas?

Short answer¶

Yes, the second merge is needed because agg contains only products that had at least one valid matched sale.

Products with no valid sale are removed here:

matched = merged.loc[valid_sale].copy()

So they are not present in agg at all, meaning there is no NaN cell to fill.

What happens step by step¶

Suppose product 2 has no sales.

After the left merge, you may have:

product_id | start_date | end_date   | price | purchase_date | units
-----------+------------+------------+-------+---------------+------
1          | 2020-01-01 | 2020-01-31 | 10    | 2020-01-10    | 5
2          | 2020-01-01 | 2020-01-31 | 20    | NaT           | NaN

Then this mask:

valid_sale = merged["purchase_date"].between(
    merged["start_date"],
    merged["end_date"],
    inclusive="both",
)

produces:

0    | True
1    | False
dtype: bool

So after filtering:

matched = merged.loc[valid_sale].copy()

you get only:

product_id | start_date | end_date   | price | purchase_date | units
-----------+------------+------------+-------+---------------+------
1          | 2020-01-01 | 2020-01-31 | 10    | 2020-01-10    | 5

Then groupby creates agg from matched, so product 2 does not exist in agg.

Therefore this will not help:

agg["average_price"] = agg["average_price"].fillna(0.0)

Because there is no row for product 2.

Why the second merge fixes it¶

This line saves the complete list of products first:

products = prices[["product_id"]].drop_duplicates()

Then this restores missing products after aggregation:

result = products.merge(
    agg[["product_id", "average_price"]],
    on="product_id",
    how="left",
)

Now product 2 reappears with NaN:

product_id | average_price
-----------+--------------
1          | 10.0
2          | NaN

Then fillna works:

result["average_price"] = result["average_price"].fillna(0.0)

Is this because the mask removes NaN rows?¶

Yes.

The mask returns False for rows where purchase_date is NaT, so products with no sales are dropped before aggregation.

This is similar to SQL behavior if you write the date condition in WHERE instead of ON.

Bad SQL shape:

FROM Prices AS p
LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
WHERE u.purchase_date BETWEEN p.start_date AND p.end_date

That removes unmatched rows.

Better SQL shape:

FROM Prices AS p
LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
   AND u.purchase_date BETWEEN p.start_date AND p.end_date

In Pandas, since we usually filter after the merge, we need to restore the product list later.

Do NaN values cause multiplication or division problems?¶

They can, but that is not the main issue here.

If a row with no sale remained, multiplication would produce NaN:

matched["revenue"] = matched["price"] * matched["units"]

Example:

price | units | revenue
------+-------+--------
20    | NaN   | NaN

But the bigger issue is that after filtering with valid_sale, that row is gone entirely.

So:

If the row exists with NaN, fillna can fix it.
If the row was removed, fillna cannot fix it.

Pandas equivalent of `NULLIF`¶

There is no exact single NULLIF function commonly used like in SQL, but the Pandas equivalent is usually where, mask, or replacing values.

SQL:

NULLIF(SUM(units), 0)

Pandas equivalent:

denominator = agg["units"].where(agg["units"] != 0)

Then division by zero becomes division by NaN:

agg["average_price"] = (agg["revenue"] / denominator).round(2)

Another common version:

denominator = agg["units"].replace(0, pd.NA)

Then:

agg["average_price"] = (agg["revenue"] / denominator).round(2)

For this problem, if matched only contains valid sales, units should normally be positive, so denominator zero is not the main concern.

Alternative Pandas solution without the second merge¶

You can keep unmatched rows and make invalid sales contribute zero revenue and zero units.

import pandas as pd


def average_selling_price(prices: pd.DataFrame, units_sold: pd.DataFrame) -> pd.DataFrame:
    merged = prices.merge(units_sold, on="product_id", how="left")

    valid_sale = merged["purchase_date"].between(
        merged["start_date"],
        merged["end_date"],
        inclusive="both",
    )

    # Invalid or missing sales contribute 0 revenue and 0 units.
    merged["weighted_units"] = merged["units"].where(valid_sale, 0)
    merged["revenue"] = (merged["price"] * merged["units"]).where(valid_sale, 0)

    agg = (
        merged.groupby("product_id", as_index=False)
        .agg(
            revenue=("revenue", "sum"),
            units=("weighted_units", "sum"),
        )
    )

    denominator = agg["units"].where(agg["units"] != 0)

    agg["average_price"] = (agg["revenue"] / denominator).round(2).fillna(0.0)

    return agg[["product_id", "average_price"]]

Here, the product with no sale is kept through the whole pipeline, so no final merge is needed.

On the LEFT JOIN matching clause¶

Does the part ON ... AND ... in the LEFT JOIN means:

for each row in Prices

if there is a row in UnitsSold with matching product_id

if that row has purchase_date in the right range in Prices's row

add the corresponding row

if not, don't add any row for tha row in UnitsSold

if no matching row (regarding product_id) can be found in UnitsSold

add a row completing row in Prices with NULL values in column from UnitsSold
FROM Prices AS p
LEFT JOIN UnitsSold AS u
    ON p.product_id = u.product_id
   AND u.purchase_date BETWEEN p.start_date AND p.end_date

pd.NaT, pd.NA, np.nan¶

pd.NaT¶

In pandas, NaT means "Not a Time" and represents missing datetime-like values.

import pandas as pd
pd.Series([pd.Timestamp("2024-01-01"), pd.NaT])
# 0   2024-01-01
# 1          NaT
# dtype: datetime64[us]

It is used for missing values in:

datetime
timedelta
period-like data

pd.NA vs. np.nan¶

np.nan is a floating-point missing value.
pd.NA is pandas’ scalar for nullable dtypes like Int64, boolean, and string.

import pandas as pd
import numpy as np

pd.Series([1.0, np.nan])
# 0    1.0
# 1    NaN
# dtype: float64

pd.Series([1, pd.NA], dtype="Int64")
# 0       1
# 1    <NA>
# dtype: Int64