SELECT Year, Event, Country FROM Summer_Medals WHERE Medal = 'Gold';

SELECT Year, Event, Country, ROW_NUMBER() OVER () AS Row_N FROM Summer_Medals WHERE Medal = 'Gold';

SELECT Year, Event, Country, ROW_NUMBER() OVER () AS Row_N FROM Summer_Medals WHERE Medal = 'Gold';

SELECT *, -- Assign numbers to each row ROW_NUMBER() OVER() AS Row_N FROM Summer_Medals ORDER BY Row_N ASC;

SELECT Year, -- Assign numbers to each year ROW_NUMBER() OVER() AS Row_N FROM ( SELECT DISTINCT (year) FROM Summer_Medals ORDER BY Year ASC ) AS Years ORDER BY Year ASC;

SELECT Year, Event, Country, ROW_NUMBER() OVER () AS Row_N FROM Summer_Medals WHERE Medal = 'Gold';

SELECT Year, Event, Country, ROW_NUMBER() OVER (ORDER BY Year DESC) AS Row_N FROM Summer_Medals WHERE Medal = 'Gold';

SELECT Year, Event, Country, ROW_NUMBER() OVER (ORDER BY Year DESC, Event ASC) AS Row_N FROM Summer_Medals WHERE Medal = 'Gold';

SELECT Year, Event, Country, ROW_NUMBER() OVER (ORDER BY Year DESC, Event ASC) AS Row_N FROM Summer_Medals WHERE Medal = 'Gold' ORDER BY Country ASC, Row_N ASC;

