About This project

Extraction

Athlete Events

SELECT * FROM 'athlete_events.csv'

IDint64

Nameobject

132219

66542

Leonidas Lanngakis

214353

107613

Carl Schuhmann

214352

107613

Carl Schuhmann

24682

12929

John Mary Pius Boland

214351

107613

Carl Schuhmann

214350

107613

Carl Schuhmann

214349

107613

Carl Schuhmann

214348

107613

Carl Schuhmann

214347

107613

Carl Schuhmann

263742

131985

Thomas Xenakis

Countries

SELECT * FROM 'noc_regions.csv'

column0object

NOC0.4%

AFG0.4%

229 others99.1%

column1object

Germany1.7%

Czech Republic1.3%

206 others97%

0

NOC

region

1

AFG

Afghanistan

2

AHO

Curacao

3

ALB

Albania

4

ALG

Algeria

5

AND

Andorra

6

ANG

Angola

7

ANT

Antigua

8

ANZ

Australia

9

ARG

Argentina

# Let's rename columns and delete the first row to set it up properly noc_regions.rename({'column0':'noc','column1':'country','column2':'notes'}, axis=1, inplace=True) noc_regions = noc_regions.drop(0,0)

noc_regions.head()

nocobject

countryobject

1

AFG

Afghanistan

2

AHO

Curacao

3

ALB

Albania

4

ALG

Algeria

5

AND

Andorra

Questions to be answered

How many olympics games have been held?

SELECT COUNT (DISTINCT games) FROM athlete_events

count(DISTINCT games)int64

0

51

List down all Olympics games held so far

SELECT DISTINCT Year, season, city FROM athlete_events ORDER BY year

Yearint64

1896 - 2016

Seasonobject

Summer57.7%

Winter42.3%

0

1896

Summer

1

1900

Summer

2

1904

Summer

3

1906

Summer

4

1908

Summer

5

1912

Summer

6

1920

Summer

7

1924

Summer

8

1924

Winter

9

1928

Winter

Mention the total no of nations who participated in each olympics games

SELECT DISTINCT ae.games AS olympic_games, COUNT(DISTINCT nr.country) AS number_countries FROM athlete_events ae LEFT JOIN noc_regions nr ON nr.noc = ae.noc GROUP BY games ORDER BY games ASC

olympic_gamesobject

1896 Summer2%

1900 Summer2%

49 others96.1%

number_countriesint64

12 - 204

0

1896 Summer

12

1

1900 Summer

31

2

1904 Summer

14

3

1906 Summer

20

4

1908 Summer

22

5

1912 Summer

29

6

1920 Summer

29

7

1924 Summer

45

8

1924 Winter

19

9

1928 Summer

46

Which year saw the highest and lowest no of countries participating in olympics?

WITH games_countries AS ( SELECT ae.games AS games, nr.country AS country FROM athlete_events ae JOIN noc_regions nr ON ae.noc = nr.noc GROUP BY ae.games, nr.country ), number_countries AS ( SELECT games, COUNT(country) AS number_countries FROM games_countries GROUP BY games ) SELECT DISTINCT CONCAT(FIRST_VALUE(games) OVER(ORDER BY number_countries), ' - ' ,FIRST_VALUE(number_countries) OVER(ORDER BY number_countries), ' countries') AS least_countries, CONCAT(FIRST_VALUE(games) OVER(ORDER BY number_countries DESC), ' - ', FIRST_VALUE(number_countries) OVER(ORDER BY number_countries DESC),' countries') AS most_countries FROM number_countries

least_countriesobject

most_countriesobject

0

1896 Summer - 12 countries

2016 Summer - 204 countries

SELECT DISTINCT ae.games AS olympic_games, COUNT(DISTINCT nr.country) AS number_countries, RANK() OVER(ORDER BY COUNT(DISTINCT nr.country)DESC) AS ranking FROM athlete_events ae LEFT JOIN noc_regions nr ON nr.noc = ae.noc GROUP BY games ORDER BY games DESC

olympic_gamesobject

2016 Summer2%

2014 Winter2%

49 others96.1%

number_countriesint64

12 - 204

0

2016 Summer

204

1

2014 Winter

88

2

2012 Summer

203

3

2010 Winter

81

4

2008 Summer

202

5

2006 Winter

78

6

2004 Summer

199

7

2002 Winter

76

8

2000 Summer

198

9

1998 Winter

72

Which nation has participated in all of the olympic games?

SELECT nr.country AS countries, COUNT(DISTINCT ae.games) AS olympic_games FROM athlete_events ae LEFT JOIN noc_regions nr ON nr.noc = ae.noc GROUP BY nr.country HAVING COUNT(DISTINCT ae.games) = 51

