SQL assesment with online coding test platform Codility

  #94 SQL assesment with online coding test platform Codility 

For a recent job application for a Business Intelligence position, I did a SQL assesment with the online coding test platform Codility. There were 2 tests, and one of them (´sql_tournament´) is similar to the ´SqlWorldCup´ test/excercise, which anyone can do themselves, see:

https://app.codility.com/programmers/trainings/6/sql_world_cup/

Here is the SQL-code that I wrote for this last test (see also Download at end of post):

My SQL code:

For this Codility test, I had to use PostgressSQL, and although I never worked with it as I mostly work with T-SQL (used in Microsoft SQL Server), the SQL-code that I wrote was almost correct, only the ISNULL function gave an error in PostgressSQL, as this is apparently a Microsoft-specific (so not ANSI-SQL compliant) function. Fix was to replace it by COALESCE (line 22). 

 I tested with Google AI chatbot Gemini if my final SQL code was ANSI-compliant:

And one other error in my SQL code was that I assumed that all teams played at least one match, but when I run my code, Codility told me that for the data in the example test-file, the query-result missed 1 row. Fix was to replace INNER JOIN by LEFT OUTER JOIN (line 30) (+ adding COALESCE before SUM), so that also teams without matches are included in the result (with #points = 0). 

Codility makes a video-recording when you are doing the test, and below you can see the result of my test. Note that at one moment I did a copy/paste of some SQL-code that I created for the previous SQL-test (´sql_tournament´) and then adapted it for this test. And another copy/paste action is the moment when I finished my SQL code and then formatted the SQL code using the Poor SQL formatter, a really nice (free) tool.

After I submitted my SQL code to Codility for validation and it said it was functionally correct, I asked Google Gemini to do a code-review:

This test (level: intermediate) was not looking at the performance of the SQL code, but I was curious if Google Gemini saw something in my SQL query that could be improved (e.g. for performance):

So the UNION-option might be better, but this would be something to test. 
I googled to find if maybe someone else did this SQL-test as well, and I found this blog-post:

https://guissmo.com/blog/codility-sample-sql-tests/#test-3-sqlworldcup

(see Test 3). The author of this post´s SQL code uses the UNION.

So this was my experience with the online test/assesment tool Codility, which I think is a nice tool which can help you to prepare for a job-interview.
And for those who want to practice SQL (PostgressSQL, MySQL, MS SQL Server), look at this (free) online tool:

https://runsql.com/r

The topic of this SQL-test, a team-sport (e.g. soccer) competition and the calculation of the points based on  the match-results (scores), is something that is not new to me. As I wrote in this post, I once made a database application (in MS Access) with whichi I could track soccer competitions/tournaments like the FIFA World Cup. And later I also made a special version for the Rotary Streetsoccer tournament in Oldenzaal (Netherlands), which included reports like this:

Source

Downloads

SqlWorldCup_SQLtest_by_MvR.SQL