1251. Average Selling Price

SQL 50 · Basic Aggregate Functions (day 3). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

EasySolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One product, including products with no sales.

2. Approach

LEFT JOIN UnitsSold ON product_id AND purchase_date BETWEEN start_date AND end_date. ROUND(SUM(price * units) / SUM(units), 2).

3. The trap

Products with no sales need 0: IFNULL. The date condition belongs in ON.SUM of integers is an integer, so SUM / SUM truncates. Cast one side to numeric. Products with no sales need COALESCE(…, 0).

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

SELECT p.product_id,
  IFNULL(ROUND(SUM(p.price * u.units) / SUM(u.units), 2), 0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold 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;SELECT p.product_id,
  COALESCE(ROUND(SUM(p.price * u.units)::numeric / SUM(u.units), 2), 0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold 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;

Patterns used

Which join · MySQL vs PostgreSQL