Northwind_Sales_Script.sql

-- ========================================================
-- 📦 Section 1: Customer Segmentation (RFM & Order Value)
-- ========================================================
-- This section includes:
-- 🧠 RFM (Recency, Frequency, Monetary) segmentation
-- 💰 Average Order Value segmentation
-- 🌍 Geographic analysis by customer location
--
-- 🧠 RFM Segments:
--   - Champions: High Frequency, Recent Purchase, High Revenue
--   - Potential Loyalists: High Frequency or Medium Revenue
--   - At Risk: Others with older/fewer orders
--
-- 💰 Order Value Segments (based on AOV):
--   - High Value: ≥ $742
--   - Medium Value: $728–$741
--   - Low Value: < $728
-- ==========================================================


-- 🔍 RFM Analysis View
CREATE VIEW RFM AS 
SELECT 
    c.CustomerID,
    c.CompanyName,
    MAX(o.OrderDate) AS LastOrderDate,
    COUNT(DISTINCT o.OrderID) AS OrderFrequency,
    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS MonetaryValue
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
LEFT JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY c.CustomerID;


-- 🧠 RFM Segmentation Logic
SELECT 
    CustomerID,
    CompanyName,
    ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) AS Recency,
    OrderFrequency,
    MonetaryValue,

    -- 🕒 Recency Category (based on last order date)
    CASE 
        WHEN ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) BETWEEN 430 AND 460 THEN 'Very Recent'
        WHEN ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) BETWEEN 461 AND 490 THEN 'Recent'
        WHEN ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) BETWEEN 491 AND 520 THEN 'Occasional'
        WHEN ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) BETWEEN 521 AND 601 THEN 'Inactive'
        ELSE 'Unknown'
    END AS RecencyCategory,

    -- 🔁 Frequency Category (number of distinct orders)
    CASE 
        WHEN OrderFrequency BETWEEN 192 AND 210 THEN 'High Frequency'
        WHEN OrderFrequency BETWEEN 173 AND 191 THEN 'Medium Frequency'
        ELSE 'Low Frequency'
    END AS FrequencyCategory,

    -- 💰 Monetary Category (total sales value)
    CASE 
        WHEN MonetaryValue > 5500000 THEN 'High Value'
        WHEN MonetaryValue BETWEEN 4500000 AND 5500000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS MonetaryCategory,

    -- 📊 Final Customer Segment
    CASE 
        WHEN ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) BETWEEN 430 AND 460 
             AND OrderFrequency BETWEEN 192 AND 210
             AND MonetaryValue > 5500000 THEN 'Champion'
        WHEN (OrderFrequency BETWEEN 173 AND 191 OR MonetaryValue BETWEEN 4500000 AND 5500000) THEN 'Potential Loyalist'
        ELSE 'At Risk'
    END AS CustomerSegment
FROM RFM;


-- 📊 Segment Distribution (RFM)
WITH Segmentation AS (
    SELECT 
        CASE 
            WHEN ROUND(julianday('2025-01-01') - julianday(LastOrderDate)) BETWEEN 430 AND 460 
                 AND OrderFrequency BETWEEN 192 AND 210 
                 AND MonetaryValue > 5500000 THEN 'Champion'
            WHEN (OrderFrequency BETWEEN 173 AND 191 OR MonetaryValue BETWEEN 4500000 AND 5500000) THEN 'Potential Loyalist'
            ELSE 'At Risk'
        END AS CustomerSegment
    FROM RFM
)
SELECT 
    CustomerSegment,
    COUNT(*) AS SegmentCount,
    ROUND(CAST(COUNT(*) AS FLOAT) * 100 / (SELECT COUNT(*) FROM Segmentation), 2) AS SegmentPercentage
FROM Segmentation
GROUP BY CustomerSegment;


-- 💰 Order Value Segmentation View
CREATE VIEW OrderValue AS
SELECT 
    c.CustomerID,
    c.CompanyName,
    ROUND(AVG(od.Quantity * od.UnitPrice * (1 - od.Discount))) AS AverageOrderValue,
    CASE 
        WHEN ROUND(AVG(od.Quantity * od.UnitPrice * (1 - od.Discount))) >= 742 THEN 'High Value'
        WHEN ROUND(AVG(od.Quantity * od.UnitPrice * (1 - od.Discount))) BETWEEN 728 AND 741 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS ValueSegment
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY c.CustomerID;


-- 📋 Order Value Segmentation Results
SELECT * 
FROM OrderValue;


-- 📈 Segment Distribution (Order Value)
SELECT 
    ValueSegment,
    COUNT(*) AS SegmentCount,
    ROUND(CAST(COUNT(*) AS FLOAT) * 100 / (SELECT COUNT(*) FROM OrderValue), 2) AS SegmentPercentage
FROM OrderValue
GROUP BY ValueSegment;


