Get startedGet started for free

Moving average of Russian medals

Using frames with aggregate window functions allow you to calculate many common metrics, including moving averages and totals. These metrics track the change in performance over time.

This exercise is part of the course

PostgreSQL Summary Stats and Window Functions

View Course

Exercise instructions

  • Calculate the 3-year moving average of medals earned.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

WITH Russian_Medals AS (
  SELECT
    Year, COUNT(*) AS Medals
  FROM Summer_Medals
  WHERE
    Country = 'RUS'
    AND Medal = 'Gold'
    AND Year >= 1980
  GROUP BY Year)

SELECT
  Year, Medals,
  --- Calculate the 3-year moving average of medals earned
  ___ OVER
    (ORDER BY Year ASC
     ROWS BETWEEN
     ___ AND ___) AS Medals_MA
FROM Russian_Medals
ORDER BY Year ASC;
Edit and Run Code