🔹 11. Calculate Growth Percentage

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

NULLIF() prevents division-by-zero errors.

🔹 12. Find the Latest Order for Every Customer

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;

🔹 13-14. Inactive Customers & Duplicates

Inactive = MAX(Order_Date) vs 90-day threshold.

Business defines the rule, SQL calculates it.

Detect 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;

🔹 15. Combining Multiple Tables

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;

⚠️ Every additional join can change the number of rows. Always check the grain.

🔹 16. The Most Important Analytical Pattern

1. Filter raw data → 2. Join tables → 3. Aggregate to correct grain → 4. Apply window functions → 5. Filter analytical result → 6. Present final output

💼 Real-World Business Problems to Practice

Sales: Top 5 products by revenue, Top products within each category, Month with highest sales, Revenue growth by month

Customers: Customers with no orders, declining purchases, most recent purchase, repeat customers, AOV per customer

Operations: Orders taking longer than expected, Products never sold, Duplicate transactions, Most active regions

🎯 SQL Interview Challenge: Find the highest-selling product in each category.

WITH Product_Sales AS (
    SELECT Product_ID, Category, SUM(Sales) AS Total_Sales
    FROM Product_Sales_Data GROUP BY Product_ID, Category
),
Ranked_Products AS (
    SELECT *, RANK() OVER (PARTITION BY Category ORDER BY Total_Sales DESC) AS Sales_Rank
    FROM Product_Sales
)
SELECT Product_ID, Category, Total_Sales FROM Ranked_Products WHERE Sales_Rank = 1;

🧠 SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ❤️ For More