PROJECT OVERVIEW

SQL_ANALYSE

A SQL project analyzing 2,000 retail transactions (Jan 2022 – Dec 2023) to answer real business questions about sales, customers, and product categories.

Retail Sales Analysis (SQL)

A SQL project analyzing retail transaction data to answer business questions about sales, customers, and category performance. Built in MySQL.

Dataset

Table: RETAIL_SALES_ANS in the RETAIL_SALES database.

Column	Description
transaction_id	Unique transaction ID
sale_date, sale_time	When the sale happened
customer_id, gender, age	Customer info
category, quantity	What was bought, how much
price_per_unit, cogs, total_sales	Pricing and revenue

Process

1. Create the database and table

CREATE DATABASE RETAIL_SALES;
USE RETAIL_SALES;

CREATE TABLE RETAIL_SALES_ANS(
    TRANSACTION_ID INT,
    SALE_DATE DATE,
    SALE_TIME TIME,
    CUSTOMER_ID INT,
    GENDER VARCHAR(10),
    AGE INT,
    CATEGORY VARCHAR(20),
    QUANTITY INT,
    PRICE_PER_UNIT FLOAT,
    COGS FLOAT,
    TOTAL_SALES FLOAT
);

Data was then imported into this table.

2. Clean the data Checked every column for nulls, then deleted any incomplete rows before analysis — a single missing total_sales or quantity would skew the aggregations.

SELECT * FROM RETAIL_SALES_ANS
WHERE TRANSACTION_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 QUANTITY IS NULL OR PRICE_PER_UNIT IS NULL
   OR COGS IS NULL OR TOTAL_SALES IS NULL;

DELETE FROM RETAIL_SALES_ANS
WHERE TRANSACTION_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 QUANTITY IS NULL OR PRICE_PER_UNIT IS NULL
   OR COGS IS NULL OR TOTAL_SALES IS NULL;

3. Explore the basics Before jumping into business questions, got a feel for the data's scale:

SELECT COUNT(*) AS TOTAL_NO_SALES FROM RETAIL_SALES_ANS;
SELECT COUNT(DISTINCT CUSTOMER_ID) AS UNIQUE_CUSTOMERS FROM RETAIL_SALES_ANS;
SELECT COUNT(DISTINCT CATEGORY) AS UNIQUE_CATEGORY FROM RETAIL_SALES_ANS;

4. Answer business questions Full queries are in RETAIL_SALES_ANS.sql.

1. Sales made on 2022-11-05
2. Clothing transactions with quantity ≥ 3 in Nov 2022
3. Total sales per category
4. Average customer age for the Beauty category
5. Transactions with total sale > 1000
6. Transaction count by gender within each category
7. Average monthly sale, with the best-selling month per year (using RANK())
8. Top 5 customers by total sales
9. Unique customers per category
10. Repeat customers with 5+ transactions
11. Most profitable category (Total Sales − COGS)

Key Findings

• A small set of customers account for repeat purchases at volume (5+ transactions), pointing to a loyal core buyer base.
• Category-level profit tells a different story than raw sales once COGS is factored in.
• Sales aren't flat across the year — certain months consistently outperform others, and this varies year to year.

Run It Yourself

git clone https://github.com/<your-username>/<repo-name>.git

Run the setup and cleaning steps in RETAIL_SALES_ANS.sql against a MySQL instance, then run the 11 analysis queries.