-- 🌍 Customers by City & Country with Orders & Revenue
SELECT 
    c.CustomerID,
    c.CompanyName,
    c.City,
    c.Country,
    COUNT(DISTINCT o.OrderID) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalSales
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName, c.City, c.Country
ORDER BY TotalSales DESC;


-- 🌎 Customer Distribution by City & Country (Aggregated)
SELECT 
    c.City,
    c.Country,
    COUNT(DISTINCT c.CustomerID) AS TotalCustomers,
    COUNT(DISTINCT o.OrderID) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY c.City, c.Country
ORDER BY TotalCustomers DESC, TotalRevenue DESC;

-- =============================================================
-- 🛍️ Section 2: Product Performance Analysis
-- =============================================================
-- This section includes analysis on:
-- 🔝 Top Revenue Products
-- 🔁 Most Frequently Ordered Products
-- 🐢 Slow Movers
-- 📦 Revenue and Order Distribution by Category
-- 💸 Most Expensive & Cheapest Products (and their performance)
-- 🤝 Frequently Bought Together Products
-- ==============================================================


-- 💰 Top 10 Revenue Generating Products
SELECT 
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue
FROM Products p
INNER JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID  
GROUP BY p.ProductID, p.ProductName, c.CategoryName
ORDER BY TotalRevenue DESC
LIMIT 10;


-- 🔁 Top 10 Most Frequently Ordered Products
SELECT 
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    SUM(od.Quantity) AS TotalQuantity
FROM Products p
INNER JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID 
GROUP BY p.ProductID, p.ProductName, c.CategoryName
ORDER BY TotalQuantity DESC
LIMIT 10;


-- 🐢 Slow Movers (Bottom 5 by Quantity Ordered)
SELECT 
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    SUM(od.Quantity) AS TotalQuantity
FROM Products p
LEFT JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID 
GROUP BY p.ProductID, p.ProductName, c.CategoryName
ORDER BY TotalQuantity ASC
LIMIT 5;


-- 📦 Revenue & Order Distribution by Product Category
SELECT
    c.CategoryName,
    COUNT(DISTINCT od.OrderID) AS TotalOrders, 
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue,
    ROUND(
        CAST(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS FLOAT) * 100 / 
        (SELECT SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) 
         FROM "Order Details" od
         INNER JOIN Products p ON od.ProductID = p.ProductID
         INNER JOIN Categories c ON p.CategoryID = c.CategoryID), 2
    ) AS RevenuePercentage
FROM "Order Details" od
INNER JOIN Products p ON od.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryName
ORDER BY TotalRevenue DESC;


-- 💎 10 Most Expensive Products: Orders and Revenue
SELECT 
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    p.UnitPrice,
    COUNT(DISTINCT od.OrderID) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalSales
FROM Products p
INNER JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY p.ProductID, p.ProductName, c.CategoryName, p.UnitPrice
ORDER BY p.UnitPrice DESC
LIMIT 10;


-- 🪙 10 Cheapest Products: Orders and Revenue
SELECT 
    p.ProductID,
    p.ProductName,
    c.CategoryName,
    p.UnitPrice,
    COUNT(DISTINCT od.OrderID) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalSales
FROM Products p
INNER JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY p.ProductID, p.ProductName, c.CategoryName, p.UnitPrice
ORDER BY p.UnitPrice ASC
LIMIT 10;


-- 🤝 Top 10 Frequently Bought Together Product Pairs
SELECT 
    p1.ProductID AS ProductID_1,
    p1.ProductName AS ProductName_1,
    p2.ProductID AS ProductID_2,
    p2.ProductName AS ProductName_2,
    COUNT(*) AS Frequency
FROM "Order Details" od1
INNER JOIN "Order Details" od2 
    ON od1.OrderID = od2.OrderID 
    AND od1.ProductID < od2.ProductID
INNER JOIN Products p1 ON od1.ProductID = p1.ProductID
INNER JOIN Products p2 ON od2.ProductID = p2.ProductID
GROUP BY p1.ProductID, p1.ProductName, p2.ProductID, p2.ProductName
ORDER BY Frequency DESC
LIMIT 10;


-- ============================================================
-- 🚚 Section 3: Supplier Performance
-- ============================================================
-- This section focuses on evaluating supplier performance through:
-- - Revenue and orders per supplier
-- - Geographic distribution (cities & countries)
-- - Aggregated statistics by supplier countries
-- ============================================================


-- 🧾 Overview of Products and Suppliers Performance --
SELECT
    p.ProductID,
    p.ProductName,
    c.CategoryName,  
    s.CompanyName AS SupplierName,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount))) AS TotalRevenue,
    COUNT(DISTINCT(od.OrderID)) AS TotalOrders
