Are You Impressing Your SQL Interviewer or Accidentally Doing the Opposite?

I’ve been a people manager for over 12 years of my 20+ year career across Amazon, Ebay, VMWare, and more, and I’ve conducted a lot of interviews throughout that time. Unfortunately, I’ve seen about 90% of candidates fail their SQL interview due to a lack of understanding of SQL functions, how the database processes the code, and how to apply SQL in the real world.

In some cases, I’ve seen candidates attempt to apply more advanced SQL functions, such as window functions. This probably sounds like it would be a good thing, however, simply knowing the functions and being able to get a result isn’t all that’s needed.

Sometimes candidates will write those functions to impress their interviewer, but the opposite happens. The candidate attempts to demonstrate that they are an advanced analyst or engineer by using advanced functions, but they fail to understand that using an advanced function doesn’t make someone an advanced analyst or engineer. Knowing how and when to use those functions does.

For example, let’s say that you have a table that looks like this and the sales_month value is always the first day of the month.

You’ve been asked to write a query that will find the total sales that occurred 1 year from your sales_month. The output should look like this.

You’ve heard that SQL window functions are something that advanced data analysts use, so you decide that to solve this problem you’ll write a window function. Sure, there are a couple of ways to solve this problem. Since you already showed the interviewer that you can use a join in a previous question, you’re pretty confident that by using a window function you’re really going to shine.

Your solution is to write the following code.

When you run the code, you get the expected results and you feel pretty confident that you just aced your interview.  Not only did you get the right output, but you used an advanced SQL function. Unfortunately, your interviewer is feeling the opposite.

They aren’t impressed by your use of the window function at all. In fact, they are very concerned about how much you actually know about using a window function and they’re more concerned your basic knowledge of SQL. This is because while the function that you wrote works in this moment, it’s at risk of producing some major bugs down the road.  

What that window function is doing is hard coding a lookup value that is 12 months from your current date. Since there’s always 12 months in a year, this shouldn’t be a problem. But what if there was a data issue on some of your back-end tables. For some reason data didn’t get loaded properly or some data was accidentally deleted. Instead of having 12 consecutive months, your data is missing a value for 2024-02-01.

But since you hard coded your window function to look 12 records into the future, the 12^th record is going to be 2025-02-01, not 2025-01-01. Now you have a major bug and there’s a good chance it will go unnoticed and that’s going to lead to some bad business decisions and a lot of headaches for your business partners. All you really needed to do was write some code like this.

Instead of using a “left self join” to solve the problem, your code is more complex and it has a bug. That decision to try to use something more advanced just cost you the job.

This is why it’s not simply enough to be able to write a few pieces of SQL code. You must understand what options are available, how to pick the best option based on performance, readability, and protection from bugs. And then you need to be able to QA your work to avoid producing bugs. When you fully understand SQL and how to apply it in the real world, you’ll increase your chances of success during your interview.