Calculate Product Final Price - Solution & Explanation
Problem Statement
Table: Products
+------------+---------+ | 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 in ascending order.
The result format is in the following example.
Example:
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.
Approach Overview
Problem Overview: You are given product data along with a table containing discount information. The task is to compute the final price of each product after applying the available discount. If a product has no matching discount entry, its original price should remain unchanged.
Approach 1: Correlated Subquery Lookup (O(n*m) time, O(1) space)
A straightforward approach is to iterate through every product and look up its discount using a correlated subquery. For each row in the Products table, a subquery searches the discount table to retrieve the discount percentage and computes price * (1 - discount / 100). While simple, this approach forces the database engine to run a lookup for every product row. With large datasets, repeated scans of the discount table significantly increase query cost. This method mainly demonstrates the logic but is rarely the preferred production solution.
Approach 2: LEFT JOIN (O(n + m) time, O(1) extra space)
The efficient solution joins the Products table with the discount table using a LEFT JOIN. The join matches rows based on the product identifier (or category, depending on schema). A left join guarantees that every product appears in the result set, even if no discount record exists. After joining, compute the final price using an expression like price * (1 - IFNULL(discount, 0) / 100). The database performs the join once instead of running repeated lookups, making the query scale much better.
This approach works well because relational databases are optimized for join operations. Indexes on the join keys allow the engine to match rows efficiently. In frameworks like Pandas, the same idea applies using merge(..., how='left'), which preserves all product rows and fills missing discount values with nulls that can be replaced with zero.
Understanding join behavior is critical for many database interview questions. The pattern of combining datasets with SQL queries and handling missing matches using LEFT JOIN appears frequently in analytics pipelines and reporting tasks. You also gain practice working with relational operations like those covered under joins.
Recommended for interviews: Interviewers expect the LEFT JOIN approach. It demonstrates that you understand relational joins and how to preserve unmatched rows while applying calculations. Mentioning the naive correlated lookup first shows baseline understanding, but the optimized join solution signals strong SQL fundamentals.
Solution
We can perform a left join between the Products table and the Discounts table on the category column, then calculate the final price. If a product's category does not have an associated discount, its price remains unchanged.
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery Lookup | O(n * m) | O(1) | Small datasets or when demonstrating baseline SQL logic without joins |
| LEFT JOIN | O(n + m) | O(1) | Preferred solution for combining product and discount tables efficiently |
Video Solution
Leetcode MEDIUM 3293 - Calculate Product Final Price - Using IFNULL in SQL | Everyday Data Science • Everyday Data Science • 486 views views
Frequently Asked Questions
Is Calculate Product Final Price easy or hard?
Calculate Product Final Price Python/Java solution
How to solve Calculate Product Final Price in O(n)?
What is the best approach for Calculate Product Final Price?
Is Calculate Product Final Price asked at Google/Amazon/Meta?
What data structure is used in Calculate Product Final Price?
What is the time complexity of Calculate Product Final Price?
Ready to solve this problem?
Practice Calculate Product Final Price with our built-in code editor and test cases.
Practice on FleetCodeProblem Info
Table of Contents
Practice this problem
Open in Editor