Retail Sales Analysis

Objectives

1. Set up a retail sales database: Create and populate a database with the provided sales data.

2. Data cleaning: Identify and remove records with missing or null values.

3. Exploratory data analysis (EDA): Perform basic EDA to understand the dataset.

4. Business analysis: Use SQL to answer specific questions and derive insights from the sales data.

1. Create Database and Create Table

2. Data Exploration & Cleaning

• Record Count: Determine the total number of records in the dataset.

• Customer Count: Identify the number of unique customers in the dataset.

• Category Count: List all unique product categories in the dataset.

• Null Value Check: Identify and remove any records with null or missing values in the dataset.

• Unique Record Count: Determine the total number of records in the dataset.

3. Data Analysis & Business Key Problems & Answers

My Analysis & Findings

• Q.1 Write a SQL query to retrieve all columns for sales made on ‘2022–11–05.

• Q.2 Write a SQL query to retrieve all transactions where the category is ‘Clothing’ and the quantity sold is more than 4 in the month of Nov-2022.

• Q.3 Write a SQL query to calculate the total sales (total_sale) for each category.

• Q.4 Write a SQL query to find the average age of customers who purchased items from the ‘Beauty’ category.

• Q.5 Write a SQL query to find all transactions where the total_sale is greater than 1000.

• Q.6 Write a SQL query to find the total number of transactions (transaction_id) made by each gender in each category.

• Q.7 Write a SQL query to calculate the average sale for each month. Find out best selling month in each year.

you can also this this but it is not optimize you can only use it for visual purpose.

• Q.8 Write a SQL query to find the top 5 customers based on the highest total sales.

• Q.9 Write a SQL query to find the number of unique customers who purchased items from each category.

• Q.10 Write a SQL query to create each shift and number of orders (Example Morning <=12, Afternoon Between 12 & 17, Evening >17).

Key Insights and Conclusions

From our SQL queries, we can derive several actionable insights:

• Best-selling products: We identified which products generate the most revenue.

• Sales trends: Monthly sales trends helped us understand peak seasons or slow months.

• Customer behavior: Understanding customer purchase frequency can guide marketing efforts and loyalty programs.

Final Thoughts

This project demonstrates the power of SQL in analyzing retail sales data. From setting up the database to running queries for business analysis, we leveraged SQL’s ability to provide deep insights.

Stay tuned for more SQL projects and analyses!