Reigning champions by gender
You've already fetched the previous year's champion for one event. However, if you have multiple events, genders, or other metrics as columns, you'll need to split your table into partitions to avoid having a champion from one event or gender appear as the previous champion of another event or gender.
Deze oefening maakt deel uit van de cursus
PostgreSQL Summary Stats and Window Functions
Oefeninstructies
- Return the previous champions of each year's event by gender.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
WITH Tennis_Gold AS (
SELECT DISTINCT
Gender, Year, Country
FROM Summer_Medals
WHERE
Year >= 2000 AND
Event = 'Javelin Throw' AND
Medal = 'Gold')
SELECT
Gender, Year,
Country AS Champion,
-- Fetch the previous year's champion by gender
___ OVER (___
ORDER BY ___ ASC) AS Last_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;