Maximum country medals by year
Getting the maximum of a country's earned medals so far helps you determine whether a country has broken its medals record by comparing the current year's earned medals and the maximum so far.
This exercise is part of the course
PostgreSQL Summary Stats and Window Functions
Exercise instructions
- Return the year, country, medals, and the maximum medals earned so far for each country, ordered by year in ascending order.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH Country_Medals AS (
SELECT
Year, Country, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
Country IN ('CHN', 'KOR', 'JPN')
AND Medal = 'Gold' AND Year >= 2000
GROUP BY Year, Country)
SELECT
-- Return the max medals earned so far per country
___,
___,
___,
___ OVER (PARTITION BY ___
ORDER BY ___ ASC) AS Max_Medals
FROM Country_Medals
ORDER BY Country ASC, Year ASC;