Analyzing Product Sales by Category
Imagine you are working for an e-commerce company and need to understand how different product categories are performing. Your task is to aggregate sales data to find the total revenue generated by each product category. This is a fundamental task in business intelligence, allowing you to identify top-performing categories and areas for improvement.
Problem Description
You are given a table named sales that contains records of individual product sales. Each record includes information about the product sold, its category, and the sale price.
Your goal is to write a SQL query that calculates the total revenue for each unique product category.
Key Requirements:
- Group by Category: The results must be grouped by the
categorycolumn. - Calculate Total Revenue: For each category, sum up the
sale_priceof all products belonging to that category. - Output Columns: The output should contain two columns:
category: The name of the product category.total_revenue: The sum ofsale_pricefor all sales within that category.
- Order Results: The results should be ordered by
total_revenuein descending order.
Expected Behavior:
The query should process all records in the sales table and produce a summary table where each row represents a distinct product category and its corresponding total sales value.
Edge Cases to Consider:
- Empty Table: If the
salestable is empty, the query should return an empty result set. - Categories with No Sales: While not directly addressable with the given table structure, conceptually, if a category existed but had no sales entries, it would not appear in the output. The current problem focuses on categories with sales.
Examples
Example 1:
Input Table: sales
| sale_id | product_name | category | sale_price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200.00 |
| 2 | T-Shirt | Apparel | 25.00 |
| 3 | Keyboard | Electronics | 75.00 |
| 4 | Jeans | Apparel | 50.00 |
| 5 | Monitor | Electronics | 300.00 |
Output:
| category | total_revenue |
|---|---|
| Electronics | 1575.00 |
| Apparel | 75.00 |
Explanation:
- For the 'Electronics' category, the total revenue is 1200.00 + 75.00 + 300.00 = 1575.00.
- For the 'Apparel' category, the total revenue is 25.00 + 50.00 = 75.00.
- The results are ordered by
total_revenuedescending.
Example 2:
Input Table: sales
| sale_id | product_name | category | sale_price |
|---|---|---|---|
| 1 | Book A | Books | 15.00 |
| 2 | Book B | Books | 20.00 |
| 3 | Pen | Office | 3.00 |
| 4 | Notebook | Office | 5.00 |
| 5 | Book C | Books | 18.00 |
| 6 | Stapler | Office | 10.00 |
Output:
| category | total_revenue |
|---|---|
| Books | 53.00 |
| Office | 18.00 |
Explanation:
- 'Books' total revenue: 15.00 + 20.00 + 18.00 = 53.00.
- 'Office' total revenue: 3.00 + 5.00 + 10.00 = 18.00.
- Ordered by
total_revenuedescending.
Example 3: (Edge Case - Empty Table)
Input Table: sales
| sale_id | product_name | category | sale_price |
|---|
Output:
(Empty Result Set)
Explanation:
When the input table is empty, no categories can be formed, and thus no aggregated results are produced.
Constraints
- The
salestable will contain at least 0 rows. - The
categorycolumn will be of a string type. - The
sale_pricecolumn will be of a numeric type (e.g., DECIMAL, FLOAT, INTEGER). - The query should be efficient and execute within reasonable time limits, even for large datasets (assume up to 1 million rows).
Notes
- You will need to use the
GROUP BYclause to group rows based on thecategory. - An aggregate function will be necessary to sum the
sale_pricefor each group. - Consider using
ORDER BYto sort the final results. - The exact SQL syntax for aggregate functions and grouping may vary slightly between different SQL dialects, but the core logic remains the same.