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
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