Advanced SQL Data Analysis

Orders Table

Customers Table

Products Table

Employees Table

OrdersArchive Table

Customer Sales Analysis

This query calculates total sales per customer, identifies their last order date, ranks them by sales performance, and segments them into categories for targeted marketing.

/*
=======================================================================
                 CTE (NON RECURSIVE) MINI PROJECT 
=======================================================================
*/

--CTE (Standalone)
--Find the total sales per customer

WITH TotalSalesPerCustomer AS
(SELECT 
    CustomerID,
    SUM(Sales) TotalSales
FROM [Sales].[Orders]
GROUP BY CustomerID
),

--CTE (Multiple Standalone) USE a comma to separate each standalone CTE and do not repeat the with
--Find the last order date for each customers

LastOrderDate AS
(SELECT 
    CustomerID,
    MAX(OrderDate) LastOrderDate
FROM [Sales].[Orders]
GROUP BY CustomerID

--Note, you can use any sql statement within the CTE except the ORDER BY
),

--CTE (Nested) 
--Rank the customers based on Total Sales per customer

CustomerRanking AS
(SELECT 
    CustomerID,
    TotalSales,
    RANK() OVER(ORDER BY TotalSales DESC) CustomerRank
FROM TotalSalesPerCustomer
),

--Segment the customers based on Total Sales (CTE (Nested) )
CustomerSegmentation AS
(SELECT 
    CustomerID,
    CASE WHEN TotalSales > 110 THEN 'HighSales'
         WHEN TotalSales BETWEEN 90 AND 110 THEN 'MediumSales'
         ELSE 'LowSales' END Segment
FROM TotalSalesPerCustomer
)


SELECT 
    C.CustomerID,
    C.FirstName,
    C.LastName,
    T.TotalSales,
    L.LastOrderDate,
    CR.CustomerRank,
    CS.Segment
FROM [Sales].[Customers] C
LEFT JOIN TotalSalesPerCustomer T ON T.CustomerID = C.CustomerID
LEFT JOIN LastOrderDate L ON L.CustomerID = C.CustomerID
LEFT JOIN CustomerRanking CR ON CR.CustomerID = C.CustomerID
LEFT JOIN CustomerSegmentation CS ON CS.CustomerID = C.CustomerID
ORDER BY TotalSales DESC;

Sales Performance Analysis

This comprehensive query provides multiple perspectives on sales performance including product totals, rankings, and comparisons to averages.

/*
========================================
          WINDOW FUNCTIONS
========================================
*/
USE SalesDB;
GO

SELECT 
    OrderID,
    ProductID,
    OrderDate,
    OrderStatus,
    Sales,
    SUM(Sales) OVER(PARTITION BY ProductID) ProductTotal,
    SUM(Sales) OVER(PARTITION BY ProductID, OrderStatus) ProductTotalbyStatus,
    SUM(Sales) OVER() TotalSales,
    ROUND(CAST(Sales AS FLOAT)/SUM(Sales) OVER() * 100, 2) PercentageOfTotal,
    AVG(Sales) OVER() AverageSales,
    AVG(Sales) OVER(PARTITION BY ProductID) AverageSalesbyProduct,
    MAX(Sales) OVER() HighestSales,
    MIN(Sales) OVER() LowestSales
FROM [Sales].[Orders]

Data Quality Check

Window functions can also be used to identify potential data quality issues, as shown in this duplicate detection query:

--Check whether the table 'OrdersArchive' contains any duplicates
--Checking data quality

SELECT *
FROM (
    SELECT 
        OrderID,
        COUNT(OrderID) OVER(PARTITION BY OrderID) IDCheck
    FROM [Sales].[OrdersArchive]
) T
WHERE IDCheck != 1

Advanced Analytics Examples

Additional window function implementations demonstrating advanced analytical capabilities:

--Rank each of the orders based on their sales from highest to lowest
SELECT 
    OrderID,
    OrderDate,
    Sales,
    RANK() OVER(ORDER BY Sales DESC) SalesRank
FROM [Sales].[Orders]

--Calculate the moving average of sales for each product over time
SELECT 
    OrderID,
    ProductID,
    OrderDate,
    Sales,
    AVG(Sales) OVER(PARTITION BY ProductID) AverageSales,
    AVG(Sales) OVER(PARTITION BY ProductID ORDER BY OrderDate) MovingAverage
FROM [Sales].[Orders]

--Find the deviation of each sales from the minimum and maximum sales amount
SELECT 
    OrderID,
    Sales,
    MAX(Sales) OVER() HighestSales,
    MIN(Sales) OVER() LowestSales,
    ABS(Sales - MAX(Sales) OVER()) SalesDeviationFromMax,
    (Sales - MIN(Sales) OVER()) SalesDeviationFromMin
FROM [Sales].[Orders]

Technical Achievements

• Demonstrated mastery of advanced SQL features including CTEs and Window Functions
• Created modular, maintainable queries that can be easily adapted as business needs change
• Implemented comprehensive data quality checks
• Delivered actionable business intelligence from raw data
• Showcased ability to handle complex analytical requirements

Business Value Delivered

• Enabled data-driven customer segmentation for targeted marketing
• Provided visibility into product performance across multiple dimensions
• Established benchmarks for evaluating sales performance
• Identified potential data integrity issues before they impact decision-making
• Created foundation for predictive analytics and forecasting