Sales-Analysis Using SQL

Overview

This project showcases the application of SQL for retail sales analytics, focusing on transforming raw transactional data into meaningful business insights. The analysis covers database creation, data quality validation, exploratory analysis, and business-focused reporting.

Designed as an end-to-end SQL project, it demonstrates how analysts can leverage structured query language to investigate sales performance, customer purchasing behavior, and product category trends.

Project Goals

The primary objectives of this project are to:

Build a structured retail sales database from scratch.
Validate and clean transactional records to ensure data accuracy.
Explore the dataset to understand customer and product patterns.
Answer key business questions through SQL-driven analysis.
Generate actionable insights that support decision-making.

Database Design

The project begins with the creation of a relational database named retail_db.

A sales fact table, retail_sales, stores transaction-level information including:

Transaction ID
Sale Date and Time
Customer ID
Gender
Age
Product Category
Quantity Purchased
Unit Price
Cost of Goods Sold (COGS)
Total Revenue

This structure provides a solid foundation for conducting retail performance analysis.

Data Quality & Preparation

Before analysis, the dataset undergoes several validation checks to ensure reliability.

Data Validation Activities

Count total transactions within the dataset.
Determine the number of unique customers.
Identify available product categories.
Detect incomplete records containing null values.
Remove records that could negatively impact analytical results.

These steps help establish a clean and trustworthy dataset for reporting.

Exploratory Data Analysis

Initial exploration was conducted to better understand the dataset and uncover high-level trends.

Key Exploration Areas

Customer distribution across demographic groups.
Product category performance.
Transaction volume analysis.
Revenue distribution patterns.
Purchase timing and customer activity trends.

Business Questions Addressed

The project answers several practical business questions commonly encountered in retail environments:

Transaction Analysis

Retrieve transactions completed on a specific date.
Identify large clothing purchases made during a selected month.
Locate high-value transactions exceeding a predefined revenue threshold.

Sales Performance

Calculate total revenue generated by each product category.
Measure category-level order volume.
Determine monthly sales performance and identify top-performing months.

Customer Analysis

Calculate the average age of customers purchasing beauty products.
Identify the highest-spending customers.
Measure customer participation across product categories.

Operational Insights

Analyze transaction activity by gender and category.
Classify purchases into Morning, Afternoon, and Evening sales periods.
Evaluate order volume by shift to understand customer purchasing behavior throughout the day.

Key Insights

Customer Behavior

Customers represent a diverse demographic range, with purchasing activity spread across multiple product categories.

Revenue Concentration

A subset of transactions generated significantly higher revenue, highlighting opportunities to understand premium customer purchasing patterns.

Seasonal Performance

Sales performance fluctuated across different months, revealing periods of increased customer demand.

Category Trends

Certain product categories attracted a larger customer base and generated stronger sales performance than others.

Customer Value

The analysis identified top-spending customers who contribute a substantial share of total revenue.

Reporting Outputs

Sales Performance Dashboard

A consolidated view of revenue, transaction volume, and category performance.

Trend Analysis Report

Monthly and shift-based sales analysis highlighting purchasing patterns over time.

Customer Intelligence Report

Insights into customer demographics, spending behavior, and category engagement.

Technologies Used

SQL
PostgreSQL
Relational Database Design
Data Cleaning Techniques
Exploratory Data Analysis (EDA)
Business Analytics

Project Outcomes

This project demonstrates the complete analytical workflow using SQL, from database creation and data preparation to business reporting and insight generation.

The analysis provides valuable visibility into:

Customer purchasing behavior
Product category performance
Revenue trends
Operational sales patterns

These insights can support strategic decisions related to marketing, inventory management, and customer engagement.

Getting Started

1. Create the Database

Execute the scripts contained in the database setup file to create the database and load the dataset.

CREATE DATABASE retail_db;

CREATE TABLE retail_sales
(
    transactions_id INT PRIMARY KEY,
    sale_date DATE,
    sale_time TIME,
    customer_id INT,
    gender VARCHAR(10),
    age INT,
    category VARCHAR(35),
    quantity INT,
    price_per_unit FLOAT,
    cogs FLOAT,
    total_sale FLOAT
);

2. Run the Analysis

Record Count: Determine the total number of records in the dataset.
Customer Count: Find out how many unique customers are in the dataset.
Category Count: Identify all unique product categories in the dataset.
Null Value Check: Check for any null values in the dataset and delete records with missing data

SELECT COUNT(*) FROM retail_sales;
SELECT COUNT(DISTINCT customer_id) FROM retail_sales;
SELECT DISTINCT category FROM retail_sales;

SELECT * FROM retail_sales
WHERE 
    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 
    quantity IS NULL OR price_per_unit IS NULL OR cogs IS NULL;

DELETE FROM retail_sales
WHERE 
    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 
    quantity IS NULL OR price_per_unit IS NULL OR cogs IS NULL;

3. Extend the Project

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 4 in the month of Nov-2022:

SELECT 
  *
FROM retail_sales
WHERE 
    category = 'Clothing'
    AND 
    TO_CHAR(sale_date, 'YYYY-MM') = '2022-11'
    AND
    quantity >= 4

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

SELECT 
    category,
    SUM(total_sale) as net_sale,
    COUNT(*) as total_orders
FROM retail_sales
GROUP BY 1

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

SELECT
    ROUND(AVG(age), 2) as avg_age
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 
    category,
    gender,
    COUNT(*) as total_trans
FROM retail_sales
GROUP 
    BY 
    category,
    gender
ORDER BY 1

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

SELECT 
       year,
       month,
    avg_sale
FROM 
(    
SELECT 
    EXTRACT(YEAR FROM sale_date) as year,
    EXTRACT(MONTH FROM sale_date) as month,
    AVG(total_sale) as avg_sale,
    RANK() OVER(PARTITION BY EXTRACT(YEAR FROM sale_date) ORDER BY AVG(total_sale) DESC) as rank
FROM retail_sales
GROUP BY 1, 2
) as t1
WHERE rank = 1

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

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

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

SELECT 
    category,    
    COUNT(DISTINCT customer_id) as cnt_unique_cs
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.

Author - Leo Mashapa

This project was developed to strengthen practical SQL skills and demonstrate the use of data analytics techniques for solving real-world retail business problems.

Name		Name	Last commit message	Last commit date
Latest commit History 9 Commits
README.md		README.md

Folders and files

Latest commit

History

Repository files navigation