the challenge

your task

With a list with football results, you need to calculate the table.

input data

I don’t like football in Switzerland that much, but I follow the FC Barcelona. That’s why I took the La Liga games from the 2018/2019 season. There are 20 teams in La Liga. Each club plays against every other club two times - once at home and once away - for 38 matchdays.
You’ll find the input data in the following format:

id round_game hometeam awayteam homegoals awaygoals
1 101 Athletic Bilbao Leganes 2 1
2 102 Valencia Atletico Madrid 1 1
.. .. .. .. .. ..
379 3809 Valladolid Valencia 0 2
380 3810 Levante Atletico Madrid 2 2

round_game is a combination of the matchday (1-38) and the game (01-10).
You’ll probably not need the id (which is the primary key), but I think it is a basic design principle which should be followed.

You can download the file here

rules

winning teams get three points, ties give one point.

Incase of equal points, La Liga uses the results of the matches between the corresponding clubs. (wikipedia: LaLiga - equal points)

To make this a bit easier, you can use this set of rules:

If two or more teams have equal points:

  • the bigger goal difference
  • the goals for a team
  • the team name

desired output

Your query should produce the following output:

rank team games points won drawn lost goals_for goals_against goal_difference
1 Barcelona 38 87 26 9 3 90 36 54
2 Atletico Madrid 38 76 22 10 6 55 29 26
.. .. .. .. .. .. .. .. .. ..
19 Huesca 38 33 7 12 19 43 65 -22
20 Rayo Vallecano 38 32 8 8 22 41 70 -29

Your solution should be able to integrate a round_game parameter, in order to produce the table e.g. after the first three full rounds:

rank team games points won drawn lost goals_for goals_against goal_difference
1 Barcelona 3 9 3 0 0 12 2 10
2 Real Madrid 3 9 3 0 0 10 2 8
3 Celta Vigo 3 7 2 1 0 5 2 3
4 Athletic Bilbao 3 5 1 2 0 5 4 1
.. .. .. .. .. .. .. .. .. ..

your solution

I’m always interested to learn about your solution. If you are the first one solving the task or if you have a particularly interesting solution you can win a chocolate bar.

one solution

First of all we bring the home- and away-stuff below eachother. For the table it is irrelevant where you made the points.

id round_game hometeam awayteam homegoals awaygoals
1 101 Athletic Bilbao Leganes 2 1
2 102 Valencia Atletico Madrid 1 1
3 103 Real Madrid Getafe 2 0
4 104 Rayo Vallecano Sevilla 1 4
.. .. .. .. .. ..
round_game site team goals_scored goals_conceded won draw lost
101 home Athletic Bilbao 2 1 1 0 0
101 away Leganes 1 2 0 0 1
102 home Valencia 1 1 0 1 0
102 away Atletico Madrid 1 1 0 1 0
103 home Real Madrid 2 0 1 0 0
103 away Getafe 0 2 0 0 1
104 home Rayo Vallecano 1 4 0 0 1
104 away Sevilla 4 1 1 0 0
.. .. .. .. .. .. .. ..

That was easy and it is already more than half of the problem solved!
(by the way: the site information on line 3 and on line 14 is not really necessary, I just added it to be more flexible on the exports for this documentation)

It is easy to see all the matches played by a team:

round_game site team goals_scored goals_conceded won draw lost
106 home Barcelona 3 0 1 0 0
206 away Barcelona 1 0 1 0 0
303 home Barcelona 8 2 1 0 0
408 away Barcelona 2 1 1 0 0
.. .. .. .. .. .. .. ..

We use the base table to get a list of all the teams (line 44). Then we join the games_stacked “view/cte”, calculate all the numbers using the values provided by games_stacked and finally add the ranking. Note the rules to qualify the rank are on top (lines 26-30), all tight together and easy to change.

quite simple, no?

Why there are so many COALESCE functions? In case you set a parameter during the first round, e.g. after the very first match

then you still want to see the full table:
(Note: Teams on place 2-19 on the ranking dind’t play yet, the order is alphabetically)

rank team games points won drawn lost goals_for goals_against goal_difference
1 Athletic Bilbao 1 3 1 0 0 2 1 1
2 Alaves 0 0 0 0 0 0 0 0
3 Atletico Madrid 0 0 0 0 0 0 0 0
4 Barcelona 0 0 0 0 0 0 0 0
5 Celta Vigo 0 0 0 0 0 0 0 0
.. .. .. .. .. .. .. .. .. ..
19 Villarreal 0 0 0 0 0 0 0 0
20 Leganes 1 0 0 0 1 1 2 -1

As a reference you can find the complete resultset here (end of season)

rank team games points won drawn lost goals_for goals_against goal_difference
1 Barcelona 38 87 26 9 3 90 36 54
2 Atletico Madrid 38 76 22 10 6 55 29 26
3 Real Madrid 38 68 21 5 12 63 46 17
4 Valencia 38 61 15 16 7 51 35 16
5 Sevilla 38 59 17 8 13 62 47 15
6 Getafe 38 59 15 14 9 48 35 13
7 Espanyol 38 53 14 11 13 48 50 -2
8 Athletic Bilbao 38 53 13 14 11 41 45 -4
9 Real Sociedad 38 50 13 11 14 45 46 -1
10 Real Betis 38 50 14 8 16 44 52 -8
11 Alaves 38 50 13 11 14 39 50 -11
12 Eibar 38 47 11 14 13 46 50 -4
13 Leganes 38 45 11 12 15 37 43 -6
14 Villarreal 38 44 10 14 14 49 52 -3
15 Levante 38 44 11 11 16 59 66 -7
16 Celta Vigo 38 41 10 11 17 53 62 -9
17 Valladolid 38 41 10 11 17 32 51 -19
18 Girona 38 37 9 10 19 37 53 -16
19 Huesca 38 33 7 12 19 43 65 -22
20 Rayo Vallecano 38 32 8 8 22 41 70 -29

about

Tiny SQL challenges are meant to be fun, small and hopefully interesting in case you want to learn more about SQL.

SQLite is a free and powerful database which runs everywhere. DB browser for SQLite is a graphical frontend. You can solve the challenge everywhere, even on the cmdline.

Don’t think too far. Sometimes we want to write universal, configurable code - as a professional approach would require. What counts here is the simplicity and thus a certain elegance. And don’t forget the fun :-)