linkedin Skip to Main Content
 

Tiny Interviews: World Cup SQL

Interviewing , Tiny interviews

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:

  1. Highlighting your code
  2. 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:

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:

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 desc
Code language: SQL (Structured Query Language) (sql)

Question 4

Learn the skills needed:

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 10
Code 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: