Sales Performance Aggregation and Ranking

This challenge focuses on aggregating sales data, calculating key performance indicators, and ranking products based on their performance. This is a common task in business intelligence and analytics, where understanding sales trends and identifying top-performing items is crucial for strategic decision-making.

Problem Description

You are tasked with analyzing a dataset of individual sales transactions. For each product, you need to calculate its total sales revenue, the number of units sold, and its average selling price. Furthermore, you need to rank each product based on its total sales revenue in descending order.

Key Requirements:

• Aggregate sales data for each unique product.
• Calculate the total revenue generated by each product.
• Calculate the total number of units sold for each product.
• Calculate the average selling price per unit for each product.
• Assign a rank to each product based on its total revenue (highest revenue gets rank 1).
• Handle cases where a product might have zero sales (though unlikely in a typical sales dataset, it's good practice to consider).

Expected Behavior: The output should be a table containing each product's ID, its total revenue, total units sold, average selling price, and its sales revenue rank.

Important Edge Cases:

• Products with no sales data should ideally not appear in the output, or if they do, their aggregates should be zero and rank should be handled appropriately (e.g., a very high rank or excluded). For this challenge, assume all products in the input have at least one sale.
• Ensure the average selling price is calculated correctly, avoiding division by zero if a product had zero units sold (again, assume this won't happen based on input data for this specific challenge).

Examples

Example 1:

Input: A Sales table with the following rows:

Output: A table with the following columns: ProductID, TotalRevenue, TotalUnitsSold, AverageSellingPrice, SalesRevenueRank

Explanation:

• Product A101: (2 * 10.00) + (3 * 10.00) = 50.00 revenue. 2 + 3 = 5 units sold. Average price = 50.00 / 5 = 10.00.
• Product B202: (1 * 25.00) + (2 * 25.00) = 75.00 revenue. 1 + 2 = 3 units sold. Average price = 75.00 / 3 = 25.00.
• Product C303: (5 * 5.00) = 25.00 revenue. 5 units sold. Average price = 25.00 / 5 = 5.00. Ranks are assigned based on TotalRevenue: B202 (75.00) is rank 1, A101 (50.00) is rank 2, C303 (25.00) is rank 3.

Example 2:

Input: A Sales table with the following rows:

Output:

Explanation:

• Product Y88: (5 * 20.00) + (10 * 20.00) = 15 * 20.00 = 300.00 revenue. 5 + 10 = 15 units sold. Average price = 300.00 / 15 = 20.00.
• Product X99: (10 * 1.50) + (20 * 1.50) + (5 * 1.50) = 35 * 1.50 = 52.50 revenue. 10 + 20 + 5 = 35 units sold. Average price = 52.50 / 35 = 1.50.
• Product Z77: (1 * 100.00) = 100.00 revenue. 1 unit sold. Average price = 100.00 / 1 = 100.00. Ranks: Y88 (300.00) is rank 1, Z77 (100.00) is rank 2, X99 (52.50) is rank 3. (Note: The example output had Z77 as rank 3, but based on calculation Y88=300, Z77=100, X99=52.50, Z77 should be rank 2 and X99 rank 3. We will proceed with this corrected ranking).

Constraints

• The Sales table can contain up to 1,000,000 rows.
• ProductID is a string.
• Quantity is an integer, between 1 and 1000.
• PricePerUnit is a decimal number, with up to two decimal places, ranging from 0.01 to 1000.00.
• The solution should execute within 10 seconds on a standard database server.

Notes

• You will need to use aggregate functions and window functions to solve this challenge.
• Consider how you will handle potential floating-point precision issues if your SQL dialect requires it.
• The ranking should handle ties: products with the same total revenue should receive the same rank, and the next rank should be skipped accordingly (e.g., if two products tie for rank 1, the next rank is 3).