Data Analyst Interview: What Each Round Tests and a Four-Week Prep Plan

Data analyst candidates arrive with very different backgrounds, yet most of them stumble in the same places: a LEFT JOIN that silently drops rows, a p-value described as "the probability that the hypothesis is true", and a product case where the candidate jumps to theories before checking whether the data itself is broken. This guide walks through a typical hiring process round by round: what each stage is really testing, which tasks come up again and again, and how to prepare in four weeks while keeping your current job.

What the hiring process usually looks like

Every company runs it a little differently, but the skeleton rarely changes. Knowing it in advance tells you what to practise first and what can wait until the final week.

Smaller companies often merge rounds, so SQL, statistics and a case may all happen in one call. It is perfectly fine to ask the recruiter about the format during the screen.

SQL: five patterns everyone gets tested on

Interview tasks look varied, but nearly all of them combine five moves: joins that respect missing data, conditional aggregation, window functions, date arithmetic and de-duplication. Let us use a small online school database: students (id, signup_date, source) and payments (id, student_id, paid_at, amount, status).

Task 1. What share of students pay within their first week?

For each traffic source, find the share of students who paid within 7 days of signing up. The classic mistake is filtering payments in WHERE, which quietly turns the LEFT JOIN into an inner join. Students without payments vanish from the denominator and the share looks better than it is.

SELECT s.source,
       COUNT(DISTINCT s.id) AS students,
       COUNT(DISTINCT p.student_id) AS paid_7d,
       ROUND(COUNT(DISTINCT p.student_id)::numeric
             / COUNT(DISTINCT s.id), 3) AS share
FROM students s
LEFT JOIN payments p
       ON p.student_id = s.id
      AND p.status = 'success'
      AND p.paid_at < s.signup_date + INTERVAL '7 days'
GROUP BY s.source;

Every condition on the right-hand table sits in ON, not WHERE. Expect the follow-up question: "What happens if we move the status filter into WHERE?"

Task 2. How many days pass between the first and second payment?

This one checks window functions. Number each student's payments in time order and pull the previous date with LAG:

WITH ordered AS (
  SELECT student_id, paid_at,
         ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY paid_at) AS n,
         LAG(paid_at) OVER (PARTITION BY student_id ORDER BY paid_at) AS prev_paid
  FROM payments
  WHERE status = 'success'
)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (
         ORDER BY EXTRACT(EPOCH FROM paid_at - prev_paid) / 86400
       ) AS median_days
FROM ordered
WHERE n = 2;

A strong extra point is explaining why you chose the median: a handful of students who come back a year later would drag the mean far upwards.

Task 3. Duplicates in the export

Payment gateways sometimes send the same transaction twice. You are asked to keep one row per student and payment date, the one with the highest amount. Same tool again: ROW_NUMBER() partitioned by student and date, ordered by amount descending, then keep n = 1. Mention that DISTINCT will not help, because the duplicate rows have different ids.

Also prepare for quick verbal questions: WHERE versus HAVING, how COUNT(*) and COUNT(column) treat NULL, when to use UNION ALL instead of UNION. The window functions chapter of the official PostgreSQL documentation is short and covers most of what interviewers ask.

Statistics and A/B tests: questions about understanding

You will rarely be asked to recite formulas. Interviewers want to know whether you understand what the numbers mean. The usual questions:

• What is a p-value? The probability of seeing an effect at least this strong if there is in fact no difference. It is not the probability that your hypothesis is true.
• Why calculate sample size before launch? So the test can detect the smallest effect the business actually cares about. Without it, "not significant" tells you nothing.
• Why not stop the test as soon as it turns significant? Repeated peeking inflates the false positive rate.
• What should you check before reading results? The split itself. If you planned 50/50 and got something noticeably different, the assignment is broken and conclusions must wait.
• How do you compare revenue per user? It has a heavy tail, so mention bootstrapping or a log transform.

If you read one book on the subject, make it "Trustworthy Online Controlled Experiments" by Kohavi, Tang and Xu. A large share of interview questions trace back to it.

The product case: do not rush to hypotheses

The classic prompt: "Last week the share of paying users fell by 12%. Where do you start?" A strong answer goes in order.

1. Check the data. Is tracking broken, did an event definition change, has all the data arrived?
2. Check the calendar. Weekend, public holiday, end of month, holiday season.
3. Slice it. Platform, app version, traffic source, new versus returning users. A drop usually lives in one segment.
4. Match it to events. Releases, price changes, payment provider outages, marketing campaigns.
5. Only now form hypotheses and say how you would test each one.

The interviewer is grading the structure, not whether you guess the cause. Think out loud.

The take-home: what reviewers look at

Take-homes are rarely judged on pretty charts. Reviewers look for four things: can the work be reproduced (code and steps, not just a final image), is there a short conclusion for the stakeholder at the top, are data limitations and assumptions stated, and did you keep the effort reasonable. If the task clearly needs several evenings, ask about the expected scope. That is normal practice, not a weakness.

A four-week prep plan

Assumes 8-10 hours a week and working knowledge of basic SQL and Python.

If you still need structured foundations before interviewing, the Google Data Analytics Professional Certificate on Coursera covers spreadsheets, SQL, Tableau and R basics without requiring prior experience, and the IBM Data Science Professional Certificate goes further into Python, SQL and machine learning with portfolio projects. Both work at your own pace, which suits the plan above.

No work experience yet?

Juniors without commercial experience are judged by their projects. One finished project with a clear question, a SQL extract, analysis and a five-line conclusion beats five notebooks of "playing with a dataset". It helps if the project resembles the company's work: cohorts and repeat purchases for an e-commerce business, completion rates and payments for an online school. If you are still unsure about the role itself, start with our comparison of data, product and business analyst roles.

FAQ

Do I need Python if the job ad only mentions SQL?

The first interview may skip it, but you will almost certainly need it on the job. The minimum: pandas for grouping and merging, simple charts, reading CSV and Excel files.

What if I cannot solve a task during the interview?

Say where you are stuck and suggest a workaround. Interviewers often give a hint and watch how you use it. Silence scores worse than a partial solution.

How many take-homes should I do at once?

One or two. Three in parallel usually means three mediocre submissions.

Key points

• Ask about the round structure during the screen and prepare in that order.
• In SQL, joins with NULLs, windows and dates decide most tasks. Keep right-table conditions in ON.
• Statistics questions test understanding: p-values, power, peeking, split checks.
• In a product case, check data and calendar first, then segments, then hypotheses.
• Compare programmes in our analytics course catalogue, and see the Data Analyst page for the career path.

Sources

• PostgreSQL: Window Functions (official documentation)
• Kohavi R., Tang D., Xu Y. Trustworthy Online Controlled Experiments. Cambridge University Press, 2020