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;