DAY 14: SQL - Window Functions Part 2 (LAG, LEAD, Running Totals)

Exercise 1: Calculate Month-Over-Month Sales Change with LAG

Goal: Learn to use the LAG function to compare current month sales with the previous month and calculate the percentage change.

Step-by-Step Instructions:

1. Open your SQL editor and connect to your sales database
2. Understand the requirement: For each month, we want to see current month sales, previous month sales, and the percentage change
3. Start with a basic monthly aggregation:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

4. Add LAG function to get previous month's sales: Press Ctrl+Home to go to the start, modify the query to:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS CurrentMonthSales,
       LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PreviousMonthSales
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

5. Calculate the month-over-month change:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS CurrentMonthSales,
       LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PreviousMonthSales,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS SalesChange
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

6. Add percentage change calculation:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS CurrentMonthSales,
       LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PreviousMonthSales,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS SalesChange,
       CASE
           WHEN LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) = 0 THEN NULL
           ELSE ROUND(((SUM(TotalAmount) - LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)))
                       / LAG(SUM(TotalAmount), 1, 0) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate))) * 100, 2)
       END AS PercentChange
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

7. Execute the query by pressing F5 or Ctrl+E and review the month-over-month comparison
8. Verify the results: Check that each month shows its sales compared to the previous month with accurate percentage calculations

💡 Tips:

• LAG syntax: LAG(column, offset, default_value) OVER (ORDER BY ...) - offset is how many rows back (1 = previous row)
• Default value matters: The third parameter (0 in this case) handles the first row which has no previous month
• OVER clause ordering: The ORDER BY in OVER determines which row is "previous" - always order by your time dimension
• Avoid division by zero: Use CASE to check if previous month sales is 0 before calculating percentage
• PARTITION BY for categories: Add PARTITION BY ProductCategory to compare within each category separately
• Keyboard shortcut: Use Ctrl+K, Ctrl+C to comment out lines while testing different variations

✅ Expected Result: A table showing each month with columns: OrderYear, OrderMonth, CurrentMonthSales, PreviousMonthSales, SalesChange, PercentChange (e.g., 2024, 10, $50,000, $45,000, $5,000, 11.11%)

🔄 Connection to Day 12: Day 12 introduced basic window functions like ROW_NUMBER and RANK. LAG extends this by allowing you to reference specific previous rows, enabling powerful time-series comparisons without self-joins!

Exercise 2: Use LEAD to Identify Future Order Trends

Goal: Master the LEAD function to look ahead in your data and identify upcoming customer behavior patterns.

Step-by-Step Instructions:

1. Open your SQL editor with the customer orders dataset
2. Understand the scenario: For each customer order, we want to see when their next order will be and how many days between orders
3. Create a base query showing customer orders:

   SELECT
       CustomerID,
       OrderID,
       OrderDate,
       TotalAmount
   FROM Orders
   ORDER BY CustomerID, OrderDate;

4. Add LEAD to find next order date:

   SELECT
       CustomerID,
       OrderID,
       OrderDate,
       TotalAmount,
       LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate
   FROM Orders
   ORDER BY CustomerID, OrderDate;

5. Calculate days until next order:

   SELECT
       CustomerID,
       OrderID,
       OrderDate,
       TotalAmount,
       LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate,
       DATEDIFF(day, OrderDate, LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) AS DaysUntilNextOrder
   FROM Orders
   ORDER BY CustomerID, OrderDate;

