The database uses a highly normalized relational schema. It links individual players, managers, and referees to match events across nearly a century of sporting history.
SELECT year, host_country, winner, CASE WHEN host_country = winner THEN 'Won' ELSE 'Did not win' END AS result FROM world_cups; jfjelstul worldcup data-sqlite
Users should verify the "Year" coverage in their specific downloaded version to ensure the 2018 and 2022 World Cup data is included if required for their analysis. The database uses a highly normalized relational schema
WITH MatchResults AS ( SELECT home_team_id AS team_id, home_team_score AS goals_for, away_team_score AS goals_against FROM matches UNION ALL SELECT away_team_id AS team_id, away_team_score AS goals_for, home_team_score AS goals_against FROM matches ) SELECT t.team_name, COUNT(*) AS matches_played, SUM(mr.goals_for) AS total_goals_scored, SUM(mr.goals_against) AS total_goals_conceded, (SUM(mr.goals_for) - SUM(mr.goals_against)) AS net_goal_differential FROM MatchResults mr JOIN teams t ON mr.team_id = t.team_id GROUP BY t.team_id ORDER BY net_goal_differential DESC; Use code with caution. ⚡ Indexing for High-Performance Queries WITH MatchResults AS ( SELECT home_team_id AS team_id,
import sqlite3 import pandas as pd # Connect to or create local SQLite database conn = sqlite3.connect("worldcup.sqlite") # Target source URLs from the official repository data structure datasets = ["tournaments", "teams", "matches", "goals"] base_url = "githubusercontent.com" for table in datasets: # Read remote CSV payload into dataframes df = pd.read_csv(f"base_urltable.csv") # Append records directly into SQLite schema df.to_sql(table, conn, if_exists="append", index=False) conn.close() Use code with caution. 📊 Advanced Analytical Queries 1. Cumulative Goalscorers in World Cup History