MySQL Sales Data Analysis Challenge

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.

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 |
+-------------+-------------+---------------+

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.