Tournament Analyst: Advanced SQL Patterns

Master JOINs, window functions, CTEs, and gaps-and-islands in PostgreSQL.

Introduction

30 Second Summary

Every sports fan knows the thrill of a winning streak. But figuring out exactly how long a team's best consecutive run lasted, especially when there are gaps in their history, is surprisingly tricky to answer with data.

In this project, you will build a football World Cup analytical database and write progressively harder SQL queries to answer questions about team performance and participation streaks.

What You'll Build

A PostgreSQL tournament database with 10+ analytical queries that progress from multi-table JOINs through window functions to the gaps-and-islands pattern for detecting consecutive streaks.

By the end of this project, you'll have:

  • A tournament_analyst database with four interrelated tables and meaningful sample data representing 8 teams across 8 tournament editions.
  • A collection of progressively harder analytical queries demonstrating INNER JOIN, window functions, CTEs, and the gaps-and-islands pattern.
  • A reusable mental model for the edition - ROW_NUMBER() trick that you can apply to any consecutive streak problem.
  • Secret Mission: Combine gaps-and-islands with performance analytics to find teams whose longest streak ended right before a performance drop.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, INSERT) and have PostgreSQL installed locally. This project assumes you have psql available in your terminal.

Set Up the Tournament Database

Before you can write analytical queries, you need data to query against. This project uses a football World Cup-style tournament database with teams, tournaments, participation records, and match results.

In this step, you will create a fresh PostgreSQL database, load a schema with four interrelated tables, and verify the sample data is ready for analysis.

In this step, get ready to:
  • Create the tournament_analyst database and connect via psql.
  • Load the schema and sample data from a SQL file.
  • Verify all tables have the expected row counts.
Create the database and connect

You need a dedicated database to keep your tournament data separate from any other PostgreSQL work on your machine. You also need a project folder to store your SQL files.

  • Open your terminal in Ubuntu (WSL).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir tournament-analyst
cd tournament-analyst
  • Confirm the folder exists by running:
ls ~/Desktop

You should see tournament-analyst in the output.

  • Create the database by running this command:
createdb tournament_analyst

What does this command do?

createdb is a PostgreSQL utility that creates a new database on your local server. The name tournament_analyst becomes the database you will connect to for all queries in this project.

  • Connect to your new database by running:
psql -d tournament_analyst

Your terminal prompt should now show tournament_analyst=#. This confirms you are inside the psql interactive shell, connected to the correct database.

Seeing 'database does not exist'?

Make sure you typed the database name exactly as tournament_analyst (with an underscore, not a hyphen).

If you see a connection refused error, confirm your PostgreSQL server is running. Try sudo service postgresql start on Ubuntu/WSL.

Still stuck? Help me troubleshoot my PostgreSQL connection on Ubuntu WSL.

Create the schema and load data

The database is empty right now. You will load a pre-built SQL file that creates four tables and populates them with tournament data spanning 8 editions.

  • Exit psql temporarily by running:
\q
  • Create the setup.sql file in your tournament-analyst folder by opening it in a text editor:
nano setup.sql
  • Paste the following SQL into the file:
-- Tournament Analyst: Database Setup
-- Run with: psql -d tournament_analyst -f setup.sql

-- Clean slate
DROP TABLE IF EXISTS matches CASCADE;
DROP TABLE IF EXISTS participations CASCADE;
DROP TABLE IF EXISTS tournaments CASCADE;
DROP TABLE IF EXISTS teams CASCADE;

-- Create tables
CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(50) NOT NULL,
    confederation VARCHAR(20) NOT NULL
);

CREATE TABLE tournaments (
    tournament_id SERIAL PRIMARY KEY,
    edition INT NOT NULL UNIQUE,
    year INT NOT NULL,
    host_country VARCHAR(50) NOT NULL
);

CREATE TABLE participations (
    participation_id SERIAL PRIMARY KEY,
    team_id INT REFERENCES teams(team_id),
    tournament_id INT REFERENCES tournaments(tournament_id),
    UNIQUE(team_id, tournament_id)
);

CREATE TABLE matches (
    match_id SERIAL PRIMARY KEY,
    tournament_id INT REFERENCES tournaments(tournament_id),
    home_team_id INT REFERENCES teams(team_id),
    away_team_id INT REFERENCES teams(team_id),
    home_score INT NOT NULL DEFAULT 0,
    away_score INT NOT NULL DEFAULT 0,
    stage VARCHAR(20) NOT NULL
);

-- Insert teams (IDs will be 1-8 in order)
INSERT INTO teams (team_name, confederation) VALUES
('Brazil', 'CONMEBOL'),
('Germany', 'UEFA'),
('Argentina', 'CONMEBOL'),
('Italy', 'UEFA'),
('France', 'UEFA'),
('Spain', 'UEFA'),
('Japan', 'AFC'),
('Nigeria', 'CAF');

-- Insert tournaments (8 editions, IDs = edition numbers)
INSERT INTO tournaments (edition, year, host_country) VALUES
(1, 1998, 'France'),
(2, 2002, 'South Korea'),
(3, 2006, 'Germany'),
(4, 2010, 'South Africa'),
(5, 2014, 'Brazil'),
(6, 2018, 'Russia'),
(7, 2022, 'Qatar'),
(8, 2026, 'USA');

-- Insert participations (with deliberate gaps for islands analysis)
-- Brazil (team_id=1): all 8 editions - perfect attendance
INSERT INTO participations (team_id, tournament_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8);

-- Germany (team_id=2): all 8 editions - perfect attendance
INSERT INTO participations (team_id, tournament_id) VALUES
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8);

-- Argentina (team_id=3): editions 1,2,3,5,6,7,8 - gap at edition 4
INSERT INTO participations (team_id, tournament_id) VALUES
(3, 1), (3, 2), (3, 3), (3, 5), (3, 6), (3, 7), (3, 8);

-- Italy (team_id=4): editions 1,2,3,4,6,7 - gap at edition 5, absent 8
INSERT INTO participations (team_id, tournament_id) VALUES
(4, 1), (4, 2), (4, 3), (4, 4), (4, 6), (4, 7);

-- France (team_id=5): editions 1,3,4,5,6,7,8 - gap at edition 2
INSERT INTO participations (team_id, tournament_id) VALUES
(5, 1), (5, 3), (5, 4), (5, 5), (5, 6), (5, 7), (5, 8);

-- Spain (team_id=6): editions 2,3,4,5,7,8 - absent edition 1 and 6
INSERT INTO participations (team_id, tournament_id) VALUES
(6, 2), (6, 3), (6, 4), (6, 5), (6, 7), (6, 8);

