🔹 16. Days Between Purchases

SELECT
    Customer_ID, Order_Date,
    LAG(Order_Date) OVER (PARTITION BY Customer_ID ORDER BY Order_Date) AS Previous_Order_Date
FROM Orders;
-- Then: Current - Previous

🔹 17. Finding Inactive Customers

SELECT Customer_ID, MAX(Order_Date) AS Last_Order_Date
FROM Orders
GROUP BY Customer_ID;
-- Compare with CURRENT_DATE for 90-day inactivity

🔹 18. Common Mistake

Avoid: WHERE YEAR(Order_Date) = 2026

Prefer: Range filter — it's clearer and index-friendly.

💼 Real-World Applications

✅ Monthly revenue, daily sales, YoY/MoM growth, retention, churn, purchase frequency, cohort, subscription expiry

🎯 SQL Interview Challenge: Find each customer's most recent order

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;

💡 Double Tap ❤️ For More