countriesobject

olympic_gamesint64

0

France

51

1

Italy

51

2

Switzerland

51

3

UK

51

WITH total_games AS ( SELECT COUNT (DISTINCT games) AS total_games FROM athlete_events ), games_countries AS ( SELECT ae.games AS games, nr.country AS country FROM athlete_events ae JOIN noc_regions nr ON nr.noc=ae.noc GROUP BY games, nr.country ), countries_participations AS ( SELECT country AS country, COUNT(country) AS number_games FROM games_countries GROUP BY country ) SELECT cp.country, cp.number_games FROM countries_participations cp JOIN total_games tg ON cp.number_games = tg.total_games

countryobject

number_gamesint64

0

France

51

1

UK

51

2

Switzerland

51

3

Italy

51

Identify the sports which were played in all summer olympics

WITH number_summer_games AS ( SELECT COUNT (DISTINCT games) AS games FROM athlete_events WHERE season ='Summer' ), games_sports AS ( SELECT games, sport FROM athlete_events WHERE season = 'Summer' GROUP BY games, sport ), number_sports_summer_games AS ( SELECT sport, COUNT(sport) number_summer_games FROM games_sports GROUP BY sport ) SELECT ns.sport, ns.number_summer_games FROM number_sports_summer_games ns JOIN number_summer_games nsg ON nsg.games=ns.number_summer_games

Sportobject

number_summer_gamesint64

0

Athletics

29

1

Fencing

29

2

Swimming

29

3

Cycling

29

4

Gymnastics

29

Which Sports were just played only once in the olympics?

WITH games_sports AS ( SELECT games, sport FROM athlete_events GROUP BY games, sport ), number_sports AS ( SELECT sport, COUNT(sport) number_games FROM games_sports GROUP BY sport ) SELECT ns.sport, ns.number_games, gs.games FROM number_sports ns JOIN games_sports gs ON gs.sport = ns.sport WHERE ns.number_games = 1

Sportobject

Rugby Sevens10%

Cricket10%

8 others80%

number_gamesint64

1 - 1

0

Rugby Sevens

1

Cricket

1

2

Motorboating

1

3

Jeu De Paume

1

4

Roque

1

5

Basque Pelota

1

6

Racquets

1

7

Aeronautics

1

8

Military Ski Patrol

1

9

Croquet

1

Fetch the total No. of sports played in each olympic games.

SELECT DISTINCT games, city, COUNT (DISTINCT sport) AS number_sports FROM athlete_events GROUP BY games, city ORDER BY COUNT (DISTINCT sport) DESC

Gamesobject

1956 Summer3.8%

2000 Summer1.9%

49 others94.2%

Cityobject

Athina5.8%

London5.8%

40 others88.5%

0

2000 Summer

Sydney

1

2008 Summer

Beijing

2

2016 Summer

Rio de Janeiro

3

2004 Summer

Athina

4

2012 Summer

London

5

1996 Summer

Atlanta

6

1992 Summer

Barcelona

7

1988 Summer

Seoul

8

1920 Summer

Antwerpen

9

1984 Summer

Los Angeles

Fetch details of the oldest athletes to win a gold medal.

SELECT name, sex, age, medal, team, games, city, sport, event, medal FROM athlete_events WHERE age IN (SELECT MAX(age) FROM athlete_events WHERE age NOT LIKE 'NA' AND medal = 'Gold') AND medal = 'Gold'

Nameobject

Sexobject

0

Charles Jacobus

M

1

Oscar Gomer Swahn

M

SELECT CONCAT('For every woman in the olympic games there have been: ', CAST(ROUND(sub.number_males, 2) AS DECIMAL(18,2)) / CAST(ROUND(sub.number_females, 2) AS DECIMAL(18,2)), ' men') AS Ratio FROM (SELECT SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) AS number_males, SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) AS number_females FROM athlete_events) AS sub

Ratioobject

0

For every woman in the olympic games there have been: 2.638066611202061 men

Fetch the top 5 athletes who have won the most gold medals.

SELECT name, gold_medals, team, sport FROM (SELECT name, COUNT(medal) AS gold_medals, team, sport, DENSE_RANK() OVER(ORDER BY COUNT(medal) DESC) AS ranking FROM athlete_events WHERE medal ='Gold' GROUP BY name, team, sport ORDER BY gold_medals DESC) AS subquery WHERE ranking <=5

Nameobject

Michael Fred Phelps, II5.9%

Raymond Clarence "Ray" Ewry5.9%

15 others88.2%

gold_medalsint64

7 - 23

0

Michael Fred Phelps, II

23

1

