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.
Deze oefening maakt deel uit van de cursus
PostgreSQL Summary Stats and Window Functions
Oefeninstructies
- Return the year, country, medals, and the maximum medals earned so far for each country, ordered by year in ascending order.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
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;