Calculating Sales Tax and Discounts in an E-Commerce Database

This challenge focuses on utilizing SQL's mathematical functions to calculate sales tax and discounts on product prices within an e-commerce database. Accurate price calculations are crucial for any e-commerce platform, and this exercise tests your ability to apply mathematical operations within SQL queries to achieve this.

Problem Description

You are working with a simplified e-commerce database containing product information. The database includes a products table with the following columns:

• product_id (INTEGER, Primary Key): Unique identifier for each product.
• product_name (VARCHAR): Name of the product.
• price (DECIMAL): Original price of the product.
• discount_percentage (DECIMAL): Discount percentage applied to the product (e.g., 0.10 for 10%). Can be NULL, indicating no discount.
• sales_tax_rate (DECIMAL): Sales tax rate applicable to the product (e.g., 0.07 for 7%).

Your task is to write SQL queries to calculate the following:

1. Discounted Price: Calculate the price after applying the discount (if any). If discount_percentage is NULL, the discounted price should be the original price.
2. Price with Tax: Calculate the final price after applying both the discount (if applicable) and the sales tax.
3. Total Revenue: Calculate the total revenue generated from all products, considering discounts and sales tax.

Examples

Example 1:

Input:
products table:
| product_id | product_name | price | discount_percentage | sales_tax_rate |
|---|---|---|---|---|
| 1 | Laptop | 1000.00 | 0.10 | 0.07 |
| 2 | Mouse | 25.00 | NULL | 0.07 |
| 3 | Keyboard | 75.00 | 0.05 | 0.07 |

Output:
| product_id | product_name | discounted_price | price_with_tax |
|---|---|---|---|
| 1 | Laptop | 900.00 | 963.00 |
| 2 | Mouse | 25.00 | 26.75 |
| 3 | Keyboard | 71.25 | 76.31 |
Explanation:
- Laptop: Discounted price = 1000 * (1 - 0.10) = 900. Price with tax = 900 * (1 + 0.07) = 963.
- Mouse: Discount percentage is NULL, so discounted price = 25. Price with tax = 25 * (1 + 0.07) = 26.75.
- Keyboard: Discounted price = 75 * (1 - 0.05) = 71.25. Price with tax = 71.25 * (1 + 0.07) = 76.31.

Example 2:

Input:
products table:
| product_id | product_name | price | discount_percentage | sales_tax_rate |
|---|---|---|---|---|
| 1 | Monitor | 300.00 | NULL | 0.08 |
| 2 | Webcam | 50.00 | 0.20 | 0.08 |

Output:
| product_id | product_name | discounted_price | price_with_tax |
|---|---|---|---|
| 1 | Monitor | 300.00 | 324.00 |
| 2 | Webcam | 40.00 | 43.20 |
Explanation:
- Monitor: Discount percentage is NULL, so discounted price = 300. Price with tax = 300 * (1 + 0.08) = 324.
- Webcam: Discounted price = 50 * (1 - 0.20) = 40. Price with tax = 40 * (1 + 0.08) = 43.20.

Example 3: (Edge Case - Zero Discount and Tax)

Input:
products table:
| product_id | product_name | price | discount_percentage | sales_tax_rate |
|---|---|---|---|---|
| 1 | Headphones | 100.00 | 0.00 | 0.00 |

Output:
| product_id | product_name | discounted_price | price_with_tax |
|---|---|---|---|
| 1 | Headphones | 100.00 | 100.00 |
Explanation:
- Both discount and tax are zero, so discounted price and price with tax are the same as the original price.

Constraints

• price, discount_percentage, and sales_tax_rate are all DECIMAL values.
• discount_percentage can be NULL.
• price will always be a positive number.
• discount_percentage will be between 0.00 and 1.00 (inclusive) when not NULL.
• sales_tax_rate will be between 0.00 and 1.00 (inclusive).
• The database system supports standard SQL mathematical functions (e.g., NULLIF, multiplication, addition).
• Performance is not a primary concern for this challenge, but avoid unnecessarily complex or inefficient queries.

Notes

• Use NULLIF to handle cases where discount_percentage is NULL. This will prevent division by zero errors.
• Remember to apply the discount before calculating the sales tax.
• Consider using aliases to make your queries more readable.
• The total revenue calculation should sum the price_with_tax for all products. You will need a separate query for this.
• Focus on clarity and correctness over extreme optimization.
• The final output should include the product_id, product_name, discounted_price, and price_with_tax for each product.
• The total revenue query should return a single value representing the sum of price_with_tax across all rows.