6. Add next order amount for comparison:

   SELECT
       CustomerID,
       OrderID,
       OrderDate,
       TotalAmount AS CurrentOrderAmount,
       LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate,
       LEAD(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderAmount,
       DATEDIFF(day, OrderDate, LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) AS DaysUntilNextOrder,
       CASE
           WHEN TotalAmount < LEAD(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) THEN 'Increasing'
           WHEN TotalAmount > LEAD(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) THEN 'Decreasing'
           ELSE 'Stable'
       END AS OrderValueTrend
   FROM Orders
   ORDER BY CustomerID, OrderDate;

7. Filter to show customers with decreasing order frequency:

   WITH OrderTrends AS (
       SELECT
           CustomerID,
           OrderID,
           OrderDate,
           DATEDIFF(day, OrderDate, LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) AS DaysUntilNextOrder
       FROM Orders
   )
   SELECT
       CustomerID,
       AVG(DaysUntilNextOrder) AS AvgDaysBetweenOrders
   FROM OrderTrends
   WHERE DaysUntilNextOrder IS NOT NULL
   GROUP BY CustomerID
   HAVING AVG(DaysUntilNextOrder) > 30
   ORDER BY AvgDaysBetweenOrders DESC;

8. Execute and analyze: Press F5 to identify customers who may be at risk of churning

💡 Tips:

• LEAD vs LAG: LEAD looks forward, LAG looks backward - use LEAD for predictions and forecasting scenarios
• PARTITION BY is crucial: Always partition by CustomerID when analyzing per-customer trends to avoid mixing data
• NULL handling: The last order for each customer will have NULL for NextOrderDate - this is expected behavior
• Multiple offsets: Use LEAD(OrderDate, 2) to look 2 orders ahead, LEAD(OrderDate, 3) for 3, etc.
• Business insight: Increasing days between orders often indicates declining engagement - great for retention campaigns
• CTE for clarity: Use Common Table Expressions (CTEs) with WITH to make complex LEAD queries more readable

✅ Expected Result: A table showing each order with its next order date and the gap in days (e.g., CustomerID: 101, OrderDate: 2024-01-15, NextOrderDate: 2024-02-20, DaysUntilNextOrder: 36, OrderValueTrend: 'Increasing')

🔄 Connection to Day 12: While Day 12 showed window functions for ranking, LEAD adds temporal analysis capabilities. Combined with DATEDIFF from Day 12, you can now perform sophisticated customer behavior analysis!

Exercise 3: Create Running Totals for Year-to-Date Revenue

Goal: Learn to calculate running totals and cumulative sums using window functions with frame specifications.

Step-by-Step Instructions:

1. Open your SQL editor and ensure you have access to daily sales data
2. Understand running totals: A running total accumulates values from the first row to the current row, essential for YTD calculations
3. Create a basic daily sales summary:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales
   FROM Orders
   WHERE YEAR(OrderDate) = 2024
   GROUP BY OrderDate
   ORDER BY OrderDate;

4. Add a simple running total:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       SUM(SUM(TotalAmount)) OVER (ORDER BY OrderDate) AS RunningTotal
   FROM Orders
   WHERE YEAR(OrderDate) = 2024
   GROUP BY OrderDate
   ORDER BY OrderDate;

5. Make it explicit with ROWS BETWEEN:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       SUM(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RunningTotal
   FROM Orders
   WHERE YEAR(OrderDate) = 2024
   GROUP BY OrderDate
   ORDER BY OrderDate;

6. Add Year-to-Date sales by month:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS YearToDateSales
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

7. Add Quarter-to-Date calculations:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       DATEPART(QUARTER, OrderDate) AS OrderQuarter,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate), DATEPART(QUARTER, OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS QuarterToDateSales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS YearToDateSales
   FROM Orders
   GROUP BY YEAR(OrderDate), DATEPART(QUARTER, OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderQuarter, OrderMonth;

8. Add percentage of annual target:

   DECLARE @AnnualTarget DECIMAL(12,2) = 1000000;
   
   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS YearToDateSales,
       ROUND((SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) / @AnnualTarget) * 100, 2) AS PercentOfTarget
   FROM Orders
   WHERE YEAR(OrderDate) = 2024
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

9. Execute the query with F5 and verify running totals increase monotonically

💡 Tips:

• ROWS BETWEEN syntax: UNBOUNDED PRECEDING AND CURRENT ROW means "from the start to current row" - this is the running total frame
• PARTITION BY resets: Use PARTITION BY YEAR to restart running total each year, essential for YTD calculations
• Performance consideration: Running totals on very large datasets can be slow - consider indexing on date columns
• Default frame: When you omit ROWS BETWEEN with ORDER BY, SQL uses RANGE UNBOUNDED PRECEDING by default (slightly different behavior)
• Nested aggregations: Notice SUM(SUM(TotalAmount)) - outer SUM is window function, inner SUM is GROUP BY aggregate
• Keyboard shortcut: Use Ctrl+Shift+U to uppercase SQL keywords for better readability

✅ Expected Result: A table showing each month with cumulative sales growing throughout the year (e.g., Jan: $50k, Feb: $105k, Mar: $165k, etc.) with YTD and QTD totals plus percentage of annual target

🔄 Connection to Day 12: Day 12 introduced window functions with OVER clause. Running totals extend this by using frame specifications (ROWS BETWEEN) to define exactly which rows participate in the calculation - a powerful analytical tool!

Exercise 4: Calculate 7-Day Moving Average for Sales Trends

Goal: Master moving averages to smooth out daily fluctuations and identify underlying sales trends.

Step-by-Step Instructions:

1. Open your SQL editor with daily sales data available
2. Understand moving averages: A 7-day moving average calculates the average of the current day plus the previous 6 days, creating a smoothed trend line
3. Start with daily sales data:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales
   FROM Orders
   WHERE OrderDate >= DATEADD(day, -90, GETDATE())
   GROUP BY OrderDate
   ORDER BY OrderDate;

4. Add a 7-day moving average:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS SevenDayMovingAvg
   FROM Orders
   WHERE OrderDate >= DATEADD(day, -90, GETDATE())
   GROUP BY OrderDate
   ORDER BY OrderDate;

5. Add 30-day moving average for comparison:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS SevenDayMovingAvg,
       AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
       ) AS ThirtyDayMovingAvg
   FROM Orders
   WHERE OrderDate >= DATEADD(day, -90, GETDATE())
   GROUP BY OrderDate
   ORDER BY OrderDate;

6. Round the averages for readability:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       ROUND(AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS SevenDayMovingAvg,
       ROUND(AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
       ), 2) AS ThirtyDayMovingAvg
   FROM Orders
   WHERE OrderDate >= DATEADD(day, -90, GETDATE())
   GROUP BY OrderDate
   ORDER BY OrderDate;

7. Add trend indicators:

   SELECT
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       ROUND(AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS SevenDayMovingAvg,
       CASE
           WHEN SUM(TotalAmount) > AVG(SUM(TotalAmount)) OVER (
               ORDER BY OrderDate
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ) THEN 'Above Average'
           ELSE 'Below Average'
       END AS TrendIndicator,
       ROUND((SUM(TotalAmount) - AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       )) / AVG(SUM(TotalAmount)) OVER (
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) * 100, 2) AS PercentDeviation
   FROM Orders
   WHERE OrderDate >= DATEADD(day, -90, GETDATE())
   GROUP BY OrderDate
   ORDER BY OrderDate;

8. Create a product-specific moving average:

   SELECT
       ProductCategory,
       OrderDate,
       SUM(TotalAmount) AS DailySales,
       ROUND(AVG(SUM(TotalAmount)) OVER (
           PARTITION BY ProductCategory
           ORDER BY OrderDate
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 2) AS SevenDayMovingAvg
   FROM Orders
   WHERE OrderDate >= DATEADD(day, -90, GETDATE())
   GROUP BY ProductCategory, OrderDate
   ORDER BY ProductCategory, OrderDate;

9. Execute the query using F5 and observe how moving averages smooth daily volatility

💡 Tips:

• Frame specification: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates a 7-day window (6 previous + current = 7 total days)
• Edge cases: First 6 days will have fewer than 7 days in the average - this is normal and expected
• Smoothing effect: Larger windows (30-day) are smoother but slower to react; smaller windows (7-day) are more responsive
• Seasonal patterns: Moving averages are excellent for identifying seasonal trends in sales data
• Multiple partitions: Add PARTITION BY to calculate separate moving averages for each product, region, or category
• Comparison insight: When 7-day MA crosses above 30-day MA, it often signals an upward trend (and vice versa)

✅ Expected Result: A table showing daily sales with smoothed 7-day and 30-day moving averages (e.g., 2024-11-15, DailySales: $12,500, 7-Day Avg: $11,200, 30-Day Avg: $10,800, TrendIndicator: 'Above Average')

🔄 Connection to Day 12: Building on Day 12's window functions, moving averages use the same OVER clause but with specific frame specifications. This transforms basic aggregations into powerful trend analysis tools used in financial and sales forecasting!

Exercise 5: Combine LAG, LEAD, and Running Totals for Comprehensive Analysis

Goal: Master combining multiple window functions in a single query to create a comprehensive sales performance dashboard.

Step-by-Step Instructions:

1. Open your SQL editor ready to build a complex analytical query
2. Understand the goal: Create a single query showing month-over-month comparisons, running totals, moving averages, and forward-looking predictions
3. Start with monthly sales aggregation:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

4. Add LAG for previous month comparison:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS MonthOverMonthChange
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

5. Add LEAD for next month forecast context:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
       LEAD(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS NextMonthSales,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS MonthOverMonthChange
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

6. Add running total for YTD:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
       LEAD(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS NextMonthSales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS UNBOUNDED PRECEDING
       ) AS YearToDateSales,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS MonthOverMonthChange
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

7. Add 3-month moving average:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
       LEAD(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS NextMonthSales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS UNBOUNDED PRECEDING
       ) AS YearToDateSales,
       ROUND(AVG(SUM(TotalAmount)) OVER (
           ORDER BY YEAR(OrderDate), MONTH(OrderDate)
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS ThreeMonthMovingAvg,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS MonthOverMonthChange
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

8. Add performance indicators and percentages:

   SELECT
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       SUM(TotalAmount) AS MonthlySales,
       LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
       LEAD(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS NextMonthSales,
       SUM(SUM(TotalAmount)) OVER (
           PARTITION BY YEAR(OrderDate)
           ORDER BY MONTH(OrderDate)
           ROWS UNBOUNDED PRECEDING
       ) AS YearToDateSales,
       ROUND(AVG(SUM(TotalAmount)) OVER (
           ORDER BY YEAR(OrderDate), MONTH(OrderDate)
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS ThreeMonthMovingAvg,
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS MonthOverMonthChange,
       CASE
           WHEN LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) IS NULL THEN NULL
           ELSE ROUND(((SUM(TotalAmount) - LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)))
                       / LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate))) * 100, 2)
       END AS PercentChange,
       CASE
           WHEN SUM(TotalAmount) > AVG(SUM(TotalAmount)) OVER (
               ORDER BY YEAR(OrderDate), MONTH(OrderDate)
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ) THEN 'Above Trend'
           WHEN SUM(TotalAmount) < AVG(SUM(TotalAmount)) OVER (
               ORDER BY YEAR(OrderDate), MONTH(OrderDate)
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ) THEN 'Below Trend'
           ELSE 'On Trend'
       END AS TrendStatus
   FROM Orders
   GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   ORDER BY OrderYear, OrderMonth;

9. Wrap in a CTE for better readability:

   WITH MonthlySalesAnalysis AS (
       SELECT
           YEAR(OrderDate) AS OrderYear,
           MONTH(OrderDate) AS OrderMonth,
           SUM(TotalAmount) AS MonthlySales,
           LAG(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
           LEAD(SUM(TotalAmount), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS NextMonthSales,
           SUM(SUM(TotalAmount)) OVER (
               PARTITION BY YEAR(OrderDate)
               ORDER BY MONTH(OrderDate)
               ROWS UNBOUNDED PRECEDING
           ) AS YearToDateSales,
           ROUND(AVG(SUM(TotalAmount)) OVER (
               ORDER BY YEAR(OrderDate), MONTH(OrderDate)
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 2) AS ThreeMonthMovingAvg
       FROM Orders
       GROUP BY YEAR(OrderDate), MONTH(OrderDate)
   )
   SELECT
       OrderYear,
       OrderMonth,
       MonthlySales,
       PrevMonthSales,
       MonthlySales - PrevMonthSales AS MonthOverMonthChange,
       CASE
           WHEN PrevMonthSales IS NULL OR PrevMonthSales = 0 THEN NULL
           ELSE ROUND(((MonthlySales - PrevMonthSales) / PrevMonthSales) * 100, 2)
       END AS PercentChange,
       YearToDateSales,
       ThreeMonthMovingAvg,
       CASE
           WHEN MonthlySales > ThreeMonthMovingAvg THEN 'Above Trend'
           WHEN MonthlySales < ThreeMonthMovingAvg THEN 'Below Trend'
           ELSE 'On Trend'
       END AS TrendStatus,
       NextMonthSales
   FROM MonthlySalesAnalysis
   ORDER BY OrderYear, OrderMonth;

10. Execute with F5 and analyze the comprehensive view of sales performance

💡 Tips:

• CTE organization: Use CTEs to separate window function calculations from final formatting - improves readability and debugging
• Reusing window specs: In SQL Server 2012+, use WINDOW clause to define window specifications once and reuse them (less verbose)
• Performance optimization: Multiple window functions in one query are more efficient than multiple separate queries
• NULL handling: First and last rows will have NULLs for LAG and LEAD respectively - handle these with CASE or COALESCE
• Business value: This single query provides a complete time-series analysis dashboard that would require multiple complex self-joins in older SQL
• Keyboard shortcut: Use Alt+F1 to get quick table information while building your query

✅ Expected Result: A comprehensive monthly dashboard showing: MonthlySales, PrevMonthSales, NextMonthSales, MonthOverMonthChange, PercentChange, YearToDateSales, ThreeMonthMovingAvg, and TrendStatus for complete sales performance visibility

🔄 Connection to Day 12: This exercise synthesizes everything from Day 12 (basic window functions) and Day 14 (LAG, LEAD, running totals, moving averages) into a single powerful query. This demonstrates how window functions eliminate the need for complex self-joins and correlated subqueries, making sophisticated analytics accessible and maintainable!