SQL and PL/SQL techniques and solutions

Menu

SQL to find World Cup matches with comebacks

Lucas Jellema is writing a great series on SQL applied to the World Cup of football / soccer. In the article about finding matches with comebacks, he challenged readers to “find ‘dramatic comebacks’ where a team was two goals behind at some stage and yet managed to win the game.” Here is my reply, since replying directly on his blog was too challenging for me!

Here is a copy of Lucas’ DDL and test data. I added three rows to his data, one with a draw, one with a scoreless draw and one with a “dramatic comeback”.

The SCORING_PROCESS column shows who scored in what order: for example ‘100’ means one team scored the first goal, then the other team scored 2 goals.

Who won?

Conceptually, the first thing I do is determine who won the game, by counting the number of ‘1’ and the number of ‘0’. When the ‘1’s are ahead, I say the winner is 1; when there are more ‘0’s, I say the winner is -1. For draws, the “winner” is 0; I then remove draws in my WHERE clause.

How far behind did the winner get?

For each row, I go through the SCORING_PROCESS column , turning ‘1’ into the number 1 and ‘0’ into the number -1. This makes it easy to use the SUM() analytic function to calculate who led by how much after each goal. Here is an example using a hard-coded value.

In this case, I know the winner was team 1, so I can see that they came back from a 3-goal deficit. Putting it all together, here is a solution that shows all the comebacks and how far behind the winning team got.