In August, the Premier League was getting ready to start a new season (The top soccer league in England), and I ran across a few articles on how the schedule gets put together:
The Athletic (Login Required)
The Premier League has a more straightforward schedule than the American sports leagues. There are 20 teams, and each team plays each other twice: One at home and once away, for a total of 38 matches for each team.
I thought it would be interesting to put together a SQL script to generate a season schedule.
I’ve posted a script on GitHub. The script is in T-SQL (I’m running SQL Server 2022 but most any version would work).
I found this Stack Overflow posting that discussed round robin scheduling. It mentioned a Circle Method scheduling algorithm (Explanation on Wikipedia) that I used. I adapted it somewhat, pairing teams together and running through the algo, alternating home and away matches each week.
There is a Team table for the 20 teams, plus a MatchWeek table, one record with a date for the 38 match weeks. In reality, teams won’t all play on the same day. If the main match day is on a Saturday, teams may actually play anywhere from Friday through Monday.
I paired teams together, and they alternate playing home and away. There are some teams in the same city (Like Everton and Liverpool) that we don’t want to play at home on the same week (Too much strain on transport, police, and other considerations). Although we can’t avoid this with the London teams, having so many teams there.
This turned out to be a more difficult exercise than I expected. Plus, there’s a lot of things I don’t take into consideration. I’m assuming that the stadiums are available for these dates. In reality, there may be other events in cities that would mean the team needs to be away on certain weeks. Plus I don’t consider other competitions the team may be in that could possibly conflict. But this was an interesting challenge to run through.