Executive Summary

Retail companies generate vast amounts of transactional data every day. However, data alone has little value unless it is transformed into actionable insights that support business decisions. SQL remains one of the most essential tools for extracting, cleaning, and analyzing business data because it allows analysts to efficiently query large databases and uncover meaningful trends.

In this project, I analyzed a U.S. Superstore retail dataset using MySQL. The objective was to explore customer purchasing behavior, regional performance, product demand, and sales trends while showing practical SQL skills used in real business environments.

The project covers the complete analytics workflow, from importing and cleaning raw data to answering business questions through SQL queries. Rather than simply writing SQL statements, the analysis focuses on interpreting the results and translating them into business recommendations.

The techniques demonstrated include:

• Data cleaning
• Data validation
• Aggregate functions
• Date functions
• CASE statements
• Common Table Expressions (CTEs)
• Window functions
• Ranking functions
• Views
• Business reporting

Introduction

Businesses rely on sales data to understand customer behavior, optimize inventory, evaluate marketing campaigns, and improve profitability. Every transaction contains valuable information about who purchased a product, where the purchase occurred, when it happened, and how much revenue it generated.

Without proper analysis, these records remain isolated pieces of information. SQL enables analysts to transform raw transactional data into meaningful business intelligence.

For this project, I explored sales data from a U.S. retail company to answer questions such as:

• Which regions generate the highest sales?
• Which customer segment contributes the most revenue?
• Which products sell the most?
• How do sales change over time?
• Which customers generate the highest lifetime value?
• Are there seasonal sales patterns?

The project shows how SQL can support business decision-making using real transactional data.

Objectives

The primary objectives were to:

• Clean and prepare the dataset for analysis.
• Explore the structure and quality of the data.
• Analyze regional sales performance.
• Identify top-performing customer segments.
• Evaluate product performance.
• Investigate customer purchasing behavior.
• Examine shipping performance.
• Demonstrate advanced SQL techniques.
• Translate findings into business recommendations.

Dataset Overview

The dataset contains retail transactions from a U.S. Superstore.

Dataset: Superstore Sales Data by divaadelia (Kaggle)

Dataset Summary

Feature	Value
Records	9,800
Columns	19
Customers	793
Products	1,861
Orders	4,922
States	49
Cities	529
Years Covered	2015–2018

Each record represents a single product purchased within an order.

The dataset includes:

• Customer information
• Product information
• Geographic information
• Order dates
• Shipping dates
• Sales amount
• Customer segment
• Shipping mode

Database Design

I first created a database specifically for this project.

CREATE DATABASE superstore;

USE superstore;

Next, I created a table containing all variables from the dataset. You will find the full CREATE TABLE statement later in the SQL section.

Data Cleaning

Raw datasets often contain inconsistencies that must be addressed before analysis, so I performed the following cleaning steps.

1. Checking Missing Values

The first step was determining whether any important variables contained missing observations. I found out that only a small number of postal codes were missing, while customer IDs, product IDs, and sales records were complete.

Since postal codes were not required for the planned analyses, these records were retained.

SELECT
SUM(Customer_ID IS NULL) AS MissingCustomers,
SUM(Product_ID IS NULL) AS MissingProducts,
SUM(Postal_Code IS NULL) AS MissingPostalCodes,
SUM(Sales IS NULL) AS MissingSales
FROM superstore_sales;

2. Checking Duplicate Records

Duplicate rows can inflate sales totals and distort analytical results. No duplicate Row IDs were found, showing that each observation represented a unique transaction.

SELECT
Row_ID,
COUNT(*)
FROM superstore_sales
GROUP BY Row_ID
HAVING COUNT(*) > 1;

3. Formatting Dates

The Order Date and Ship Date fields were converted into MySQL DATE format.

UPDATE superstore_sales
SET Order_Date = STR_TO_DATE(Order_Date,'%m/%d/%Y');

