While hunting for your first dev job, or your next, you’ll often be given a code challenge. These will often be performed in front of one or more interviewers or it might be sent to you as a take home challenge. In my case, the challenge was the latter, and defying what I’d heard from peers who had been given take home challenges in the past, mine was in SQL! For those who don’t know, SQL stands for Structured Query Language made to manage data in a relational database. You either write it in a SQL file and run it, or type it straight into the command line.
SQL isn’t exactly rocket science. My introduction to it was brief, and quickly eclipsed and replaced by the use of ActiveRecord. I recall being told that if you could write a query to grab everything from a table, you were in pretty good shape! Something like…
SELECT * FROM table_name;Easy right? SQL tends to have pretty straightforward semantic syntax. In my first interview with the outfit that issued the challenge I was asked what the most complicated SQL query I’d ever written was, and I regurgitated the query above verbatim. I followed that by cockily saying that query was about all most people needed, “right?”. The interviewer rolled with it, maybe knowing they’d find out soon enough if that would still hold true for me.
The first interview went great, despite my obvious arrogance. The challenge followed shortly after by email. The instructions were brief. I was given a data structure and asked to write some queries using PostgreSQL to produce some desired outcomes. The data structure wasn’t too intense, each piece was something like this (there’s more, but who knows, maybe you’re doing the same one ;) ):
Users [
id: bigint,
username: string,
details: jsonb
],
payment_methods [
id: bigint,
external_id: string,
account_id: bigint
],I needed to write three queries, and guess what? The first one was the very same query I had offered in the first interview. Easy. For sanities sake, I replicated the data structure on my machine and tested it, no problems so far. Next I needed to return all of a specific users payment info (from another part of the structure). That’s only a little more complicated, but still, no sweat:
SELECT * FROM payment_methods
WHERE external_id = 'the_user';You can use WHERE to set a parameter to be met during your search. Again, you can see how readable SQL can be, at least when it’s at its simplest. The way this challenge was structured was quite clever. First query was basic, and also perhaps a confirmation that I could practice what I’d preached. Next, a more intermediate query, checking if I understand SQL well enough to be more explicit in my queries. You might be ahead of me if you’ve gathered where this is headed. The last query was rather more advanced.
I was asked to write a query that changes a users field values in ‘details’, a jsonb object. I was challenged to remove two keys ‘Ayy’ and ‘Bee’ and return the updated row. Do you know what jsonb is? I didn’t. Where json stores an exact copy of the text inputted, jsonb is stored as decomposed binary! Great, we’re learning. So how do we get those keys out of there?
I’ll explore my process for getting to the bottom of this query in part 2!
