PrepBank — Free Sample Chapter (7 real-style data analyst interview Q&As)

This is the sample chapter delivered by the email gate: 7 real-style questions with structured model answers, a genuine subset of the full bank. Questions are written in a realistic style based on patterns candidates report; none is attributed to any company.

How to use it: read the question, try to answer out loud in 3–5 minutes before reading the model answer, then compare structure: did you cover the approach, the key points, and the likely follow-ups?

---

1. SQL — ranking with window functions

Question: Write a query that returns, for each region, the top 3 products by total revenue in the last 90 days, along with the region, product name, and total revenue.

Model answer

• Approach. First confirm the schema: assume orders(order_id, region, product_id, amount, created_at) and products(product_id, product_name). Plan before writing: aggregate revenue per region + product, then rank within each region, then keep the top 3 rows per region.
• SQL.

  WITH rev AS (
    SELECT region, product_id, SUM(amount) AS total_revenue
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY region, product_id
  )
  SELECT region, product_name, total_revenue
  FROM (
    SELECT r.region, p.product_name, r.total_revenue,
           ROW_NUMBER() OVER (PARTITION BY r.region ORDER BY r.total_revenue DESC) AS rn
    FROM rev r
    JOIN products p ON p.product_id = r.product_id
  ) ranked
  WHERE rn <= 3;
  

• Why this structure. A window function with PARTITION BY region gives per-region rankings without self-joins, which stay correct when region sizes differ. I'd say out loud why ROW_NUMBER vs RANK: ROW_NUMBER always returns exactly 3 rows even with ties; RANK/DENSE_RANK would return more if products tie, which may or may not be desired — worth asking the interviewer which they want.
• Verification. Spot-check one region by hand, confirm row counts (≤3 per region), confirm no NULLs from the join (inner join drops products with a missing name — worth flagging).
• Likely follow-ups: tie-breaking policy; what if a region has fewer than 3 products; adding a running total; why not use GROUP BY region, product_id with a correlated subquery.

2. SQL — handling duplicates

Question: You discover that the same order_id appears on multiple rows in an orders table. How would you find the most recent row for each order, and what would you check before "just deduplicating"?

Model answer

• Approach. Don't delete anything first. Understand the duplicate cause before choosing the dedupe strategy: is it a broken ETL that re-inserts (idempotency bug), or legitimate late corrections/voids where multiple rows are each true?
• SQL for "most recent row per order".

  SELECT *
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY updated_at DESC
    ) AS rn
    FROM orders
  )
  WHERE rn = 1;
  

  updated_at (or the event timestamp) decides recency; if ties are possible, break them with a stable secondary key (e.g., id) so the result is deterministic.
• What I'd check before deduplicating. (1) How many duplicates and when do they appear (a trend or a one day)? (2) Do the duplicate rows differ only in timestamp, or in amount/status (the latter changes the answer: maybe I need the row with the latest status, not the latest row)? (3) Do downstream reports currently count or sum these rows (a silent double-count is the actual business bug to fix)?
• Likely follow-ups: deduping at the source vs in the reporting layer; using QUALIFY/DISTINCT ON equivalents; how to make the pipeline idempotent so duplicates don't recur.

3. Statistics — explaining a p-value

Question: A stakeholder asks: "If our p-value is 0.05, does that mean there's a 5% chance our result is wrong?" Explain a p-value to a non-technical person.

Model answer

• Approach. Answer the actual question first (no — that's the common misreading), then give the plain definition, then an analogy, then say what number does answer their real concern.
• Plain definition. A p-value is the probability of seeing data at least as extreme as ours if the effect we're looking for were actually zero. It measures surprise under the "no effect" assumption — it is not the probability that our effect is real, and not the probability that our result is "wrong".
• Analogy. "Imagine a coin that's actually fair. We flip it 10 times and get 9 heads. The p-value answers: how often would a fair coin be this surprising? About 1% of the time. So either the coin is off, or we got unlucky — and this number tells us how rare this kind of luck would be."
• Redirect. What the stakeholder really wants is "how much should I believe this?" That depends on the effect size, the sample size, how cleanly the test was run, and prior plausibility — the p-value is one input, not the answer.
• Likely follow-ups: why 0.05 is a convention, not a truth; p-hacking and multiple comparisons; confidence intervals as a more useful summary.

4. A/B testing — small but significant lift

Question: Your team runs an A/B test. Variant B lifts conversion by 0.4%, and the result is statistically significant (p < 0.05). Do you ship it? Why or why not?

Model answer

• Approach. "Significant" is not the decision. I'd say the decision depends on expected value, which needs four checks before yes or no.
• Check 1 — business value. Convert the 0.4% lift into revenue: 0.4% of how many conversions per year, at what average order value? A statistically significant 0.4% lift can be worth a lot at scale, or rounding error in revenue terms.
• Check 2 — operational cost and risk. What does the change cost to run long-term? Does it complicate the product, other teams' work, or degrade an experience elsewhere? A tiny lift plus permanent cost can be negative expected value.
• Check 3 — test hygiene. How long did it run (full weekly cycles? newness effects?), was traffic randomization clean, and did the effect hold in every segment or only one? Segment-level checks protect against a gain that hides a loss for a subgroup.
• Check 4 — interaction with future tests. If this variant changes the baseline, it can bias tests we run next.
• Answer. Ship it if the revenue lift clears the running cost and the test was clean; hold it if the lift is revenue-trivial or concentrated in one segment. I'd state the decision with the trade-off, not with the p-value.
• Likely follow-ups: multiple testing across many variants; novelty effects; sequential/peeking problems.

5. Case study — investigating a metric drop

Question: Weekly active users dropped 10% week over week. Walk me through how you'd investigate.

Model answer

• Approach. Structure it out loud: (1) verify the number, (2) decompose, (3) correlate with events, (4) prioritize hypotheses, (5) report with the most likely cause and what to do next.
• Step 1 — is the number real? Check the data pipeline first: was there a tracker deploy, a schema change, a timezone or definition change, or a holiday (e.g., the prior week was a spike, not this one a drop)? A 10% "drop" is sometimes a measurement artifact.
• Step 2 — decompose. Segment: new vs returning, platform (iOS/Android/web), geography, signup channel, and funnel stage (did visits drop, or activation, or retention within the week?). One slice that explains most of the drop narrows the search fast.
• Step 3 — correlate. Line the drop's exact start date against: app releases, marketing/spend changes, pricing or onboarding changes, platform incidents, and external events (competitor launch, news, seasonality of that week a year ago).
• Step 4 — prioritize. Rank the remaining hypotheses by plausibility × evidence, and design the cheapest check for each (a query, a segment cut, a log scan).
• Step 5 — report. Lead with the most likely cause and the evidence, name the other candidates, give the recommended action, and state how we'd confirm it.
• Likely follow-ups: "What if every segment dropped proportionally?" (points to pipeline or definition); how you'd quantify each hypothesis's contribution.

6. Behavioral — pushing back on a stakeholder (STAR)

Question: Tell me about a time a stakeholder asked for something you didn't think was a good idea. What did you do?

Model answer — structure to hit (adapt to your own real example):

• Situation. The stakeholder wanted a dashboard that "showed everything" for a team — dozens of metrics, no prioritization. (In your version: a real request from your real context.)
• Task. I needed to ship something the team would actually use, without dismissing the stakeholder's underlying need.
• Action. I didn't say no. I asked which decisions the team makes weekly and which metrics they check to make them, and mapped the request to those decisions. I then proposed three focused views tied to real decisions, explained the cost of "everything" (nobody reads what nobody can scan), and offered to add more tiles if a use case emerged.
• Result. The team adopted the three views as their weekly check-in; the "everything" page went unused within a month. The stakeholder later asked for more views from the focused template.
• What interviewers are probing. Whether you can disagree with a senior person productively: did you listen first, translate the request into outcomes, and win on evidence rather than volume?
• Likely follow-ups: "What if they'd insisted anyway?" (escalation path, documentation, agreed trade-off); "What would you do differently?"

7. Python / data tools — handling missing values

Question: How do you decide how to handle missing values in a dataset? Give me two different approaches and when each one is right.

Model answer

• Approach. The decision comes from why the values are missing. "Missing at random" and "missing because of the phenomenon we're studying" lead to different handling — so step one is always to inspect the missingness itself.
• Diagnose. Check the rate per column, whether missingness clusters by segment or time, and whether the presence of the value correlates with the target (that pattern — missingness being informative — is a signal in itself and shouldn't be smeared away).
• Approach A — drop. Right when missingness is random, low-rate, and row-level (e.g., 2% of rows missing one optional field), and the remaining sample still represents the population. Dropping is honest and simple when the bias it introduces is small.
• Approach B — impute with care. Right when rows are valuable but a field is sparse: median for skewed numeric data (mean for symmetric), mode or a dedicated "unknown" category for categorical, and — important — add a companion flag column (is_missing) so the model or analysis can use the fact of missingness. Never silently impute when missingness is informative; that erases the signal.
• State the trade-off out loud. Every choice biases the data in a different direction; I'd say which bias I'm accepting and why it's the smaller one.
• Likely follow-ups: multivariate imputation vs simple; missing in the target variable; how you'd test whether imputation hurt.

---

What's next

If the style works for you, the full bank extends this pattern across SQL & Databases, Statistics (including A/B testing), Case Studies, Behavioral (STAR), and Python & Tools — 100+ questions, each with a structured model answer and likely follow-ups, for $39 one-time (pre-order via the button on the site).

PrepBank — a study aid. No job or salary guarantees. Signed: the PrepBank team (AI-assisted).