The same process was repeated for Ship Date. This process is important because date conversion enables SQL functions such as YEAR(), MONTH(), MONTHNAME(), and DATEDIFF(). These functions are essential for trend analysis.

4. Cleaning the Sales Column

The Sales column contained currency symbols and commas. These characters were removed before converting the values to numeric format. Converting sales into numeric format enables aggregation using functions such as SUM(), AVG(), MAX(), and MIN().

UPDATE superstore_sales
SET Sales =
CAST(
REPLACE(
REPLACE(Sales,'$',''),
',','')
AS DECIMAL(15,2));

Exploratory Data Analysis

Before answering the business questions, I first explored the overall structure of the dataset.

Question 1

How many unique customers, products, and orders are represented?

The dataset contains thousands of individual transactions generated by hundreds of customers purchasing nearly two thousand unique products.

This diversity makes the dataset suitable for customer analytics and product performance evaluation.

SELECT

COUNT(DISTINCT Customer_ID) AS Customers,

COUNT(DISTINCT Product_ID) AS Products,

COUNT(DISTINCT Order_ID) AS Orders

FROM superstore_sales;

Question 2

Which customer segment places the most orders?

Comparing customer segments helps businesses determine where marketing investments should be concentrated.

If one segment consistently dominates order volume, targeted promotions may produce higher returns.

SELECT

Segment,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Segment

ORDER BY Orders DESC;

The Consumer segment accounted for 5,101 transaction line items, making it the retailer’s largest customer group. The Corporate segment followed with 2,953 transactions, while the Home Office segment contributed 1,746. The results indicate that individual consumers drive most purchasing activity, suggesting that customer acquisition and retention strategies should primarily target this segment while exploring opportunities to increase engagement among Corporate and Home Office customers.

Question 3

Which regions generate the most orders?

Regional comparisons show geographic differences in customer demand and can guide decisions related to inventory allocation and expansion.

SELECT

Region,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Region

ORDER BY Orders DESC;

The West region recorded the highest customer activity with 3,140 transaction line items, followed by the East (2,785), Central (2,277), and South (1,598). This suggests that demand is strongest in the western United States, making it an important region for inventory planning and marketing investment. The lower activity observed in the South may warrant further investigation into customer reach, competition, or market penetration.

Question 4

Which states contain the highest customer activity?

High-performing states represent strong markets where businesses may prioritize marketing campaigns, distribution centers, and inventory investments.

SELECT

State,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY State

ORDER BY Orders DESC

LIMIT 10;

California recorded the highest number of transaction line items (1,946), followed by New York (1,097) and Texas (973). These states represent the company’s largest markets by transaction volume and may benefit from continued investment in inventory, distribution, and customer retention initiatives.

Question 5

Which product categories are ordered most frequently?

Product categories with consistently high order volumes should receive careful inventory management to reduce stock shortages.

SELECT

Category,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Category

ORDER BY Orders DESC;

Office Supplies was the most frequently purchased category, accounting for 5,909 transaction line items. Furniture contributed 2,078 transactions, while Technology accounted for 1,813. The high purchasing frequency of Office Supplies suggests consistent customer demand and highlights the importance of maintaining adequate inventory levels for these products.

Question 6

Which sub-categories receive the most customer demand?

SELECT

Sub_Category,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Sub_Category

ORDER BY Orders DESC

LIMIT 10;

Customer demand was concentrated in a few sub-categories. Binders led with 1,492 transaction line items, followed by Paper (1,338), Furnishings (931), Phones (876), and Storage (832). These products appear to be purchased regularly and should receive close attention during inventory planning and supplier negotiations.

Question 7

Which shipping mode is most commonly used?

Understanding customer shipping preferences helps businesses balance shipping costs with delivery speed.

SELECT

Ship_Mode,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Ship_Mode

ORDER BY Orders DESC;

Standard Class was the preferred shipping option, accounting for 5,859 transaction line items, more than all other shipping methods combined. It was followed by Second Class (1,902), First Class (1,501), and Same Day (538). The strong preference for Standard Class suggests that many customers prioritize economical shipping over faster delivery options.

