1251. Average Selling Price : leetcode

# Join tables where the purchase date is within the start and end dates
SELECT *
FROM Prices p INNER JOIN UnitsSold u
    ON p.product_id = u.product_id
WHERE u.purchase_date BETWEEN p.start_date AND p.end_date + 1;

SELECT p.product_id, p.price * u.units as total, u.units 
FROM Prices p INNER JOIN UnitsSold u
    ON p.product_id = u.product_id
WHERE u.purchase_date BETWEEN p.start_date AND p.end_date + 1;

product_id	total	units
1	500	100
1	300	15
2	3000	200
2	900	30

SELECT product_id, SUM(total), sum(units)
FROM (SELECT p.product_id, p.price * u.units as total, u.units 
    FROM Prices p INNER JOIN UnitsSold u
        ON p.product_id = u.product_id
    WHERE u.purchase_date BETWEEN p.start_date AND p.end_date + 1) as a
GROUP BY product_id;

product_id	SUM(total)	sum(units)
1	800	115
2	3900	230

SELECT product_id, IF(sum(units) <> 0, Round(SUM(total) / sum(units), 2), 0) as average_price
FROM (SELECT p.product_id, p.price * u.units as total, u.units 
    FROM Prices p INNER JOIN UnitsSold u
        ON p.product_id = u.product_id
    WHERE u.purchase_date BETWEEN p.start_date AND p.end_date + 1) as a
GROUP BY product_id;

UnitsSold 테이블에 NULL 값이 있는 테스트 케이스로 인한 오류

SELECT product_id, IF(sum(units) IS NOT NULL OR sum(units) <> 0, Round(SUM(total) / sum(units), 2), 0) as average_price
FROM (SELECT p.product_id, p.price * u.units as total, u.units 
    FROM Prices p LEFT JOIN UnitsSold u
        ON p.product_id = u.product_id
    WHERE u.purchase_date BETWEEN p.start_date AND p.end_date + 1) as a
GROUP BY product_id;

LEFT JOIN으로 NULL 값을 나타나도록 하려고 했는데 WHERE절로 인해 성립되지 않는다. 따라서 JOIN 조건절로 WHERE 조건절 이동시킨다.

DATE 형식에서 BETWEEN을 사용하면 경계선의 자정까지가 범위이기 때문에 오후 2:00와 같은 시간은 포함되지 않기 때문에 경계선이 포함이 안 된다고 생각해서 경계선에 +1을 하는 경우가 종종 있다. 하지만, 이는 테이블의 DATE FORMAT에 시간이 나타날 때의 이야기이고, DAY까지만 나오는 형식에서는 BETWEEN을 경계선 포함한다는 의미 그대로 사용하면 된다.

answer

SELECT product_id, IF(sum(units) IS NOT NULL OR sum(units) <> 0, Round(SUM(total) / sum(units), 2), 0) as average_price
FROM (SELECT p.product_id, p.price * u.units as total, u.units 
    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) as a
GROUP BY product_id;