FROM "Order Details" od
INNER JOIN Products p ON od.ProductID = p.ProductID
INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID  
GROUP BY p.ProductID, p.ProductName, c.CategoryName, s.CompanyName
ORDER BY TotalRevenue DESC;


-- 🏙️ Supplier Cities and Countries: Orders & Revenue --
SELECT 
    s.SupplierID,
    s.CompanyName,
    s.City,
    s.Country,
    COUNT(DISTINCT(o.OrderID)) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalSales
FROM Suppliers s
INNER JOIN Products p ON s.SupplierID = p.SupplierID
INNER JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Orders o ON od.OrderID = o.OrderID
GROUP BY s.SupplierID, s.CompanyName, s.City, s.Country
ORDER BY TotalSales DESC;


-- 🌍 Aggregation by Supplier Country --
SELECT 
    s.Country,
    COUNT(DISTINCT(s.SupplierID)) AS TotalSuppliers,
    COUNT(DISTINCT(o.OrderID)) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue
FROM Suppliers s
INNER JOIN Products p ON s.SupplierID = p.SupplierID
INNER JOIN "Order Details" od ON p.ProductID = od.ProductID
INNER JOIN Orders o ON o.OrderID = od.OrderID
GROUP BY s.Country
ORDER BY TotalSuppliers DESC, TotalRevenue DESC;


-- ========================================================
-- 📈 Section 4: Order Trends (Seasonality & Timing)
-- ========================================================
-- This section uncovers order patterns by:
-- - Month, quarter, and day of the week
-- - Hour of the day
-- - Order size categories based on quantity
-- Useful for identifying peak periods and planning logistics.
-- ========================================================


-- 📅 Orders by Month & Year --
SELECT 
    strftime('%Y-%m', o.OrderDate) AS OrderMonth,
    COUNT(o.OrderID) AS TotalOrders
FROM Orders o
GROUP BY OrderMonth
ORDER BY TotalOrders DESC;


-- 📊 Monthly Revenue Overview --
WITH MonthlySummary AS (
    SELECT 
        strftime('%m', o.OrderDate) AS MonthNumber,
        CASE strftime('%m', o.OrderDate)
            WHEN '01' THEN 'January'
            WHEN '02' THEN 'February'
            WHEN '03' THEN 'March'
            WHEN '04' THEN 'April'
            WHEN '05' THEN 'May'
            WHEN '06' THEN 'June'
            WHEN '07' THEN 'July'
            WHEN '08' THEN 'August'
            WHEN '09' THEN 'September'
            WHEN '10' THEN 'October'
            WHEN '11' THEN 'November'
            WHEN '12' THEN 'December'
        END AS MonthName,
        COUNT(DISTINCT(o.OrderID)) AS TotalOrders,
        ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue
    FROM "Order Details" od
    INNER JOIN Orders o ON od.OrderID = o.OrderID
    GROUP BY MonthNumber, MonthName
)
SELECT 
    MonthName,
    TotalOrders,
    TotalRevenue,
    ROUND((TotalRevenue * 100.0) / (SELECT SUM(TotalRevenue) FROM MonthlySummary), 2) AS RevenuePercentage
FROM MonthlySummary
ORDER BY TotalOrders DESC;


-- 🗓️ Quarterly Revenue Overview --
WITH QuarterlySummary AS (
    SELECT 
        'Q' || ((CAST(strftime('%m', o.OrderDate) AS INTEGER) - 1) / 3 + 1) AS Quarter,
        ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue,
        COUNT(DISTINCT(o.OrderID)) AS TotalOrders
    FROM "Order Details" od
    INNER JOIN Orders o ON od.OrderID = o.OrderID
    GROUP BY Quarter
)
SELECT 
    Quarter,
    TotalOrders,
    TotalRevenue,
    ROUND((TotalRevenue * 100.0) / (SELECT SUM(TotalRevenue) FROM QuarterlySummary), 2) AS RevenuePercentage
FROM QuarterlySummary
ORDER BY TotalOrders DESC;


-- 🕒 Hourly Order Analysis --
SELECT 
    strftime('%H', o.OrderDate) AS HourOfDay,
    COUNT(DISTINCT(o.OrderID)) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue,
    ROUND(CAST(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS FLOAT) * 100 / 
          (SELECT SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) 
           FROM "Order Details" od 
           INNER JOIN Orders o ON od.OrderID = o.OrderID), 2) AS RevenuePercentage
FROM "Order Details" od
INNER JOIN Orders o ON od.OrderID = o.OrderID
GROUP BY HourOfDay
ORDER BY TotalOrders DESC;


