Calculating Total Sales Revenue

This challenge focuses on using the SUM aggregate function in SQL to calculate the total revenue generated from sales. Understanding how to sum values across multiple rows is a fundamental skill for data analysis, enabling you to derive meaningful insights from your datasets.

Problem Description

You are provided with a Sales table containing records of individual sales transactions. Each record includes information about the product_id, quantity sold, and the price_per_unit at the time of sale. Your task is to calculate the total revenue generated across all sales transactions.

Key Requirements:

• Calculate the total revenue for each sale by multiplying quantity by price_per_unit.
• Aggregate these individual revenues to find the grand total revenue across all sales.

Expected Behavior:

The solution should return a single value representing the sum of the revenue from all transactions.

Edge Cases:

• Empty Table: If the Sales table is empty, the total revenue should be 0.
• Zero Quantity or Price: Transactions with a quantity or price_per_unit of 0 should be handled correctly (their revenue contribution will be 0).

Examples

Example 1:

Sales Table:
+------------+----------+----------------+
| product_id | quantity | price_per_unit |
+------------+----------+----------------+
| 101        | 5        | 10.50          |
| 102        | 2        | 25.00          |
| 101        | 3        | 10.50          |
+------------+----------+----------------+

Output: 134.00

Explanation:
- Sale 1: 5 * 10.50 = 52.50
- Sale 2: 2 * 25.00 = 50.00
- Sale 3: 3 * 10.50 = 31.50
Total Revenue = 52.50 + 50.00 + 31.50 = 134.00

Example 2:

Sales Table:
+------------+----------+----------------+
| product_id | quantity | price_per_unit |
+------------+----------+----------------+
| 201        | 0        | 5.00           |
| 202        | 10       | 0.00           |
+------------+----------+----------------+

Output: 0.00

Explanation:
- Sale 1: 0 * 5.00 = 0.00
- Sale 2: 10 * 0.00 = 0.00
Total Revenue = 0.00 + 0.00 = 0.00

Example 3: (Empty Table)

Sales Table:
(empty)

Output: 0.00

Explanation:
When there are no sales records, the total revenue is 0.

Constraints

• The Sales table will have at least one column named product_id (integer type), quantity (integer type), and price_per_unit (decimal or float type).
• quantity will be a non-negative integer.
• price_per_unit will be a non-negative decimal or float.
• The total revenue will not exceed the maximum value representable by a standard decimal or float type.
• Your SQL query should be efficient, especially for large datasets.

Notes

• Pseudocode for the calculation of revenue for a single sale is: revenue = quantity * price_per_unit.
• Consider how to apply this calculation to every row in the Sales table before summing the results.
• The SUM() aggregate function in SQL is the key to solving this problem.