Data Analyst Interview Prep: Questions and a Mock Test

Interviewers do not set hard SQL. They set SQL with a trap, and watch whether you notice.

NULL handling is the most common. NULL is the absence of a value, so any comparison with it yields unknown rather than true. A filter of the form status != 'cancelled' silently drops every row where status is NULL, and NOT IN against a subquery containing one NULL returns nothing at all. COUNT(column) skips NULLs while COUNT(*) does not, and an average over a column with NULLs divides by the number of non-null values, which is frequently not the denominator anyone intended.

Join fan-out is the second. Joining to a table where the key is not unique multiplies rows, so any SUM computed afterwards is inflated. Checking cardinality before aggregating is the habit being assessed, and saying so out loud is worth marks even when the data turns out to be clean.

The third is the difference between filtering in WHERE and filtering in the ON clause of an outer join. A predicate on the right-hand table placed in WHERE quietly converts a LEFT JOIN into an inner join, because the NULLs the join produced fail the test. This one appears in nearly every loop.

The fourth is the difference between WHERE and HAVING: WHERE filters rows before grouping, HAVING filters groups after. Using HAVING for a row-level condition works and scans more than it needs to; using WHERE for an aggregate condition is an error.

Window functions are the clearest dividing line between a junior and a mid-level analyst, and the PostgreSQL documentation defines them exactly: "a window function performs a calculation across a set of table rows that are somehow related to the current row".

The distinction from an aggregate is the part to be able to state. "Window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities." That is why you can show each order alongside the customer's total without a self-join, and it is the reason window functions replace so much awkward SQL.

PARTITION BY defines the neighbourhood: it "divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s)", and the function is computed across the rows in the current row's partition.

The evaluation order explains a rule that otherwise looks arbitrary. The rows a window function sees are those of the virtual table produced by FROM "as filtered by its WHERE, GROUP BY, and HAVING clauses". So window functions run after filtering, which is why they "are permitted only in the SELECT list and the ORDER BY clause" and are forbidden in WHERE, GROUP BY and HAVING. To filter on a window function's result, you compute it in a subquery or common table expression and filter in the outer query. That pattern, ranking inside and filtering outside, is the single most useful thing to have ready.

Know the three ranking functions apart: ROW_NUMBER breaks ties arbitrarily, RANK ties then skips, DENSE_RANK ties without skipping.

The case round usually asks you to define a metric or explain a movement, and precision about definitions does most of the work.

A rate is a ratio, so it can move for three different reasons: the numerator changed, the denominator changed, or the mix underneath changed while every segment stayed flat. That third case reverses conclusions and is worth naming explicitly whenever a rate is discussed.

Funnels have their own traps. The denominator at each step should be stated, since step conversion and overall conversion are different numbers. The window matters, because a user who converts three days later either counts or does not depending on a choice somebody made. And ordering assumptions frequently do not hold, since real users skip steps, repeat them and arrive mid-funnel from a link.

Definitional drift is the recurring real-world problem. Two teams reporting different numbers for the same metric almost always differ on whether bots and internal traffic are excluded, whether the day boundary is UTC or local, whether anonymous identifiers count as users, and whether identity is stitched across devices. The professional response is to agree the definition once and encode it in one place rather than re-deriving it per query.

Data quality checks belong in the same conversation. The dbt framing generalises well: tests are select statements that "seek to grab failing records, ones that disprove your assertion", and "if the data test returns zero failing rows, it passes". Uniqueness, not-null, accepted values and referential integrity catch most of what silently corrupts a dashboard.

Analysts are asked to interpret experiments far more often than to design them, and the failure modes are consistent.

A p-value is the probability of data at least as extreme as yours, assuming the null hypothesis is true. It is not the probability that the null is true, and one minus the p-value is not the probability that the effect is real. This reversal is the most common statistical error in the field.

Peeking inflates false positives, because checking repeatedly gives the statistic many chances to cross a threshold that random fluctuation will eventually cross. Multiple comparisons do the same across metrics: at twenty metrics and a 0.05 threshold, one significant result is what chance alone produces. The defence is nominating the primary metric before looking.

A non-significant result is not evidence of no effect. The informative output is the confidence interval, which distinguishes a study that genuinely found nothing from one that could not have detected anything.

Sample ratio mismatch deserves specific attention because analysts are often the ones who notice it. If the split is meant to be even and is not, at realistic sample sizes that is not chance; something in assignment or logging is broken, and whatever caused it may also be selecting which users appear. Reweighting patches the symptom and leaves the selection bias in place, so the correct response is to treat the result as untrustworthy until the cause is explained.

And the general principle: an observational difference between users who did and did not do something is a comparison between kinds of people, not an effect.

The last round is usually communication, and it is not a soft skill in this context. An analysis that nobody acts on has failed regardless of its correctness.

Start from the decision. Knowing who will act on the number, and what they would do differently at different values, determines the whole shape of the output, and an analysis that changes nobody's behaviour at any plausible value is a report rather than a finding.

Lead with the answer. Analysts frequently narrate their method and arrive at the conclusion last, which is the reverse of what a decision-maker needs. Conclusion, then the evidence, then the caveats, then the method for anyone who wants it.

Be explicit about uncertainty without hiding behind it. Saying that a difference is within normal weekly variation is useful; refusing to give a view because the data is imperfect is not. Stating the confidence interval, or the range of plausible values, gives someone what they need to act proportionately.

The question that comes up in most loops is what you do when a stakeholder does not like the answer. The professional response separates the facts from the interpretation: if they have information that changes the analysis, incorporate it gladly; if they simply prefer a different conclusion, document the finding and let the decision be theirs. Quietly adjusting an analysis to fit a preferred conclusion is the one failure that ends an analyst's credibility permanently, and interviewers ask about it for exactly that reason.