Skip to content

PostgreSQL Summary Stats and Window Functions

Here you can access the summer_medals table used in the course. To access the table, you will need to specify the medals schema in your queries (e.g., medals.summer_medals).

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Spinner
DataFrameas
df2
variable
SELECT *
FROM medals.summer_medals

Explore Datasets

Use the summer_medals table to explore the data and practice your skills!

  • Select the athlete, event, and year from the summer_medals table.
    • Add another column, previous_winner, which contains the previous winner of the same event.
    • Filter your results for gold medalists.
  • Return the year, total number of medalists per year, and running total number of medalists in the history of the Summer Olympics.
    • Order your results by year in ascending order.
  • Return the country, year, and the number of gold medals earned.
    • Limit your results to the years 2004, 2008, and 2012.
    • Each country should have a subtotal of all gold medals earned across the three years.

Select the athlete, event, and year from the summer_medals table.

  • Add another column, previous_winner, which contains the previous winner of the same event.
  • Filter your results for gold medalists.
Spinner
DataFrameas
df4
variable
	Select row_Number()over() as RowNumber,athlete,event,country,
	lag(country)over(order by year asc) as previous_winner
	from medals.summer_medals
	ORDER BY Year 
	limit 5;
Spinner
DataFrameas
df3
variable
SELECT country, YEAR,COUNT(*) AS GOLD_MEDALIST from medals.summer_medals
where medal='Gold' and year in(2004,2008,2012)
GROUP BY year, country
ORDER BY GOLD_MEDALIST desc
limit 10
Spinner
DataFrameas
df
variable
SELECT  athlete, event,  year,  lag(year,1) over(order by year) as previous_winner from  medals.summer_medals where medal='Gold'
Spinner
DataFrameas
df1
variable
Select year, count(*) as total_medalist,count(CASE WHEN discipline='Athletics' THEN 1 END) as athletics from medals.summer_medals
Where medal='Gold'
Group by year
order by year asc;