Raymond Clarence "Ray" Ewry

10

2

Larysa Semenivna Latynina (Diriy-)

9

3

Paavo Johannes Nurmi

9

4

Frederick Carlton "Carl" Lewis

9

5

Mark Andrew Spitz

9

6

Ole Einar Bjrndalen

8

7

Matthew Nicholas "Matt" Biondi

8

Usain St. Leo Bolt

8

9

Sawao Kato

8

Fetch the top 5 athletes who have won the most medals (gold/silver/bronze).

SELECT name, gold_medals, silver_medals, bronze_medals, total_medals, team, sport FROM (SELECT name, SUM(CASE WHEN medal = 'Gold' THEN 1 ELSE 0 END) AS gold_medals, SUM(CASE WHEN medal = 'Silver' THEN 1 ELSE 0 END) AS silver_medals, SUM(CASE WHEN medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze_medals, SUM(CASE WHEN medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) AS total_medals, team, sport, DENSE_RANK() OVER(ORDER BY SUM(CASE WHEN medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC) AS ranking FROM athlete_events GROUP BY name, team, sport ORDER BY SUM(CASE WHEN medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC) WHERE ranking <= 5

Nameobject

Michael Fred Phelps, II7.1%

Larysa Semenivna Latynina (Diriy-)7.1%

12 others85.7%

gold_medalsint64

3 - 23

0

Michael Fred Phelps, II

23

1

Larysa Semenivna Latynina (Diriy-)

9

2

Nikolay Yefimovich Andrianov

7

3

Ole Einar Bjrndalen

8

4

Edoardo Mangiarotti

6

5

Borys Anfiyanovych Shakhlin

7

6

Takashi Ono

5

7

Sawao Kato

8

Ryan Steven Lochte

6

9

Jennifer Elisabeth "Jenny" Thompson (-Cumpelik)

8

Fetch the top 5 most successful countries in olympics. Success is defined by no of medals won.

SELECT country, gold_medals, silver_medals, bronze_medals, total_medals FROM (SELECT nr.country AS country, SUM(CASE WHEN ae.medal = 'Gold' THEN 1 ELSE 0 END) AS gold_medals, SUM(CASE WHEN ae.medal = 'Silver' THEN 1 ELSE 0 END) AS silver_medals, SUM(CASE WHEN ae.medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze_medals, SUM(CASE WHEN ae.medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) AS total_medals, DENSE_RANK() OVER(ORDER BY SUM(CASE WHEN ae.medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC) AS ranking FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc GROUP BY nr.country ORDER BY SUM(CASE WHEN ae.medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC) WHERE ranking <= 5

countryobject

gold_medalsint64

0

USA

2638

1

Russia

1599

2

Germany

1301

3

UK

678

4

France

501

List down total gold, silver and broze medals won by each country corresponding to each olympic games.

SELECT nr.country AS country, ae.games, SUM(CASE WHEN ae.medal = 'Gold' THEN 1 ELSE 0 END) AS gold_medals, SUM(CASE WHEN ae.medal = 'Silver' THEN 1 ELSE 0 END) AS silver_medals, SUM(CASE WHEN ae.medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze_medals, SUM(CASE WHEN ae.medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) AS total_medals FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc GROUP BY nr.country, ae.games ORDER BY SUM(CASE WHEN ae.medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC

countryobject

UK1.3%

France1.3%

204 others97.3%

Gamesobject

2016 Summer5.4%

2012 Summer5.4%

49 others89.3%

0

Russia

1980 Summer

1

USA

1904 Summer

2

UK

1908 Summer

3

USA

1984 Summer

4

USA

2008 Summer

5

Russia

1988 Summer

6

Germany

1988 Summer

7

Russia

1976 Summer

8

Germany

1976 Summer

9

USA

2016 Summer

Identify which country won the most gold, most silver and most bronze medals in each olympic games.

WITH country_games_medals AS ( SELECT nr.country AS country, ae.games, SUM(CASE WHEN ae.medal = 'Gold' THEN 1 ELSE 0 END) AS gold_medals, SUM(CASE WHEN ae.medal = 'Silver' THEN 1 ELSE 0 END) AS silver_medals, SUM(CASE WHEN ae.medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze_medals, FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc GROUP BY nr.country, ae.games ORDER BY SUM(CASE WHEN ae.medal IN ('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC), games_max_medals AS ( SELECT games AS games_max, MAX(gold_medals) AS max_gold, MAX(silver_medals) AS max_silver, MAX(bronze_medals) AS max_bronze FROM country_games_medals GROUP BY games ) SELECT cgm.country, gmm.games_max, MAX(gmm.max_gold), MAX(gmm.max_silver), MAX(max_bronze) FROM country_games_medals cgm RIGHT JOIN games_max_medals gmm ON cgm.games = gmm.games_max AND cgm.gold_medals = gmm.max_gold GROUP BY cgm.country, gmm.games_max ORDER BY games_max -- WORK IN PROGRESS

countryobject

games_maxobject

0

Germany

1896 Summer

Which countries have never won gold medal but have won silver/bronze medals?

WITH country_medals AS (SELECT nr.country AS country, SUM(CASE WHEN ae.medal = 'Gold' THEN 1 ELSE 0 END) AS gold_medals, SUM(CASE WHEN ae.medal = 'Silver' THEN 1 ELSE 0 END) AS silver_medals, SUM(CASE WHEN ae.medal = 'Bronze' THEN 1 ELSE 0 END) AS bronze_medals FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc GROUP BY nr.country) SELECT country, gold_medals, silver_medals, bronze_medals FROM country_medals WHERE gold_medals = 0 AND (silver_medals > 0 OR bronze_medals > 0) ORDER BY silver_medals DESC, bronze_medals DESC

countryobject

Paraguay2.7%

Iceland2.7%

35 others94.6%

gold_medalsint64

0 - 0

0

Paraguay

0

1

Iceland

0

2

Montenegro

0

3

Malaysia

0

4

Namibia

0

5

Philippines

0

6

Moldova

0

7

Lebanon

0

8

Sri Lanka

0

9

Tanzania

0

In which Sport/event, India has won highest medals.

SELECT nr.country, ae.sport, ae.event, SUM(CASE WHEN ae.medal IN('Gold','Silver','Bronze') THEN 1 ELSE 0 END) AS total_medals FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc WHERE nr.country = 'India' GROUP BY nr.country, ae.sport, ae.event ORDER BY SUM(CASE WHEN ae.medal IN('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC

countryobject

India100%

Sportobject

Athletics23.5%

Wrestling10.7%

25 others65.8%

0

India

Hockey

1

India

Alpinism

2

India

Wrestling

3

India

Shooting

4

India

Badminton

5

India

Tennis

6

India

Wrestling

7

India

Athletics

8

India

Wrestling

9

India

Boxing

Break down all olympic games where india won medal for Hockey and how many medals in each olympic games.

SELECT nr.country, ae.games, ae.sport, ae.event, SUM(CASE WHEN ae.medal IN('Gold','Silver','Bronze') THEN 1 ELSE 0 END) AS total_medals FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc WHERE nr.country = 'India' AND sport = 'Hockey' GROUP BY nr.country, ae.sport, ae.event, ae.games HAVING SUM(CASE WHEN ae.medal IN('Gold','Silver','Bronze') THEN 1 ELSE 0 END) > 0 ORDER BY SUM(CASE WHEN ae.medal IN('Gold','Silver','Bronze') THEN 1 ELSE 0 END) DESC

countryobject

India100%

Gamesobject

1948 Summer9.1%

1936 Summer9.1%

9 others81.8%

0

India

1948 Summer

1

India

1936 Summer

2

India

1956 Summer

3

India

1980 Summer

4

India

1968 Summer

5

India

1932 Summer

6

India

1964 Summer

7

India

1952 Summer

8

India

1972 Summer

9

India

1928 Summer

SELECT nr.country, ae.games, ae.medal, ae.event, SUM(CASE WHEN ae.medal IN('Gold','Silver','Bronze') THEN 1 ELSE 0 END) AS number_medals, ae.name FROM athlete_events ae JOIN noc_regions nr ON ae.noc=nr.noc WHERE nr.country = 'Colombia' AND ae.medal NOT IN('NA') GROUP BY ae.games, nr.country, ae.medal, ae.event, ae.name ORDER BY ae.games DESC

countryobject

Colombia100%

Gamesobject

2016 Summer28.6%

2012 Summer28.6%

8 others42.9%

20

Colombia

2000 Summer

21

Colombia

1992 Summer

22

Colombia

1988 Summer

23

Colombia

1984 Summer

24

Colombia

1972 Summer

25

Colombia

1972 Summer

26

Colombia

1972 Summer

27

Colombia

1900 Summer

athlete_events

IDint64

Nameobject

0

1

A Dijiang

1

2

A Lamusi

2

3

Gunnar Nielsen Aaby

3

4

Edgar Lindenau Aabye

4

5

Christine Jacoba Aaftink

5

Christine Jacoba Aaftink

6

5

Christine Jacoba Aaftink

7

5

Christine Jacoba Aaftink

8

5

Christine Jacoba Aaftink

9

5

Christine Jacoba Aaftink