The most common technical interview that analysts face, especially early in their careers, is the SQL interview. It has a bad reputation as a profoundly awkward experience, but from a recruiting perspective, it remains a good way to answer two questions:
Is this candidate able to independently write logic to pull data successfully and efficiently? Are they able to interpret the data appropriately?
These interviews (assuming they're live, rather than take-home, or interviews) usually go something like this:
If you're concerned about a SQL interview or have struggled through them, here are some tips that have helped me throughout my career:
1. Accept the fact that it could be awkward, and that's okay.
SQL interviews often remind me of exams in school, except someone is carefully watching me as I work. It adds a level of stress that simply doesn't exist in the day-to-day of most analysts.
Here are a list of behaviors that people may find awkward, but are completely okay during a SQL interview:
2. Take great notes, especially about the format of the tables. (Bring pen and paper just in case.)
I struggle to remember the structure of new tables, even simple ones. Write the table formats down in an accessible place, and ask the interviewer any questions you might have. Missed the name of one of the columns? Confused about what something means? Not sure if the table is on a per user or per account basis? Ask.
Remember that your interviewer is a resource, and it's their responsibility to be clear on the premise of the problem.
3. Say what you're thinking. Ask to take a moment if you need time to put your thoughts together.
Although these questions often have "right" and "wrong" (and "efficient" and "inefficient") solutions, a SQL exercise is about logical reasoning. Interviewers give out partial credit for your line of thinking, but if you never express it, the interviewer has no idea what happened.
A helpful framework to use when describing your approach can be to:
| Do | Example |
|---|---|
| Confirm the objective and your understanding. Ask any questions if necessary. | I need the mean revenue of customer group A and customer group B to understand which group typically spends more on our products. |
| Explain any initial thoughts or approach considerations. | I need both table A and table B, the first for revenue, the second for what group a customer belongs to. Because table A is a list of transactions, I'll need to aggregate up first. |
| Explain the different parts of your query as you write them. | This part of my query sums the revenue for each customer in table A. I want to nail this down before I join to our group type data. |
| Once finished, look over your work and describe the query holistically. | I summed revenue data for each customer in this first section before joining in the customer type data from a separate table by customer ID. I then calculated the mean revenue per customer by customer group here. |
| Remember to answer the question itself. | Group A has a higher mean revenue, but it would be interesting to see if this group is big enough that we should focus more on them than on Group B. |
If you've never had to explain how you wrote some SQL, take some time to practice doing so. In the interview itself, it's also okay to ask for time so that you can speak more clearly about the steps you've gone through or determine your next step.
4. Remember that some things don't matter.
SQL interviews are rarely designed to test someone's ability to remember details in syntax, how quickly they type, how nicely they write, or any number of factors that simply aren't relevant to success as an analyst. Memorizing the syntax of a `case when` statement in the type of SQL we use won't hurt you, but we don't test for things that are easily solved by a Google search.
Interviewers usually aren't trying to trick you either (after all, databases aren't usually designed to trick analysts). We just want to make sure you can build the logic (and/or see the flaws in your logic) to get the data you need.
5. Write your SQL for clarity.
As an interviewer, I'm sometimes afraid that I won't be able to read or understand a query and panic any time I see a long nested query. If it's difficult for you to write, it's likely difficult for me to read.
Although there is no need to stress over the details of your SQL formatting or note-taking, it's helped both me and my interviewers to invest here. Indent where it helps, label what different parts of your query do, give new columns practical names, and use `with`/CTE statements to break up your query instead of using nested queries. Notes like "assuming X is distinct" or "condition assumes none of these values are negative or zero" are helpful reminders both for your interviewers and yourself of the query's underlying logic.
This skill is also great to demonstrate, since this kind of organization and documentation is important for an analyst out in the wild. We do, after all, revisit our own queries and share queries with each other.
6. Practice, especially with joins.
When it comes to a SQL interview, there is no substitute for being really good at writing SQL to pull data. Practice!
Far and away, joins are the most common "difficult" part of a query to handle because it tends to be the most common, abstract statement we use that has the real ability to ruin absolutely everything. Visuals such as these can help:
Images from: https://www.w3schools.com/sql/sql_join.asp
Other common statements that are likely to appear are `group by` (and their associated aggregations), `case when,` and `union`.
In my personal experience, SQL exercises are focused on fundamental logic, since more complicated statements can be learned fairly quickly (and sometimes done through more simple statements).
And that's it for the tips. Good luck!
Interested in becoming an Analyst at HubSpot? Apply to one of our open Analyst positions today!