-- Japan (team_id=7): editions 2,3,5,6,8 - multiple gaps
INSERT INTO participations (team_id, tournament_id) VALUES
(7, 2), (7, 3), (7, 5), (7, 6), (7, 8);

-- Nigeria (team_id=8): editions 1,2,4,7 - sporadic appearances
INSERT INTO participations (team_id, tournament_id) VALUES
(8, 1), (8, 2), (8, 4), (8, 7);

-- Insert matches (ensuring teams only play in tournaments they participate in)
-- Tournament 1 (1998) - Participants: Brazil, Germany, Argentina, Italy, France, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(1, 1, 4, 3, 1, 'Group'),
(1, 2, 8, 2, 0, 'Group'),
(1, 3, 5, 1, 2, 'Group'),
(1, 1, 5, 0, 3, 'Final');

-- Tournament 2 (2002) - Participants: Brazil, Germany, Argentina, Spain, Japan, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(2, 1, 2, 2, 0, 'Final'),
(2, 7, 6, 1, 1, 'Group'),
(2, 3, 8, 3, 1, 'Group');

-- Tournament 3 (2006) - Participants: Brazil, Germany, Argentina, Italy, France, Spain, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(3, 2, 5, 1, 1, 'Semi'),
(3, 4, 3, 2, 1, 'Group'),
(3, 6, 7, 3, 0, 'Group'),
(3, 4, 5, 1, 1, 'Final');

-- Tournament 4 (2010) - Participants: Brazil, Germany, Italy, France, Spain, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(4, 6, 2, 1, 0, 'Semi'),
(4, 1, 4, 2, 1, 'Quarter'),
(4, 5, 6, 0, 1, 'Final');

-- Tournament 5 (2014) - Participants: Brazil, Germany, Argentina, France, Spain, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(5, 1, 2, 1, 7, 'Semi'),
(5, 3, 5, 0, 1, 'Quarter'),
(5, 6, 7, 2, 0, 'Group');

-- Tournament 6 (2018) - Participants: Brazil, Germany, Argentina, Italy, France, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(6, 5, 3, 4, 3, 'Round of 16'),
(6, 1, 2, 1, 2, 'Quarter'),
(6, 4, 7, 2, 0, 'Group');

-- Tournament 7 (2022) - Participants: Brazil, Germany, Argentina, Italy, France, Spain, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(7, 3, 5, 3, 3, 'Final'),
(7, 1, 6, 1, 0, 'Quarter'),
(7, 2, 8, 4, 1, 'Group');

-- Tournament 8 (2026) - Participants: Brazil, Germany, Argentina, France, Spain, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(8, 1, 3, 2, 2, 'Semi'),
(8, 5, 2, 3, 1, 'Final'),
(8, 6, 7, 1, 2, 'Group');

What is in this file?

This SQL file sets up your entire tournament dataset in one shot:

  • teams stores 8 national football teams with their confederation (UEFA, CONMEBOL, AFC, CAF).
  • tournaments stores 8 tournament editions from 1998 to 2026.
  • participations tracks which teams entered which editions. Some teams have deliberate gaps (missed editions) that you will detect later with the gaps-and-islands pattern.
  • matches records game results with home/away scores and tournament stage.
  • Save and exit nano by pressing Ctrl+O then Enter to write, then Ctrl+X to exit.
  • Reconnect to the database by running:
psql -d tournament_analyst
  • Load the schema and data by running this inside psql:
\i setup.sql

You should see a series of DROP TABLE, CREATE TABLE, and INSERT confirmations scroll past.

Seeing 'No such file or directory'?

The \i command looks for the file relative to the directory where you launched psql. Make sure you ran psql -d tournament_analyst from inside the tournament-analyst folder (where setup.sql lives).

If you connected from a different directory, type \q to exit, cd ~/Desktop/tournament-analyst, and reconnect.

Help me figure out why psql cannot find my setup.sql file.

✔️ Awesome, I've got everything!

Great. Make sure you saw the INSERT confirmations in your terminal before moving on.

ⓧ I'd like to double check the full code

Here is the complete setup.sql file for comparison:

-- Tournament Analyst: Database Setup
-- Run with: psql -d tournament_analyst -f setup.sql

-- Clean slate
DROP TABLE IF EXISTS matches CASCADE;
DROP TABLE IF EXISTS participations CASCADE;
DROP TABLE IF EXISTS tournaments CASCADE;
DROP TABLE IF EXISTS teams CASCADE;

-- Create tables
CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(50) NOT NULL,
    confederation VARCHAR(20) NOT NULL
);

CREATE TABLE tournaments (
    tournament_id SERIAL PRIMARY KEY,
    edition INT NOT NULL UNIQUE,
    year INT NOT NULL,
    host_country VARCHAR(50) NOT NULL
);

CREATE TABLE participations (
    participation_id SERIAL PRIMARY KEY,
    team_id INT REFERENCES teams(team_id),
    tournament_id INT REFERENCES tournaments(tournament_id),
    UNIQUE(team_id, tournament_id)
);

CREATE TABLE matches (
    match_id SERIAL PRIMARY KEY,
    tournament_id INT REFERENCES tournaments(tournament_id),
    home_team_id INT REFERENCES teams(team_id),
    away_team_id INT REFERENCES teams(team_id),
    home_score INT NOT NULL DEFAULT 0,
    away_score INT NOT NULL DEFAULT 0,
    stage VARCHAR(20) NOT NULL
);

-- Insert teams (IDs will be 1-8 in order)
INSERT INTO teams (team_name, confederation) VALUES
('Brazil', 'CONMEBOL'),
('Germany', 'UEFA'),
('Argentina', 'CONMEBOL'),
('Italy', 'UEFA'),
('France', 'UEFA'),
('Spain', 'UEFA'),
('Japan', 'AFC'),
('Nigeria', 'CAF');

-- Insert tournaments (8 editions, IDs = edition numbers)
INSERT INTO tournaments (edition, year, host_country) VALUES
(1, 1998, 'France'),
(2, 2002, 'South Korea'),
(3, 2006, 'Germany'),
(4, 2010, 'South Africa'),
(5, 2014, 'Brazil'),
(6, 2018, 'Russia'),
(7, 2022, 'Qatar'),
(8, 2026, 'USA');

-- Insert participations (with deliberate gaps for islands analysis)
-- Brazil (team_id=1): all 8 editions - perfect attendance
INSERT INTO participations (team_id, tournament_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8);

-- Germany (team_id=2): all 8 editions - perfect attendance
INSERT INTO participations (team_id, tournament_id) VALUES
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8);

