Moving maximum of Chinese athletes' medals
Frames allow you to "peek" forwards or backward without first using the relative fetching functions, LAG
and LEAD
, to fetch previous rows' values into the current row.
This exercise is part of the course
PostgreSQL Summary Stats and Window Functions
Exercise instructions
- Return the athletes, medals earned, and the maximum medals earned, comparing only the last two and current athletes, ordering by athletes' names in alphabetical order.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH Chinese_Medals AS (
SELECT
Athlete, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
Country = 'CHN' AND Medal = 'Gold'
AND Year >= 2000
GROUP BY Athlete)
SELECT
-- Select the athletes and the medals they've earned
___,
___,
-- Get the max of the last two and current rows' medals
___ OVER (ORDER BY ___ ASC
ROWS BETWEEN ___
AND ___) AS Max_Medals
FROM Chinese_Medals
ORDER BY Athlete ASC;