🔹 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
