FIFA World Cup 2026 Live SQL Analytics
Master SQL by querying the official 2026 Host Cities, matches, players, and goals database. Complete all three challenges to unlock the exclusive World Cup Analyst achievement badge!
Analytical Task & Scenario
Write a SQL query to list all stadiums located in the 'USA' that have a capacity of 70,000 or greater. Return the name and capacity of the stadiums, sorted by capacity in descending order.
Expected Columns:
Schema References
FIFA World Cup 2026 Database Schema & Entity Relationships
Sports analytics relies on a highly normalized relational database to ensure data integrity and query efficiency. The FIFA World Cup 2026 dataset maps qualified teams, international stadiums, matches, players, and goals. Understanding these entity relationships is crucial for writing clean SQL queries.
| Table Name | Primary Key | Foreign Keys | Key Columns & Descriptions |
|---|---|---|---|
| teams | team_id | None | Stores team profiles, including country name, FIFA rankings, confederation, and group letters. |
| stadiums | stadium_id | None | Contains tournament venues across USA, Canada, and Mexico, with capacities and hosting cities. |
| matches | match_id | home_team_id, away_team_id, stadium_id | Tracks match dates, participating team pairings, home/away scores, match stage, and attendances. |
| players | player_id | team_id | Profiles player details: names, position (Forward, Midfielder, Defender, Goalkeeper), age, and club. |
| goals | goal_id | match_id, player_id | Logs each individual goal including minute scored and goal type (Header, Penalty, Open Play). |
How to Calculate Team Standings & Points
To build a live group standing database, you aggregate wins, losses, and draws using conditional logic. Here is the standard SQL query using a Common Table Expression (CTE) to compute points:
WITH TeamResults AS (
SELECT home_team_id AS team_id,
CASE WHEN home_score > away_score THEN 3
WHEN home_score = away_score THEN 1
ELSE 0 END AS points,
home_score AS gf, away_score AS ga
FROM matches WHERE home_score IS NOT NULL
UNION ALL
SELECT away_team_id AS team_id,
CASE WHEN away_score > home_score THEN 3
WHEN away_score = home_score THEN 1
ELSE 0 END AS points,
away_score AS gf, home_score AS ga
FROM matches WHERE away_score IS NOT NULL
)
SELECT t.country,
COUNT(*) AS matches_played,
SUM(r.points) AS total_points,
(SUM(r.gf) - SUM(r.ga)) AS goal_difference
FROM TeamResults r
JOIN teams t ON r.team_id = t.team_id
GROUP BY t.team_id
ORDER BY total_points DESC, goal_difference DESC;Find the Top Performing Venues
Analyzing stadium utilization and average ticket attendance is key for administrative insights. This aggregate query tracks total attendance, stadium name, and utilization percentages:
SELECT s.name AS stadium,
s.city,
s.capacity,
ROUND(AVG(m.attendance), 0) AS avg_attendance,
ROUND((AVG(m.attendance) * 100.0 / s.capacity), 2) AS utilization_percent
FROM matches m
JOIN stadiums s ON m.stadium_id = s.stadium_id
WHERE m.attendance IS NOT NULL
GROUP BY s.stadium_id
ORDER BY avg_attendance DESC;World Cup SQL Analytics: Frequently Asked Questions
What is a SQL Join in Sports Databases?
A SQL JOIN operation combines columns from multiple tables. For instance, linking the goals table with the players table on player_id matches scorers to their profiles, while joining with matches identifies the corresponding matchday.
How is Goal Difference Calculated?
Goal Difference (GD) is computed as Goals For (GF) minus Goals Against (GA). In SQL, you use the aggregate expression SUM(home_score) - SUM(away_score) grouped by team to determine overall performance.
Can I Practice Window Functions here?
Yes! Our database sandbox supports all advanced SQLite/SQL window functions. You can write window queries like DENSE_RANK() OVER (PARTITION BY group_letter ORDER BY fifa_ranking ASC) to rank teams dynamically inside their respective groups.