Code Challenge: World Cup SQL

November 23, 2022 • Coding Challenges

By Mathis Hammel

The FIFA World Cup is a global sporting event where national soccer teams compete to determine the world champion every four years! Use your SQL knowledge to discover exciting world cup history facts like “which team has the largest world cup following in FIFA history?” Use the sandbox below to get started and practice your SQL skills!

💡 Warm up your SQL skills with this article on SQL functions and techniques every data person should know.

Please see the interview prompts written directly in the pad. Question solutions are provided in the next section after the sandbox.

‼️ Attempt the questions in the pad before checking out the solutions.

Useful tips

Running SQL: Comment out any SQL code from a previous question before moving to the next Question (or else all your SQL code will run at once). You can do this quickly by:

Highlighting your code
Typing Cmd + / or Ctrl + /

Viewing data

To view all data in a single row easily, replace the semicolon at the end of your query with /G.

Example:

— Returns a single row in a vertical, easy to ready format

select * from World_Cup_Matches limit 1 \G

— Returns a single row in a horizontal format, better suited for a wide screen

select * from World_Cup_Matches limit 1;

Interview data

The data for the tiny interview can be viewed on this kaggle dataset.

CoderPad provides a basic SQL sandbox with the following schema. You can also use commands like show tables and desc world_cup_tournaments.

The schema diagram for the `world_cup_tournaments`, `world_cup_matches` and `world_cup_line_ups` table.

Solutions

Question 1

Learn the skills needed:

ORDER BY: https://www.w3schools.com/sql/sql_orderby.asp

select 
Year
, Host_Country
, Goals_Scored
from world_cup_tournaments
order by Goals_Scored desc Code language: SQL (Structured Query Language) (sql)

Question 2

Learn the skills needed:

LEFT JOIN: https://www.w3schools.com/sql/sql_join_left.asp
COUNT(): https://www.w3schools.com/sql/func_mysql_count.asp
GROUP BY: https://www.w3schools.com/sql/sql_groupby.asp
ORDER BY: https://www.w3schools.com/sql/sql_orderby.asp

select 
  a.Year
  , a.Host_Country
  , count(distinct b.Stadium) as num_pitch_locations 
from 
  world_cup_tournaments as a 
  left join world_cup_matches as b on a.Year = b.Year 
group by 1,2
order by 3 desc Code language: SQL (Structured Query Language) (sql)

Question 3

Learn the skills needed:

Common Table Expressions (CTE): https://www.w3schools.blog/sql-cte-how-to-master-it-with-easy-examples https://mariadb.com/kb/en/non-recursive-common-table-expressions-overview/
ORDER BY: https://www.w3schools.com/sql/sql_orderby.asp

with calculate_avg_goals_per_match as (
  select 
    Year
    , Host_Country 
    , Goals_Scored / Matches_Played as avg_goals_per_match
  from world_cup_tournaments
)
select *
from calculate_avg_goals_per_match
order by avg_goals_per_match descCode language: SQL (Structured Query Language) (sql)

Question 4

Learn the skills needed:

Common Table Expressions (CTE): https://www.w3schools.blog/sql-cte-how-to-master-it-with-easy-examples https://mariadb.com/kb/en/non-recursive-common-table-expressions-overview/
UNION ALL: https://www.w3schools.com/sql/sql_ref_union_all.asp
ORDER BY: https://www.w3schools.com/sql/sql_orderby.asp
SUM(): https://www.w3schools.com/mysql/func_mysql_sum.asp
LIMIT: https://www.w3schools.com/mysql/mysql_limit.asp

with list_matches_with_attendance as (
  -- List stadium_attendance for Home_Team_Name 
  select 
    Match_ID 
    , Home_Team_Name as team_name 
    , Attendance as stadium_attendance
  from world_cup_matches

  UNION ALL 

  -- List all stadium_attendance for Away_Team_Name 
  select 
    Match_ID 
    , Away_Team_Name as team_name 
    , Attendance as stadium_attendance
  from world_cup_matches
)
select 
  team_name
  , sum(stadium_attendance) as team_following 
from list_matches_with_attendance
group by 1
order by 2 desc 
limit 10Code language: SQL (Structured Query Language) (sql)

More interviewing resources

Even for the most experienced managers, hiring the right engineers can be tricky.

To make it easier, here are some more resources to help make your hiring process as smooth as possible: