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:
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.
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.
| 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 |
Leetcode MEDIUM 3293 - Calculate Product Final Price - Using IFNULL in SQL | Everyday Data Science • Everyday Data Science • 464 views views
Practice Calculate Product Final Price with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor