Winning and Losing Streaks

A streak is a consecutive run of the same result — all wins, all losses, or all draws. Streak analysis reveals momentum and is one of the most requested stats in sports media. The "current unbeaten run" or "hasn't won in 8 games" narrative is built directly from this kind of SQL.

Technically, streak detection is a gaps-and-islands problem — one of the most important patterns in SQL analytics. The technique applies wherever you need to find consecutive runs of the same value.

The Gaps-and-Islands Mental Model

Gaps-and-islands problems arise when you have a sequence of events and want to group consecutive occurrences of the same value. For streaks, you want to group consecutive wins together, consecutive losses together, etc.

The classic technique uses two row numbers:

1. ROW_NUMBER() over the full sequence (global ordering)
2. ROW_NUMBER() over only the same-result rows (partitioned ordering)

The difference between these two numbers is constant within each consecutive run — it only changes when the result changes. This constant difference becomes the "group ID" for each island.

Here's why it works with a small example:

match# | result | rn_all | rn_same_result | rn_all - rn_same_result (= island group)
-------|--------|--------|----------------|------------------------------------------
  1    |   W    |   1    |       1        |   0   ← Group 0 (W's)
  2    |   W    |   2    |       2        |   0   ← Group 0 (W's)
  3    |   L    |   3    |       1        |   2   ← Group 2 (L's)
  4    |   W    |   4    |       3        |   1   ← Group 1 (W's, second run)
  5    |   W    |   5    |       4        |   1   ← Group 1 (W's, second run)

The group ID differs between W-run-1 (group 0) and W-run-2 (group 1) because a loss interrupted them. This is what makes the technique work.

Step 1 — Flatten Matches to Team Results

First, transform match data to one row per team per match, exactly as we do for league tables:

WITH team_results AS (
  SELECT home_team_id AS team_id,
    match_date,
    CASE WHEN home_goals > away_goals THEN 'W'
         WHEN home_goals < away_goals THEN 'L'
         ELSE 'D' END AS result
  FROM matches
  UNION ALL
  SELECT away_team_id,
    match_date,
    CASE WHEN away_goals > home_goals THEN 'W'
         WHEN away_goals < home_goals THEN 'L'
         ELSE 'D' END
  FROM matches
),
ordered AS (
  SELECT
    team_id,
    match_date,
    result,
    ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY match_date) AS rn
  FROM team_results
)
SELECT * FROM ordered ORDER BY team_id, match_date;

What This Returns

Each row is one team's result in one match. The rn column numbers each team's matches chronologically — Team A's first match is 1, their second is 2, etc. This ordering is essential for the next step.