1251. Average Selling Price

https://leetcode.com/problems/average-selling-price/description/

SELECT
    p.product_id,
    COALESCE(ROUND(SUM(p.price * IFNULL(u.units, 0)) / NULLIF(SUM(IFNULL(u.units, 0)), 0), 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
ORDER BY
    p.product_id;

NULL 처리에서 계속 애먹어서 찾아봤다.
NULLIF, IFNULL 을 등을 사용해서 NULL 값 확인하고 COALESCE 로 NULL 이면 0값 처리..
단순한거같으면서도 예외처리가 좀 빡샌거같다.

Oracle

SELECT
    p.product_id,
    NVL(ROUND(SUM(p.price * NVL(u.units, 0)) / NULLIF(SUM(NVL(u.units, 0)), 0), 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
ORDER BY
    p.product_id;

동일하지만 NULL 값을 체크, 대체 하는게 NVL 이므로 맞게 바꿔준다.