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.
Questo esercizio fa parte del corso
PostgreSQL Summary Stats and Window Functions
Istruzioni dell'esercizio
- Return the year, country, medals, and the maximum medals earned so far for each country, ordered by year in ascending order.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
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;