# Beyond Basic SELECTs: Building a PostgreSQL Analytics Layer for a World Cup Game

A football dataset is a convenient way to learn SQL because its entities feel immediately familiar: teams play matches, players score goals, and groups produce standings. However, once we move beyond a few SELECT statements, the exercise starts to resemble a real software engineering problem.

How should a match be represented? Should standings be stored or calculated? How do we prevent duplicate events? Which indexes improve performance without slowing every write? And how can the same database support a statistics website, a tournament simulator, or the analytics screen of a football game?

This tutorial develops a small PostgreSQL analytics layer around a fictional 2026 World Cup dataset. The examples use invented records and focus on reusable database techniques rather than predictions about the actual tournament.

## Start with the Questions the Application Must Answer

Before creating tables, define the queries that the application will need. A useful first version might answer:

Which matches belong to a particular group? How many goals has each team scored and conceded? Which players have scored the most goals? What is the current group ranking? Which match events occurred during a given period? How quickly can the API retrieve a team’s recent results?

This query-first approach helps prevent a common mistake: designing tables that mirror an input file but do not support the product’s actual read patterns.

For a tournament analytics feature, we can begin with four main entities:

teams players matches match\_events

Scores could be derived entirely from goal events, but storing the final score on the match row is often practical. It makes frequent summary queries cheaper while detailed events remain available for audits and deeper analysis.

Design a Schema That Protects the Data

A minimal schema might look like this:

CREATE TABLE teams ( team\_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, team\_name text NOT NULL UNIQUE, group\_code varchar(2) NOT NULL );

CREATE TABLE players ( player\_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, team\_id bigint NOT NULL REFERENCES teams(team\_id), player\_name text NOT NULL, shirt\_number smallint, UNIQUE (team\_id, shirt\_number) );

CREATE TABLE matches ( match\_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, stage text NOT NULL, kickoff\_at timestamptz NOT NULL, home\_team\_id bigint NOT NULL REFERENCES teams(team\_id), away\_team\_id bigint NOT NULL REFERENCES teams(team\_id), home\_score smallint, away\_score smallint, status text NOT NULL DEFAULT 'scheduled', CHECK (home\_team\_id <> away\_team\_id), CHECK (home\_score IS NULL OR home\_score >= 0), CHECK (away\_score IS NULL OR away\_score >= 0), CHECK (status IN ('scheduled', 'live', 'finished', 'cancelled')) );

CREATE TABLE match\_events ( event\_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, match\_id bigint NOT NULL REFERENCES matches(match\_id), team\_id bigint NOT NULL REFERENCES teams(team\_id), player\_id bigint REFERENCES players(player\_id), minute smallint NOT NULL, stoppage\_minute smallint NOT NULL DEFAULT 0, event\_type text NOT NULL, CHECK (minute BETWEEN 0 AND 130), CHECK (stoppage\_minute >= 0), CHECK ( event\_type IN ( 'goal', 'own\_goal', 'yellow\_card', 'red\_card', 'substitution' ) ) );

Several design decisions matter here.

First, timestamptz is preferable to a timezone-free timestamp. An international tournament serves users in many regions, so the database should store an unambiguous moment and let the presentation layer convert it for each user.

Second, constraints move important rules into the database. Even if an API validates scores, another import script may not. A CHECK constraint prevents invalid values regardless of which client performs the write.

Third, a match event references both a match and a team. This makes aggregation convenient, although the application must also verify that the team actually participates in that match. That cross-row rule can be enforced in an ingestion service or, when stronger protection is necessary, with a PostgreSQL trigger.

## Query Match Results Without Losing Teams

A straightforward result query joins the team table twice:

SELECT m.match\_id, home.team\_name AS home\_team, away.team\_name AS away\_team, m.home\_score, m.away\_score, m.kickoff\_at FROM matches AS m JOIN teams AS home ON home.team\_id = m.home\_team\_id JOIN teams AS away ON away.team\_id = m.away\_team\_id WHERE m.status = 'finished' ORDER BY m.kickoff\_at DESC;

Aliases are essential because teams has two different roles in the same row.

Be careful when querying teams with no events. An inner join would silently remove them from the result. For example, a top-scorer report should use a left join if it must include players with zero goals:

SELECT p.player\_id, p.player\_name, t.team\_name, COUNT(e.event\_id) FILTER ( WHERE e.event\_type = 'goal' ) AS goals FROM players AS p JOIN teams AS t ON t.team\_id = p.team\_id LEFT JOIN match\_events AS e ON e.player\_id = p.player\_id GROUP BY p.player\_id, p.player\_name, t.team\_name ORDER BY goals DESC, p.player\_name;

The FILTER clause expresses the aggregation condition clearly and avoids counting unrelated events such as cards or substitutions.

Calculate Team Statistics with a Reusable View

Group standings require each match to be viewed from both teams’ perspectives. A useful technique is to normalize every completed match into two logical rows: one for the home team and one for the away team.

CREATE VIEW team\_match\_results AS SELECT match\_id, home\_team\_id AS team\_id, home\_score AS goals\_for, away\_score AS goals\_against FROM matches WHERE status = 'finished'

UNION ALL

SELECT match\_id, away\_team\_id AS team\_id, away\_score AS goals\_for, home\_score AS goals\_against FROM matches WHERE status = 'finished';

UNION ALL is intentional. We need both rows and do not want PostgreSQL to spend time checking for duplicates.