-- Argentina (team_id=3): editions 1,2,3,5,6,7,8 - gap at edition 4
INSERT INTO participations (team_id, tournament_id) VALUES
(3, 1), (3, 2), (3, 3), (3, 5), (3, 6), (3, 7), (3, 8);

-- Italy (team_id=4): editions 1,2,3,4,6,7 - gap at edition 5, absent 8
INSERT INTO participations (team_id, tournament_id) VALUES
(4, 1), (4, 2), (4, 3), (4, 4), (4, 6), (4, 7);

-- France (team_id=5): editions 1,3,4,5,6,7,8 - gap at edition 2
INSERT INTO participations (team_id, tournament_id) VALUES
(5, 1), (5, 3), (5, 4), (5, 5), (5, 6), (5, 7), (5, 8);

-- Spain (team_id=6): editions 2,3,4,5,7,8 - absent edition 1 and 6
INSERT INTO participations (team_id, tournament_id) VALUES
(6, 2), (6, 3), (6, 4), (6, 5), (6, 7), (6, 8);

-- Japan (team_id=7): editions 2,3,5,6,8 - multiple gaps
INSERT INTO participations (team_id, tournament_id) VALUES
(7, 2), (7, 3), (7, 5), (7, 6), (7, 8);

-- Nigeria (team_id=8): editions 1,2,4,7 - sporadic appearances
INSERT INTO participations (team_id, tournament_id) VALUES
(8, 1), (8, 2), (8, 4), (8, 7);

-- Insert matches (ensuring teams only play in tournaments they participate in)
-- Tournament 1 (1998) - Participants: Brazil, Germany, Argentina, Italy, France, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(1, 1, 4, 3, 1, 'Group'),
(1, 2, 8, 2, 0, 'Group'),
(1, 3, 5, 1, 2, 'Group'),
(1, 1, 5, 0, 3, 'Final');

-- Tournament 2 (2002) - Participants: Brazil, Germany, Argentina, Spain, Japan, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(2, 1, 2, 2, 0, 'Final'),
(2, 7, 6, 1, 1, 'Group'),
(2, 3, 8, 3, 1, 'Group');

-- Tournament 3 (2006) - Participants: Brazil, Germany, Argentina, Italy, France, Spain, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(3, 2, 5, 1, 1, 'Semi'),
(3, 4, 3, 2, 1, 'Group'),
(3, 6, 7, 3, 0, 'Group'),
(3, 4, 5, 1, 1, 'Final');

-- Tournament 4 (2010) - Participants: Brazil, Germany, Italy, France, Spain, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(4, 6, 2, 1, 0, 'Semi'),
(4, 1, 4, 2, 1, 'Quarter'),
(4, 5, 6, 0, 1, 'Final');

-- Tournament 5 (2014) - Participants: Brazil, Germany, Argentina, France, Spain, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(5, 1, 2, 1, 7, 'Semi'),
(5, 3, 5, 0, 1, 'Quarter'),
(5, 6, 7, 2, 0, 'Group');

-- Tournament 6 (2018) - Participants: Brazil, Germany, Argentina, Italy, France, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(6, 5, 3, 4, 3, 'Round of 16'),
(6, 1, 2, 1, 2, 'Quarter'),
(6, 4, 7, 2, 0, 'Group');

-- Tournament 7 (2022) - Participants: Brazil, Germany, Argentina, Italy, France, Spain, Nigeria
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(7, 3, 5, 3, 3, 'Final'),
(7, 1, 6, 1, 0, 'Quarter'),
(7, 2, 8, 4, 1, 'Group');

-- Tournament 8 (2026) - Participants: Brazil, Germany, Argentina, France, Spain, Japan
INSERT INTO matches (tournament_id, home_team_id, away_team_id, home_score, away_score, stage) VALUES
(8, 1, 3, 2, 2, 'Semi'),
(8, 5, 2, 3, 1, 'Final'),
(8, 6, 7, 1, 2, 'Group');
Verify the data

A quick count on each table confirms the data loaded correctly. Each count maps to the exact number of rows the INSERT statements created.

  • Run these four count queries inside psql:
SELECT COUNT(*) FROM teams;
SELECT COUNT(*) FROM tournaments;
SELECT COUNT(*) FROM participations;
SELECT COUNT(*) FROM matches;

What should I see?

  • teams returns 8 (one per national team).
  • tournaments returns 8 (one per edition from 1998 to 2026).
  • participations returns 50 (total entries across all teams and editions).
  • matches returns 25 (total recorded games).

Counts don't match?

If you see zero rows, the \i setup.sql command may not have run successfully. Scroll up in your terminal to check for error messages during the import.

If you see a syntax error, make sure you copied the entire setup.sql file without missing the final semicolons. Re-save the file and run \i setup.sql again (the DROP statements at the top make it safe to re-run).

Help me debug why my table counts are wrong after running setup.sql.

Your database is loaded and verified. Next up, you will write multi-table JOINs to pull readable match results from across these four tables.

Write Multi-Table JOINs and Aggregations

Your tournament database is loaded with 8 teams, 8 editions, and 25 matches spread across separate tables. Now it's time to ask analytical questions of this data.

The challenge with relational databases is that related information lives in different tables. A match record stores team IDs, not team names. To produce human-readable results, you need JOINs to pull data from multiple tables into a single result set.

In this step, get ready to:
  • Write a multi-table JOIN with a self-join to display match results with team names.
  • Use GROUP BY with HAVING to filter aggregated results.
  • Use LEFT JOIN to include all teams regardless of participation count.
JOIN multiple tables to produce readable match results

The matches table stores home_team_id and away_team_id, but you want to see "Brazil vs Germany", not "1 vs 2". You need to JOIN the teams table twice, once for each side of the match.

  • Run this query in your psql session to show every match with team names, scores, and tournament year:
SELECT
    t.year,
    home.team_name AS home_team,
    m.home_score,
    m.away_score,
    away.team_name AS away_team,
    m.stage
FROM matches m
INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
INNER JOIN teams home ON m.home_team_id = home.team_id
INNER JOIN teams away ON m.away_team_id = away.team_id
ORDER BY t.year, m.match_id;

What does this query do?

  • INNER JOIN tournaments t brings in the tournament year for each match.
  • INNER JOIN teams home joins the teams table with the alias home to look up the home team's name.
  • INNER JOIN teams away joins the same table again with a different alias to look up the away team's name.
  • You can JOIN the same table multiple times with different aliases. Think of each JOIN as "bring in one piece of related info."
  • You should see 25 rows showing every match with readable team names, scores, and tournament stages ordered by year.

Seeing an error about column names?

