Back to all blogposts

SQL can (sometimes) be fun – a tale from TSH’s coding challenge

Karol Goraus

Karol Goraus

Node.js Developer

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!

Fiddle #1

Fiddle #2

Fiddle #3

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! 🚀

Interviews
CTO vs Status Quo

Find the exact rules Tech Managers used to organize IT & deliver solutions the Board praised.

Read here

The Software House is promoting EU projects and driving innovation with the support of EU funds

What would you like to do?

    Your personal data will be processed in order to handle your question, and their administrator will be The Software House sp. z o.o. with its registered office in Gliwice. Other information regarding the processing of personal data, including information on your rights, can be found in our Privacy Policy.

    This site is protected by reCAPTCHA and the Google
    Privacy Policy and Terms of Service apply.

    We regard the TSH team as co-founders in our business. The entire team from The Software House has invested an incredible amount of time to truly understand our business, our users and their needs.

    Eyass Shakrah

    Co-Founder of Pet Media Group

    Thanks

    Thank you for your inquiry!

    We'll be back to you shortly to discuss your needs in more detail.