04 April 2019
SQL can (sometimes) be fun – a tale from TSH’s coding challenge
To some developers, SQL is this boring database language, which they only use when they have to. And it’s probably the last thing they would use in a freestyle coding challenge competition. That’s too bad, because… it might have brought them acclaim, like it did to me during The Software House’s Coding Challenge! And with my little SQL queries practice, I brushed up on some SQL techniques and tricks that may come in handy to you as well.
From time to time, The Software House is holding a coding challenge event. So far, I have participated twice. Today, I’m going to focus on just one of them, when it was SQL that helped me wow the judges. Sounds interesting? Not really? 🙁
Well, prepare to be surprised!
SQL queries practice
The goal of the coding challenge was to write a piece of code that, given an array of currency codes, returns an array of currency rates. After all…
Roughly, it should be something similar to what you can see below:
I wanted to present an original solution, so I decided to create an SQL query for that particular problem. I chose the PostgreSQL dialect, since that’s the one I feel most familiar with.
What I eventually arrived at was more or less what you can see below. In the original version, I used shorter table and column names to make the entire code appear shorter (and thus less readable). It’s no longer my goal, so I improved the readability.
There are a couple of interesting ideas you can observe in this query. One of them is the Common Table Expressions (CTE’s). It is a temporary result, which can be later used in a SELECT statement. It is defined within the statement using WITH table (list of columns) AS (CTE query definition) language construct. It allows us to write cleaner, more elegant queries, reducing the amount of nested subqueries and increasing readability.
Interestingly, despite CTE’s being part of the SQL standard since 1999, MySQL only started supporting it in version 8.0. PostgreSQL introduced CTE’s in 8.4.
Interesting fact: CTE’s let us write neat recursive statements. Ever been interested in what the 1000th number of the Fibonacci sequence is? 🙂
> 4.346655768693743e+208
As I was writing this, I realized that I have never actually used CROSS JOIN before the Code Challenge. It turns out that it has some, albeit specific, uses. For example – building a base of items, which combines some properties, can be achieved as follows:
See also: Practical gRPC tutorial 👇
Why should you (still) use pure SQL?
While explaining the merits of using pure SQL might sometimes feel a bit like this…
I’m still going to give it a try 🙂
In the era of various database layer abstractions such as ORM, it isn’t every day when we get to work with pure SQL. And here it is – a situation that calls for rolling up your sleeves and getting your hands dirty (including having to deal with loads of production environment errors).
SQL sure does have huge capabilities. A lot of them are relatively overlooked or even unknown to many developers (like a sort of recent addition of CTE’s or window functions in MySQL).
That’s why I encourage you all to purposely limit your possibilities every once in a while to work on your SQL-fu. A fine-grained knowledge of SQL will surely help you stand out. Not to mention, SQL queries practice is simply so much fun!
Also, if you want to play/tweak (or fiddle, if you’d rather) with the queries presented in my article, use the URLs below.
Have fun!
Have you seen the coding challenge article? 🤔
A great example of how an unexpected and unconventional solution may actually help you learn… and impress your peers! 🚀
