Data analyst interview: SQL, A/B tests, and the…

An analyst interview is almost always built from the same blocks: SQL on a shared screen, statistics and A/B tests, a bit of Python, and a product case along the lines of "revenue dropped 10%, figure it out". The questions repeat from company to company. In the Sobes question bank, collected from real interviews mostly at companies hiring in Russia, these are "How is WHERE different from HAVING?", "Which window functions have you used most? How does RANK differ from DENSE_RANK?", and "How do you analyze a metric drop, where do you start?". The last one was asked at 7 companies.

Still, people stumble on SQL more often than on the case. In the interview reviews of Sobes users, "SQL window functions" came out weak in 40% of the reviews where the topic was assessed, and "SQL and JOIN" in 39%. For comparison, A/B testing was weak in 24%.

SQL: window functions and JOINs

You usually get one or two tasks and write them in front of the interviewer. Three things are checked: whether you know the syntax without hints, whether you understand how many rows the query returns, and whether you verify the result.

With JOINs there is one main mistake: row multiplication. You join orders to payments, an order has two payments, and the order total doubles. Before writing, say out loud what the relationship between the tables is: one to one or one to many. The question "Why is LEFT JOIN used here and not another JOIN type?" checks the same thing: whether you know which rows an INNER join would drop.

With window functions people trip in three places: the difference between ROW_NUMBER, RANK, and DENSE_RANK, the default window frame with ORDER BY, and trying to filter on a window function in WHERE. The standard "first order of each user" task goes like this:

select user_id, order_id, created_at
from (
  select o.*,
         row_number() over (partition by user_id order by created_at) as rn
  from orders o
) t
where rn = 1;

It helps to explain the subquery right away: a window function is evaluated after WHERE, so you can only filter on it one level up. The question "Why do we need window functions in SQL and how do they differ from GROUP BY?" is about the same idea: GROUP BY collapses rows, a window keeps them.

A set worth being able to write without thinking: top N within a group, a running total, the difference from the previous row with LAG, a share of the group total, cohort retention. You can practice these on tasks in the SQL trainer.

Statistics and A/B tests

Definition questions: "What are the null and alternative hypotheses?", "What types of statistical tests do you know?", "Where do the median and the mean sit in a right-skewed distribution?". A practical one nearly always follows: "Tell me about an A/B test case: what was the test and what did you do in it?".

On definitions, watch your precision. A p-value is not "the probability that the hypothesis is true". It is the probability of seeing a difference at least this large when the null hypothesis is true. On the practical question, go step by step:

We tested a new payment screen. The primary metric was conversion to payment, and we calculated the sample size up front for a one percentage point effect, which came to two weeks. We split by user, not by session. The difference came out at half a point and not significant, so we didn't ship it, but the effect was more visible among new users, so we ran the next test on them only.

It has the metric, the duration calculated before launch, the randomization unit, and an honest result. An insignificant test with a clear decision sounds more convincing than "it grew by 30%".

Common follow-ups: why you cannot stop a test the moment you see significance, what to do with several metrics at once, and when an A/B test cannot be run at all.

Python and pandas

For an analyst this is usually a short block: "How do you filter a DataFrame by one condition?", "How do you aggregate values by one field and one measure in pandas?". Being comfortable with groupby, merge, pivot_table, dates, and missing values is enough. A good move is to say how you would solve the same task in SQL. The interviewer sees that you understand the operation and are not just recalling a method name.

The product case

The most common case is a metric drop. In the bank it sounds like this: "If the North Star metric (number of successful orders) is going down, what do you do?". The interviewer is not waiting for a correct answer. They are watching the order of your steps:

Clarify the metric: how it is calculated, over what period it dropped, compared with what.
Check the data: whether tracking broke, the calculation logic changed, or a load was delayed.
Rule out external causes: seasonality, holidays, competitor promotions, a payment provider outage.
Break the metric into factors. Orders = visitors × cart conversion × payment conversion. Find which factor sank.
Slice by segment: platform, app version, region, channel, new versus returning users.
Tie it to changes: releases, experiments, price changes.
State a hypothesis and say how you would test it.

The start of a good answer sounds like this:

First I'd clarify how the metric is calculated and what "dropped" means: day over day, week over week? Then I'd check it isn't a data problem, for example events from one platform going missing. If the data is fine, I'd break orders down along the funnel, see which step sank, and then slice that step by platform and app version.

The candidate narrows the search area step by step, and each step can be checked with a query.

How to run a case out loud

Cases are failed through silence and guessing more often than through not knowing metrics. A few rules:

Ask questions. The prompt is almost always incomplete on purpose, and the interviewer expects you to ask about the period, segments, and recent releases.
Name what you are doing: "now I'm checking the data", "now I'm breaking it into factors". The interviewer can follow your reasoning even if you get a detail wrong.
Test one hypothesis at a time and state the conclusion: "it's flat across platforms, so it isn't the app release".
Finish with a two-sentence summary: what you found and what you propose to do.

The same principles apply to live coding and are covered in detail in Live coding interview: what to say while you code. And if a follow-up lands on something you don't know, the techniques from the post on what to say when you don't know the answer will help.

How to rehearse

SQL can only be rehearsed by hand. Solve window and JOIN tasks in the SQL trainer without autocomplete and explain out loud how many rows the query will return. Go through statistics theory using the data analytics and product analytics sections of the bank: a one-minute spoken answer with an example.

The case is convenient to practice in the mock interview, which has a case interview mode with analytics tasks. You lead the conversation, and the interviewer gives out facts only in response to a precise question. Ask "what happened?" and you get nothing. Ask "how did payment conversion on Android change over the last week?" and you get a number. It is a good cure for the habit of guessing instead of asking. The review after the case shows which facts you never requested.

Which other topics trip candidates most often is covered in the post on where candidates fail interviews.