• This can help marketing teams identify potential customers who need activation campaigns.

🔹 9. Customers Above Average Spending

First calculate customer totals. Then compare them with the overall average.

WITH Customer_Sales AS (
    SELECT Customer_ID, SUM(Sales) AS Total_Sales FROM Orders GROUP BY Customer_ID
)
SELECT Customer_ID, Total_Sales
FROM Customer_Sales
WHERE Total_Sales > (SELECT AVG(Total_Sales) FROM Customer_Sales);

• Notice how concepts from earlier parts work together: CTE + Aggregation + Subquery

🔹 10. Month-over-Month Sales Growth

First aggregate sales by month. Then use LAG().

WITH Monthly_Sales AS (
    SELECT EXTRACT(YEAR FROM Order_Date) AS Year,
           EXTRACT(MONTH FROM Order_Date) AS Month,
           SUM(Sales) AS Total_Sales
    FROM Orders
    GROUP BY EXTRACT(YEAR FROM Order_Date), EXTRACT(MONTH FROM Order_Date)
),
Comparison AS (
    SELECT Year, Month, Total_Sales,
           LAG(Total_Sales) OVER (ORDER BY Year, Month) AS Previous_Sales
    FROM Monthly_Sales
)
SELECT Year, Month, Total_Sales, Previous_Sales, Total_Sales - Previous_Sales AS Sales_Change
FROM Comparison;

• This produces a time-based comparison instead of just a total.

🔹 11. Calculate Growth Percentage

You can extend the previous query:

(Total_Sales - Previous_Sales) / NULLIF(Previous_Sales, 0) * 100

• NULLIF() is important because it prevents division-by-zero errors.

• A practical analyst must always think about edge cases.

🔹 12. Find the Latest Order for Every Customer

From Part 17, combine PARTITION BY + ORDER BY + ROW_NUMBER()

WITH Ranked_Orders AS (
    SELECT Customer_ID, Order_ID, Order_Date,
           ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Order_Date DESC) AS rn
    FROM Orders
)
SELECT Customer_ID, Order_ID, Order_Date FROM Ranked_Orders WHERE rn = 1;

This is useful for:

• Customer activity

• Churn analysis

• Last purchase reporting

• CRM segmentation

🔹 13. Identify Potentially Inactive Customers

First find the last purchase date:

SELECT Customer_ID, MAX(Order_Date) AS Last_Order_Date FROM Orders GROUP BY Customer_ID;

Then compare the last order against a chosen inactivity threshold.

• The business rule might be: No purchase for 90 days → Potentially inactive

• The important lesson: SQL provides the calculation. The business defines what "inactive" means.

🔹 14. Find Duplicate Records

Window functions are excellent for detecting duplicates.

WITH Duplicate_Check AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Customer_ID, Order_Date, Sales ORDER BY Order_ID) AS rn
    FROM Orders
)
SELECT * FROM Duplicate_Check WHERE rn > 1;

• These records can then be investigated before cleaning the dataset.

🔹 15. Combining Multiple Tables

Real analysis often requires several joins.

For example: Customers → Orders → Order_Items → Products

SELECT c.Customer_ID, c.Customer_Name, p.Product_Name, oi.Quantity, oi.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
JOIN Order_Items oi ON o.Order_ID = oi.Order_ID
JOIN Products p ON oi.Product_ID = p.Product_ID;