All the Matches of the League - Practice Coding Problems

All the Matches of the League - Problem

Database Easy

All the Matches of the League

You are tasked with organizing a sports league where every team must play against every other team exactly twice - once as the home team and once as the away team. This creates a complete round-robin tournament format.

Given a table Teams with team names, you need to generate all possible match combinations:

Teams table:

Column Name	Type
team_name	varchar

Your task:
• Generate all possible matches where each pair of teams plays twice
• Each team should be home_team once and away_team once against every other team
• Return the matches in any order

Example: If teams are [A, B, C], the matches would be:
A vs B (A home), B vs A (B home), A vs C (A home), C vs A (C home), B vs C (B home), C vs B (C home)

This is a classic Cartesian product problem with a self-exclusion condition!

Input & Output

example_1.sql — Basic League

$ Input: Teams table: | team_name | |----------| | Arsenal | | Chelsea | | Liverpool |

› Output: | home_team | away_team | |-----------|----------| | Arsenal | Chelsea | | Arsenal | Liverpool | | Chelsea | Arsenal | | Chelsea | Liverpool | | Liverpool | Arsenal | | Liverpool | Chelsea |

💡 Note: Each team plays against every other team once as home and once as away. With 3 teams, we get 3×2 = 6 total matches.

example_2.sql — Two Teams Only

$ Input: Teams table: | team_name | |----------| | TeamA | | TeamB |

› Output: | home_team | away_team | |-----------|----------| | TeamA | TeamB | | TeamB | TeamA |

💡 Note: With only 2 teams, each team plays the other once at home and once away, resulting in 2 matches total.

example_3.sql — Single Team Edge Case

$ Input: Teams table: | team_name | |----------| | OnlyTeam |

› Output: Empty result set (no matches possible)

💡 Note: A single team cannot play against itself, so no matches are generated. The WHERE clause filters out the self-match.

Visualization

Tap to expand

Understanding the Visualization

Cross Join Teams

Create all possible combinations by joining Teams table with itself

Filter Self-Matches

Remove invalid matches where a team would play against itself

Generate Schedule

Result contains complete home/away match schedule for the league

Key Takeaway

🎯 Key Insight: Use CROSS JOIN to get all team combinations, then filter out self-matches with WHERE clause to create a complete round-robin schedule

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Cross join creates n² combinations, where n is the number of teams

⚠ Quadratic Growth

Space Complexity

O(n²)

Result set contains n×(n-1) matches for n teams

⚠ Quadratic Space

Constraints

1 ≤ number of teams ≤ 1000
Team names are unique varchar strings
Each team name length ≤ 100 characters
No duplicate team names in the input table

Asked in

a Amazon 25 ⊞ Microsoft 18 G Google 15 f Meta 12

The optimal solution uses SQL CROSS JOIN to generate all possible team combinations, then filters out self-matches with a WHERE clause. This creates a complete round-robin tournament schedule where every team plays every other team twice - once as home team and once as away team. Time complexity is O(n²) for n teams.

Common Approaches

Approach	Time	Space	Notes
✓ Cross Join with Self-Exclusion	O(n²)	O(n²)	Use CROSS JOIN to get all team combinations, filter out self-matches

Cross Join with Self-Exclusion — Algorithm Steps

Perform CROSS JOIN on Teams table with itself using different aliases
Filter out matches where home_team equals away_team
Select home_team and away_team columns
Return all valid match combinations

Visualization

Tap to expand

Step-by-Step Walkthrough

Cross Join

Create Cartesian product of Teams table with itself

Filter

Remove matches where team plays against itself

Result

Get all valid home/away combinations

Code -

solution.c — C

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#define MAX_TEAMS 100
#define MAX_NAME_LEN 50

// SQL Solution for All Matches of the League
const char* SQL_QUERY = 
    "SELECT t1.team_name AS home_team, t2.team_name AS away_team "
    "FROM Teams t1 CROSS JOIN Teams t2 "
    "WHERE t1.team_name != t2.team_name "
    "ORDER BY t1.team_name, t2.team_name;";

typedef struct {
    char home_team[MAX_NAME_LEN];
    char away_team[MAX_NAME_LEN];
} Match;

// Simulation function
int simulateSolution(char teams[][MAX_NAME_LEN], int teamCount, Match* matches) {
    int matchCount = 0;
    
    for (int i = 0; i < teamCount; i++) {
        for (int j = 0; j < teamCount; j++) {
            if (strcmp(teams[i], teams[j]) != 0) {
                strcpy(matches[matchCount].home_team, teams[i]);
                strcpy(matches[matchCount].away_team, teams[j]);
                matchCount++;
            }
        }
    }
    
    return matchCount;
}

int main() {
    char teams[3][MAX_NAME_LEN] = {"Arsenal", "Chelsea", "Liverpool"};
    Match matches[MAX_TEAMS * MAX_TEAMS];
    
    int matchCount = simulateSolution(teams, 3, matches);
    
    for (int i = 0; i < matchCount; i++) {
        printf("%s vs %s (Home: %s)\n", 
               matches[i].home_team, 
               matches[i].away_team, 
               matches[i].home_team);
    }
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Cross join creates n² combinations, where n is the number of teams

⚠ Quadratic Growth

Space Complexity

O(n²)

Result set contains n×(n-1) matches for n teams

⚠ Quadratic Space

Constraints

1 ≤ number of teams ≤ 1000
Team names are unique varchar strings
Each team name length ≤ 100 characters
No duplicate team names in the input table

28.5K Views

Medium Frequency

~8 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Cross Join with Self-Exclusion — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler