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

2 min

read

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: gRPC tutorial – the quick & practical introduction

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

On a side note, I highly recommend you also read another coding challenged-related article from my colleague Marcin Gajda. It’s another great example of how an unexpected and unconventional solution may actually help you learn… and impress your peers as well!

Estimate your project





Thanks

Thank you!

Your message has been sent. We’ll get back to you in 24 hours.

Back to page
24h

We’ll get back to you in 24 hours

to address your needs as quick as possible.

Estimation

We’ll prepare an estimation of the project

describing the team compostition, timeline and costs.

Code review

We’ll perform a free code review

if you already have an existing system or a part of it.

Our work was featured in:

Tech Crunch
Forbes
Business Insider

Aplikujesz do

The Software House

Aplikuj teraz

wyślij CV na adres: [email protected]

CopiedTekst skopiowany!

Nie zapomnij dodać klauzuli:

Kopiuj do schowka Copy

Jakie będą kolejne kroki?

Phone

Rozmowa telefoniczna

Krótka rozmowa o twoim doświadczeniu,
umiejętnościach i oczekiwaniach.

Test task

Zadanie testowe

Praktyczne zadanie sprawdzające dokładnie
poziom twoich umiejętności.

Meeting

Spotkanie w biurze

Rozmowa w biurze The Software House,
pozwalająca nam się lepiej poznać.

Response 200

Response 200

Ostateczna odpowiedź i propozycja
finansowa (w ciągu kilku dni od spotkania).