Question 8

How many orders occur each year?

Annual order counts provide an initial indication of business growth before revenue analysis.

SELECT

YEAR(Order_Date) AS Year,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Year

ORDER BY Year;

Question 9

Which months experience the highest order activity?

Monthly order patterns help identify seasonal demand, enabling better staffing, inventory planning, and promotional scheduling.

SELECT

MONTHNAME(Order_Date) AS Month,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Month

ORDER BY Orders DESC;

Question 10

Which customers place the largest number of orders?

Frequent customers represent valuable business relationships and may benefit from loyalty programs or personalized promotions.

SELECT

Customer_Name,

COUNT(*) AS Orders

FROM superstore_sales

GROUP BY Customer_Name

ORDER BY Orders DESC

LIMIT 10;

The most active customer in the dataset was William Brown, who appeared in 35 transaction line items. He was followed closely by Paul Prost and Matt Abelman, each with 34 transactions. While frequent purchases do not necessarily indicate the highest revenue, these customers represent strong candidates for loyalty programs and personalized marketing campaigns.

Sales Performance Analysis

While order counts provide a useful overview of customer activity, businesses ultimately measure success through revenue generation. In this section, SQL is used to evaluate sales performance across different regions, customer segments, product categories, and time periods.

The objective is to identify where revenue is being generated and uncover opportunities for business growth.

Business Question 11

What is the company’s total sales?

One of the first metrics executives review is total revenue generated over the study period.

Total sales provide an overall measure of company performance and establish a baseline for all subsequent analyses. This metric is often monitored alongside profit, operating costs, and customer acquisition to evaluate overall business health.

SELECT
ROUND(SUM(Sales),2) AS TotalSales
FROM superstore_sales;

Business Question 12

Which regions generate the highest sales?

Knowing where revenue originates helps businesses allocate marketing resources and inventory more effectively.

Comparing regional revenue highlights geographic differences in business performance. High-performing regions may justify increased inventory investment or expansion, while lower-performing regions may require additional marketing or operational review.

SELECT

Region,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY Region

ORDER BY TotalSales DESC;

Business Question 13

Which states contribute the highest revenue?

State-level analysis provides a more detailed understanding of geographic performance.

Top-performing states often represent key markets where businesses can prioritize customer retention, faster delivery, and targeted promotional campaigns.

SELECT

State,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY State

ORDER BY TotalSales DESC

LIMIT 10;

Business Question 14

Which customer segment generates the highest sales?

Not all customer groups contribute equally to company revenue.

Although one customer segment may place more orders, another may generate larger purchases. Understanding both transaction frequency and revenue helps businesses optimize marketing strategies.

SELECT

Segment,

ROUND(SUM(Sales),2) AS TotalSales,

ROUND(AVG(Sales),2) AS AverageTransaction

FROM superstore_sales

GROUP BY Segment

ORDER BY TotalSales DESC;

Although the Consumer segment generated the largest number of transactions in the dataset, further revenue analysis is needed to determine whether it also contributes the greatest share of sales. Comparing transaction frequency with revenue provides a more complete understanding of customer value.

Business Question 15

Which product categories generate the highest revenue?

Product categories differ in both sales volume and revenue contribution.

High-performing categories should receive priority during inventory planning and supplier negotiations. Lower-performing categories may require promotional campaigns or product rationalization.

SELECT

Category,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY Category

ORDER BY TotalSales DESC;

Office Supplies dominated transaction volume, but transaction frequency alone does not necessarily indicate the highest revenue contribution. Categories containing fewer but higher-priced products may ultimately generate greater sales despite having fewer transactions.

Business Question 16

Which sub-categories generate the most revenue?

Product categories can be broken down further into more specific product groups.

Sub-category analysis identifies the company’s strongest product lines and helps managers prioritize purchasing decisions and shelf allocation.

SELECT

Sub_Category,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY Sub_Category

