{"id":1395,"date":"2024-10-03T00:00:00","date_gmt":"2024-10-02T22:00:00","guid":{"rendered":"https:\/\/demo.sidekick.be\/itenium\/sql-training\/"},"modified":"2025-11-28T13:31:42","modified_gmt":"2025-11-28T12:31:42","slug":"sql-training","status":"publish","type":"post","link":"https:\/\/demo.sidekick.be\/itenium\/blog\/databases\/sql-training\/","title":{"rendered":"QA TechEvent: SQL Training"},"content":{"rendered":"

The Game<\/h1>\n

The game started with a hickup, other machines were not discoverable on the guest network so the leaderboard server had to be deployed on an external server \u2013 while everyone was already solving exercises! This messed up our scoreboard considerably, especially the ranking for \u201csolved fastest\u201d \ud83d\ude03<\/p>\n

After this it was pretty smooth sailing. Tom found an error in the calculation of the query to calculate the average GDP per capita in South America. That and other small issues were fixed on the spot, on production \u2013 as it should! \ud83d\ude09<\/p>\n

Before we start<\/h2>\n

\"Meme<\/p>\n

Notable Hacks<\/h2>\n

Even though we had definitely said \u201cno cheating\u201d during the intro powerpoint, some contestants still tried to get extra points for \u201cleanest solution\u201d or just \u201cfixed\u201d their query to get to the hinted at result \ud83d\ude00<\/p>\n

Mike<\/h3>\n

Q: Which countries have a GDP greater than every country in Europe?<\/em><\/p>\n

select name\nfrom countries\nwhere gdp > 3425956000000\n-- I think we were looking instead for this:\n-- WHERE gdp > (SELECT MAX(gdp) FROM countries WHERE continent='Europe')\norder by gdp\n<\/code><\/pre>\n
Straight to jail Mike! \ud83d\ude09<\/p>\n
Alexander<\/h3>\n
Alexander scored some +4<\/code> style points for this one!<\/p>\n
Q: Select the year of the worldcup in which the most penalties were scored, also select the amount of penalties.<\/em><\/p>\n
SELECT top 1 s.name, COUNT(*)+4 AS o\nFROM goals g JOIN matches m ON g.match_id = m.id\nJOIN groups a ON m.group_id = a.id\nJOIN events e ON a.event_id = e.id JOIN seasons s ON e.season_id = s.id\nWHERE g.penalty = 1\nGROUP BY s.name order by o desc\n<\/code><\/pre>\n
The reason was the join on the groups<\/code> table. This led to penalties scored after the \u201cgroup phase\u201d to not be counted in his query!<\/p>\n
The Leaderboards<\/h2>\n
The \ud83c\udfc6 top 4 on the final leaderboard \ud83d\udcaf:<\/p>\n\n\n\n\n\n\n\n\n
Rank<\/th>\nPlayer<\/th>\nPoints<\/th>\n# Fastest<\/th>\n# Shortest<\/th>\nTotal Score<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nMichael<\/td>\n35<\/td>\n6<\/td>\n8<\/td>\n63<\/td>\n<\/tr>\n
3<\/td>\nMikedonna<\/td>\n32<\/td>\n4<\/td>\n2<\/td>\n44<\/td>\n<\/tr>\n
2<\/td>\nJonathan<\/td>\n34<\/td>\n1<\/td>\n2<\/td>\n40<\/td>\n<\/tr>\n
4<\/td>\nGery<\/td>\n30<\/td>\n3<\/td>\n1<\/td>\n38<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The Prize<\/h2>\n
In the end Michael proved too strong and with 63 points, he went home with the title of itenium SQL Master<\/strong> and the gaming cup \ud83c\udf88 \ud83c\udf1f \ud83d\ude80!<\/p>\n
\"SQL
Michael scored most points and went home with the prize! \ud83c\udf89<\/figcaption><\/figure>\n
Host Your Own?<\/h1>\n
You want to host your own SQL Training competition?
 You can! The game, exercises and datasets are on our Github<\/a>!<\/p>\n
Each contestant runs his own React frontend and databases with Docker:<\/p>\n
git clone https:\/\/github.com\/itenium-be\/sql-training\ncd sql-training\ndocker compose up -d --build\n<\/code><\/pre>\n
The game leader hosts the leaderboard:<\/p>\n
cd sql-leaderboard\ncp .env.template .env\ndocker compose up -d --build\n<\/code><\/pre>\n
You\u2019ll have to change some configuration (see the README<\/a>) but that\u2019s basically it!
 Possible solutions can be found on a separate branch<\/a>.<\/p>\n
Interesting Queries<\/h1>\n
World: largest country per continent<\/h2>\n
Q: Find the largest country (by area) in each continent, show the continent, the name and the area.<\/em><\/p>\n
During the preparation, I solved this exercise with the following query:<\/p>\n
SELECT continent, name, area\nFROM countries\nWHERE area = (\n  SELECT MAX(area)\n  FROM countries AS subquery\n  WHERE subquery.continent = countries.continent\n)\nORDER BY continent\n<\/code><\/pre>\n
Mike was using ChatGPT extensively and by just entering the exercise question, it came up with the following query:<\/p>\n
SELECT DISTINCT ON (continent), continent, name, area\nFROM countries\nORDER BY continent, area DESC\n<\/code><\/pre>\n
A pretty interesting solution which won him the \u201cleanest query\u201d award for this question. I didn\u2019t even know the DISTINCT ON (col_name)<\/code> was a thing!<\/p>\n
It\u2019s the ORDER BY area DESC<\/code> part that makes this work. Pretty neat!<\/p>\n
World: all vowels, no spaces<\/h2>\n
Q: Find the country that has all the vowels and no spaces in its name.<\/em><\/p>\n
Michael came up with a cool solution for this one:<\/p>\n
select name from countries where name not like ('% %')\nand name like all (array['%a%', '%e%', '%i%', '%o%', '%u%'])\n<\/code><\/pre>\n
But I was surprised no one solved this with a good old regex!? It would most definitely have won the \u201cleanest query\u201d award!<\/p>\n
SELECT name FROM countries\nWHERE name ~ '^(?=.*a)(?=.*e)(?=.*i)(?=.*o)(?=.*u)[^ ]+$'\n<\/code><\/pre>\n
Teachers: Teacher seniority<\/h2>\n
Typically calculated like:<\/p>\n
EXTRACT(YEAR FROM AGE(NOW(), employed_at)) AS seniority\n<\/code><\/pre>\n
Michael won the leanest query by instead doing:<\/p>\n
(CURRENT_DATE - employed_at)\/365\n<\/code><\/pre>\n
Worldcup: country with most own goals<\/h2>\n
As promised in the intro\u2026<\/p>\n
SELECT TOP 1 c.name, COUNT(g.id) AS owngoals\nFROM goals g\nJOIN teams t ON g.team_id = t.id\nJOIN countries c ON t.country_id = c.id\nWHERE g.owngoal = 1\nGROUP BY c.name\nORDER BY owngoals DESC\n<\/code><\/pre>\n
And the \u201cwinner\u201d is\u2026 France, with 4 own goals!
 (Valladdares, Yobo, Behich, Mand\u017eukic)<\/p>\n
Unsolved<\/h2>\n
World-8<\/strong>: Get confusing top level domains: tld that start\/end in a different country name.
 An exercise on joining a table with itself.<\/p>\n
Woldcup-4<\/strong>: Find players that played for different countries and a \u201c,\u201d separated list of countries they played for.<\/p>\n
Worldcup-5<\/strong>: Which countries always survived the \u201cGroup Stage\u201d when present? (A round not called \u201cMatchday\u201d)<\/p>\n
Worldcup-6<\/strong>: For the player that scored the most goals, list the worldcup.YYYY, the round name, the match date, the goal minute, scored Y\/N and the total amount of goals scored so far. To keep things \u201csimple\u201d, evaluate only events.id 20, 21 and 22 and team.id 1.<\/p>\n
Anyone willing to give them a go? \ud83d\ude00<\/p>\n
Extras<\/h1>\n
Stuff that came into being during the making of this post<\/b><\/p>\n