1. Write a query to find the second highest salary from an Employees table.
Use a subquery with MAX where salary < (SELECT MAX(salary)), or DENSE_RANK() = 2.
Data Analyst interview questions
Data Analyst interviews lean on SQL, spreadsheet fluency, and reasoning about metrics, can you pull the number and explain what it means. Below are real questions across the rounds you'll face, with worked answers.
How a Data Analyst interview runs
- SQL (live querying)
- Spreadsheet / Excel modeling
- Product & metrics case
- Behavioral (STAR)
- Take-home analysis
How to prepare for a Data Analyst interview
What the loop actually tests
A Data Analyst loop is checking one thing from three angles: can you get the right number, and then say what it means to someone who cannot read SQL. The SQL round tests the first half. The case and the take-home test the second, and most candidates who fail do so on the second.
Expect the SQL to be practical rather than clever: joins across three or four tables, a window function for a running total or a rank, a date bucket, and one question designed to see whether you notice duplicates or NULLs before they distort the answer. Recursive CTEs are usually less important than getting grain, joins, and edge cases right.
The rounds, in order
A recruiter screen, then usually a SQL screen in a shared editor with a small schema on screen. Then an onsite of three or four sessions: a longer SQL or spreadsheet round, a metrics case (define a metric, diagnose a drop, design a simple A/B test), a stakeholder or behavioral round, and at many companies a take-home analysis presented back to the panel.
The take-home is where the most time goes and the least is explained. It is judged on whether the analysis answers a business question, states its assumptions, and ends in a recommendation, far more than on the code.
What interviewers score
Correctness first, then the narration. In the SQL round the score is whether the query returns the right rows and whether you said out loud what could go wrong (fan-out on the join, NULLs in the aggregate, the grain of the table). In the case round it is structure: clarify the question, pick a metric and defend it, list the causes you would check in the order you would check them, and know when you would stop.
The behavioral round scores concrete stories with a number in them. A story about a dashboard that changed a decision beats one about building a dashboard.
How to prepare, and what to do first
If you have two weeks: spend the first three days on SQL under time pressure, timed, out loud, until joins, window functions and date logic are automatic. Then spend the middle of the time on cases, the round candidates rehearse least. Practice explaining a metric drop in ninety seconds. Finish with two or three behavioral stories rewritten so each has a decision and an outcome.
Run a scored mock for each round rather than reading answers. Reading tells you what a good answer looks like. A score tells you whether yours was one.
Where candidates lose the offer
Silence while writing SQL, a query that runs but double-counts because of a join fan-out, a case answer that lists twenty possible causes with no order, and a take-home with beautiful charts and no recommendation. The fix for all four is the same: say what you are doing, and say what the number means.
12 Data Analyst interview questions
2. Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
Aggregate orders to one row per day, then use SUM(daily_revenue) over a six-day date interval plus the current day. Use a date spine when the SQL engine cannot frame by interval.
3. Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
INNER keeps only matches, LEFT keeps all left rows plus matched right rows, FULL keeps all rows from both sides.
4. Calculate the median order value using SQL.
Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) where supported.
5. Find the top 3 products by revenue within each category.
RANK()/ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3.
6. How do you handle missing values in a pandas DataFrame?
fillna (mean/median/mode/forward-fill), dropna, or model-based imputation depending on context.
7. Merge two DataFrames on a key and keep unmatched rows from the left.
pd.merge(left, right, on='key', how='left').
8. When would you use a t-test vs a chi-square test?
A t-test compares means of a continuous variable, a chi-square test checks association between categorical variables.
9. What is Simpson's paradox?
A trend within groups reverses when groups are combined due to a confounder.
10. Estimate the number of ride requests in a city per day.
Market sizing: population times adoption times trips per day, with every assumption stated.
11. Tell me about a time you influenced a decision using data.
Use STAR: Situation, Task, Action, Result with a quantified impact.
12. Tell me about a time you had to meet a tight deadline.
Show prioritization, scoping, and communication under pressure.