5.23

Consider the nyse relation of Exercise 5.9. For each month of each year, show the total monthly dollar volume and the average monthly dollar volume for that month and the two prior months. (Hint: First write a query to find the total dollar volume for each month of each year. Once that is right, put that in the from clause of the outer query that solves the full problem. That outer query will need windowing. The subquery does not.)

---

WITH nyse_monthly(year,month,total_monthly_dollar_volume) AS (
    SELECT year,month,SUM(dollar_volume) AS total_monthly_dollar_volume
    FROM nyse
    GROUP BY year,month
) 
SELECT year,month,total_monthly_dollar_volume, 
    AVG(total_monthly_dollar_volume) OVER (ORDER BY (year,month) ASC ROWS 2 PRECEDING)
FROM nyse_monthly;