How to Ace the SQL Interview
What to do NOW to feel confidant while interviewing
Almost every company looking to hire for its data team has some form of a SQL interview. Whether it’s a live coding interview or a take-home assignment, it exists as an integral part of the interviewing process.
I’ve been on both sides of the SQL interview, doing the interviewing and being the interviewee. As the interviewee, it can be quite intimidating to problem-solve with someone watching your every move. I honestly hate coding in front of people; it makes me nervous! So I get it.
In today’s newsletter, I’ll talk about how to prepare for a SQL interview properly. What concepts should you know like the back of your hand? How should you practice? But also, what are interviewers looking for in a candidate? Surprisingly, it may not always be the right answer. Let’s get into it.
Key SQL Concepts to Know
Going into an interview, there are typically a few common themes. Interviewers will most likely test you on some, if not all, of the following concepts.
Aggregates
It’s important to know SQL aggregate functions so you can answer any type of analysis question that comes your way. As an analytics engineer, you may not use aggregate functions in your data models, but they are key for quick data pulls and computing business metrics.
The most important aggregate functions to know are:
COUNT
SUM
MIN
MAX
I guarantee you will have to use at least one of these in whatever question you are asked to solve using SQL. I’ve done an entire in-depth post walking you through how to use SUM and COUNT. You can check that out here.
Window Functions
I never thought I needed to learn window functions until I had my first live coding interview. And boy has learning them ever since made my life a WHOLE lot easier. LEARN WINDOW FUNCTIONS.
A problem that can be solved with or without a window function will be solved 10x faster when you use a window function. In SQL interviews, time is precious. You’re expected to get through a few different questions, not waste all of your time on one.
Window functions will help you solve any problem faster and more efficiently. Faster and more efficient in this case also translates into more readable and easier to debug.
Here are a few of my favorite window functions:
FIRST_VALUE
LEAD
LAG
SUM
COUNT
This is another topic that I’ve done an entire post on, so I’ll spare you and link to that in case you want to learn more.
Date Functions
Working with dates in SQL can be messy. It’s still one of those things that I still don’t feel 100% confident in. I’m always learning better ways to deal with them in my data models and analyses.
This being said, know the basics. What’s the difference between a timestamp and a date? When should you use each of these?
Make sure you know the most common date functions. These are the ones that come in most handy for me:
DATE_TRUNC- round a timestamp to the interval that you need it (EX: will round
01-13-2023 10:13:11to01-01-2023 00:00:00if looking to round to the month)DATEDIFF- find the difference in days between two dates
DATE_ADD- add a specified number (1, 2, 10, etc.) and unit of time (minute, hour, week, year, etc.) to a date
EXTRACT- pull out the day, month, year, etc. from a timestamp or date value
CURRENT_DATE/CURRENT_TIME- get the current date/time
Using Data Lemur to Practice, Practice, Practice
Once you read about and understand the SQL concepts listed above, it’s time to put them into action. You are never going to get better by only reading how to solve problems, you need to do them yourself!
To add another layer, you are never going to get better at interview problems unless you practice the format of the interview problems you will be given. Unfortunately, most problems you’re given in interviews do not mimic the actual problems you will be solving in your day-to-day. Interview questions are a whole other breed of problems.
Luckily, there are a lot of great resources out there that mimic these interview questions. One of those is a free tool called Data Lemur. It has a whole library of SQL questions asked by companies like Facebook, Tesla, Twitter, and UnitedHealth, ranging from easy to difficult.
I’ve also been sharing monthly SQL challenges like this one with paid newsletter subscribers.
Creating a Study Plan
Don’t go into an interview thinking you’ll start practicing a few days before. If you don’t have a proper plan, you’re always going to be overwhelmed when the days before an interview comes around.
Start practicing your SQL skills while you are still applying to new roles, preparing enough time ahead for when that interview comes. I recommend setting aside just 30 minutes each day to do 1-3 problems.
Don’t overwhelm yourself! Short bursts of consistency will get you further than one 3-hour session. By practicing daily, you can fail, learn, and then try again the next day. If you get a problem wrong, read the solution, learn the function or clause used to solve it, and then come back to the problem another day until you get it right.
How to Approach the Interview
Of course, knowing what you are doing is a huge part of the interview, but surprisingly, so is what you say and don’t say. If you do ANYTHING, make sure you talk through your thought process during an interview.
I can’t tell you the number of times I’ve sat watching someone struggle without them saying a word. They were staring at the problem and thinking but I couldn’t help them because they weren’t saying what they were thinking.
You want to talk about:
why you are using a certain function
how you are planning to solve the problem
what you are struggling with understanding
best practices that you are or aren’t following
Lastly, ASK QUESTIONS. If you don’t fully understand the problem, ask for clarification. Even if you can’t remember the exact name of a function you want to use, ask the interviewer!
For example, you may use MySQL in your day-to-day but the code editor for the interview may be using NoSQL. Because the functions are slightly different, you may have to ask for some help. If you don’t, the interviewer may just assume you have no idea what you are doing. By explaining, most people would be more than willing to help you out and see past that.
I’ve ended interviews that I thought I bombed, because I knew I didn’t give the most optimal solution, only to find out that I made it to the next round. The interviewers appreciated my way of solving things and how I called out the things I didn’t know along the way.
Conclusion
Interviews don’t have to be a scary thing. Look at them as a chance to improve your technical and communication skills. Sometimes we don’t know what we don’t know and having someone else point that out is extremely helpful in our self-growth.
And, if you get rejected, it’s not the end of the world. It’s just a bump in the road. I’m a firm believer that every experience teaches us something, even if it didn’t end the way we had planned. Get back out there, practice your SQL problems, and find the opportunity that is meant for you!
Have a great week!
Madison Mae
Want the fast track to becoming an analytics engineer, or landing any job in data for that matter? My ebook The ABCs of Analytics Engineering will help you learn every skill you need to know to embark on this career path and land your first role.
Investing in your own learning is the best investment you can make. Check out the book, use it to create a study plan, and allow it to accelerate you on your analytics engineering journey.