Translating SQL Queries using Map-Reduce-Filter in JavaScript


Exercise: Suppose the following tables are given in the form of arrays of objects:

Translate the following SQL query using map, reduce, and filter:

You are not allowed to use loops, if statements, logical operators, or the ternary operator.

Video solution:

Solution:

We need to list all character names and the sum of the value of their items.

As characters and inventory are arrays, we can use the map, reduce, and filter.

The projection

corresponds to a simple map operation:

The next step is to join the characters and the inventory tables by filtering items belonging to a given character. We can also return the number of items belonging to a character in this step.

We have to return the name just like before, but we also have to find the corresponding items.

If you execute this code in the console, you can see that all three characters are there with their correct item count:

We only have one step left: summing the value of the items.

The SUM function is implemented by a reduce call in JavaScript:

The result is:

During the task, it is worth looking up the signature of reduce if you forgot it. Alternatively, you can reverse engineer it using a simple example if you get stuck and your interviewers insist in you not opening other tabs.

Sometimes you might have to use a testing platform to submit an answer, and the software might monitor when you leave a tab.

This is a stupid way of evaluating candidates, because it puts you in a big advantage if you have two computers side-by-side. You can execute any google searches you want in one computer, and solve the task in another. This is one reason why I believe all tests should be open book tests. If you want to browse the Internet, go for it! You have limited time, so you won’t be able to learn the basics of JavaScript while solving a task anyway.

This exercise is solved. Notice the analogy between SQL SELECT statements and map, reduce, filter. These are common questions not only in JavaScript.

Learn ES6 in Practice

Sign up below to access an ES6 course with many exercises and reference solutions.