Moving maximum of Chinese athletes' medals
Frames allow you to "peek" forwards or backward without first using the relative fetching functions, LAG and LEAD, to fetch previous rows' values into the current row.
Deze oefening maakt deel uit van de cursus
PostgreSQL Summary Stats and Window Functions
Oefeninstructies
- Return the athletes, medals earned, and the maximum medals earned, comparing only the last two and current athletes, ordering by athletes' names in alphabetical order.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
WITH Chinese_Medals AS (
SELECT
Athlete, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
Country = 'CHN' AND Medal = 'Gold'
AND Year >= 2000
GROUP BY Athlete)
SELECT
-- Select the athletes and the medals they've earned
___,
___,
-- Get the max of the last two and current rows' medals
___ OVER (ORDER BY ___ ASC
ROWS BETWEEN ___
AND ___) AS Max_Medals
FROM Chinese_Medals
ORDER BY Athlete ASC;