69 Lines of SQL

Sometimes I wonder what programming really is and why we hackers find it so compelling. Most of the time we – or atleast I – bang our heads to wall for hours while trying to conquer the challenge. After all those hearth-breaking moments we finally succeed to get the code running and it solves the problem. All the dark clouds disappear, flowers start to bloom and happy squirrels are jumping everywhere. I had one of these moments today while trying to do some SQL magic.

I do SQL and database operations for almost every project I do. I was relatively good at database courses at the university and I always thought that SQL is so simple. Yeah, about that… I only evere had to do very basic queries which just involved some basic SELECTs with couple of tables usually just JOINin them on their keys. Today, however, I was wondering how I could make a sports statistics database – web page connection better and I started to think if things could be done differently than I usually do while dealing with this kind of data.

The Data:

The sports data is as follows: there are individual games (id, home team, away team, home goals, away goals and tournament) which belong to tournaments which belong to seasons. Previously I always had to tables for the game data – the indivual games and a stats table to which I calculated the games, wins, draws, losses, goals for, goals against and points every time I added or removed a tournament from the db.

The Need:

Sometimes it might be a good idea but I wanted to keep the data in just one place so I put my SQL-magician hat on and started to think if I could do all that magic with pure SQL. It would help me to easily generate different statistics. I could just choose a particular season or tournament or sets of tournaments to combine. And oh boy, it was not an super-easy task.

The Code:

My code ended up being 69 lines of SQL that does just the trick. (edit 2019-02-09, the code does not exist anymore) I collect all the different things I need with separate subqueries and then join them together in one. Looking at it now, after finishing, it seems like obvious thing. But it took me 4 hours to tackle the situation and make all small pieces fall together. I’m very proud of the code, even if it’s not the best or optimal solution, it gives me huge opportunity to now get the data to my web site in a way I want it for each situation.