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 PriceInput Tablesidcategoryprice1Electronics10004Home500categorydiscountElectronics10LEFT JOINCalculate PriceFinal Resultproduct_idfinal_pricecategory1900Electronics4500HomeFormula: price × (1 - discount/100)1000 × (1 - 10/100) = 900 | 500 × (1 - 0/100) = 500
Understanding the Visualization
1
Products
Original product data with prices
2
JOIN
LEFT JOIN with discount data
3
Calculate
Apply discount formula to get final prices
Key Takeaway
🎯 Key Insight: LEFT JOIN preserves all products while applying available discounts, using COALESCE to handle missing discount data

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