CodeSignal challenge - SQL - Mischievous Nephews

Today we are going to solve the SQL challenge from CodeSignal challenge MischievousNephews, we will take a look at the challenge and solve it step by step while explaining how such a test can be solved.

If you haven’t tried the test yet, please go to CodeSignal and try the test before reading, it will be more interesting that way, always try to solve the test before looking at the solutions 😇.

The full solution is at the bottom of the post*

Huey, Dewey, and Louie are characters from the show/comics “duck tales”

This time we are solving a query challenge, we can only use MySQL (though I didn’t use SQL for a while now 😪).

The test states this, your 3 nephews are doing trouble, and you try to figure out if there is any pattern in the chaos, so you mark them on a table with the mischievous things they did and the dates they did them, the table looks like this:

From this information, we would like to query the data so

1. We add a weekday, that starts at 0 (Monday) and ends at 6 (Sunday).
2. We order the results by the weekday
   a. we order the results by the names in the order: ‘Huey’, ‘Dewey’,’ Louie’.
   b. we order the results by the date
   c. we order the results by lexicographical value — which is a fancy name to say it’s ordered by the dictionary value, AKA the default sorting algorithm for MySQL.

So our query should return this table:

That’s pretty simple once we divided the challenge into smaller tasks, let’s see how each of them is done, this is how the challenge starts, with an empty procedure that we have to fill.

Convert dates to days of the week

There are a lot of date functions in MySql the one we want is the one that starts on Monday (0) and ends on Sunday (6), the function that does that is called WEEKDAY.

MySQL documentation for WEEKDAY

With that we can write the first part of the query, it would look like this, we simply specify the fields we want using SELECT, use the WEEKDAY function to create a new field named weekday and that’s it.

CREATE PROCEDURE mischievousNephews()
BEGIN
  SELECT
    WEEKDAY(mischief_date) AS weekday,
    mischief_date,
    author,
    title
  FROM
    mischief
END

Order the results

We first want to order by the weekday that we got from the previous part, then the names in the order ‘Huey’, ‘Dewey’, ’ Louie’, to do exact ordering we can use the function FIELD which allows us to order the exact names we give, usually used to order enums, and then we can order by the date and title, so our ORDER BY call looks like:

ORDER BY
  weekday,
  Field(author, 'Huey', 'Dewey' ,'Louie'),
  mischief_date,
  title;

With that we are done, if we put it all together the solution looks like this:

https://gist.github.com/liron-navon/c8850723e714222dc21d0c263764392e

Last words

This challenge is pretty easy in my opinion, but if you don’t know SQL and how to use the MySql functions it might be a bit intimidating at first.

Please clap and follow as I will publish content every couple of weeks, I truly appreciate every follower, clapper, and commenter 🙂.