-- 📆 Day of the Week Analysis --
WITH DayOfWeekSummary AS (
    SELECT 
        CASE strftime('%w', o.OrderDate)
            WHEN '0' THEN 'Sunday'
            WHEN '1' THEN 'Monday'
            WHEN '2' THEN 'Tuesday'
            WHEN '3' THEN 'Wednesday'
            WHEN '4' THEN 'Thursday'
            WHEN '5' THEN 'Friday'
            WHEN '6' THEN 'Saturday'
        END AS DayOfWeekName,
        COUNT(DISTINCT(o.OrderID)) AS TotalOrders,
        ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue
    FROM "Order Details" od
    INNER JOIN Orders o ON od.OrderID = o.OrderID
    GROUP BY DayOfWeekName
)
SELECT 
    DayOfWeekName,
    TotalOrders,
    TotalRevenue,
    ROUND((TotalRevenue * 100.0) / (SELECT SUM(TotalRevenue) FROM DayOfWeekSummary), 2) AS RevenuePercentage
FROM DayOfWeekSummary
ORDER BY TotalOrders DESC;


-- 📦 Order Size Distribution by Quantity --
SELECT 
    od.OrderID,
    SUM(od.Quantity) AS TotalQuantity,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue,
    CASE 
        WHEN SUM(od.Quantity) BETWEEN 1 AND 10 THEN 'Small'
        WHEN SUM(od.Quantity) BETWEEN 11 AND 50 THEN 'Medium'
        WHEN SUM(od.Quantity) BETWEEN 51 AND 500 THEN 'Large'
        WHEN SUM(od.Quantity) BETWEEN 501 AND 1000 THEN 'Very Large'
        ELSE 'Bulk Orders'
    END AS OrderSizeCategory
FROM "Order Details" od
GROUP BY od.OrderID
ORDER BY TotalQuantity DESC;


-- 📊 Summary View of Order Size Distribution --
WITH OrderSizeSummary AS (
    SELECT 
        CASE 
            WHEN TotalQuantity BETWEEN 1 AND 10 THEN 'Small'
            WHEN TotalQuantity BETWEEN 11 AND 50 THEN 'Medium'
            WHEN TotalQuantity BETWEEN 51 AND 500 THEN 'Large'
            WHEN TotalQuantity BETWEEN 501 AND 1000 THEN 'Very Large'
            ELSE 'Bulk Orders'
        END AS OrderSizeCategory,
        COUNT(OrderID) AS TotalOrders,
        SUM(TotalQuantity) AS TotalQuantity,
        ROUND(SUM(TotalRevenue), 2) AS TotalRevenue
    FROM (
        SELECT 
            od.OrderID,
            SUM(od.Quantity) AS TotalQuantity,
            ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalRevenue
        FROM "Order Details" od
        GROUP BY od.OrderID
    ) AS OrderSummary
    GROUP BY OrderSizeCategory
)
SELECT 
    OrderSizeCategory,
    TotalOrders,
    TotalRevenue,
    ROUND((TotalRevenue * 100.0) / (SELECT SUM(TotalRevenue) FROM OrderSizeSummary), 3) AS RevenuePercentage
FROM OrderSizeSummary
ORDER BY TotalRevenue DESC;

-- ========================================================
-- 🧑‍💼 Section 5: Employee Insights (Performance & Revenue)
-- ========================================================
-- This section analyzes:
-- - Total sales volume and number of orders per employee
-- - Average order value handled by each employee
-- - Geographic performance (city and country)
-- Helps identify top performers and regional differences in employee output.
-- ========================================================


-- 🏆 Total Sales Volume by Employee --
SELECT 
    e.EmployeeID,
    e.FirstName || ' ' || e.LastName AS EmployeeName,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount))) AS TotalSales
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
INNER JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY e.EmployeeID, EmployeeName
ORDER BY TotalSales DESC;


-- 📦 Number of Orders Processed by Each Employee --
SELECT 
    e.EmployeeID,
    e.FirstName || ' ' || e.LastName AS FullName,
    COUNT(o.OrderID) AS TotalOrders
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
GROUP BY e.EmployeeID, FullName
ORDER BY TotalOrders DESC;


-- 💰 Average Order Value (AOV) by Employee --
SELECT 
    e.EmployeeID,
    e.FirstName || ' ' || e.LastName AS FullName,
    ROUND(AVG(od.Quantity * od.UnitPrice * (1 - od.Discount))) AS AvgOrderValue
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
INNER JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY e.EmployeeID, FullName
ORDER BY AvgOrderValue DESC;


-- 🌍 Employees by City and Country with Total Orders & Revenue --
SELECT 
    e.EmployeeID,
    e.FirstName || ' ' || e.LastName AS EmployeeName,
    e.City, 
    e.Country,
    COUNT(DISTINCT(o.OrderID)) AS TotalOrders,
    ROUND(SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)), 2) AS TotalSales
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
INNER JOIN "Order Details" od ON o.OrderID = od.OrderID
GROUP BY e.EmployeeID, e.LastName, e.FirstName, e.City, e.Country
ORDER BY TotalSales DESC;