Data analyst interview questions

As asked

Without writing it on a whiteboard, walk me through how you would structure a SQL query to return the second highest salary within each department from an employees table.

Sample answer outline

Explain a window function approach: DENSE_RANK or ROW_NUMBER partitioned by department and ordered by salary descending, then filter to rank equal to two in an outer query. Cover why DENSE_RANK handles ties differently from ROW_NUMBER, and what happens when a department has only one salary. Mention that a correlated subquery is the older alternative but reads worse and scales poorly.

Expect these follow-ups

• How does your answer change if two people tie for the top salary?
• Why pick DENSE_RANK over ROW_NUMBER here?
• What if the department has fewer than two employees?

As asked

A stakeholder says a report is missing customers. Explain the difference between an inner join and a left join, and how the wrong choice could drop those customers.

Sample answer outline

An inner join returns only rows with a match on both sides, so customers with no orders silently disappear. A left join keeps every customer and fills order columns with nulls where there is no match. Tie it to the business case: a revenue report joined on orders with an inner join will undercount the customer base. Mention checking row counts before and after a join as a sanity check and handling the nulls deliberately.

Expect these follow-ups

• How would you spot that a join silently dropped rows?
• When would a full outer join actually be the right call?
• What does a fan-out from a one to many join do to your sums?

As asked

You are handed a CSV export of 200,000 sales records that you have never seen before. Walk me through your process to get it analysis ready.

Sample answer outline

Start with profiling: row count, column types, null rates, distinct values, and ranges per column. Check for duplicates, inconsistent categories such as UK versus United Kingdom, mixed date formats, currency and unit consistency, and impossible values like negative quantities. Decide deliberately how to treat missing values rather than dropping silently. Document every transformation so the cleaning is reproducible and reviewable.

Expect these follow-ups

• How do you decide whether to drop or impute a missing value?
• What would make you go back to the data owner before analysing?
• How do you make this cleaning reproducible next month?

As asked

You are about to send a headline figure to the leadership team. What checks do you run to be confident the number is right?

Sample answer outline

Triangulate the figure against a second source or a known total, sanity check the order of magnitude, and reconcile against last period to explain any large movement. Re-run the query on a small known slice you can hand verify, confirm the date range and filters match the question asked, and check for nulls or excluded rows that quietly change the denominator. Have a peer review the logic for anything high stakes.

Expect these follow-ups

• What is a number you got wrong once and how did you catch it?
• How do you reconcile two systems that disagree by a few percent?
• How do you balance speed against this level of rigour?

As asked

A stakeholder always asks for the average. When is the average the wrong summary, and what would you report instead?

Sample answer outline

The mean is distorted by skew and outliers, so for salaries, house prices, or order values the median is usually more honest about the typical case. Explain skewed distributions plainly, mention that the mode matters for categorical or bimodal data, and that reporting a spread such as the interquartile range stops a single number from hiding the shape. Tie the choice back to the decision the stakeholder is making.

Expect these follow-ups

• How would you explain skew to someone with no stats background?
• What does the gap between mean and median tell you?
• When is the mean genuinely the better choice?

As asked

An experiment shows the variant beat control on clicks but the result is not statistically significant. The team wants to ship it. What do you say?

Sample answer outline

Separate the observed lift from the evidence for it: a positive point estimate that is not significant means the data is consistent with no real effect. Check sample size and whether the test reached its planned power, look at the guardrail metrics, and ask whether the team peeked early. Recommend either running longer to reach the planned sample or shipping with eyes open if the downside is low, but be clear about the risk.

Expect these follow-ups

• How would you explain not significant without saying it failed?
• What guardrail metrics would you insist on checking first?
• How does peeking inflate the false positive rate?