Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: LeetCode 1251. Average Selling Price (15/17 Test Cases Passed)

SELECT p.product_id, 
ROUND(COALESCE(SUM(price * units) / NULLIF(SUM(units), 0), 0), 2) AS average_price
FROM Prices p
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;

Only passes 15/17 test cases, not sure why

  • Tried using NULLIF, COALESCE function

  • syntax correct, passes test case in leetcode

Tags:

2

Answers


  1. 0

    You should still output a product even if it has no entry in the UnitsSold table, so use a left outer join instead of an inner join.

    LEFT JOIN UnitsSold u ON u.product_id = p.product_id
AND u.purchase_date BETWEEN p.start_date AND p.end_date
    Login or Signup to reply.
  2. 0

    As you can see

    For the price you can use 0 as alternative Value for NULL

    but for divider you nned to return 1

    Final word before this will be deleted, you need only the last query for your test

    SELECT p.product_id, 
ROUND(SUM(price * COALESCE(units,0)) / SUM(COALESCE(units,1)),2) 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;
    product_id average_price
    1 6.96
    2 16.96 
    SELECT p.product_id, 
ROUND(SUM(price * units) / SUM(units),2) AS average_price
FROM Prices p
INNER 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;
    product_id average_price
    1 6.96
    2 16.96

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search