Welcome to Subscribe On Youtube
3293. Calculate Product Final Price 🔒
Description
Table: Products
+++ \| product_id \| int \| \| category \| varchar \| \| price \| decimal \| +++ \| Column Name\| Type \| +++ 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:
++-+ \| Electronics\| 10 \| \| Clothing \| 20 \| ++----+
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.
Solutions
Solution 1: Left Join
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.
-
import pandas as pd def calculate_final_prices( products: pd.DataFrame, discounts: pd.DataFrame ) -> pd.DataFrame: # Perform a left join on the 'category' column merged_df = pd.merge(products, discounts, on="category", how="left") # Calculate the final price merged_df["final_price"] = ( merged_df["price"] * (100 - merged_df["discount"].fillna(0)) / 100 ) # Select the necessary columns and sort by 'product_id' result_df = merged_df[["product_id", "final_price", "category"]].sort_values( "product_id" ) return result_df -
# Write your MySQL query statement below SELECT product_id, price * (100 - IFNULL(discount, 0)) / 100 final_price, category FROM Products LEFT JOIN Discounts USING (category) ORDER BY 1;