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.
Bu egzersiz
PostgreSQL Summary Stats and Window Functions
kursunun bir parçasıdırEgzersiz talimatları
- Return the year, country, medals, and the maximum medals earned so far for each country, ordered by year in ascending order.
Uygulamalı interaktif egzersiz
Bu örnek kodu tamamlayarak bu egzersizi bitirin.
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;