Jfjelstul Worldcup Data-sqlite Jun 2026

.mode csv .import worldcup.csv worldcup

SELECT m.year, m.home_team || ' vs ' || m.away_team AS match_up, m.attendance, v.stadium FROM matches m JOIN venues v ON m.venue_id = v.venue_id ORDER BY m.attendance DESC LIMIT 5; jfjelstul worldcup data-sqlite

With over 1.5 million data points, searching unindexed tables during multi-table joins will cause performance issues. Speed up query execution by applying strategic indexes to foreign keys and fields that are frequently grouped: With over 1.5 million data points

Because the data is already cleaned and provided in SQLite format, it is an excellent resource for teaching: home_team_score AS goals_for

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