Average Selling Price

The average selling price of a product is not the average of its list prices — it is total money taken in divided by total units moved, so a price that applied to 100 sales must count 100 times while a price that applied to 15 sales counts 15 times. That single fact decides the whole query: you weight each price period by the units sold inside it, which is why the answer is SUM(units * price) / SUM(units) and never AVG(price).

The problem

Two tables. Prices gives each product a list of non-overlapping date windows, each with a price. UnitsSold records individual sales, each with a purchase_date and a unit count. For every product we want one row: its units-weighted average selling price, rounded to 2 decimals. Products that never sold must still appear (with 0).

Prices                                  UnitsSold
+------------+------------+----------+   +------------+---------------+-------+
| product_id | start_date | end_date | price |  product_id | purchase_date | units |
+------------+------------+----------+---+   +------------+---------------+-------+
| 1          | 2019-02-17 | 2019-02-28 |  5 |   | 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01 | 2019-03-22 | 20 |   | 1          | 2019-03-01    | 15    |
| 2          | 2019-02-01 | 2019-02-20 | 15 |   | 2          | 2019-02-10    | 200   |
| 2          | 2019-02-21 | 2019-03-31 | 30 |   | 2          | 2019-03-22    | 30    |
+------------+------------+----------+---+   +------------+---------------+-------+

Notice a sale carries no price of its own. The price lives in Prices, keyed by which window the purchase_date falls into. So before we can do any arithmetic we must route each sale to its price.

Why a weighted average — the trap

It is tempting to write AVG(price) after the join. For product 1 that gives (5 + 20) / 2 = 12.50. But the real answer is 6.96. The two prices are nowhere near equally important: 100 units sold at 5, only 15 units at 20. AVG(price) treats both price rows as one vote each; the market does not. The correct mean is the volume-weighted one:

          SUM(units * price)     (100*5) + (15*20)     500 + 300     800
weighted = ------------------  =  -----------------  =  ---------  =  ---  = 6.9565… → 6.96
          SUM(units)              100 + 15              115           115

This is identical to SUM(revenue) / SUM(quantity) — the only definition of "average price" a finance or analytics team would accept. Memorize the shape: weight each value by the count it represents, sum the weighted values, divide by the sum of weights.

The query

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

Three load-bearing pieces:

• The join is on two conditions, not one. Equality on product_id alone would attach every sale to every price window of that product. The extra purchase_date BETWEEN start_date AND end_date is what selects the one window that was in effect on the day of the sale. This is a range join, and it is the actual heart of the problem.
• LEFT JOIN, not INNER JOIN. We are driving from Prices. A product that exists in Prices but never sold must survive into the result. INNER JOIN would silently drop it.
• IFNULL guards the no-sales case. For an unsold product the LEFT JOIN produces one row with all UnitsSold columns NULL. Then SUM(units) is NULL (SUM over an all-NULL group is NULL, not 0), the division is NULL / NULL = NULL, and IFNULL(..., 0) turns that into the required 0.

Traced end to end

After the range join, each sale sits next to the price that was in force on its date:

p.product_id | price | u.units | purchase_date | units*price
-------------+-------+---------+---------------+-----------
     1       |   5   |   100   | 2019-02-25    |   500     <- 02-25 in [02-17,02-28]
     1       |  20   |    15   | 2019-03-01    |   300     <- 03-01 in [03-01,03-22]
     2       |  15   |   200   | 2019-02-10    |  3000     <- 02-10 in [02-01,02-20]
     2       |  30   |    30   | 2019-03-22    |   900     <- 03-22 in [02-21,03-31]

Now GROUP BY product_id collapses each product and the SUMs run:

product_1: SUM(units*price)=500+300=800   SUM(units)=100+15=115   800/115 = 6.9565 -> 6.96
product_2: SUM(units*price)=3000+900=3900 SUM(units)=200+30=230   3900/230 = 16.9565 -> 16.96

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

Why the naive version is wrong

Three rewrites that look reasonable and all break:

• AVG(price) — gives 12.50 for product 1. Averages the price rows, ignoring how many units each row sold. This is the single most common wrong answer.
• SUM(units * price) / COUNT(*) — divides revenue by the number of sale rows instead of total units. For product 1 that is 800 / 2 = 400, nonsense. The denominator must be SUM(units).
• INNER JOIN instead of LEFT JOIN — correct for products that sold, but a product with zero sales vanishes from the output entirely instead of returning 0. The grader's hidden tests include such a product.

Pitfalls

• Integer division. In MySQL / already yields a decimal, so 800/115 is 6.9565. But in PostgreSQL, integer / integer truncates: 800/115 would give 6 before ROUND ever sees it. On Postgres cast first: SUM(units * price)::numeric / SUM(units). Know your engine.
• SUM over NULLs is NULL, not 0. People expect the unsold-product group to compute 0/0 and reach for NULLIF to dodge a divide-by-zero. There is no division error here — SUM of an all-NULL column is NULL, the whole expression is NULL, and IFNULL/COALESCE is the right tool. (A real 0/0 only happens if a row had units = 0; guard that with SUM(units) in a NULLIF(SUM(units),0) if your data allows zero-unit sales.)
• Overlapping price windows. The whole approach assumes a sale falls into exactly one window. The problem guarantees no overlaps per product. If that guarantee did not hold, a sale would match two windows, get counted twice, and the SUMs would inflate. Always confirm the windows are disjoint before trusting a BETWEEN range join.
• Inclusive boundaries. BETWEEN is inclusive on both ends. If two windows shared a boundary date (one ending and the next starting on the same day), a sale on that day would match both. Disjoint windows here avoid it, but in production with adjacent ranges prefer >= start AND < next_start half-open intervals.

Takeaways

• "Average price" in business terms is always SUM(revenue)/SUM(quantity) — a units-weighted mean. AVG(price) answers a different, almost-never-asked question.
• When a fact (the price) depends on which time window another row falls into, the join carries a BETWEEN range condition alongside the equality. That range condition is the real logic.
• Drive the join from the table whose rows must all survive, use LEFT JOIN, and wrap the aggregate in IFNULL/COALESCE so the no-match group produces a real default instead of NULL.
• Before deploying a BETWEEN range join, prove the windows are non-overlapping — otherwise rows double-count silently.

Based on LeetCode 1251 "Average Selling Price" (the canonical Prices / UnitsSold dataset). Weighted-mean reasoning follows standard analytics practice (volume-weighted average price); NULL-aggregation and integer-division behavior cross-checked against the MySQL and PostgreSQL documentation. Re-authored and deepened for this guide to name the central insight — why a units-weighted average is required and how the LEFT-JOIN-to-NULL path makes IFNULL necessary — rather than narrate clauses.