The view makes the standings query much easier to understand:

SELECT t.team\_name, COUNT(r.match\_id) AS played, COUNT(*) FILTER ( WHERE r.goals\_for > r.goals\_against ) AS won, COUNT(*) FILTER ( WHERE r.goals\_for = r.goals\_against ) AS drawn, COUNT(\*) FILTER ( WHERE r.goals\_for < r.goals\_against ) AS lost, SUM(r.goals\_for) AS goals\_for, SUM(r.goals\_against) AS goals\_against, SUM(r.goals\_for - r.goals\_against) AS goal\_difference, SUM( CASE WHEN r.goals\_for > r.goals\_against THEN 3 WHEN r.goals\_for = r.goals\_against THEN 1 ELSE 0 END ) AS points FROM teams AS t JOIN team\_match\_results AS r ON r.team\_id = t.team\_id WHERE t.group\_code = 'A' GROUP BY t.team\_id, t.team\_name ORDER BY points DESC, goal\_difference DESC, goals\_for DESC, t.team\_name;

This query demonstrates an important SQL principle: reshape complicated data once, then build readable aggregations on top of that representation.

The ordering above is only an application example. Real tournament tie-breaking rules may include additional criteria, so production code should implement the regulations applicable to the competition instead of assuming that points, goal difference, and goals scored are sufficient.

Use Window Functions for Rankings and Timelines

Suppose the application needs to number a player’s goals in chronological order. A window function can calculate that value without collapsing the individual event rows:

SELECT e.event\_id, p.player\_name, m.kickoff\_at, e.minute, ROW\_NUMBER() OVER ( PARTITION BY e.player\_id ORDER BY m.kickoff\_at, e.minute, e.event\_id ) AS player\_goal\_number FROM match\_events AS e JOIN players AS p ON p.player\_id = e.player\_id JOIN matches AS m ON m.match\_id = e.match\_id WHERE e.event\_type = 'goal';

Unlike GROUP BY, a window function preserves each event. This is valuable for achievement systems, commentary timelines, player progression screens, and statistical APIs.

The final event\_id in the ordering is also important. Two events can share the same match minute, so a stable tie-breaker makes the output deterministic.

Treat Imported Data as Untrusted

Public sports datasets often contain inconsistent team names, missing identifiers, duplicated events, or mixed timestamp formats. A reliable ingestion process should not insert raw records directly into production tables.

A safer workflow is:

Import the source into staging tables. Normalize names and timestamps. validate required fields and relationships. Detect duplicates using stable source identifiers. Insert accepted records inside a transaction. Record rejected rows for investigation.

If the import may run repeatedly, use an external identifier and an upsert:

INSERT INTO teams (team\_name, group\_code) VALUES ('Example United', 'A') ON CONFLICT (team\_name) DO UPDATE SET group\_code = EXCLUDED.group\_code;

Idempotent imports are easier to retry after network failures and less likely to duplicate data.

The same caution applies when game data comes from community-created modifications. A mod is content created by the community rather than necessarily by the original developer; directories such as [Play Mod](https://modplaygame.com/) may help researchers discover game-related examples, but compatibility, download sources, file integrity, and the game’s terms should be independently checked before any material is used.

## Add Indexes Based on Real Query Patterns

Indexes should follow measured access patterns rather than be added to every column. For the queries above, reasonable starting points include:

CREATE INDEX idx\_matches\_status\_kickoff ON matches (status, kickoff\_at DESC);

CREATE INDEX idx\_match\_events\_match\_minute ON match\_events (match\_id, minute, stoppage\_minute);

CREATE INDEX idx\_match\_events\_goals\_by\_player ON match\_events (player\_id) WHERE event\_type = 'goal';

The last statement creates a partial index. It contains only goal events, which can make scorer queries smaller and cheaper than indexing every type of event.

Use EXPLAIN (ANALYZE, BUFFERS) with representative data to verify whether PostgreSQL uses an index and whether it actually improves the query. A tiny development database may choose a sequential scan simply because reading the whole table is cheaper.

Remember that every index has a cost. It consumes storage and must be updated during inserts and updates, which matters for live event ingestion.

Keep Derived Scores Consistent

Storing both goal events and final scores introduces intentional duplication. That is acceptable only if the system defines which value is authoritative.

One option is to treat events as the source of truth and calculate scores from them. Another is to accept an official final score feed and use events as descriptive detail. Mixing both approaches without a rule can produce a match that says 2–1 while containing only two recorded goals.

For critical updates, use a transaction:

BEGIN;

UPDATE matches SET home\_score = 2, away\_score = 1, status = 'finished' WHERE match\_id = 42;

\-- Insert or reconcile the corresponding events here.

COMMIT;

A reconciliation job can periodically compare stored scores with aggregated goal events and report mismatches. This is especially useful when events arrive asynchronously or are corrected after a match.

## Conclusion

A World Cup dataset can teach much more than SQL syntax. It provides a compact environment for practicing schema design, constraints, joins, filtered aggregates, views, window functions, imports, indexing, and consistency management.

The central lesson is to design around trustworthy data and concrete application questions. PostgreSQL can calculate sophisticated analytics, but clear rules about identity, validation, authority, and tie-breaking must come first.

Once those foundations are in place, the same database structure can support a learning project, a tournament dashboard, a simulation engine, or a game analytics API without forcing every feature to reinvent the underlying logic.