Get startedGet started for free

Moving maximum of Scandinavian athletes' medals

Frames allow you to restrict the rows passed as input to your window function to a sliding window for you to define the start and finish.

Adding a frame to your window function allows you to calculate "moving" metrics, inputs of which slide from row to row.

This exercise is part of the course

PostgreSQL Summary Stats and Window Functions

View Course

Exercise instructions

  • Return the year, medals earned, and the maximum medals earned, comparing only the current year and the next year.

Hands-on interactive exercise

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

WITH Scandinavian_Medals AS (
  SELECT
    Year, COUNT(*) AS Medals
  FROM Summer_Medals
  WHERE
    Country IN ('DEN', 'NOR', 'FIN', 'SWE', 'ISL')
    AND Medal = 'Gold'
  GROUP BY Year)

SELECT
  -- Select each year's medals
  ___,
  ___,
  -- Get the max of the current and next years'  medals
  ___ OVER (ORDER BY ___ ASC
             ROWS BETWEEN ___
             AND ___) AS Max_Medals
FROM Scandinavian_Medals
ORDER BY Year ASC;
Edit and Run Code