10% discount, so the final price is 1200 - (10% of 1200) = 1080. * For product 4, no discount is available for the Home category, so the final price remains 500. Result table is ordered by product_id in ascending order.

Solution

Method 1 – LEFT JOIN and COALESCE for Discount Application

Intuition

We need to apply a category discount to each product if available. If a product's category has no discount, its price remains unchanged. We use a LEFT JOIN to bring in the discount (if any) and COALESCE to treat missing discounts as 0.

Approach

LEFT JOIN Products with Discounts on category.
Use COALESCE to treat NULL discounts as 0.
Calculate the final price as: price * (1 - discount / 100.0).
Round the final price to 2 decimal places.
Return product_id and final_price, ordered by product_id ascending.

Code

[{"language":"MySQL","code":"SELECT p.product_id, ROUND(p.price * (1 - COALESCE(d.discount, 0) / 100), 2) AS final_price\nFROM Products p\nLEFT JOIN Discounts d ON p.category = d.category\nORDER BY p.product_id ASC;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">SELECT</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\"> p</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">product_id</span><spa

Related Tags