Skip to content

Asian Cup Football Data 1972 - 2019

This dataset includes 45,315 results of international football matches starting from the very first official match to in 1872 up to 2023. The matches range from FIFA World Cup to regular friendly matches. The matches are strictly men's full internationals.

you can download it from here and we used Excel to filter on Asian Cup History 1972 - 2019 and create a table from list of winners dataset

We should answer on these questions:

  1. top 10 for teams scores
  2. The most 10 teams wins
  3. champions list history
  4. loctaions for countries host
  5. Top 10 scorers
  6. Top 5 countries host for matches
  7. Avg scores for each champion overtime

Spinner
DataFrameas
df
variable
--we have look to table goalscorers
SELECT * 
FROM 'goalscorers.csv';
Spinner
DataFrameas
df1
variable
--we have look to table asianfootball matches
SELECT * 
FROM 'asianfootball.csv'
LIMIT 10;
Spinner
DataFrameas
df2
variable
--we have look to which match asianfootball matches with goalscorers
SELECT g.* 
FROM 'asianfootball.csv' AS r
LEFT JOIN 'goalscorers.csv' AS g
ON r.date = g.date And r.home_team = g.home_team And r.away_team = g.away_team;
Spinner
DataFrameas
df3
variable
--we have look to table shootouts penalties
SELECT *
FROM 'shootouts.csv'
LIMIT 10;
Spinner
DataFrameas
df10
variable
--we have look to winners table
SELECT *
FROM 'winners.csv';

1. Top 10 for teams scores

Spinner
DataFrameas
df5
variable
--Total Teamm scores
--We used CTE to find total scores for each home_team
WITH home AS (SELECT home_team,
SUM(home_score) AS total_home_score
FROM 'asianfootball.csv'
			  WHERE date > '1968-12-31T00:00:00.000'
GROUP BY home_team
ORDER BY total_home_score DESC)
,
--We used CTE to find total scores for each away_team
away AS (SELECT away_team,
SUM(away_score) AS total_away_score
FROM 'asianfootball.csv'
		 WHERE date > '1968-12-31T00:00:00.000'
GROUP BY away_team
ORDER BY total_away_score DESC)
--we used FULL JOIN for match and combine CTE ( home and away)
SELECT home.home_team As team , (home.total_home_score + away.total_away_score) AS Total_Scores
FROM home
FULL JOIN away 
ON home.home_team = away.away_team
ORDER BY total_scores DESC
LIMIT 10;

2. The most 10 teams wins

Spinner
DataFrameas
df6
variable
--- we used CTE to Sum home_team wins 
WITH Teams_win_home AS (SELECT home_team ,
	  SUM(Case When Home_score > Away_score THEN 1
	 Else 0 End ) AS results_home
	 
	 FROM 'asianfootball.csv'
						WHERE date > '1968-12-31T00:00:00.000'
GROUP BY home_team
ORDER BY results_home DESC)
,
-- we used CTE to Sum away_team wins 
Teams_win_Away AS (SELECT Away_team ,
	  SUM(Case When Home_score < Away_score THEN 1
	 Else 0 End ) AS results_away
				   
FROM 'asianfootball.csv'
				   WHERE date > '1968-12-31T00:00:00.000'
GROUP BY Away_team
ORDER BY results_away DESC)
	 

-- we used INNER JOIN to combine CTE(Teams_win_home , Teams_win_)
SELECT h.home_team AS Team_name, (h.results_home + a.results_away) AS No_Win_of_Teams
FROM teams_win_home AS h
INNER JOIN teams_win_away AS a
ON h.home_team = a.away_team
WHERE No_Win_of_Teams <> 0
ORDER BY No_Win_of_Teams DESC
LIMIT 10;

3. champions list

Spinner
DataFrameas
df13
variable
SELECT *
FROM 'winners.csv'
WHERE date_of_championship > 1968;

4. Host_Countries_Cities

Hidden code df4
‌
‌
‌