CTE Rolling 3 Mo Avg

Question

working on an assigment, below is the ask, she has directed us to use a CTE

Write SQL query code used to explore the database tables and write a query that retrieves finance amounts from "FactFinance" in the "AdventureWorksDW2016CTP3" database and returns those amounts, organized by month, and showing a 3-month rolling average

SELECT DateKey,
  month(date) as [Month],
  year(date) as [Year],
SUM ( ALL Amount) OVER (PARTITION BY Date ORDER BY Date ASC) AS Amount
FROM FactFinance

SELECT
    YEAR(Date) AS Year,
    MONTH(Date) AS Month,
    SUM(Amount) AS Amount
FROM FactFinance
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Year, Month;


WITH CTE AS (
SELECT 
    DateKey AS Month, 
      AVG(Amount) AS AvgAmt
  from FactFinance
  group by DateKey
  )
  SELECT 
    Month,
    AvgAmt
FROM CTE

GO

oUTPUT for last query Needing 3 month rolling average

Would you please provide sample input and desired output too? And also please tell us what is the problem or error? — Vahid Farahmandian, Aug 11 '19 at 06:36

score 0 · Answer 1 · answered Aug 11 '19 at 11:57

First, you should know the right way to answer this. Assuming you have data for all three months, then:

SELECT YEAR(Date) AS Year,
       MONTH(Date) AS Month,
       SUM(Amount) AS Amount,
       AVG(SUM(Amount)) OVER (ORDER BY MIN(DATE)
                              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as rolling_3month_avg
FROM FactFinance
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Year, Month;

If I were told to use a CTE for this, I might be tempted to do:

WITH unnecessary_cte as (
      SELECT YEAR(Date) AS Year,
             MONTH(Date) AS Month,
             SUM(Amount) AS Amount,
             AVG(SUM(Amount)) OVER (ORDER BY MIN(DATE)
                                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as rolling_3month_avg
      FROM FactFinance
      GROUP BY YEAR(Date), MONTH(Date)
     )
SELECT *
FROM unnecessary_cte
ORDER BY YEAR, MONTH;

However, we can try to read your instructor's mind and speculate that she wants you to write something like this:

WITH ym as (
      SELECT YEAR(Date) AS Year,
             MONTH(Date) AS Month,
             SUM(Amount) AS Amount
      FROM FactFinance
      GROUP BY YEAR(Date), MONTH(Date)
     )
SELECT ym.*,
       (SELECT AVG(Amount)
        FROM ym ym2
        WHERE 12 * ym2.year + ym2.month
                  BETWEEN 12 * ym.year + ym.month - 2 AND
                          12 * ym.year + ym.month
       ) as rolling_3month_avg
FROM ym
ORDER BY YEAR, MONTH;

Thank you so much for your help, I really appreciate it, i was pretty lost on this one! — Alicia Chavez, Aug 11 '19 at 19:11
I think that the assumption of having data for all months is generally a bad idea. You'd better stick with the latest version. — Manos Anastasiadis, Aug 12 '19 at 07:22

CTE Rolling 3 Mo Avg

1 Answers1