Identifying average shipping times, high-freight-cost customers , and seasonal order trends for Geemac Embassy Ltd

Geemacsyntax.txt

--Using CTE to analyze freight costs by year and month, identifying months where freight exceeds the yearly average.
WITH YearlyFreight AS (
    SELECT 
        YEAR(CAST(OrderDate AS DATE)) AS OrderYear,
        SUM(Freight) AS TotalYearlyFreight,
        COUNT(OrderID) AS YearlyOrderCount
    FROM Geemac_Orders
    GROUP BY YEAR(CAST(OrderDate AS DATE))
),
MonthlyFreight AS (
    SELECT 
        YEAR(CAST(OrderDate AS DATE)) AS OrderYear,
        MONTH(CAST(OrderDate AS DATE)) AS OrderMonth,
        SUM(Freight) AS TotalMonthlyFreight,
        COUNT(OrderID) AS MonthlyOrderCount
    FROM Geemac_Orders
    GROUP BY YEAR(CAST(OrderDate AS DATE)), MONTH(CAST(OrderDate AS DATE))
)
SELECT 
    m.OrderYear,
    m.OrderMonth,
    m.TotalMonthlyFreight,
    m.MonthlyOrderCount,
    y.TotalYearlyFreight,
    y.YearlyOrderCount,
    CASE 
        WHEN m.TotalMonthlyFreight > (y.TotalYearlyFreight / 12) THEN 'Above Average'
        ELSE 'Below Average'
    END AS FreightStatus
FROM MonthlyFreight m
JOIN YearlyFreight y ON m.OrderYear = y.OrderYear
ORDER BY m.OrderYear, m.OrderMonth; GO

--Create a view to summarize quarterly order statistics per customer, including total and average freight.
CREATE VIEW QuarterlyCustomerSummary AS
SELECT 
    CustomerID,
    DATEPART(QUARTER, CAST(OrderDate AS DATE)) AS OrderQuarter,
    YEAR(CAST(OrderDate AS DATE)) AS OrderYear,
    COUNT(OrderID) AS TotalOrders,
    SUM(Freight) AS TotalFreight,
    AVG(Freight) AS AvgFreight
FROM Geemac_Orders
GROUP BY 
    CustomerID,
    DATEPART(QUARTER, CAST(OrderDate AS DATE)),
    YEAR(CAST(OrderDate AS DATE)); GO

-- Query the view
SELECT 
    CustomerID,
    OrderYear,
    OrderQuarter,
    TotalFreight,
    AvgFreight
FROM QuarterlyCustomerSummary
WHERE TotalOrders > 5
ORDER BY OrderYear, OrderQuarter, TotalFreight DESC; GO

--Spotting outliers in the order data by using CTE and some filtering on orders where freight exceeds two standard deviations above the mean freight for that year.
--I started by looking at the average order count per customer
WITH AvgOrderCount AS (
    SELECT AVG(OrderCount) AS AvgCount
    FROM (
        SELECT COUNT(OrderID) AS OrderCount
        FROM Geemac_Orders
        GROUP BY CustomerID
    ) AS OrderCounts
),
-- I then spotted customers with above-average order counts and their total freight
CustomerMetrics AS (
    SELECT 
        CustomerID,
        SUM(Freight) AS TotalFreight,
        COUNT(OrderID) AS OrderCount
    FROM Geemac_Orders
    GROUP BY CustomerID
    HAVING COUNT(OrderID) > (SELECT AvgCount FROM AvgOrderCount)
)
-- Queried the CTE above to then get the top 3 customers by total freight
SELECT TOP 3
    CustomerID,
    TotalFreight,
    OrderCount
FROM CustomerMetrics GO

-- I was asked to spot customers who placed high-freight orders (above $100) in every quarter of 1997. Achieved this by performing  complex presence check on the data.
SELECT DISTINCT CustomerID
FROM Geemac_Orders o1
WHERE YEAR(CAST(OrderDate AS DATE)) = 1997
AND Freight > 100
AND NOT EXISTS (
    SELECT q.Quarter
    FROM (SELECT 1 AS Quarter UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) q
    WHERE NOT EXISTS (
        SELECT 1
        FROM Geemac_Orders o2
        WHERE o2.CustomerID = o1.CustomerID
        AND YEAR(CAST(o2.OrderDate AS DATE)) = 1997
        AND DATEPART(QUARTER, CAST(o2.OrderDate AS DATE)) = q.Quarter
        AND o2.Freight > 100
    )
); GO

--Performance Tuning by indexing and optimzing queries eg. I noticed I queried historic data on orders by customer and order date, that became my target to optimise.
CREATE INDEX IX_Geemac_Orders_CustomerID ON Geemac_Orders (CustomerID);

CREATE INDEX IX_Geemac_Orders_OrderDate ON Geemac_Orders (OrderDate); GO

WITH FilteredOrders AS (
    SELECT 
        OrderID,
        CustomerID,
        Freight,
        OrderDate
    FROM 
        Geemac_Orders
    WHERE 
        CustomerID = 'ALFKI'
        AND YEAR(CAST(OrderDate AS DATE)) = 1997
)
SELECT 
    o1.OrderID AS Order1,
    o2.OrderID AS Order2,
    o1.CustomerID,
    o1.Freight AS Freight1,
    o2.Freight AS Freight2,
    YEAR(CAST(o1.OrderDate AS DATE)) AS OrderYear
FROM 
    FilteredOrders o1
JOIN 
    FilteredOrders o2 ON o1.CustomerID = o2.CustomerID 
    AND YEAR(CAST(o1.OrderDate AS DATE)) = YEAR(CAST(o2.OrderDate AS DATE))
    AND o1.Freight > o2.Freight; GO

Re-enabling DB

--Re-enabling acces to a disabled database.
-- I had to identify the active session and then terminate it, then I rechecked the state of the database to make sure it was switched from single user back to multi user

SELECT session_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('Ordersandshipments'); GO

KILL 53; GO

ALTER DATABASE GeemacShipments SET MULTI_USER;

SELECT user_access_desc
FROM sys.databases
WHERE name = 'Ordersandshipments';