Make sure you use the aliases exactly as shown. The alias after each JOIN defines how you reference that table's columns.

If you see a "column reference is ambiguous" error, it means you referenced a column like team_name without specifying which alias (home or away) it belongs to.

Help me fix my multi-table JOIN query

Use GROUP BY with HAVING to filter aggregated results

Now that you can read individual matches, the next question is: which teams played the most matches? GROUP BY collapses rows into groups, and HAVING filters those groups after aggregation.

  • Count how many matches each team played, filtering to only teams with more than 5 matches by running this query:
SELECT
    te.team_name,
    COUNT(*) AS matches_played
FROM teams te
INNER JOIN matches m
    ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
GROUP BY te.team_name
HAVING COUNT(*) > 5
ORDER BY matches_played DESC;

What does this query do?

  • The ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id condition matches a team to any match where they played, whether home or away.
  • GROUP BY te.team_name collapses all of a team's match rows into a single group.
  • HAVING COUNT(*) > 5 filters out teams with 5 or fewer matches after the count is computed.

WHERE filters rows before grouping. HAVING filters groups after aggregation. If your condition uses an aggregate function, it belongs in HAVING.

  • You should see a short list of teams that appeared in more than 5 matches. Brazil, Germany, and France should be near the top.

Query returns no rows?

Check that you used HAVING (not WHERE) for the count filter. Placing COUNT(*) > 5 in a WHERE clause produces an error because aggregates cannot appear there.

Help me understand WHERE vs HAVING

Use LEFT JOIN to include all teams regardless of participation count

INNER JOIN drops rows that have no match on the other side. LEFT JOIN keeps every row from the left table, filling NULLs where no match exists on the right.

  • Show every team's participation count by running this query:
SELECT
    te.team_name,
    te.confederation,
    COUNT(p.participation_id) AS tournaments_entered
FROM teams te
LEFT JOIN participations p ON te.team_id = p.team_id
GROUP BY te.team_name, te.confederation
ORDER BY tournaments_entered DESC;

Why COUNT(p.participation_id) instead of COUNT(*)?

COUNT(*) counts every row, including those with NULLs from the LEFT JOIN. COUNT(p.participation_id) only counts non-NULL values. If a team had zero participations, the LEFT JOIN produces one row with NULL in the participation columns. COUNT(column) correctly returns 0 for that team.

  • You should see all 8 teams listed with their confederation and tournament count. Brazil and Germany should show 8 (perfect attendance). Nigeria should show 4.

All teams show the same count?

Make sure you used LEFT JOIN (not INNER JOIN). Also verify you are counting p.participation_id rather than *. Using COUNT(*) with a LEFT JOIN inflates results for teams with no matches.

Help me understand LEFT JOIN vs INNER JOIN

Save your queries to a file

You have three working analytical queries. Save them together in a file so you can reference and rerun them later.

  • Create a file called step2_joins.sql in the same directory as your setup.sql file.
  • Paste all three queries into the file with comments separating each one.

Your file should contain:

-- Step 2: Multi-Table JOINs and Aggregations

-- Query 1: Match results with team names (multi-table JOIN with self-join)
SELECT
    t.year,
    home.team_name AS home_team,
    m.home_score,
    m.away_score,
    away.team_name AS away_team,
    m.stage
FROM matches m
INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
INNER JOIN teams home ON m.home_team_id = home.team_id
INNER JOIN teams away ON m.away_team_id = away.team_id
ORDER BY t.year, m.match_id;

-- Query 2: Matches played per team, filtered with HAVING
SELECT
    te.team_name,
    COUNT(*) AS matches_played
FROM teams te
INNER JOIN matches m
    ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
GROUP BY te.team_name
HAVING COUNT(*) > 5
ORDER BY matches_played DESC;

-- Query 3: LEFT JOIN - participation count for all teams
SELECT
    te.team_name,
    te.confederation,
    COUNT(p.participation_id) AS tournaments_entered
FROM teams te
LEFT JOIN participations p ON te.team_id = p.team_id
GROUP BY te.team_name, te.confederation
ORDER BY tournaments_entered DESC;

What does this file give you?

This file is a reusable reference of your JOIN and aggregation queries. You can rerun the entire file at any time with the psql command shown below.

  • Save step2_joins.sql.
  • Verify it works by running it from your psql session:
\i step2_joins.sql
  • You should see all three query results print in sequence. The first shows 25 match rows, the second shows teams with more than 5 matches, and the third shows all 8 teams with their participation counts.

Seeing a "No such file or directory" error?

The \i command looks for the file relative to where you launched psql. Make sure step2_joins.sql is in the same directory you ran psql from. You can check your current directory with \! pwd inside psql.

Help me run a SQL file in psql

✔️ Awesome, I've got everything!

Great. Make sure you've saved step2_joins.sql and all three queries ran successfully.

ⓧ I'd like to double check the full code

Here is the complete step2_joins.sql file for comparison:

-- Step 2: Multi-Table JOINs and Aggregations

-- Query 1: Match results with team names (multi-table JOIN with self-join)
SELECT
    t.year,
    home.team_name AS home_team,
    m.home_score,
    m.away_score,
    away.team_name AS away_team,
    m.stage
FROM matches m
INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
INNER JOIN teams home ON m.home_team_id = home.team_id
INNER JOIN teams away ON m.away_team_id = away.team_id
ORDER BY t.year, m.match_id;

-- Query 2: Matches played per team, filtered with HAVING
SELECT
    te.team_name,
    COUNT(*) AS matches_played
FROM teams te
INNER JOIN matches m
    ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
GROUP BY te.team_name
HAVING COUNT(*) > 5
ORDER BY matches_played DESC;

-- Query 3: LEFT JOIN - participation count for all teams
SELECT
    te.team_name,
    te.confederation,
    COUNT(p.participation_id) AS tournaments_entered
FROM teams te
LEFT JOIN participations p ON te.team_id = p.team_id
GROUP BY te.team_name, te.confederation
ORDER BY tournaments_entered DESC;

You now have three fundamental query patterns in your toolkit: multi-table JOINs, GROUP BY with HAVING, and LEFT JOIN for inclusive results. Next up, you'll use window functions to number, rank, and compare rows without collapsing them into groups.

Apply Window Functions

Your JOIN queries from the previous step pulled data from multiple tables and collapsed rows with GROUP BY. That collapsing is powerful, but sometimes you want to compute a value across related rows without losing the individual rows.

Window functions solve this. They let you peek at neighbouring rows, assign rankings, and compute running totals while keeping every row intact in the result set. In this step, you will use three window functions that build directly toward the gaps-and-islands technique in Step 5.

