Handling Missing Data with COALESCE

In real-world datasets, it's common to encounter missing values, often represented as NULL. When performing calculations or displaying data, these NULLs can cause unexpected results or errors. This challenge focuses on using the COALESCE function to gracefully handle NULL values and provide default replacements.

Problem Description

You are tasked with querying a Products table and need to display product information, including their discounted price. However, not all products might have a discount applied, meaning the discount_percentage column could contain NULL values. When calculating the final price after discount, you need to ensure that if a discount is not specified (i.e., discount_percentage is NULL), the original price is used instead.

Your goal is to retrieve the product_name and its final_price. The final_price should be calculated by applying the discount if available, otherwise, it should be the original price.

Key Requirements:

• Retrieve the product_name for each product.
• Calculate the final_price for each product.
• If discount_percentage is NULL, the final_price should be equal to the price.
• If discount_percentage is not NULL, the final_price should be price * (1 - discount_percentage).

Expected Behavior:

The query should return a result set with two columns: product_name and final_price. The final_price must correctly reflect the discounted price or the original price when no discount is present.

Examples

Example 1:

Input Table: Products

product_id | product_name | price | discount_percentage
-----------|--------------|-------|---------------------
1          | Laptop       | 1200  | 0.10
2          | Keyboard     | 75    | NULL
3          | Mouse        | 25    | 0.05

Output:

product_name | final_price
-------------|------------
Laptop       | 1080.00
Keyboard     | 75.00
Mouse        | 23.75

Explanation:
For 'Laptop', price is 1200 and discount is 0.10, so final_price = 1200 * (1 - 0.10) = 1080.00.
For 'Keyboard', discount_percentage is NULL, so final_price = 75.00 (original price).
For 'Mouse', price is 25 and discount is 0.05, so final_price = 25 * (1 - 0.05) = 23.75.

Example 2:

Input Table: Products

product_id | product_name | price | discount_percentage
-----------|--------------|-------|---------------------
4          | Monitor      | 300   | NULL
5          | Webcam       | 50    | NULL

Output:

product_name | final_price
-------------|------------
Monitor      | 300.00
Webcam       | 50.00

Explanation:
Both 'Monitor' and 'Webcam' have NULL discount percentages, so their final prices are their original prices.

Example 3: (Edge Case - Zero Discount)

Input Table: Products

product_id | product_name | price | discount_percentage
-----------|--------------|-------|---------------------
6          | Desk Lamp    | 40    | 0.00

Output:

product_name | final_price
-------------|------------
Desk Lamp    | 40.00

Explanation:
A discount percentage of 0.00 is a valid discount, and the calculation should proceed normally, resulting in the original price.

Constraints

• The price column will always contain a non-negative numeric value.
• The discount_percentage column can contain NULL values or numeric values between 0.00 and 1.00 (inclusive).
• The number of products in the table will not exceed 1000.
• The query should be efficient and execute within reasonable time limits for the given constraints.

Notes

The COALESCE function is designed to return the first non-NULL expression in its argument list. Consider how you can use COALESCE to provide a default value for the discount_percentage when it is NULL, allowing you to perform a single calculation for the final_price. You might need to cast or convert data types for calculations if your specific SQL dialect requires it, but for this challenge, assume standard numeric types.