Problem

Table: Products

1
2
3
4
5
6
7
8
9

+------------+---------+
| Column Name| Type    |
+------------+---------+
| product_id | int     |
| category   | varchar |
| price      | decimal |
+------------+---------+
product_id is the unique key for this table.
Each row includes the product's ID, its category, and its price.

Table: Discounts

+------------+---------+
| Column Name| Type    |
+------------+---------+
| category   | varchar |
| discount   | int     |
+------------+---------+
category is the primary key for this table.
Each row contains a product category and the percentage discount applied to that category (values range from 0 to 100).

Write a solution to find the final price of each product after applying the category discount. If a product’s category has no associated discount , its price remains unchanged.

Return the result table ordered by product_id inascending order.

The result format is in the following example.

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

Input:
`Products` table:
+------------+-------------+-------+
| product_id | category    | price |
+------------+-------------+-------+
| 1          | Electronics | 1000  |
| 2          | Clothing    | 50    |
| 3          | Electronics | 1200  | 
| 4          | Home        | 500   |
+------------+-------------+-------+
`Discounts` table:
+------------+----------+
| category   | discount |
+------------+----------+
| Electronics| 10       |
| Clothing   | 20       |
+------------+----------+
Output:
+------------+------------+-------------+
| product_id | final_price| category    |
+------------+------------+-------------+
| 1          | 900        | Electronics |
| 2          | 40         | Clothing    |
| 3          | 1080       | Electronics |
| 4          | 500        | Home        |
+------------+------------+-------------+
Explanation:
* For product 1, it belongs to the Electronics category which has a 10% discount, so the final price is 1000 - (10% of 1000) = 900.
* For product 2, it belongs to the Clothing category which has a 20% discount, so the final price is 50 - (20% of 50) = 40.
* For product 3, it belongs to the Electronics category and receives a 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

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

Code

1
2
3
4

SELECT p.product_id, ROUND(p.price * (1 - COALESCE(d.discount, 0) / 100), 2) AS final_price
FROM Products p
LEFT JOIN Discounts d ON p.category = d.category
ORDER BY p.product_id ASC;

Complexity

  • ⏰ Time complexity: O(n) — n is the number of products (single scan and join).
  • 🧺 Space complexity: O(1) — Only accumulators are used.