Team3_Sql_Script.sql

-- Create a cleaned version of Transactions table

CREATE TABLE clean_transactions AS
SELECT DISTINCT
    Invoice,
    LTRIM(RTRIM(StockCode)) AS StockCode,
    Description,
    Quantity,
    InvoiceDate,
    Price,
    CustomerID,
    Country,
    ISNULL(Channel, 'Unknown') AS Channel,
    PaymentMethod,
    (Quantity * Price) AS Revenue
INTO clean_transactions
FROM Transactions
WHERE 
    Price > 0
    AND Quantity > 0
    AND CustomerID IS NOT NULL;


--Join Products sheet to get category
	SELECT 
    t.*,
    p.ProductName,
    p.Category
INTO final_transactions
FROM clean_transactions t
LEFT JOIN Products p
ON LTRIM(RTRIM(t.StockCode)) = LTRIM(RTRIM(p.StockCode));

SELECT TOP 10 * FROM final_transactions;

SELECT COUNT(*) FROM final_transactions;
SELECT COUNT(*) FROM clean_transactions;


--Revenue by Country & Month

SELECT 
    Country,
    FORMAT(InvoiceDate, 'yyyy-MM') AS Month,
    SUM(Revenue) AS Total_Revenue
FROM final_transactions
GROUP BY 
    Country,
    FORMAT(InvoiceDate, 'yyyy-MM')
ORDER BY 
    Month,
    Total_Revenue DESC;


--Top 10 Customers by Total Spend

SELECT TOP 10
    CustomerID,
    SUM(Revenue) AS Total_Spend
FROM final_transactions
GROUP BY CustomerID
ORDER BY Total_Spend DESC;


--Monthly Active Customers (MAC)

SELECT 
    FORMAT(InvoiceDate, 'yyyy-MM') AS Month,
    COUNT(DISTINCT CustomerID) AS Active_Customers
FROM final_transactions
GROUP BY FORMAT(InvoiceDate, 'yyyy-MM')
ORDER BY Month;


--Products with Highest Cancellation Rate

SELECT TOP 10
    t.StockCode,
    p.ProductName,
    p.Category,
    COUNT(*) AS Total_Orders,
    SUM(CASE WHEN t.Quantity < 0 THEN 1 ELSE 0 END) AS Cancelled_Orders,
    CAST(SUM(CASE WHEN t.Quantity < 0 THEN 1 ELSE 0 END) AS FLOAT) 
        / COUNT(*) AS Cancellation_Rate
FROM Transactions t
LEFT JOIN Products p
ON LTRIM(RTRIM(t.StockCode)) = LTRIM(RTRIM(p.StockCode))
GROUP BY 
    t.StockCode,
    p.ProductName,
    p.Category
ORDER BY Cancellation_Rate DESC;


--Average Order Value (AOV) per Customer

SELECT 
    CustomerID,
    AVG(OrderValue) AS Avg_Order_Value
FROM (
    SELECT 
        CustomerID,
        Invoice,
        SUM(Revenue) AS OrderValue
    FROM final_transactions
    GROUP BY CustomerID, Invoice
) sub
GROUP BY CustomerID
ORDER BY Avg_Order_Value DESC;


--Repeat vs One-Time Buyers

SELECT 
    Buyer_Type,
    COUNT(*) AS Customer_Count
FROM (
    SELECT 
        CustomerID,
        CASE 
            WHEN COUNT(DISTINCT Invoice) = 1 THEN 'One-Time Buyer'
            ELSE 'Repeat Buyer'
        END AS Buyer_Type
    FROM final_transactions
    GROUP BY CustomerID
) sub
GROUP BY Buyer_Type;


--Pareto Analysis (Top Customers Contribution)

WITH customer_spend AS (
    SELECT 
        CustomerID,
        SUM(Revenue) AS Total_Spend
    FROM final_transactions
    GROUP BY CustomerID
),
ranked AS (
    SELECT 
        CustomerID,
        Total_Spend,
        NTILE(5) OVER (ORDER BY Total_Spend DESC) AS Spend_Group
    FROM customer_spend
)
SELECT 
    Spend_Group,
    SUM(Total_Spend) AS Group_Revenue
FROM ranked
GROUP BY Spend_Group
ORDER BY Spend_Group;


--Cohort Analysis (Customer Retention)

WITH first_purchase AS (
    SELECT 
        CustomerID,
        MIN(FORMAT(InvoiceDate, 'yyyy-MM')) AS First_Month
    FROM final_transactions
    GROUP BY CustomerID
),
cohort_data AS (
    SELECT 
        t.CustomerID,
        f.First_Month,
        FORMAT(t.InvoiceDate, 'yyyy-MM') AS Activity_Month
    FROM final_transactions t
    JOIN first_purchase f
    ON t.CustomerID = f.CustomerID
)
SELECT 
    First_Month,
    Activity_Month,
    COUNT(DISTINCT CustomerID) AS Customers
FROM cohort_data
GROUP BY First_Month, Activity_Month
ORDER BY First_Month, Activity_Month;


--Revenue by Product Category

SELECT 
    Category,
    SUM(Revenue) AS Total_Revenue
FROM final_transactions
GROUP BY Category
ORDER BY Total_Revenue DESC;


--Channel-wise Revenue & Orders

SELECT 
    Channel,
    COUNT(DISTINCT Invoice) AS Total_Orders,
    SUM(Revenue) AS Total_Revenue
FROM final_transactions
GROUP BY Channel
ORDER BY Total_Revenue DESC;


SELECT * FROM final_transactions;