In this step, get ready to:
  • Number each team's tournament appearances chronologically with ROW_NUMBER.
  • Detect gaps between consecutive appearances using LAG.
  • Compare ROW_NUMBER, RANK, and DENSE_RANK side by side.
Use ROW_NUMBER and PARTITION BY to number appearances

A window function applies a calculation across a set of rows related to the current row. Unlike GROUP BY, it does not collapse multiple rows into one.

  • Run this query in your psql session to number each team's tournament appearances in chronological order:
SELECT
    te.team_name,
    t.edition,
    t.year,
    ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS appearance_number
FROM participations p
JOIN teams te ON p.team_id = te.team_id
JOIN tournaments t ON p.tournament_id = t.tournament_id
ORDER BY te.team_name, t.edition;

What does this query do?

PARTITION BY te.team_name tells PostgreSQL to restart the count for each team. Think of it as drawing a boundary around each team's rows.

ORDER BY t.edition inside the OVER() clause tells it which sequence to count in. Together they say: for each team, number their appearances from 1, in edition order.

You should see Brazil and Germany both counting up to 8 (perfect attendance). Argentina counts to 7. Japan counts to 5.

Debug trick for window functions

Always SELECT intermediate window function columns when building complex queries. Seeing ROW_NUMBER values side-by-side with your data makes the pattern visible.

Seeing a syntax error?

Make sure the OVER keyword comes immediately after ROW_NUMBER() with no comma between them. The OVER() clause is part of the window function call, not a separate column.

help me fix a window function syntax error in my ROW_NUMBER query

Use LAG to detect gaps between appearances

ROW_NUMBER told you how many times a team appeared. Now you need to know when they skipped an edition. The LAG function looks at the previous row within the same partition.

  • Run this query to show each team's previous tournament edition and the size of any gap:
SELECT
    te.team_name,
    t.edition AS current_edition,
    LAG(t.edition) OVER (PARTITION BY te.team_name ORDER BY t.edition) AS prev_edition,
    t.edition - LAG(t.edition) OVER (PARTITION BY te.team_name ORDER BY t.edition) AS gap_size
FROM participations p
JOIN teams te ON p.team_id = te.team_id
JOIN tournaments t ON p.tournament_id = t.tournament_id
ORDER BY te.team_name, t.edition;

What does this query do?