ORDER BY TotalSales DESC

LIMIT 10;

Business Question 17

Which products generate the highest sales?

Individual product analysis identifies the company’s best-selling products.

Best-selling products often deserve increased inventory levels and stronger promotional support because they contribute significantly to company revenue.

SELECT

Product_Name,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY Product_Name

ORDER BY TotalSales DESC

LIMIT 10;

Ranking products by sales helps identify the retailer’s flagship products and supports inventory planning, promotional campaigns, and supplier negotiations. Once the sales rankings are generated, management can focus resources on products that contribute most to business performance.

Business Question 18

What is the average order value?

Average Order Value (AOV) is a widely used retail metric.

Increasing Average Order Value is often more cost-effective than acquiring new customers. Businesses frequently use product bundles, cross-selling, and recommendations to encourage larger purchases.

SELECT

ROUND(

SUM(Sales)/COUNT(DISTINCT Order_ID),

2

) AS AverageOrderValue

FROM superstore_sales;

Customer Analytics

Customer analytics focuses on understanding purchasing behavior rather than simply measuring sales.

Businesses often find that a relatively small number of customers contribute a disproportionately large share of total revenue.

SQL allows us to identify these high-value customers.

Business Question 19

Which customers generate the highest lifetime sales?

High-value customers are important assets. Retaining existing loyal customers is generally less expensive than acquiring new ones, making customer relationship management an important business strategy.

SELECT

Customer_Name,

ROUND(SUM(Sales),2) AS LifetimeSales,

COUNT(DISTINCT Order_ID) AS Orders

FROM superstore_sales

GROUP BY Customer_Name

ORDER BY LifetimeSales DESC

LIMIT 10;

Identifying customers with the highest lifetime spending allows businesses to prioritize retention efforts and develop targeted loyalty initiatives. Combining purchase frequency with revenue provides a more comprehensive measure of customer value.

Business Question 20

Ranking customers using Window Functions

Window functions allow analysts to rank customers without removing detail from the dataset.

SELECT

Customer_Name,

ROUND(SUM(Sales),2) AS LifetimeSales,

DENSE_RANK()

OVER(

ORDER BY SUM(Sales) DESC

) AS CustomerRank

FROM superstore_sales

GROUP BY Customer_Name;

I used DENSE_RANK() because it does not leave gaps when two customers share the same ranking, unlike RANK(). This produces cleaner business reports.

Business Question 21

Categorizing customers using CASE

Businesses frequently classify customers according to purchasing behavior.

SELECT

Customer_Name,

SUM(Sales) AS LifetimeSales,

CASE

WHEN SUM(Sales)>=100000 THEN 'Premium'

WHEN SUM(Sales)>=50000 THEN 'Gold'

WHEN SUM(Sales)>=20000 THEN 'Silver'

ELSE 'Standard'

END AS CustomerTier

FROM superstore_sales

GROUP BY Customer_Name;

Time-Series Analysis

Sales rarely remain constant throughout the year.

Analyzing temporal trends helps businesses anticipate demand and improve operational planning.

Business Question 22

How do sales change each year?

Yearly comparisons reveal long-term business growth and support strategic planning.

SELECT

YEAR(Order_Date) AS Year,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY Year

ORDER BY Year;

Business Question 23

Monthly sales trend

Monthly trends reveal seasonal fluctuations that influence inventory management, staffing, and promotional scheduling.

SELECT

DATE_FORMAT(Order_Date,'%Y-%m') AS Month,

ROUND(SUM(Sales),2) AS TotalSales

FROM superstore_sales

GROUP BY Month

ORDER BY Month;

Business Question 24

Running total of company sales

Window functions can calculate cumulative revenue over time.

WITH MonthlySales AS

(

SELECT

DATE_FORMAT(Order_Date,'%Y-%m') AS Month,

SUM(Sales) AS MonthlyRevenue

FROM superstore_sales

GROUP BY Month

)

SELECT

Month,