SELECT Year, Country AS Champion FROM Summer_Medals WHERE Year IN (1996, 2000, 2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event = 'Discus Throw';

WITH Discus_Gold AS ( SELECT Year, Country AS Champion FROM Summer_Medals WHERE Year IN (1996, 2000, 2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event = 'Discus Throw') SELECT Year, Champion, LAG(Champion, 1) OVER (ORDER BY Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Year ASC;

SELECT Year, -- Assign the lowest numbers to the most recent years ROW_NUMBER() OVER (ORDER BY year DESC) AS Row_N FROM ( SELECT DISTINCT Year FROM Summer_Medals ) AS Years ORDER BY Year;

SELECT -- Count the number of medals each athlete has earned athlete, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Athlete ORDER BY Medals DESC;

WITH Athlete_Medals AS ( SELECT -- Count the number of medals each athlete has earned Athlete, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Athlete) SELECT -- Number each athlete by how many medals they've earned Athlete, ROW_NUMBER() OVER (ORDER BY Medals DESC) AS Row_N FROM Athlete_Medals ORDER BY Medals DESC;

SELECT -- Return each year's champions' countries year, country AS champion FROM Summer_Medals WHERE Discipline = 'Weightlifting' AND Event = '69KG' AND Gender = 'Men' AND Medal = 'Gold';

WITH Weightlifting_Gold AS ( SELECT -- Return each year's champions' countries Year, Country AS champion FROM Summer_Medals WHERE Discipline = 'Weightlifting' AND Event = '69KG' AND Gender = 'Men' AND Medal = 'Gold') SELECT Year, Champion, -- Fetch the previous year's champion LAG(Champion, 1) OVER (ORDER BY Year ASC) AS Last_Champion FROM Weightlifting_Gold ORDER BY Year ASC;

WITH Discus_Gold AS ( SELECT Year, Event, Country AS Champion FROM Summer_Medals WHERE Year IN (2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event IN ('Discus Throw', 'Triple Jump') AND Gender = 'Men') SELECT Year, Event, Champion, LAG(Champion) OVER (ORDER BY Event ASC, Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Event ASC, Year ASC;

WITH Discus_Gold AS ( SELECT Year, Event, Country AS Champion FROM Summer_Medals WHERE Year IN (2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event IN ('Discus Throw', 'Triple Jump') AND Gender = 'Men') SELECT Year, Event, Champion, LAG(Champion) OVER (PARTITION BY Event ORDER BY Event ASC, Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Event ASC, Year ASC;

WITH Country_Gold AS ( SELECT DISTINCT Year, Country, Event FROM Summer_Medals WHERE Year IN (2008, 2012) AND Country IN ('CHN', 'JPN') AND Gender = 'Women' AND Medal = 'Gold') SELECT Year, Country, Event, ROW_NUMBER() OVER (PARTITION BY Year, Country) FROM Country_Gold;

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 LAG(Country) OVER (PARTITION BY Gender ORDER BY year ASC) AS Last_Champion FROM Tennis_Gold ORDER BY Gender ASC, Year ASC;

WITH Athletics_Gold AS ( SELECT DISTINCT Gender, Year, Event, Country FROM Summer_Medals WHERE Year >= 2000 AND Discipline = 'Athletics' AND Event IN ('100M', '10000M') AND Medal = 'Gold') SELECT Gender, Year, Event, Country AS Champion, -- Fetch the previous year's champion by gender and event LAG(Country) OVER (PARTITION BY gender, event ORDER BY Year ASC) AS Last_Champion FROM Athletics_Gold ORDER BY Event ASC, Gender ASC, Year ASC;

WITH Hosts AS ( SELECT DISTINCT Year, City FROM Summer_Medals) SELECT Year, City, LEAD(City, 1) OVER (ORDER BY Year ASC) AS Next_City, LEAD(City, 2) OVER (ORDER BY Year ASC) AS After_Next_City FROM Hosts ORDER BY Year ASC;

WITH Hosts AS ( SELECT DISTINCT Year, City FROM Summer_Medals) SELECT Year, City, FIRST_VALUE(City) OVER (ORDER BY Year ASC) AS First_City, LAST_VALUE(City) OVER ( ORDER BY Year ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Last_City FROM Hosts ORDER BY Year ASC;

WITH Discus_Gold AS ( SELECT Year, Event, Country AS Champion FROM Summer_Medals WHERE Year IN (2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event IN ('Discus Throw', 'Triple Jump') AND Gender = 'Men') SELECT Year, Event, Champion, LEAD(Champion, 1) OVER (ORDER BY Event ASC, Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Event ASC, Year ASC;

WITH Discus_Gold AS ( SELECT Year, Event, Country AS Champion FROM Summer_Medals WHERE Year IN (2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event IN ('Discus Throw', 'Triple Jump') AND Gender = 'Men') SELECT Year, Event, Champion, LEAD(Champion, 1) OVER (PARTITION BY Event ORDER BY Event ASC, Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Event ASC, Year ASC;

WITH Discus_Gold AS ( SELECT Year, Event, Country AS Champion FROM Summer_Medals WHERE Year IN (2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event IN ('Discus Throw', 'Triple Jump') AND Gender = 'Men') SELECT Year, Event, Champion, FIRST_VALUE(Champion) OVER (ORDER BY Event ASC, Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Event ASC, Year ASC;

WITH Discus_Gold AS ( SELECT Year, Event, Country AS Champion FROM Summer_Medals WHERE Year IN (2004, 2008, 2012) AND Gender = 'Men' AND Medal = 'Gold' AND Event IN ('Discus Throw', 'Triple Jump') AND Gender = 'Men') SELECT Year, Event, Champion, FIRST_VALUE(Champion) OVER (PARTITION BY Event ORDER BY Event ASC, Year ASC) AS Last_Champion FROM Discus_Gold ORDER BY Event ASC, Year ASC;

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 Year, Athlete, LEAD(Athlete, 3) OVER (ORDER BY year ASC) AS Future_Champion FROM Discus_Medalists ORDER BY Year ASC;

WITH All_Male_Medalists AS ( SELECT DISTINCT Athlete FROM Summer_Medals WHERE Medal = 'Gold' AND Gender = 'Men') SELECT -- Fetch all athletes and the first athlete alphabetically Athlete, FIRST_VALUE(Athlete) OVER ( ORDER BY Athlete ASC ) AS First_Athlete FROM All_Male_Medalists;

WITH Hosts AS ( SELECT DISTINCT Year, City FROM Summer_Medals) SELECT Year, City, -- Get the last city in which the Olympic games were held LAST_VALUE(City) OVER ( ORDER BY Year ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Last_City FROM Hosts ORDER BY Year ASC;

SELECT Country, COUNT(DISTINCT Year) AS Games FROM Summer_Medals WHERE Country IN ('GBR', 'DEN', 'FRA', 'ITA', 'AUT', 'BEL', 'NOR', 'POL', 'ESP') GROUP BY Country ORDER BY Games DESC;

WITH Country_Games AS ( SELECT Country, COUNT(DISTINCT Year) AS Games FROM Summer_Medals WHERE Country IN ('GBR', 'DEN', 'FRA', 'ITA', 'AUT', 'BEL', 'NOR', 'POL', 'ESP') GROUP BY Country ORDER BY Games DESC) SELECT Country, Games, ROW_NUMBER() OVER (ORDER BY Games DESC) AS Row_N FROM Country_Games ORDER BY Games DESC, Country ASC;

WITH Country_Games AS ( SELECT Country, COUNT(DISTINCT Year) AS Games FROM Summer_Medals WHERE Country IN ('GBR', 'DEN', 'FRA', 'ITA', 'AUT', 'BEL', 'NOR', 'POL', 'ESP') GROUP BY Country ORDER BY Games DESC) SELECT Country, Games, ROW_NUMBER() OVER (ORDER BY Games DESC) AS Row_N, RANK() OVER (ORDER BY Games DESC) AS Rank_N FROM Country_Games ORDER BY Games DESC, Country ASC;

WITH Country_Games AS ( SELECT Country, COUNT(DISTINCT Year) AS Games FROM Summer_Medals WHERE Country IN ('GBR', 'DEN', 'FRA', 'ITA', 'AUT', 'BEL', 'NOR', 'POL', 'ESP') GROUP BY Country ORDER BY Games DESC) SELECT Country, Games, ROW_NUMBER() OVER (ORDER BY Games DESC) AS Row_N, RANK() OVER (ORDER BY Games DESC) AS Rank_N, DENSE_RANK() OVER (ORDER BY Games DESC) AS Dense_Rank_N FROM Country_Games ORDER BY Games DESC, Country ASC;

SELECT Country, Athlete, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('CHN', 'RUS') AND Year = 2012 GROUP BY Country, Athlete HAVING COUNT(*) > 1 ORDER BY Country ASC, Medals DESC;

WITH Country_Medals AS ( SELECT Country, Athlete, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('CHN', 'RUS') AND Year = 2012 GROUP BY Country, Athlete HAVING COUNT(*) > 1 ORDER BY Country ASC, Medals DESC ) SELECT Country, Athlete, Medals, DENSE_RANK() OVER (ORDER BY Medals DESC) AS Rank_N FROM Country_Medals ORDER BY Country ASC, Medals DESC;

WITH Country_Medals AS ( SELECT Country, Athlete, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('CHN', 'RUS') AND Year = 2012 GROUP BY Country, Athlete HAVING COUNT(*) > 1 ORDER BY Country ASC, Medals DESC ) SELECT Country, Athlete, DENSE_RANK() OVER (PARTITION BY Country ORDER BY Medals DESC) AS Rank_N FROM Country_Medals ORDER BY Country ASC, Medals DESC;

WITH Athlete_Medals AS ( SELECT Athlete, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Athlete) SELECT Athlete, Medals, -- Rank athletes by the medals they've won RANK() OVER (ORDER BY Medals DESC) AS Rank_N FROM Athlete_Medals ORDER BY Medals DESC;

WITH Athlete_Medals AS ( SELECT Country, Athlete, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('JPN', 'KOR') AND Year >= 2000 GROUP BY Country, Athlete HAVING COUNT(*) > 1) SELECT Country, -- Rank athletes in each country by the medals they've won Athlete, DENSE_RANK() OVER (PARTITION BY Country ORDER BY Medals DESC) AS Rank_N FROM Athlete_Medals ORDER BY Country ASC, RANK_N ASC;

SELECT DISTINCT Discipline FROM Summer_Medals;

WITH Disciplines AS ( SELECT DISTINCT Discipline FROM Summer_Medals) SELECT Discipline, NTILE(15) OVER () AS Page From Disciplines ORDER BY Page ASC;

WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Country) SELECT Country, Medals, NTILE(3) OVER (ORDER BY Medals DESC) AS Third FROM Country_Medals;

WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Country), Thirds AS ( SELECT Country, Medals, NTILE(3) OVER (ORDER BY Medals DESC) AS Third FROM Country_Medals) SELECT Third, ROUND(AVG(Medals), 2) AS Avg_Medals FROM Thirds GROUP BY Third ORDER BY Third ASC;

WITH Events AS ( SELECT DISTINCT Event FROM Summer_Medals) SELECT --- Split up the distinct events into 111 unique groups DISTINCT event, NTILE(111) OVER (ORDER BY event ASC) AS Page FROM Events ORDER BY Event ASC;

WITH Athlete_Medals AS ( SELECT Athlete, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Athlete HAVING COUNT(*) > 1) SELECT Athlete, Medals, -- Split athletes into thirds by their earned medals NTILE(3) OVER (ORDER BY Medals DESC) AS Third FROM Athlete_Medals ORDER BY Medals DESC, Athlete ASC;

WITH Athlete_Medals AS ( SELECT Athlete, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Athlete HAVING COUNT(*) > 1), Thirds AS ( SELECT Athlete, Medals, NTILE(3) OVER (ORDER BY Medals DESC) AS Third FROM Athlete_Medals) SELECT -- Get the average medals earned in each third Third, AVG(Medals) AS Avg_Medals FROM Thirds GROUP BY Third ORDER BY Third ASC;

SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'BRA' AND Medal = 'Gold' AND Year >= 1992 GROUP BY Year ORDER BY Year ASC;

WITH Brazil_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'BRA' AND Medal = 'Gold' AND Year >= 1992 GROUP BY Year ORDER BY Year ASC) SELECT MAX(Medals) AS Max_Medals FROM Brazil_Medals;

WITH Brazil_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'BRA' AND Medal = 'Gold' AND Year >= 1992 GROUP BY Year ORDER BY Year ASC) SELECT SUM(Medals) AS Total_Medals FROM Brazil_Medals;

WITH Brazil_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'BRA' AND Medal = 'Gold' AND Year >= 1992 GROUP BY Year ORDER BY Year ASC) SELECT Year, Medals, MAX(Medals) OVER (ORDER BY Year ASC) AS Max_Medals FROM Brazil_Medals;

WITH Brazil_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'BRA' AND Medal = 'Gold' AND Year >= 1992 GROUP BY Year ORDER BY Year ASC) SELECT Year, Medals, SUM(Medals) OVER (ORDER BY Year ASC) AS Medals_RT FROM Brazil_Medals;

WITH Medals AS ( SELECT Year, Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('BRA', 'CUB') AND Medal = 'Gold' AND Year >= 2004 GROUP BY Year, Country ORDER BY Country ASC, Year ASC) SELECT Year, Country, Medals, SUM(Medals) OVER (ORDER BY Country ASC, Year ASC) FROM Medals;

WITH Medals AS ( SELECT Year, Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('BRA', 'CUB') AND Medal = 'Gold' AND Year >= 2004 GROUP BY Year, Country ORDER BY Country ASC, Year ASC) SELECT Year, Country, Medals, SUM(Medals) OVER (PARTITION BY Country ORDER BY Country ASC, Year ASC) FROM Medals;

WITH Athlete_Medals AS ( SELECT Athlete, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'USA' AND Medal = 'Gold' AND Year >= 2000 GROUP BY Athlete) SELECT -- Calculate the running total of athlete medals Athlete, Medals, SUM(Medals) OVER (ORDER BY Athlete ASC) AS Medals_RT FROM Athlete_Medals ORDER BY Athlete ASC;

WITH Country_Medals AS ( SELECT Year, Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('CHN', 'KOR', 'JPN') AND Medal = 'Gold' AND Year >= 2000 GROUP BY Year, Country) SELECT -- Return the max medals earned so far per country Year, Country, Medals, MAX(Medals) OVER (PARTITION BY Country ORDER BY Year ASC) AS Max_Medals FROM Country_Medals ORDER BY Country ASC, Year ASC;

WITH France_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'FRA' AND Medal = 'Gold' AND Year >= 2000 GROUP BY Year) SELECT Year, Medals, MIN(Medals) OVER (ORDER BY Year ASC) AS Min_Medals FROM France_Medals ORDER BY Year ASC;

LAST_VALUE(City) OVER ( ORDER BY Year ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Last_City

SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'RUS' AND Medal = 'Gold' GROUP BY Year ORDER BY Year ASC;

WITH Russia_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'RUS' AND Medal = 'Gold' GROUP BY Year ORDER BY Year ASC) SELECT Year, Medals, MAX(Medals) OVER (ORDER BY Year ASC) AS Max_Medals FROM Russia_Medals ORDER BY Year ASC;

WITH Russia_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'RUS' AND Medal = 'Gold' GROUP BY Year ORDER BY Year ASC) SELECT Year, Medals, MAX(Medals) OVER (ORDER BY Year ASC) AS Max_Medals, MAX(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Max_Medals_Last FROM Russia_Medals ORDER BY Year ASC;

WITH Russia_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'RUS' AND Medal = 'Gold' GROUP BY Year ORDER BY Year ASC) SELECT Year, Medals, MAX(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS Max_Medals_Next FROM Russia_Medals ORDER BY Year ASC;

WITH Scandinavian_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country IN ('DEN', 'NOR', 'FIN', 'SWE', 'ISL') AND Medal = 'Gold' GROUP BY Year) SELECT -- Select each year's medals Year, Medals, -- Get the max of the current and next years' medals MAX(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS Max_Medals FROM Scandinavian_Medals ORDER BY Year ASC;

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 Athlete, Medals, -- Get the max of the last two and current rows' medals MAX(Medals) OVER (ORDER BY Athlete ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Max_Medals FROM Chinese_Medals ORDER BY Athlete ASC;

SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'USA' AND Medal = 'Gold' AND Year >= 1980 GROUP BY Year ORDER BY Year ASC;

WITH US_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'USA' AND Medal = 'Gold' AND Year >= 1980 GROUP BY Year ORDER BY Year ASC ) SELECT Year, Medals, AVG(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Medals_MA FROM US_Medals ORDER BY Year ASC;

WITH US_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'USA' AND Medal = 'Gold' AND Year >= 1980 GROUP BY Year ORDER BY Year ASC ) SELECT Year, Medals, SUM(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Medals_MA FROM US_Medals ORDER BY Year ASC;

WITH Russian_Medals AS ( SELECT Year, COUNT(*) AS Medals FROM Summer_Medals WHERE Country = 'RUS' AND Medal = 'Gold' AND Year >= 1980 GROUP BY Year) SELECT Year, Medals, --- Calculate the 3-year moving average of medals earned AVG(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Medals_MA FROM Russian_Medals ORDER BY Year ASC;

WITH Country_Medals AS ( SELECT Year, Country, COUNT(*) AS Medals FROM Summer_Medals GROUP BY Year, Country) SELECT Year, Country, Medals, -- Calculate each country's 3-game moving total SUM(Medals) OVER (PARTITION BY Country ORDER BY Year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Medals_MA FROM Country_Medals ORDER BY Country ASC, Year ASC;

CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM CROSSTAB($$ source_sql TEXT $$) AS ct (column_1 DATA_TYPE_1, column_2 DATA_TYPE_2, ..., column_n DATA_TYPE_N);

-- Before SELECT Country, Year, COUNT(*) :: INTEGER AS Awards FROM Summer_Medals WHERE Country IN ('CHN', 'RUS', 'USA') AND Year IN (2008, 2012) AND Medal = 'Gold' GROUP BY Country, Year ORDER BY Country ASC, Year ASC;

-- After CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM CROSSTAB($$ SELECT Country, Year, COUNT(*) :: INTEGER AS Awards FROM Summer_Medals WHERE Country IN ('CHN', 'RUS', 'USA') AND Year IN (2008, 2012) AND Medal = 'Gold' GROUP BY Country, Year ORDER BY Country ASC, Year ASC; $$) AS ct (Country VARCHAR, "2008" INTEGER, "2012" INTEGER) ORDER BY Country ASC;

WITH Country_Awards AS ( SELECT Country, Year, COUNT(*) AS Awards FROM Summer_Medals WHERE Country IN ('CHN', 'RUS', 'USA') AND Year IN (2004, 2008, 2012) AND Medal = 'Gold' AND Sport = 'Gymnastics' GROUP BY Country, Year ORDER BY Country ASC, Year ASC) SELECT Country, Year, RANK() OVER (PARTITION BY Year ORDER BY Awards DESC) :: INTEGER AS rank FROM Country_Awards ORDER BY Country ASC, Year ASC;

CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM CROSSTAB($$ WITH Country_Awards AS ( SELECT Country, Year, COUNT(*) AS Awards FROM Summer_Medals WHERE Country IN ('CHN', 'RUS', 'USA') AND Year IN (2004, 2008, 2012) AND Medal = 'Gold' AND Sport = 'Gymnastics' GROUP BY Country, Year ORDER BY Country ASC, Year ASC) SELECT Country, Year, RANK() OVER (PARTITION BY Year ORDER BY Awards DESC) :: INTEGER AS rank FROM Country_Awards ORDER BY Country ASC, Year ASC; $$) AS ct (Country VARCHAR, "2004" INTEGER, "2008" INTEGER, "2012" INTEGER) ORDER BY Country ASC;

-- Create the correct extension to enable CROSSTAB CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM CROSSTAB($$ SELECT Gender, Year, Country FROM Summer_Medals WHERE Year IN (2008, 2012) AND Medal = 'Gold' AND Event = 'Pole Vault' ORDER By Gender ASC, Year ASC; -- Fill in the correct column names for the pivoted table $$) AS ct (Gender VARCHAR, "2008" VARCHAR, "2012" VARCHAR) ORDER BY Gender ASC;

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

WITH Country_Awards AS ( SELECT Country, Year, COUNT(*) AS Awards FROM Summer_Medals WHERE Country IN ('FRA', 'GBR', 'GER') AND Year IN (2004, 2008, 2012) AND Medal = 'Gold' GROUP BY Country, Year) SELECT -- Select Country and Year Country, YEAR, -- Rank by gold medals earned per year RANK() OVER(PARTITION BY Year ORDER BY Awards DESC) :: INTEGER AS rank FROM Country_Awards ORDER BY Country ASC, Year ASC;

CREATE EXTENSION IF NOT EXISTS tablefunc; SELECT * FROM CROSSTAB($$ WITH Country_Awards AS ( SELECT Country, Year, COUNT(*) AS Awards FROM Summer_Medals WHERE Country IN ('FRA', 'GBR', 'GER') AND Year IN (2004, 2008, 2012) AND Medal = 'Gold' GROUP BY Country, Year) SELECT Country, Year, RANK() OVER (PARTITION BY Year ORDER BY Awards DESC) :: INTEGER AS rank FROM Country_Awards ORDER BY Country ASC, Year ASC; -- Fill in the correct column names for the pivoted table $$) AS ct (Country VARCHAR, "2004" INTEGER, "2008" INTEGER, "2012" INTEGER) Order by Country ASC;

SELECT Country, Medal, COUNT(*) AS Awards FROM Summer_Medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY Country, Medal ORDER BY Country ASC, Medal ASC UNION ALL SELECT Country, 'Total', COUNT(*) AS Awards FROM Summer_Medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY Country, 2 ORDER BY Country ASC;

SELECT Country, Medal, COUNT(*) AS Awards FROM Summer_Medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY Country, ROLLUP(Medal) ORDER BY Country ASC, Medal ASC;

SELECT Country, Medal, COUNT(*) AS Awards FROM summer_medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY ROLLUP(Country, Medal) ORDER BY Country ASC, Medal ASC;

SELECT Country, Medal, COUNT(*) AS Awards FROM summer_medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY CUBE(Country, Medal) ORDER BY Country ASC, Medal ASC;

-- Count the gold medals per country and gender SELECT Country, Gender, COUNT(*) AS Gold_Awards FROM Summer_Medals WHERE Year = 2004 AND Medal = 'Gold' AND Country IN ('DEN', 'NOR', 'SWE') -- Generate Country-level subtotals GROUP BY Country, ROLLUP(Gender) ORDER BY Country ASC, Gender ASC;

-- Count the medals per gender and medal type SELECT Gender, Medal, COUNT(*) AS Awards FROM Summer_Medals WHERE Year = 2012 AND Country = 'RUS' -- Get all possible group-level subtotals GROUP BY CUBE(Gender, Medal) ORDER BY Gender ASC, Medal ASC;

SELECT Country, Medal, COUNT(*) AS Awards FROM summer_medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY ROLLUP(Country, Medal) ORDER BY Country ASC, Medal ASC;

SELECT COALESCE(Country, 'Both countries') AS Country, COALESCE(Medal, 'All medals') AS Medal, COUNT(*) AS Awards FROM summer_medals WHERE Year = 2008 AND Country IN ('CHN', 'RUS') GROUP BY ROLLUP(Country, Medal) ORDER BY Country ASC, Medal ASC;

WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Year = 2012 AND Country IN ('CHN', 'RUS', 'USA') AND Medal = 'Gold' AND Sport = 'Gymnastics' GROUP BY Country), Country_Ranks AS ( SELECT Country, RANK() OVER (ORDER BY Medals DESC) AS Rank FROM Country_Medals ORDER BY Rank ASC) SELECT STRING_AGG(Country,', ') FROM Country_Medals;

-- Before WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Year = 2012 AND Country IN ('CHN', 'RUS', 'USA') AND Medal = 'Gold' AND Sport = 'Gymnastics' GROUP BY Country) SELECT Country, RANK() OVER (ORDER BY Medals DESC) AS Rank FROM Country_Medals ORDER BY Rank ASC;

-- After WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Year = 2012 AND Country IN ('CHN', 'RUS', 'USA') AND Medal = 'Gold' AND Sport = 'Gymnastics' GROUP BY Country), Country_Ranks AS ( SELECT Country, RANK() OVER (ORDER BY Medals DESC) AS Rank FROM Country_Medals ORDER BY Rank ASC) SELECT STRING_AGG(Country,', ') FROM Country_Medals;

SELECT -- Replace the nulls in the columns with meaningful text COALESCE(Country, 'All countries') AS Country, COALESCE(Gender, 'All genders') AS Gender, COUNT(*) AS Awards FROM Summer_Medals WHERE Year = 2004 AND Medal = 'Gold' AND Country IN ('DEN', 'NOR', 'SWE') GROUP BY ROLLUP(Country, Gender) ORDER BY Country ASC, Gender ASC;

WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Year = 2000 AND Medal = 'Gold' GROUP BY Country) SELECT Country, -- Rank countries by the medals awarded RANK() OVER(ORDER BY Medals DESC) AS Rank FROM Country_Medals ORDER BY Rank ASC;

WITH Country_Medals AS ( SELECT Country, COUNT(*) AS Medals FROM Summer_Medals WHERE Year = 2000 AND Medal = 'Gold' GROUP BY Country), Country_Ranks AS ( SELECT Country, RANK() OVER (ORDER BY Medals DESC) AS Rank FROM Country_Medals ORDER BY Rank ASC) -- Compress the countries column SELECT STRING_AGG(Country, ', ') FROM Country_Ranks -- Select only the top three ranks WHERE Rank <= 3;