Back to all blogposts

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

Karol Goraus

Karol Goraus

PHP 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: 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!

You may also like

What do you want to achieve?





    You can upload a file (optional)

    Upload file

    File should be .pdf, .doc, .docx, .rtf, .jpg, .jpeg, .png format, max size 5 MB

    Uploaded
    0 % of

    or contact us directly at [email protected]

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

    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 get to know each other and address your needs as quick as possible.

    Strategy

    We'll work together on possible scenarios

    for the software development strategy in sync with your goals.

    Strategy

    We’ll turn the strategy into an actionable plan

    and provide you with experienced development teams to execute it.

    Our work was featured in:

    Tech Crunch
    Forbes
    Business Insider

    Aplikujesz do

    The Software House

    Copied Tekst skopiowany!

    Nie zapomnij dodać klauzuli:

    Kopiuj do schowka

    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).

    spinner