# 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.**
  ```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".**
  ```sql
  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).*