Retail Sales Analysis SQL Project

Project Overview

Project Title: Retail Sales Analysis
Database: p1_retail_db

Objectives

1. Set up a retail sales database
2. Data Cleaning
3. Exploratory Data Analysis (EDA)
4. Business Analysis

Project Structure

1. Database Setup

CREATE TABLE retail_sales
        (
			transactions_id	INT PRIMARY KEY,
			sale_date	DATE,
			sale_time	TIME,
			customer_id	INT,
			gender VARCHAR(15),
			age	INT,
			category VARCHAR(15),
			quantiy INT,
			price_per_unit	FLOAT,
			cogs	FLOAT,
			total_sale FLOAT

)
SELECT count (*) FROM retail_sales

2. Data Exploration & Cleaning

SELECT * FROM retail_sales
where
     transactions_id is null
or 
           sale_date	is null
or 
			sale_time	is null
or
			customer_id	is null
or
			gender is null
or
			age	is null
or
			category is null
or
			quantiy is null
or
			price_per_unit	is null
or
            cogs	is null
or
			total_sale is null


delete from retail_sales
where
     transactions_id is null
or 
           sale_date	is null
or 
			sale_time	is null
or
			customer_id	is null
or
			gender is null
or
			age	is null
or
			category is null
or
			quantiy is null
or
			price_per_unit	is null
or
            cogs	is null
or
			total_sale is null


--How many sales we have 
select count(*) from retail_sales

--How many unique customers we have 
select count(distinct customer_id) as unique_customers from retail_sales

3. Data Analysis & Findings

The following SQL queries were developed to answer specific business questions:

1. Write a SQL query to retrieve all columns for sales made on '2022-11-05:

select * from retail_sales
where sale_date = '2022-11-05' ;

2. Write a SQL query to retrieve all transactions where the category is 'Clothing' and the quantity sold is more than 3 in the month of Nov-2022:

select * from retail_sales
where category = 'Clothing'
and quantiy > 3
and sale_date between '2022-11-1' and '2022-11-30'  ;

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

select sum(total_sale) as sales from retail_sales
group by category;

4. Write a SQL query to find the average age of customers who purchased items from the 'Beauty' category.:

select round(avg(age),3) from retail_sales
where category = 'Beauty';

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

select * from retail_sales
where total_sale > 1000 ;

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

SELECT gender, category, COUNT(*)
GROUP BY gender, category;

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

SELECT DISTINCT ON (year) year, month, total_sales
FROM (SELECT 
        EXTRACT(YEAR FROM sale_date) AS year,
        EXTRACT(MONTH FROM sale_date) AS month,
        SUM(total_sale) AS total_sales
    FROM retail_sales
    GROUP BY year, month
) t
ORDER BY year, total_sales DESC;

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

SELECT customer_id, SUM(total_sale) FROM retail_sales
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 5;

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

SELECT count (distinct customer_id), category FROM retail_sales
GROUP BY category;

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

WITH hourly_sale
AS
(
SELECT *,
    CASE
        WHEN EXTRACT(HOUR FROM sale_time) < 12 THEN 'Morning'
        WHEN EXTRACT(HOUR FROM sale_time) BETWEEN 12 AND 17 THEN 'Afternoon'
        ELSE 'Evening'
    END as shift
FROM retail_sales
)
SELECT 
    shift,
    COUNT(*) as total_orders    
FROM hourly_sale
GROUP BY shift

Findings

• Customer Demographics: The dataset includes customers from various age groups, with sales distributed across different categories such as Clothing and Beauty.
• High-Value Transactions: Several transactions had a total sale amount greater than 1000, indicating premium purchases.
• Sales Trends: Monthly analysis shows variations in sales, helping identify peak seasons.
• Customer Insights: The analysis identifies the top-spending customers and the most popular product categories.

Reports

• Sales Summary: A detailed report summarizing total sales, customer demographics, and category performance.
• Trend Analysis: Insights into sales trends across different months and shifts.
• Customer Insights: Reports on top customers and unique customer counts per category.