SQL Sales Analysis Challenge - Calculate Category Revenue Performance

Introduction

A retail store manager needs help analyzing their sales data to make inventory decisions. As a database analyst, you need to help them calculate some key metrics using MySQL aggregate functions and grouping operations.

This is a Challenge, which differs from a Guided Lab in that you need to try to complete the challenge task independently, rather than following the steps of a lab to learn. Challenges are usually a bit difficult. If you find it difficult, you can discuss with Labby or check the solution. Historical data shows that this is a beginner level challenge with a 97.73% pass rate. It has received a 100% positive review rate from learners.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sql(("SQL")) -.-> sql/AdvancedDataOperationsGroup(["Advanced Data Operations"]) mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) sql(("SQL")) -.-> sql/DataManipulationandQueryingGroup(["Data Manipulation and Querying"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") sql/DataManipulationandQueryingGroup -.-> sql/order_by("ORDER BY clause") sql/DataManipulationandQueryingGroup -.-> sql/group_by("GROUP BY clause") sql/DataManipulationandQueryingGroup -.-> sql/having("HAVING clause") sql/AdvancedDataOperationsGroup -.-> sql/numeric_functions("Numeric functions") subgraph Lab Skills mysql/use_database -.-> lab-418301{{"MySQL Sales Data Analysis Challenge"}} mysql/select -.-> lab-418301{{"MySQL Sales Data Analysis Challenge"}} sql/order_by -.-> lab-418301{{"MySQL Sales Data Analysis Challenge"}} sql/group_by -.-> lab-418301{{"MySQL Sales Data Analysis Challenge"}} sql/having -.-> lab-418301{{"MySQL Sales Data Analysis Challenge"}} sql/numeric_functions -.-> lab-418301{{"MySQL Sales Data Analysis Challenge"}} end

Calculate Category Performance

The store manager needs a summary report showing the performance of each product category. The report should include the total number of items sold and total revenue for each category.

Tasks

Connect to MySQL as the root user
Use the retail_store database
Write a query that:
- Groups data by product category
- Calculates total units sold per category
- Calculates total revenue per category
- Orders results by total revenue in descending order
Save the results to a file named category_sales.txt in the ~/project directory

Requirements

All operations must be performed in the ~/project directory
The query must use GROUP BY to group by category
Revenue must be calculated by multiplying units_sold by unit_price
Revenue values must be rounded to 2 decimal places
Results must be ordered with highest revenue first
The output must be saved to a file named category_sales.txt in the ~/project directory

Example

After writing the correct query and saving the results, you can verify the output:

cat ~/project/category_sales.txt

+-------------+-------------+---------------+
| category    | total_units | total_revenue |
+-------------+-------------+---------------+
| Electronics |          35 |      13174.65 |
| Furniture   |          23 |       3519.77 |
| Appliances  |          10 |        799.90 |
+-------------+-------------+---------------+

✨ Check Solution and Practice

Summary

In this challenge, you practiced using MySQL aggregate functions and the GROUP BY clause to analyze sales data. The skills demonstrated include calculating sum totals, using arithmetic operations in SQL queries, grouping data by category, and ordering results. These fundamental SQL skills are essential for generating business reports and analyzing sales performance metrics.