LAG(t.edition) grabs the edition value from the previous row (within the same team's partition). The first row for each team returns NULL because there is no previous row.

The gap_size column subtracts the previous edition from the current one. Where gap_size is 1, the team participated in consecutive editions. Where it is greater than 1, there is a gap.

Look at Argentina's results. You should see gap_size = 1 for editions 1 through 3, then gap_size = 2 between editions 3 and 5 (they missed edition 4). This is your first glimpse at detecting streaks.

Seeing NULL for every gap_size?

If every row shows NULL, check that your ORDER BY inside the OVER() clause uses t.edition. Without a defined order, LAG cannot determine which row is "previous".

help me debug my LAG window function

Compare RANK, DENSE_RANK, and ROW_NUMBER

PostgreSQL offers three ranking functions. They behave identically until two rows tie for the same position. Understanding the difference helps you choose the right one for your use case.

  • Run this query to compare all three ranking functions on teams ranked by participation count:
SELECT
    te.team_name,
    COUNT(p.participation_id) AS participations,
    ROW_NUMBER() OVER (ORDER BY COUNT(p.participation_id) DESC) AS row_num,
    RANK() OVER (ORDER BY COUNT(p.participation_id) DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY COUNT(p.participation_id) DESC) AS dense_rank
FROM teams te
JOIN participations p ON te.team_id = p.team_id
GROUP BY te.team_name
ORDER BY participations DESC;

How do the three functions differ?

  • ROW_NUMBER always gives unique numbers. When two teams tie, it picks one arbitrarily to go first.
  • RANK gives the same number to ties, then skips. If two teams share rank 3, the next team gets rank 5 (not 4).
  • DENSE_RANK gives the same number to ties without skipping. If two teams share rank 3, the next team gets rank 4.

Notice that Brazil and Germany both have 8 participations. Look at how row_num assigns them 1 and 2, while rank and dense_rank both assign them 1.

Choosing the right ranking function

Use ROW_NUMBER when you need "position in a list" (e.g., pick the top 3 rows). Use RANK or DENSE_RANK when you need "how many are better than me" (e.g., medal standings where ties share a place).

Getting an error about aggregates and window functions?

Make sure you have the GROUP BY te.team_name clause before the ORDER BY. Window functions execute after GROUP BY in the query processing order, which is why you can use COUNT() inside the OVER() clause.

help me fix my ranking query

  • Save all three queries to a file called step3_windows.sql for reference. Create this file in the same directory as your setup.sql and step2_joins.sql.

✔️ Awesome, I've got everything!

Great. Make sure you have saved step3_windows.sql with all three window function queries.

ⓧ I'd like to double check the full code

Here is the complete step3_windows.sql file:

-- Step 3: Window Functions

-- Query 1: ROW_NUMBER with PARTITION BY
SELECT
    te.team_name,
    t.edition,
    t.year,
    ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS appearance_number
FROM participations p
JOIN teams te ON p.team_id = te.team_id
JOIN tournaments t ON p.tournament_id = t.tournament_id
ORDER BY te.team_name, t.edition;

-- Query 2: LAG to detect gaps between appearances
SELECT
    te.team_name,
    t.edition AS current_edition,
    LAG(t.edition) OVER (PARTITION BY te.team_name ORDER BY t.edition) AS prev_edition,
    t.edition - LAG(t.edition) OVER (PARTITION BY te.team_name ORDER BY t.edition) AS gap_size
FROM participations p
JOIN teams te ON p.team_id = te.team_id
JOIN tournaments t ON p.tournament_id = t.tournament_id
ORDER BY te.team_name, t.edition;

-- Query 3: ROW_NUMBER vs RANK vs DENSE_RANK comparison
SELECT
    te.team_name,
    COUNT(p.participation_id) AS participations,
    ROW_NUMBER() OVER (ORDER BY COUNT(p.participation_id) DESC) AS row_num,
    RANK() OVER (ORDER BY COUNT(p.participation_id) DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY COUNT(p.participation_id) DESC) AS dense_rank
FROM teams te
JOIN participations p ON te.team_id = p.team_id
GROUP BY te.team_name
ORDER BY participations DESC;

You now have three window functions in your toolkit: ROW_NUMBER for sequential numbering, LAG for peeking at previous rows, and RANK/DENSE_RANK for handling ties. Next up, you will chain multiple queries together using Common Table Expressions to build complex analyses step by step.

Build Queries with CTEs

In the last step, you used window functions to number appearances, detect gaps with LAG, and compare ranking strategies.

Now you need to answer a harder question: what is each team's total goal output across all tournaments, and how does their best single-tournament performance compare to their average? Answering this requires computing intermediate results and building on top of them.

Common Table Expressions (CTEs) let you name your intermediate steps like a recipe. First you compute X, then you use X to compute Y, then you present Y.

In this step, get ready to:
  • Chain multiple CTEs to compute team performance summaries step-by-step.
  • Compare early vs late era performance using FULL OUTER JOIN and COALESCE.
Chain CTEs to build complex analysis step-by-step

You want to know each team's total goals, their best single-tournament performance, and their average goals per tournament. A single query would require deeply nested subqueries that are impossible to debug.

What is a CTE?

A CTE is a temporary named result set defined with the WITH keyword. It exists only for the duration of the query.

The trick is to write the query you WISH you had first, then make it a CTE. Each CTE is independently testable, so you can verify intermediate results before building on top.

  • Run this query in your psql session to compute each team's goal summary using chained CTEs:
-- Chained CTEs - team performance summary
WITH team_goals_per_tournament AS (
    SELECT
        te.team_id,
        te.team_name,
        t.edition,
        SUM(CASE
            WHEN m.home_team_id = te.team_id THEN m.home_score
            WHEN m.away_team_id = te.team_id THEN m.away_score
        END) AS goals
    FROM teams te
    INNER JOIN matches m
        ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
    INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
    GROUP BY te.team_id, te.team_name, t.edition
),
team_summary AS (
    SELECT
        team_name,
        SUM(goals) AS total_goals,
        MAX(goals) AS best_tournament_goals,
        COUNT(*) AS tournaments_with_matches
    FROM team_goals_per_tournament
    GROUP BY team_name
)
SELECT
    team_name,
    total_goals,
    best_tournament_goals,
    ROUND(total_goals::numeric / tournaments_with_matches, 1) AS avg_goals_per_tournament
FROM team_summary
ORDER BY total_goals DESC;

What does this query do?

This query chains two CTEs together, each building on the last.

  • team_goals_per_tournament uses a CASE expression to figure out whether the team was home or away, then sums their score from the correct column.
  • team_summary references the first CTE by name and aggregates goals into totals, maximums, and counts.
  • The final SELECT computes the average using the ::numeric cast to get decimal division instead of integer division.

You should see a table with all 8 teams ranked by total goals, with Brazil near the top.

Test CTEs in isolation

You can run just the first CTE to verify intermediate results. Replace everything after the first CTE with SELECT * FROM team_goals_per_tournament; to see what it produces before building on top.

Seeing a division by zero error?

Make sure you have match data loaded. If tournaments_with_matches is 0, the division fails. Run SELECT COUNT(*) FROM matches; to confirm you have 25 rows.

Help me debug my CTE query

Compare early vs late era performance using multiple CTEs

Now for a harder question: are teams getting better or worse over time? You need to split tournaments into two eras, compute averages for each, and compare them side-by-side.

  • Run this query to compare each team's early era (editions 1-4) vs late era (editions 5-8) goal averages:
-- Early vs late era comparison
WITH team_goals_per_tournament AS (
    SELECT
        te.team_id,
        te.team_name,
        t.edition,
        SUM(CASE
            WHEN m.home_team_id = te.team_id THEN m.home_score
            WHEN m.away_team_id = te.team_id THEN m.away_score
        END) AS goals
    FROM teams te
    INNER JOIN matches m
        ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
    INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
    GROUP BY te.team_id, te.team_name, t.edition
),
early_era AS (
    SELECT team_name, ROUND(AVG(goals), 1) AS avg_goals_early
    FROM team_goals_per_tournament
    WHERE edition <= 4
    GROUP BY team_name
),
late_era AS (
    SELECT team_name, ROUND(AVG(goals), 1) AS avg_goals_late
    FROM team_goals_per_tournament
    WHERE edition > 4
    GROUP BY team_name
)
SELECT
    COALESCE(e.team_name, l.team_name) AS team_name,
    COALESCE(e.avg_goals_early, 0) AS avg_goals_editions_1_to_4,
    COALESCE(l.avg_goals_late, 0) AS avg_goals_editions_5_to_8,
    CASE
        WHEN COALESCE(l.avg_goals_late, 0) > COALESCE(e.avg_goals_early, 0) THEN 'Improving'
        WHEN COALESCE(l.avg_goals_late, 0) < COALESCE(e.avg_goals_early, 0) THEN 'Declining'
        ELSE 'Stable'
    END AS trend
FROM early_era e
FULL OUTER JOIN late_era l ON e.team_name = l.team_name
ORDER BY team_name;

What does this query do?

  • team_goals_per_tournament is reused from the previous query. It computes goals per team per edition using the same CASE home/away pattern.
  • early_era and late_era each filter to their half of the editions and compute averages.
  • FULL OUTER JOIN keeps teams even if they only appeared in one era. COALESCE fills NULLs with 0 so the comparison logic works cleanly.
  • The final CASE labels each team as Improving, Declining, or Stable based on whether their late-era average exceeds their early-era average.

You should see each team with their two era averages and a trend label. Teams like Nigeria that only played in the early era will show 0 for the late era column.

Why FULL OUTER JOIN instead of INNER JOIN?

Some teams only have matches in one era. An INNER JOIN would silently drop them from the results. FULL OUTER JOIN preserves all rows from both sides, letting you see teams that are absent from one era.

Seeing NULL instead of 0 in the output?

Make sure you wrapped both era columns in COALESCE(..., 0). Without it, teams missing from one era show NULL instead of 0, and the CASE comparison produces unexpected results.

Help me fix NULLs in my era comparison query

  • Save both queries to a file called step4_ctes.sql for reference.

✔️ Awesome, I've got everything!

Great. Double check you have saved your step4_ctes.sql file.

ⓧ I'd like to double check the full code

Here is the complete step4_ctes.sql file with both queries:

-- Step 4: Common Table Expressions (CTEs)

-- Query 1: Chained CTEs - team performance summary
WITH team_goals_per_tournament AS (
    SELECT
        te.team_id,
        te.team_name,
        t.edition,
        SUM(CASE
            WHEN m.home_team_id = te.team_id THEN m.home_score
            WHEN m.away_team_id = te.team_id THEN m.away_score
        END) AS goals
    FROM teams te
    INNER JOIN matches m
        ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
    INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
    GROUP BY te.team_id, te.team_name, t.edition
),
team_summary AS (
    SELECT
        team_name,
        SUM(goals) AS total_goals,
        MAX(goals) AS best_tournament_goals,
        COUNT(*) AS tournaments_with_matches
    FROM team_goals_per_tournament
    GROUP BY team_name
)
SELECT
    team_name,
    total_goals,
    best_tournament_goals,
    ROUND(total_goals::numeric / tournaments_with_matches, 1) AS avg_goals_per_tournament
FROM team_summary
ORDER BY total_goals DESC;

-- Query 2: Early vs late era comparison
WITH team_goals_per_tournament AS (
    SELECT
        te.team_id,
        te.team_name,
        t.edition,
        SUM(CASE
            WHEN m.home_team_id = te.team_id THEN m.home_score
            WHEN m.away_team_id = te.team_id THEN m.away_score
        END) AS goals
    FROM teams te
    INNER JOIN matches m
        ON te.team_id = m.home_team_id OR te.team_id = m.away_team_id
    INNER JOIN tournaments t ON m.tournament_id = t.tournament_id
    GROUP BY te.team_id, te.team_name, t.edition
),
early_era AS (
    SELECT team_name, ROUND(AVG(goals), 1) AS avg_goals_early
    FROM team_goals_per_tournament
    WHERE edition <= 4
    GROUP BY team_name
),
late_era AS (
    SELECT team_name, ROUND(AVG(goals), 1) AS avg_goals_late
    FROM team_goals_per_tournament
    WHERE edition > 4
    GROUP BY team_name
)
SELECT
    COALESCE(e.team_name, l.team_name) AS team_name,
    COALESCE(e.avg_goals_early, 0) AS avg_goals_editions_1_to_4,
    COALESCE(l.avg_goals_late, 0) AS avg_goals_editions_5_to_8,
    CASE
        WHEN COALESCE(l.avg_goals_late, 0) > COALESCE(e.avg_goals_early, 0) THEN 'Improving'
        WHEN COALESCE(l.avg_goals_late, 0) < COALESCE(e.avg_goals_early, 0) THEN 'Declining'
        ELSE 'Stable'
    END AS trend
FROM early_era e
FULL OUTER JOIN late_era l ON e.team_name = l.team_name
ORDER BY team_name;

You now have the building blocks for any complex analytical query: break it into named steps with CTEs, test each one, and compose them together. Next up, you will use this exact skill to solve the hardest pattern in the project: detecting consecutive participation streaks with gaps-and-islands.

Master the Gaps-and-Islands Pattern

In the previous step, you chained CTEs to build complex analyses from simple building blocks. Now you have every skill you need for the grand finale.

The gaps-and-islands pattern detects consecutive streaks in data. It answers questions like "which teams participated in consecutive tournament editions without a break?" The core trick is a single arithmetic expression: edition - ROW_NUMBER(). You will see exactly why this works in your first query.

In this step, get ready to:
  • Compute island_key values using edition minus ROW_NUMBER to reveal streak groupings.
  • Wrap the island key logic in a CTE and GROUP BY to extract each streak's start, end, and length.
  • Layer a second CTE to find each team's longest consecutive participation streak.
See the magic with edition minus ROW_NUMBER()

The gaps-and-islands pattern relies on a mathematical property. Within a consecutive run of editions, both the edition value and ROW_NUMBER() increase by exactly 1 per row. Their difference stays constant. The moment there is a gap, the difference changes.

  • Run this query in your psql session and study the island_key column:
-- Query 1: See the magic - edition minus ROW_NUMBER produces island keys
SELECT
    te.team_name,
    t.edition,
    ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS rn,
    t.edition - ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS island_key
FROM participations p
JOIN teams te ON p.team_id = te.team_id
JOIN tournaments t ON p.tournament_id = t.tournament_id
ORDER BY te.team_name, t.edition;

What does this query do?

Look at Argentina's rows. Editions 1, 2, 3 produce ROW_NUMBERs 1, 2, 3. The difference (island_key) is 0 for all three. That constant value means they belong to the same streak.

Then Argentina skips edition 4. Their next appearance is edition 5, but ROW_NUMBER is only 4. The difference jumps to 1. Editions 5, 6, 7, 8 all share island_key = 1. Two different constants means two different streaks.

  • Check your output for Argentina. You should see island_key = 0 for editions 1-3, and island_key = 1 for editions 5-8.
  • Check Brazil and Germany. They participated in all 8 editions, so their island_key is 0 for every row (one continuous streak).

Seeing unexpected island_key values?

Make sure your PARTITION BY uses te.team_name and your ORDER BY inside the OVER clause uses t.edition. Swapping these produces wrong groupings.

Also confirm the subtraction is t.edition - ROW_NUMBER() (not the other way around). The order matters for consistent grouping keys.

Help me debug my island_key query

Why does this work mathematically?

Think of it this way. In a consecutive run, edition goes 3, 4, 5 while ROW_NUMBER goes 1, 2, 3. Both increment by 1 each row, so the difference (2, 2, 2) stays constant. That constant becomes your grouping key.

When a gap appears (edition jumps from 3 to 5 while ROW_NUMBER only goes from 3 to 4), the difference shifts. A new constant begins. Each constant identifies one "island" of consecutive participation.

Group by the island key to extract streaks

Now that you can see the island keys, the next step is to GROUP BY them. This collapses each streak into a single summary row showing where it starts, where it ends, and how long it is.

  • Run this query to wrap the island key logic in a CTE and aggregate each streak:
-- Query 2: Group by island key to extract streaks
WITH numbered AS (
    SELECT
        te.team_name,
        t.edition,
        t.edition - ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS island_key
    FROM participations p
    JOIN teams te ON p.team_id = te.team_id
    JOIN tournaments t ON p.tournament_id = t.tournament_id
)
SELECT
    team_name,
    MIN(edition) AS streak_start,
    MAX(edition) AS streak_end,
    COUNT(*) AS streak_length
FROM numbered
GROUP BY team_name, island_key
ORDER BY team_name, streak_start;

What does this query do?

  • The numbered CTE computes the island_key for every participation row (same logic as Query 1).
  • The outer SELECT groups by both team_name and island_key. Each group IS one streak.
  • MIN(edition) gives the streak's first edition, MAX(edition) gives the last, and COUNT(*) gives the length.
  • Verify the results against these expected streaks:
  • Brazil and Germany each show one streak of length 8 (perfect attendance).
  • Argentina shows streaks of 3 (editions 1-3) and 4 (editions 5-8).
  • Japan shows streaks of 2 (editions 2-3), 2 (editions 5-6), and 1 (edition 8).

Results don't match expected streaks?

If you see only one row per team, check that your GROUP BY includes both team_name and island_key. Without island_key in the GROUP BY, all of a team's participations collapse into one row.

If streak lengths seem wrong, run Query 1 first and manually verify the island_key values for that team. The CTE should produce identical island_key values to Query 1.

Help me debug my streak grouping query

Find each team's longest streak

The final query adds one more layer. You already have all streaks for all teams. Now you need to filter down to just the longest streak per team.

  • Run this two-CTE query that finds each team's longest consecutive participation streak:
-- Query 3: Find each team's longest streak
WITH numbered AS (
    SELECT
        te.team_name,
        t.edition,
        t.edition - ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS island_key
    FROM participations p
    JOIN teams te ON p.team_id = te.team_id
    JOIN tournaments t ON p.tournament_id = t.tournament_id
),
streaks AS (
    SELECT
        team_name,
        MIN(edition) AS streak_start,
        MAX(edition) AS streak_end,
        COUNT(*) AS streak_length
    FROM numbered
    GROUP BY team_name, island_key
)
SELECT
    team_name,
    streak_start,
    streak_end,
    streak_length
FROM streaks
WHERE (team_name, streak_length) IN (
    SELECT team_name, MAX(streak_length)
    FROM streaks
    GROUP BY team_name
)
ORDER BY streak_length DESC, team_name;

What does this query do?

  • The numbered CTE computes island keys (same as before).
  • The streaks CTE groups by island key to produce one row per streak (same as Query 2).
  • The final SELECT uses a subquery in the WHERE clause to keep only streaks matching each team's maximum length. The IN clause compares a tuple of (team_name, streak_length) against each team's max.
  • Check your output. Brazil and Germany should appear at the top with a streak_length of 8. Argentina should show a streak of 4 (editions 5-8). Japan should show 2.

Getting a syntax error near WHERE?

Make sure the comma between the two CTEs (numbered and streaks) is present. Each CTE definition is separated by a comma, with no extra WITH keyword.

Also confirm the parentheses around (team_name, streak_length) in the WHERE clause. The tuple comparison requires those outer parentheses.

Help me fix my longest-streak query

  • Save all three queries to a file called step5_islands.sql in the same directory as your other SQL files.

Where can I reuse this pattern?

The value - ROW_NUMBER() OVER (PARTITION BY group ORDER BY value) trick works for any "find consecutive X" problem. Consecutive login days, consecutive months with revenue, consecutive editions participated in. The sequence column and partition column change, but the structure is identical.

✔️ Awesome, I've got everything!

Great. Double check you have saved your step5_islands.sql file with all three queries.

ⓧ I'd like to double check the full code

Here is the complete step5_islands.sql file with all three queries:

-- Step 5: The Gaps-and-Islands Pattern

-- Query 1: See the magic - edition minus ROW_NUMBER produces island keys
SELECT
    te.team_name,
    t.edition,
    ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS rn,
    t.edition - ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS island_key
FROM participations p
JOIN teams te ON p.team_id = te.team_id
JOIN tournaments t ON p.tournament_id = t.tournament_id
ORDER BY te.team_name, t.edition;

-- Query 2: Group by island key to extract streaks
WITH numbered AS (
    SELECT
        te.team_name,
        t.edition,
        t.edition - ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS island_key
    FROM participations p
    JOIN teams te ON p.team_id = te.team_id
    JOIN tournaments t ON p.tournament_id = t.tournament_id
)
SELECT
    team_name,
    MIN(edition) AS streak_start,
    MAX(edition) AS streak_end,
    COUNT(*) AS streak_length
FROM numbered
GROUP BY team_name, island_key
ORDER BY team_name, streak_start;

-- Query 3: Find each team's longest streak
WITH numbered AS (
    SELECT
        te.team_name,
        t.edition,
        t.edition - ROW_NUMBER() OVER (PARTITION BY te.team_name ORDER BY t.edition) AS island_key
    FROM participations p
    JOIN teams te ON p.team_id = te.team_id
    JOIN tournaments t ON p.tournament_id = t.tournament_id
),
streaks AS (
    SELECT
        team_name,
        MIN(edition) AS streak_start,
        MAX(edition) AS streak_end,
        COUNT(*) AS streak_length
    FROM numbered
    GROUP BY team_name, island_key
)
SELECT
    team_name,
    streak_start,
    streak_end,
    streak_length
FROM streaks
WHERE (team_name, streak_length) IN (
    SELECT team_name, MAX(streak_length)
    FROM streaks
    GROUP BY team_name
)
ORDER BY streak_length DESC, team_name;

Secret mission

Streak Endings and Performance Drops

You have mastered gaps-and-islands individually. Now combine it with the CASE-based goal aggregation from Step 4 to answer a harder question: when a team's participation streak breaks, does their scoring performance drop too?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The tournament_analyst PostgreSQL database (4 tables with sample data).
  • SQL query files: setup.sql, step2_joins.sql, step3_windows.sql, step4_ctes.sql, step5_islands.sql, secret_mission.sql.

Keep everything running

No action needed. Choose this if you plan to keep experimenting with the database or writing more queries against the tournament data.

  • Your tournament_analyst database stays available whenever you connect with psql.
  • Your SQL files remain on disk as a reference for future projects.

Pause - I'll come back to this later

Free up active connections but keep your database and files intact for later.

  • Exit your psql session by running:
  • \q
  • Your database and SQL files remain on disk. Reconnect any time with psql -d tournament_analyst.

Delete - I don't want to use this again

Remove the database and all project files completely.

  • Exit your psql session first by running:
\q
  • Drop the tournament_analyst database by running:
dropdb tournament_analyst
  • Delete your SQL files by running:
rm setup.sql step2_joins.sql step3_windows.sql step4_ctes.sql step5_islands.sql secret_mission.sql
  • Confirm the files are gone by running:
ls *.sql

You should see a "No such file or directory" message or no output. The database and all query files are now removed.

Nice Work!

Nice Work!

You just went from basic JOINs all the way to the gaps-and-islands pattern, one of SQL's most elegant analytical techniques. You now have a reusable mental model for detecting consecutive streaks in any sequential dataset.

You've learned how to:

  • Write multi-table JOINs including self-joins on the same table with aliases, and filter aggregated groups with HAVING.
  • Apply window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG) with PARTITION BY to compute per-group rankings and detect gaps.
  • Master the gaps-and-islands pattern using edition - ROW_NUMBER() to detect consecutive participation streaks and extract them with chained CTEs.
  • Secret Mission: Combined all techniques into a single query analyzing performance trends around streak breaks, comparing average goals during vs after a team's longest streak.

Ready to quiz yourself?