ROUND(MonthlyRevenue,2),

ROUND(

SUM(MonthlyRevenue)

OVER(

ORDER BY Month

),

2

) AS RunningSales

FROM MonthlySales;

Business Question 25

Year-over-Year Growth using LAG()

One of the most useful SQL window functions is LAG(), which compares current values with previous periods.

This query allows analysts to compare consecutive years and evaluate business growth over time. It is commonly used in executive dashboards and financial reporting.

WITH AnnualSales AS

(

SELECT

YEAR(Order_Date) AS SalesYear,

SUM(Sales) AS Revenue

FROM superstore_sales

GROUP BY SalesYear

)

SELECT

SalesYear,

ROUND(Revenue,2),

ROUND(

LAG(Revenue)

OVER(

ORDER BY SalesYear

),

2

) AS PreviousYearRevenue

FROM AnnualSales;

Shipping Performance Analysis

Customer satisfaction is influenced not only by product quality but also by delivery speed.

Business Question 26

What is the average shipping time?

Understanding delivery times helps businesses evaluate logistics performance and identify opportunities to improve customer satisfaction.

SELECT

Ship_Mode,

ROUND(

AVG(

DATEDIFF(

Ship_Date,

Order_Date

)

),

2

) AS AverageShippingDays

FROM superstore_sales

GROUP BY Ship_Mode;

Executive Dashboard

Before presenting recommendations, it is useful to summarize the key performance indicators (KPIs) that executives would typically monitor.

SELECT
ROUND(SUM(Sales),2) AS TotalSales,
COUNT(DISTINCT Order_ID) AS TotalOrders,
COUNT(DISTINCT Customer_ID) AS TotalCustomers,
COUNT(DISTINCT Product_ID) AS TotalProducts,
ROUND(SUM(Sales)/COUNT(DISTINCT Order_ID),2) AS AverageOrderValue
FROM superstore_sales;

This query creates a concise executive summary of business performance. These KPIs provide a high-level overview of revenue generation, customer activity, product diversity, and average spending per order. Such metrics are often displayed on executive dashboards to monitor overall business performance.

Creating a Reusable Sales View

In business environments, analysts often create SQL views to simplify recurring analyses and avoid rewriting complex queries.

CREATE VIEW vw_sales_summary AS

SELECT

Order_ID,

Order_Date,

Customer_ID,

Customer_Name,

Segment,

Region,

State,

Category,

Sub_Category,

Sales

FROM superstore_sales;

Using views improves query readability and promotes consistency across reports. Instead of repeatedly querying the base table, analysts can build reports directly from reusable views.

Project Summary

This project demonstrates how SQL can be used throughout the data analysis workflow, from importing and cleaning data to answering business questions and supporting strategic decision-making.

The analysis focused on four major business areas:

• Sales performance
• Customer behavior
• Product demand
• Shipping efficiency

By applying SQL to each of these areas, it became possible to identify patterns that may not be immediately apparent from raw transactional data.

Conclusion

Using MySQL, I analyzed 9,800 retail transaction records representing 4,922 unique orders, 793 customers, 1,861 products, 529 cities, and 49 U.S. states. The project demonstrated how SQL can be used to clean data, explore customer behavior, evaluate product demand, investigate shipping preferences, and support business decision-making.

The analysis revealed several notable patterns. The West region recorded the highest transaction activity, while the Consumer segment represented the retailer’s largest customer group. Office Supplies accounted for the highest number of product transactions, with Binders and Paper emerging as the most frequently purchased sub-categories. Additionally, Standard Class was by far the most commonly selected shipping method, indicating that customers generally preferred cost-effective delivery options.

Overall, this project illustrates that SQL is more than a language for querying databases. It is a powerful analytical tool for transforming transactional data into meaningful business insights. By combining data cleaning, exploratory analysis, aggregate functions, Common Table Expressions, window functions, and ranking functions, SQL can support informed decision-making across sales, customer analytics, inventory management, and operations.