Get startedGet started for free

Future gold medalists

Fetching functions allow you to get values from different parts of the table into one row. If you have time-ordered data, you can "peek into the future" with the LEAD fetching function. This is especially useful if you want to compare a current value to a future value.

This exercise is part of the course

PostgreSQL Summary Stats and Window Functions

View Course

Exercise instructions

  • For each year, fetch the current gold medalist and the gold medalist 3 competitions ahead of the current row.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

WITH Discus_Medalists AS (
  SELECT DISTINCT
    Year,
    Athlete
  FROM Summer_Medals
  WHERE Medal = 'Gold'
    AND Event = 'Discus Throw'
    AND Gender = 'Women'
    AND Year >= 2000)

SELECT
  -- For each year, fetch the current and future medalists
  ___,
  ___,
  ___ OVER (ORDER BY ___ ASC) AS Future_Champion
FROM Discus_Medalists
ORDER BY Year ASC;
Edit and Run Code