Calculate Product Final Price - Problem

You are given two tables: Products and Discounts.

The Products table contains information about products including their ID, category, and price. The Discounts table contains the discount percentage for each product category.

Write a SQL query to calculate 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 in ascending order.

Table Schema

Products
Column Name Type Description
product_id PK int Unique identifier for each product
category varchar Product category
price decimal Original product price
Primary Key: product_id
Discounts
Column Name Type Description
category PK varchar Product category
discount int Discount percentage (0-100)
Primary Key: category

Input & Output

Example 1 — Products with Mixed Discounts
Input Tables:
Products
product_id category price
1 Electronics 1000
2 Clothing 50
3 Electronics 1200
4 Home 500
Discounts
category discount
Electronics 10
Clothing 20
Output:
product_id final_price category
1 900 Electronics
2 40 Clothing
3 1080 Electronics
4 500 Home
💡 Note:

Electronics products get 10% discount (1000 → 900, 1200 → 1080), Clothing gets 20% discount (50 → 40), and Home category has no discount so price remains 500.
Example 2 — All Products Have Discounts
Input Tables:
Products
product_id category price
1 Books 25
2 Sports 100
Discounts
category discount
Books 15
Sports 25
Output:
product_id final_price category
1 21.25 Books
2 75 Sports
💡 Note:

Books get 15% discount (25 → 21.25) and Sports get 25% discount (100 → 75). All products have matching discount categories.
Example 3 — No Discounts Available
Input Tables:
Products
product_id category price
1 Jewelry 200
2 Art 500
Discounts
category discount
Output:
product_id final_price category
1 200 Jewelry
2 500 Art
💡 Note:

No discounts are available for any category, so all products retain their original prices.

Constraints

  • 1 ≤ product_id ≤ 1000
  • category is a non-empty string
  • price > 0
  • 0 ≤ discount ≤ 100

Visualization

Tap to expand
Calculate Product Final Price INPUT Products Table id name price category 1 Phone 800 Electronics 2 Laptop 1500 Electronics 3 Shirt 50 Clothing 4 Book 25 Books Discounts Table category discount % Electronics 10 Clothing 20 No discount for Books ALGORITHM STEPS 1 LEFT JOIN Tables Join Products with Discounts on category column 2 Handle NULL Discounts Use COALESCE/IFNULL to default discount to 0 3 Calculate Final Price final = price * (100 - disc) / 100 Round to 2 decimals 4 Order Results ORDER BY product_id ASC SELECT p.id, p.name, ROUND(p.price * (100-COALESCE(d.disc,0)) /100, 2) AS final_price FROM Products p LEFT JOIN... FINAL RESULT Output Table id name final_price 1 Phone 720.00 2 Laptop 1350.00 3 Shirt 40.00 4 Book 25.00 Calculations: Phone: 800*(100-10)/100 = 720 Shirt: 50*(100-20)/100 = 40 Book: 25*(100-0)/100 = 25 OK Key Insight: Use LEFT JOIN to preserve all products even without matching discounts. COALESCE handles NULL values by defaulting to 0, ensuring products without category discounts keep their original price. Time Complexity: O(n*m) where n=products, m=discounts | Space Complexity: O(n) for result set TutorialsPoint - Calculate Product Final Price | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Oracle 15
28.5K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen