CodeSignal Solves It, Interview Practice Edition: productExceptSelf

CodeFights Solves It Interview Practice

If it’s been asked as an interview question at Amazon, LinkedIn, Facebook, Microsoft, AND Apple, you know it’s got to be a good one! Have you solved the challenge productExceptSelf in Interview Practice yet? If not, go give it a shot. Once you’re done, head back here. I’ll walk you through a naive solution, a better solution, and even a few ways to optimize.

…Done? Okay, let’s get into it!

The object of this problem is to calculate the value of a somewhat contrived function. The function productExceptSelf is given two inputs, an array of numbers nums and a modulus m. It should return the sum of all N terms f(nums, i) modulo m, where:

f(nums,i) = nums[0] * nums[1] * .... * nums[i-1] * nums[i+1] * ... * nums[N-1]

Whew!

We can see this most easily with an example. To calculate productExceptSelf([1,2,3,4],12) we would calculate:

  • f([1,2,3,4], 0 ) = 2*3*4 = 24
  • f([1,2,3,4], 1 ) = 1*3*4 = 12
  • f([1,2,3,4], 2 ) = 1*2*4 = 8
  • f([1,2,3,4], 3 ) = 1*2*3 = 6

The sum of all these numbers is 50, so we should return 50 % 12 = 2.

A naive solution

The explanation of the code suggests an implementation:

# Don't use this function!
def f(nums,i):
  ans = 1
  for index, n in enumerate(nums):
    if index != i:
    ans *= n
  return ans

def productExceptSelf(nums, m):
  # Add up all the results of the f(nums, i), modulo m
  return sum([f(nums,i) % m for i in range(len(nums))]) % m

This is technically correct, but the execution time is bad. Each call to the function f(nums, i) has to do a scan (and multiplication) in the array, so we know the function is O(N). We call f(nums,i) a total of N times, so this function is O(N2)!

Sure enough, this function passes all the test cases. But it gives us a time length execution error on test case #16, so we have to find a more efficient solution.

Division is a better solution (but still not good enough)

A different way of approaching this problem is to find the product of all the numbers, and then divide by the one you are leaving out. We would have to scan to see if any of the numbers were zero first, as we can run into trouble dividing by zero. Essentially, we’d have to deal with that case separately, but it turns out that any array nums with a zero in it is easy to calculate. (This would be a good extension exercise!)

If we look under the constraints of the code, we are told that 1 <= nums[i], so we don’t have to worry about this case. We can simplify our problem to:

# This version still doesn't run fast enough...
# but why??
def productExceptSelf(nums, m):
  productAll = 1
  for n in nums:
    productAll *= n
  # these are the f(nums,i) we calculated before
  f_i = [productAll / n for n in nums]
  return sum(f_i) % m

Again, we get a time execution error! Note that the running time is much better. We make a pass through the array once to get productAll, then a pass through the array again to get the f_i, and one more pass through the array to do the sum. That makes this is a O(N) solution!

Why is the interviewer asking this question?

In other words, what is this question testing? As I mentioned in the introduction, the function we’re calculating is a little contrived. Because it doesn’t seem to have any immediate applicability, the companies asking us this question in interviews are probably looking to see if we know a particular technique or trick.

One of the assumptions that I made when calling the algorithms O(N) or O(N2) was that multiplication was a constant time operation. This is a reasonable assumption for small numbers, but even for a computer there is a significant difference between calculating

456 x 434

and

324834750321321355120958 x 934274724556120

There are a couple of math properties of residues (the technical name for the “remainders” the moduli give us) that we can use. One is:

(a + b + c ) % m is the same as (a % m + b % m + c % m) % m

This is nice because a%m, b%m, and c%m are all small numbers, so adding them is fast.

The other property is:

(a * b) % m is the same as ((a % m) * (b % m)) % m

That is, I can multiply the remainders of a and b after division by m, and the result I get will have the correct remainder.

At first glance, this doesn’t seem to be saving us much time because we’re doing a lot more operations. We are taking the modulus three times per multiplication, instead of just once! But it turns out that the modulus operation is fast. We more than make up for it by only multiplying small numbers.

So we can change our calculation of f_i to

  # same as before
  f_i = [(productAll / n) % m for n in nums]
  return sum(f_i) % m

This still isn’t good enough to pass the test, but we’re getting there. The problems we still have are:

  1. The number productAll is still very large
  2. Integer division is (relatively) slow

Our next approach will eliminate both of these problems.

Note: NOT a property

The big number is `productAll`, so you might hope that we can find `productAll % m`, and _then_ do the division. This doesn’t work.

The mathematical problem is that non-zero numbers can be multiplied to give 0, so division is problematic. Looking at division, and then taking a modulus:

48 / 6 = 8_ so _(48 / 6) % 12 = 8

but reversing the order (taking the modulus, then doing the division) yields:

(48 % 12) / 6 = 0 / 6 = 0

So we can’t take the modulus of `productAll` and avoid big numbers altogether.

Prefix products (aka cumulative products)

We can speed up the execution by building by an array, prefixProduct, so that prefixProduct[i] contains the product of the first i-1 numbers in nums. We will leave prefixProduct[0] = 1.

  ...
  ...
  prefixProduct = [1] * len(nums)
  for i in range(1,len(nums)):
    prefixProduct[i] = prefixProduct[i-1]*nums[i-1]
  ...

The neat thing about this array is that prefixProduct[i] contains the product of all elements of the array up to i, not including i. If we also made a suffixProduct such that suffixProduct[i] was equal to all the product of all numbers in nums past index position i, then the productExceptSelf for number i would just be the product of all numbers except the ith one = prefixProduct[i] * suffixProduct[i]

We have eliminated one of the costly operations: division! We can also avoid seeing large numbers in the multiplication as well, by changing the step inside the loop to contain a modulus.

Our new solution is:

def productExceptSelf(nums, m):
  prefixProduct = [1]*len(nums)
  suffixProduct = [1]*len(nums)  

  # setup the cumulative product from left and right
  for i in range(1,len(nums)):
    # Need parenthesis, as % has higher precedence than *
    prefixProduct[i] = (prefixProduct[i-1] * nums[i-1]) % m
    suffixProduct[-i-1] = (suffixProduct[-i] * nums[-i]) % m
  total = 0
  for i in range(len(nums)):
    # start at the end, with prefixProduct -1
    # and scan right
    total += (prefixProduct[i]*suffixProduct[i]) % m

  return total % m

This finally works! We’ve eliminated all multiplication by big numbers (but still have multiplications by small numbers), and no divisions at all. But we can still do better…

For the technical interview, an even better solution

It turns out that we don’t need to have a suffixProduct. We can build it as we go! This is the accumulator pattern:

def productExceptSelf(nums, m):
  prefixProduct = [1]*len(nums)
  suffixProduct = 1    # now this is just a number

  # setup the cumulative product from left and right
  for i in range(1,len(nums)):
    # Need parenthesis, as % has higher precedence than *
    prefixProduct[i] = (prefixProduct[i-1] * nums[i-1]) % m

  total = 0
  for i in range(len(nums)):
    # start at the end, with prefixProduct -1
    # and scan right
    total += (prefixProduct[-1 - i]*suffixProduct) % m
    suffixProduct = (suffixProduct * nums[-1-i]) % m
    # now multiply suffixProduct by the number that
    # was excluded

  return total % m

Takeaways

The main things you’re being asked to think about in this task are:

  • Arithmetic operations aren’t always constant time. Multiplying big numbers is much slower than multiplying small numbers.
  • Operations are not all the same speed. Integer modulus is very fast, addition and multiplication are fast, while division is (relatively) slow.
  • Some number theory: You can multiply the residues of numbers, instead of the numbers themselves. But you cannot divide by residues, or divide the residues unless you have certain guarantees about divisibility.
  • The idea of precomputing certain operations, which is where the prefixProduct comes in.

Other problems that use the cumulative or prefix techniques are finding the lower and upper quartiles of an array, or finding the equilibrium point of an array. (I cover prefix sums in a lot more detail in this article.)

Footnote: Horner’s Method

One of the solutions presented used a method of calculation known as Horner’s method. Take the cubic

f(x) = 2 x^3 + 3 x^2 + 2 x + 6

To evaluate f(3) naively would require 8 multiplications (every power x^n is n copies of x multiplied together, and then they are multiplied by a coefficient), and three additions. There is a lot of wasted calculation here, because when we calculate x^3 we calculate x^2 in the process! We could store the powers of x separately to reduce the number of multiplications.

Horner’s method is a way of doing this without using additional storage. The idea is, for example, that we can use operator precedence to store numbers for us:

3 x^2 + 2 x + 6 = (3 * x + 2) * x + 6

The left side has a (naive) count of 4 multiplications and 2 additions, while the right side has 2 multiplications and 2 additions. Moving to the cubic is even more dramatic:

f(x) = 2 x^3 + 3x^2 + 2 x + 6 = ( (2 * x + 3) * x + 2 ) * x + 6

This takes our 8 multiplications and 3 additions to only 3 multiplications and 3 additions!

The shortest solution so far, submitted by CodeFighter k_lee, uses Horner’s method, along with taking moduli at the different steps. See if you can decipher it.

def productExceptSelf(nums, m):
  p = 1
  g = 0
  for x in nums:
    g = (g * x + p) % m
    p = (p * x) % m
  return g

Tell us…

Did your solution for this Interview Practice challenge look different than mine? How did you approach the problem? Let us know over on the CodeSignal forum!

CodeSignal Solves It: hostnamesOrdering

hostnamesOrdering solution

The Challenge of the Week that ended on March 21 was hostnamesOrdering. Only 72 CodeFighters submitted solutions – this was a tricky one! In this breakdown, I’m going to walk you through my thought process in writing a solution.

In this database challenge, the task is to take a table of numeric ids and hostnames, and return a sorted version of the table. The challenge is that we have to order the hostnames by the reverse hostnameThe hostname is a string of domains separated by periods, such as codesignal.com or www.github.com. The domains don’t include any protocols (such as http://), nor do they include any paths (such as codesignal.com/forums). The general form of a hostname is domain1.domain2. … .domainN. The reversed hostname is domainN. … .domain2.domain1. For example:

[table id=6 /]

Note that the list above is ordered by the reverse hostname. We are told that there are at most 3 domains in any given hostname that appears in this challenge.

Getting started

Let’s start with an example of the table hostnames:

[table id=7 /]

Let’s suppose for a moment that we have a way of generating the reversed hostnames, which will actually be the hardest part of the challenge.

[table id=8 /]

The query we want to return is:

# Assuming we can make a ‘reversed’ column
SELECT id, hostname FROM hostnames ORDER BY reversed;

which would return:

[table id=9 /]

Reversing the domain name

We won’t actually construct an explicit reversed column. Instead, we will generate the pieces of the domain we want on the fly. To do this, we will be using the SUBSTRING_INDEX function. From the documentation, we see that SUBSTRING_INDEX( string, delim, count) returns a substring.

If count is positive, SUBSTRING_INDEX( string, delim, count) returns the substring from the beginning to the countth occurrence of delim. For example:

mysql> SELECT SUBSTRING_INDEX('www.codesignal.com', '.',1);
          -> www
mysql> SELECT SUBSTRING_INDEX('www.codesignal.com', '.', 2);
          -> www.codesignal
mysql> SELECT SUBSTRING_INDEX('www.codesignal.com', '.', 45);
          -> www.codesignal.com

If count is negative, SUBSTRING_INDEX finds the |count|th occurrence of delim from the right, and returns the substring from there to the end of the string. For example:

mysql> SELECT SUBSTRING_INDEX('www.codesignal.com', '.', -1);
       -> com
mysql> SELECT SUBSTRING_INDEX('www.codesignal.com', '.', -2);
       -> codesignal.com
mysql> SELECT SUBSTRING_INDEX('www.codesignal.com', '.', -100);
       -> www.codesignal.com

Using the same example we’ve been working with, here is a query that splits the hostname into three pieces. Using SUBSTRING_INDEX, we can extract the top level domain, the midlevel domain, and the lowest level of domain just by changing the index value.

SELECT id, hostname, SUBSTRING_INDEX(hostname,'.',-1) as top,
                                      SUBSTRING_INDEX(hostname,'.',-2) as mid
               FROM hostnames ORDER BY top, mid, hostname;

This would work if every domain has exactly three pieces. Unfortunately, some domains only have one or two pieces. What is actually returned is:

[table id=10 /]

The first two rows are in the wrong order. They match at the top level (i.e., they are both .com domains). The com domain has no lower level, but the SUBSTRING_INDEX just returns the entire string com again. Since codesignal.com is earlier in the alphabet than com, the table puts codesignal.com first. What should happen is that codesignal should be compared to the empty string, and com should appear first.

One trick to fix this problem is to prepend the hostnames with ‘…’, guaranteeing that there are three periods in each domain. So our query is now:

SELECT id, hostname, SUBSTRING_INDEX(CONCAT('...', hostname),'.',-1) as top,
                                      SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2) as mid
               FROM hostnames ORDER BY top, mid, hostname;

Writing down just the first four rows, this query returns:

[table id=11 /]

This has everything in the right order!

A problem and a workaround

In principle, we just have to eliminate the top and middle column, so we are only reporting the id and hostname. Before making this change, we can try running this in CodeSignal. When we do it, we run into a problem: the CodeSignal MySQL engine returns workbench.mysql.com before dev.mysql.com!

This seems to be a bug; running MySQL on a local machine returns dev.mysql.com before workbench.mysql.com. However, ordering by the hostname on CodeSignal returns the opposite. When using ORDER BY top, mid, hostname, workbench.mysql.com appears first. This seems wrong, since both these hostnames have identical top and mids, so the ordering should be determined by hostname.

Part of being a programmer is working around issues in any software! The problem we are encountering comes from doing multiple orderings. Our workaround will be to concatenate the top, middle, and hostname into one string. We will separate the top, middle, and hostname by a space, because spaces cannot occur in a domain and spaces appear earlier in ASCII than any letter.

So our workaround is:

SELECT id, hostname, SUBSTRING_INDEX(CONCAT('...', hostname),'.',-1) as top,
                                      SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2) as mid
                FROM hostnames ORDER BY CONCAT(top, ' ', mid, ' ', hostname);

This orders dev.mysql.com and workbench.mysql.com correctly.

Eliminating columns

Now we need to eliminate the top and mid columns. We could do a subquery, but instead we will actually construct the top and the middle strings as part of the ORDER BY clause.

Our solution ends up looking like this:

CREATE PROCEDURE hostnamesOrdering()
    SELECT id, hostname FROM hostnames
          ORDER BY CONCAT(SUBSTRING_INDEX(CONCAT('...',hostname),'.',-1),
                                               ' ',
                                               SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2),
                                               ' ',
                                               hostname);

This solution only selects the desired columns. Notice that in the ORDER BY we have a single string, made from the “top” level domain, a space, the “middle level” domain, a space, and then the hostname. This gets around the implementation bug, and gets us a solution with 188 characters.

On a local MySQL database, we don’t need the final concatenation, and we could use multiple ordering to get:

CREATE PROCEDURE hostnamesOrdering()
    SELECT id, hostname FROM hostnames
          ORDER BY SUBSTRING_INDEX(CONCAT('...',hostname),'.',-1),
                              SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2),
                              hostname;

Going further

As a code golfing exercise, we have some compact solutions. As a coding exercise, you might be interested in trying to solve the general case where we don’t have a limit on the maximum number of domains in a hostname!

This takes us back to our original solution:

SELECT id, hostname FROM hostnames ORDER BY reverse_hostname(hostnames);

where the interesting exercise is to create a custom function reverse_hostname. If you get stuck, this StackOverflow post might be useful.

Tell us…

How did you tackle this challenge? What do you think about the approach I took in solving it? Let us hear from you in the CodeSignal forum!

CodeFights Solves It: hostnamesOrdering

hostnamesOrdering solution

The Challenge of the Week that ended on March 21 was hostnamesOrdering. Only 72 CodeFighters submitted solutions – this was a tricky one! In this breakdown, I’m going to walk you through my thought process in writing a solution.

In this database challenge, the task is to take a table of numeric ids and hostnames, and return a sorted version of the table. The challenge is that we have to order the hostnames by the reverse hostnameThe hostname is a string of domains separated by periods, such as codefights.com or www.github.com. The domains don’t include any protocols (such as http://), nor do they include any paths (such as codefights.com/forums). The general form of a hostname is domain1.domain2. … .domainN. The reversed hostname is domainN. … .domain2.domain1. For example:

[table id=6 /]

Note that the list above is ordered by the reverse hostname. We are told that there are at most 3 domains in any given hostname that appears in this challenge.

Getting started

Let’s start with an example of the table hostnames:

[table id=7 /]

Let’s suppose for a moment that we have a way of generating the reversed hostnames, which will actually be the hardest part of the challenge.

[table id=8 /]

The query we want to return is:

# Assuming we can make a ‘reversed’ column
SELECT id, hostname FROM hostnames ORDER BY reversed;

which would return:

[table id=9 /]

Reversing the domain name

We won’t actually construct an explicit reversed column. Instead, we will generate the pieces of the domain we want on the fly. To do this, we will be using the SUBSTRING_INDEX function. From the documentation, we see that SUBSTRING_INDEX( string, delim, count) returns a substring.

If count is positive, SUBSTRING_INDEX( string, delim, count) returns the substring from the beginning to the countth occurrence of delim. For example:

mysql> SELECT SUBSTRING_INDEX('www.codefights.com', '.',1);
          -> www
mysql> SELECT SUBSTRING_INDEX('www.codefights.com', '.', 2);
          -> www.codefights
mysql> SELECT SUBSTRING_INDEX('www.codefights.com', '.', 45);
          -> www.codefights.com

If count is negative, SUBSTRING_INDEX finds the |count|th occurrence of delim from the right, and returns the substring from there to the end of the string. For example:

mysql> SELECT SUBSTRING_INDEX('www.codefights.com', '.', -1);
       -> com
mysql> SELECT SUBSTRING_INDEX('www.codefights.com', '.', -2);
       -> codefights.com
mysql> SELECT SUBSTRING_INDEX('www.codefights.com', '.', -100);
       -> www.codefights.com

Using the same example we’ve been working with, here is a query that splits the hostname into three pieces. Using SUBSTRING_INDEX, we can extract the top level domain, the midlevel domain, and the lowest level of domain just by changing the index value.

SELECT id, hostname, SUBSTRING_INDEX(hostname,'.',-1) as top,
                                      SUBSTRING_INDEX(hostname,'.',-2) as mid
               FROM hostnames ORDER BY top, mid, hostname;

This would work if every domain has exactly three pieces. Unfortunately, some domains only have one or two pieces. What is actually returned is:

[table id=10 /]

The first two rows are in the wrong order. They match at the top level (i.e., they are both .com domains). The com domain has no lower level, but the SUBSTRING_INDEX just returns the entire string com again. Since codefights.com is earlier in the alphabet than com, the table puts codefights.com first. What should happen is that codefights should be compared to the empty string, and com should appear first.

One trick to fix this problem is to prepend the hostnames with ‘…’, guaranteeing that there are three periods in each domain. So our query is now:

SELECT id, hostname, SUBSTRING_INDEX(CONCAT('...', hostname),'.',-1) as top,
                                      SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2) as mid
               FROM hostnames ORDER BY top, mid, hostname;

Writing down just the first four rows, this query returns:

[table id=11 /]

This has everything in the right order!

A problem and a workaround

In principle, we just have to eliminate the top and middle column, so we are only reporting the id and hostname. Before making this change, we can try running this in CodeFights. When we do it, we run into a problem: the CodeFights MySQL engine returns workbench.mysql.com before dev.mysql.com!

This seems to be a bug; running MySQL on a local machine returns dev.mysql.com before workbench.mysql.com. However, ordering by the hostname on CodeFights returns the opposite. When using ORDER BY top, mid, hostname, workbench.mysql.com appears first. This seems wrong, since both these hostnames have identical top and mids, so the ordering should be determined by hostname.

Part of being a programmer is working around issues in any software! The problem we are encountering comes from doing multiple orderings. Our workaround will be to concatenate the top, middle, and hostname into one string. We will separate the top, middle, and hostname by a space, because spaces cannot occur in a domain and spaces appear earlier in ASCII than any letter.

So our workaround is:

SELECT id, hostname, SUBSTRING_INDEX(CONCAT('...', hostname),'.',-1) as top,
                                      SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2) as mid
                FROM hostnames ORDER BY CONCAT(top, ' ', mid, ' ', hostname);

This orders dev.mysql.com and workbench.mysql.com correctly.

Eliminating columns

Now we need to eliminate the top and mid columns. We could do a subquery, but instead we will actually construct the top and the middle strings as part of the ORDER BY clause.

Our solution ends up looking like this:

CREATE PROCEDURE hostnamesOrdering()
    SELECT id, hostname FROM hostnames
          ORDER BY CONCAT(SUBSTRING_INDEX(CONCAT('...',hostname),'.',-1),
                                               ' ',
                                               SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2),
                                               ' ',
                                               hostname);

This solution only selects the desired columns. Notice that in the ORDER BY we have a single string, made from the “top” level domain, a space, the “middle level” domain, a space, and then the hostname. This gets around the implementation bug, and gets us a solution with 188 characters.

On a local MySQL database, we don’t need the final concatenation, and we could use multiple ordering to get:

CREATE PROCEDURE hostnamesOrdering()
    SELECT id, hostname FROM hostnames
          ORDER BY SUBSTRING_INDEX(CONCAT('...',hostname),'.',-1),
                              SUBSTRING_INDEX(CONCAT('...',hostname),'.',-2),
                              hostname;

Going further

As a code golfing exercise, we have some compact solutions. As a coding exercise, you might be interested in trying to solve the general case where we don’t have a limit on the maximum number of domains in a hostname!

This takes us back to our original solution:

SELECT id, hostname FROM hostnames ORDER BY reverse_hostname(hostnames);

where the interesting exercise is to create a custom function reverse_hostname. If you get stuck, this StackOverflow post might be useful.

Tell us…

How did you tackle this challenge? What do you think about the approach I took in solving it? Let us hear from you in the CodeFights forum!

CodeSignal Solves It: pastEvents

A little while back, our Challenge  of the Week was a tricky database challenge called pastEvents. 281 of our awesome CodeFighters solved it! I’ve created a walkthrough of the problem and a few different tactics for finding a solution. Here’s the problem, in case you need a refresher:

During the most recent social event you suddenly realized that you had forgotten your USB drive on one of the previous events. You are pretty sure that you had your flash drive with you just last week, which means that it was probably lost during one of the events of the last 7 days. So, you would like to find all the events you attended during this period.

And the output should be:

A table of all names and dates of events within 7 days of the most recent event, not including the most recent event, order from most to least recent.

The problem gives us a database with the table events, as well as a sample dataset:

[table id=1 /]

How would we construct the solution by hand?

  1. We would look for the most recent date, in this case the event with id 4 is the most recent with an event_date of 2016-12-02.
  2. We would then SELECT all dates that occur within 7 days before 2016-12-02. This would select the events with ids 2 and 3.
  3. Finally we would sort the returned events based on event_date with ORDER BY.

With this strategy in mind, let’s start putting the pieces together!

Step 1: Find the most recent event

For this, we need to find the maximum value of the event_date column:

SELECT max FROM events

The time taken for this query will scale linearly with the number of events we have stored, which is about the best we can hope for. After all, to find the maximum we have to look at the date of every single record!

Here is a different approach that also selects the most recent date:

SELECT event_date FROM events ORDER BY event_date DESC LIMIT 1

This approach is to take all the dates, order them from latest to earliest, and then select the first one off the list. This approach isn’t as good, because it isn’t as clear what the code is trying to do. If you are reviewing this code, you have to get to the very end of the line to realize that we’re only picking one value. You have to carefully think if you want to order the list in ASCending or DESCending order. A naive implementation of this query would also be slower than our first attempt, because first the entire list would need to be sorted before the most recent event was selected (MySQL is almost certainly smart enough to optimize this away). Finally, if our goal is to get a short solution, this line is less clear and more verbose than our attempt based off the max function.

Now we need to write a query that uses the latest date and selects events in the 7 days prior to our event. We can either use our query above as a subquery, or store the results as a variable and use that. We will start by using the variable @recent, as it leads to more readable code, and then optimize once we’re done.

Our solution so far looks like this:

CREATE PROCEDURE pastEvents()
BEGIN
    SET @recent = (SELECT max(event_date) FROM Events);
       /* can also use
            SELECT MAX(event_date) INTO @recent FROM Events;
        */
END

Step 2: Select events within 7 days of @recent

Given an event_date, we need to determine if it was within 7 days of @recent. MySQL provides 60 functions for dealing with date calculations. Some of the options relevant to us are:

[table id=3 /]

All of these include the most recent event. We can throw out the most recent event by adding another DATEDIFF, or by using BETWEEN to ensure that our DATEDIFF is also bigger than 1. Because we are assured that there is at most one event per day, eliminating the most recent one can be achieved with event_date != @recent.

We now add a SELECT with a WHERE clause to get the events within 7 days, so our code now takes the form:

CREATE PROCEDURE pastEvents()
BEGIN
    SET @recent = (SELECT MAX(event_date) FROM Events);
       
       SELECT name,event_date FROM Events WHERE DATEDIFF(@recent, event_date) <= 7 AND event_date != @recent;
END

There is a really sneaky way of eliminating the extra clause to see if the event is the most recent event. We are interested in selecting events where

 

Instead of using subtraction, what do we know about the reciprocal of date differences? We know that

 

 

We get the lower limit by @recent being 7 days ahead of event_date. If the difference between the dates is 8 days, then 1/(@recent-event_date) is . The cool part is the upper limit is achieved if @recent and event_date are one day apart. If we look at the case where @recent and event_date are the same day, then 1/(@recent-event_date) is undefined, so MySQL will return false for any comparison. This means we don’t have to worry about the upper limit, and instead we only care if

 

 

Writing this another way:

 

 

Our shorter (but conceptually harder to understand) solution is

CREATE PROCEDURE pastEvents()
BEGIN
       SET @recent = (SELECT MAX(event_date) FROM Events);
       SELECT name,event_date FROM Events WHERE 7/DATEDIFF(@recent, event_date) >= 1;
END

Step 3: Order the output

Once we’ve gotten this far, it’s simply a case of adding an ORDER BY clause to the end. Finally we have something that passes the test cases and the hidden tests! Let’s call it a solution with 171 characters:

CREATE PROCEDURE pastEvents()
BEGIN
       SET @recent = (SELECT MAX(event_date) FROM Events);  
       SELECT name,event_date FROM Events 
              WHERE 7/DATEDIFF(@recent, event_date) >= 1
              ORDER BY event_date DESC;
END

Refinement 1: Subclause

It’s clear that we could get shorter code by naming @recent something like @r. We can also eliminate some characters by running a subclause to get the maximum date:

CREATE PROCEDURE pastEvents()
BEGIN  
       SELECT name,event_date FROM Events,
              (SELECT MAX(event_date) as r FROM Events) recent
              WHERE 7/DATEDIFF(r, event_date) >= 1
              ORDER BY event_date DESC;
END

This code JOINS every event with the single value from the subclause SELECT MAX(event_date) as r FROM Events, which is just the variable we stored in @recent in our previous example. We want to be careful with JOINS, because the number of rows in the returned table is the product of the rows in the two input tables. In this case, the derived table recent only has one row, so we only have to sort through the same number of events as we started with.

Refinement 2: Change >= to >; eliminate BEGIN and END

We can also eliminate a character by noting that DATEDIFF has to return an integer, so requiring 7/DATEDIFF(r,event_date) >= 1 gives the same dates as 8/DATEDIFF(r,event_date) > 1. MySQL doesn’t require BEGIN and END for procedures, so we can eliminate those too. This gets us to:

CREATE PROCEDURE pastEvents()
       SELECT name,event_date FROM Events,
              (SELECT MAX(event_date) as r FROM Events) recent
              WHERE 8/DATEDIFF(r, event_date) > 1
              ORDER BY event_date DESC;

The shortest solution

The very short solutions to this challenge took a very different approach from the one I just demonstrated. Here’s the code submitted by CodeFighter gennadi_m:

CREATE PROCEDURE pastEvents()
    SELECT e.name, e.event_date FROM Events e, Events v
           GROUP BY 2 DESC
           HAVING 8/datediff(max(v.event_date), event_date) > 1

The first step is taking the Event table, and taking the cartesian product (i.e. OUTER JOIN) with itself. In our case, this gives us a 25 row table:

[table id=4 /]

The columns are grouped by “column 2” in the output, e.event_date. The group by is only important when we are using an aggregate function, which is this case is max. The first group on this table is the group e.event_date = 2016-11-07, for example. Out of this group, we ask for the maximum v.event_date, which is 2016-12-02. We can only use aggregate properties, so our table becomes:

[table id=5 /]

The combination of using a self-join, grouping on event date,  and max aggregation is just a complicated way of adding the most recent date to every row.

While the query is a lot smaller, joining a table with itself squares the number of entries in it. MySQL might be able to optimize your query in some cases, but you want to be careful before trying this trick in production code!

Tell us…

How did you tackle this challenge? What do you think about the approach I took in solving it, and the refinements I added? Let us hear from you in the CodeSignal forum!

CodeFights Solves It: pastEvents

pastEvent solution

Preparing for interviews? Looking for a new job?

Practice for interviews using real questions on need-to-know topics.
Get matched with our hiring partners when you're ready for a new job!

Watch video

See how Marcus used CodeFights and landed a job at Evernote!