Get startedGet started for free

Pivoting with ranking

You want to produce an easy scannable table of the rankings of the three most populous EU countries by how many gold medals they've earned in the 2004 through 2012 Olympic games. The table needs to be in this format:

| Country | 2004 | 2008 | 2012 |
|---------|------|------|------|
| FRA     | ...  | ...  | ...  |
| GBR     | ...  | ...  | ...  |
| GER     | ...  | ...  | ...  |

You'll need to count the gold medals each country has earned, produce the ranks of each country by medals earned, then pivot the table to this shape.

This exercise is part of the course

PostgreSQL Summary Stats and Window Functions

View Course

Hands-on interactive exercise

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

-- Count the gold medals per country and year
SELECT
  ___,
  ___,
  ___ AS Awards
FROM Summer_Medals
WHERE
  Country IN ('FRA', 'GBR', 'GER')
  AND Year IN (2004, 2008, 2012)
  AND Medal = 'Gold'
GROUP BY ___, ___
ORDER BY Country ASC, Year ASC
Edit and Run Code