Battelle Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Battelle, you are at the intersection of high-stakes research and practical application. Battelle is a global organization that thrives on solving complex challenges, often requiring the application of advanced analytics to fields ranging from national security to public health. Your role is to transform raw, often messy data into actionable insights that drive strategic decisions and support the organization's mission-critical objectives.

Learn the economics of the product category before the loop. Marketplaces, subscription products and ad-supported products turn on different core quantities (match rate and liquidity, retention and churn, fill rate and yield) and fail in different characteristic ways.

Focus areas

• Read vintage curves, not blended portfolio averages
• Separate authorization, settlement and dispute outcomes cleanly
• Decompose expected loss into PD, LGD, EAD

Overview

You will function as a bridge between technical data streams and business stakeholders. This requires not only deep technical proficiency in statistical modeling and machine learning but also the ability to translate technical findings into clear, impactful narratives. Whether you are diagnosing a sudden drop in a critical metric or designing a robust experimentation framework, your work will directly influence the success of the projects you support.

Expect a work environment that values scientific rigor and collaborative problem-solving. While the projects are diverse, the core of the Data Scientist role remains consistent: to provide clarity through data. You will be expected to demonstrate independence in your technical approach while maintaining the communication skills necessary to thrive in a cross-functional, mission-driven team.

The Battelle interview process

Battelle candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Recruiter or Hiring Manager Call

Underneath the questions about your past work sits a resourcing question. Given four things worth doing and one of you, which gets done and what happens to the rest? Managers ask because that is the daily texture of the job, and because the answer shows whether you rank work by effort or by what it changes. The weak version sorts by personal interest or by whoever asked most insistently. The strong version ties each candidate piece of work to a decision somebody downstream is waiting on, and then names the one you would drop and who you would tell.

What to demonstrate

• Whether you rank work by the decision it unblocks or by how interesting the method is
• How you describe a request you declined, and whether you can say who you said it to
• Whether your sense of how long something takes survives one follow-up question about the messy part
• How you decide something is good enough to hand over unfinished

How to prepare

• Write out your current queue and, next to each item, the decision that stays stalled until it lands. Anything with no waiting decision becomes your example of work you would cut
• Rehearse turning down a plausible stakeholder request out loud, including the smaller alternative you offered instead
• Have one case where you shipped a rough answer early and one where you refused to, with the reason that separated them

Technical Discussions

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

• Whether your row counts survive each join, and whether you notice on your own when they do not
• Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
• Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
• Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

• Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
• Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
• Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Formal Presentation

Rounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.

What to demonstrate

• Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
• The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
• Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options

How to prepare

• Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
• Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
• Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.

Where candidates lose points

Reading the most recent months of fraud and dispute rates as final

Consumer dispute rights commonly run around 120 days from the transaction or expected delivery date, and several reason codes run considerably longer, so the disputes belonging to a recent transaction month have simply not been filed yet. Any chart attributed by transaction date therefore slopes down at the right edge regardless of what is happening. The fix is to report only matured cohorts, or to apply development factors estimated from completed months and to show the estimate as an estimate.

Assuming a model is fair because protected attributes are not among its inputs

Postcode, device, tenure, income proxies and even transaction patterns correlate with protected characteristics, so a model can produce a disparate outcome without ever reading the attribute. Credit decisions additionally carry an explainability obligation in many jurisdictions, since a denial has to be accompanied by its principal reasons, which constrains model form and feature engineering rather than being a reporting afterthought. Treating fairness testing and reason-code generation as design constraints from the first model version is far cheaper than retrofitting them to a deployed one.

Interpreting a change before checking data quality and logging

Spend the first pass on row volume by day, null rates, duplicate keys, and whether the step change lands on a release or tracking-migration date. A discontinuity that coincides with a deploy is an instrumentation hypothesis before it is a behavioural one.

Optimising accuracy on a heavily imbalanced target

State the base rate first, then choose the metric from the relative cost of a false positive against a false negative: precision and recall at the operating threshold, PR-AUC, or expected cost. At a 1 percent positive rate, predicting the majority class for everyone scores 99 percent accuracy and is worthless.

Questions & practice

Collapse retry chains and compute a dollar-weighted approval rate

Medium · sessionisation · window functions · dollar-weighted rates

fct_payment_authorization gives auth_id, card_token_id, merchant_id, amount_minor, transaction_currency, requested_at, auth_result, is_reversal, channel and issuer_country. Two reference frames give the minor-unit exponent per currency and a daily rate to one reporting currency. Collapse retry chains first: attempts sharing card_token_id, merchant_id and amount_minor whose consecutive gaps are under 15 minutes form a single attempt, whose outcome is its last row. Exclude reversals and zero-amount verifications. Return a 7-day rolling dollar-weighted approval rate by channel and issuer_country.

Approach

1. Filter before grouping: drop is_reversal rows and zero-amount verifications, since neither is a purchase attempt and both would otherwise sit in the denominator.
2. Sort by card_token_id, merchant_id, amount_minor and requested_at, take the gap to the previous row within that key, mark a chain start where the gap exceeds 15 minutes or the key changes, and label chains with a cumulative sum of that flag. This is a gap rule between consecutive attempts, not a fixed clock bucket, so a chain may span more than 15 minutes in total.
3. Keep each chain's terminal row by requested_at. If a retry was approved, the purchase was approved; keeping the first row reports the decline that caused the retry as the outcome.
4. Convert amounts exactly once: amount_minor divided by 10 to the power of the currency exponent, multiplied by the reference rate for the authorization date. Do not reach for settlement_fx_rate, which is null on precisely the declined rows the denominator needs.
5. Build the rolling window as a ratio of two rolling sums, approved value over total value, per channel and issuer_country. A rolling mean of daily ratios weights a quiet Sunday the same as a busy Friday.

Follow-up

• The count-weighted rate is flat while the dollar-weighted rate falls 80 basis points. What do you look at first?
• How would you choose the 15-minute window rather than inheriting it?
• A merchant moves from two retries to five. Which of your two rates moves, and is that a real change in approval quality?

Simulate false alarms in a merchant chargeback monitoring rule

Medium · simulation · rare events · monitoring thresholds

Baseline matured first-chargeback rate is 12 per 10,000 settled transactions. A monitoring rule alerts when a merchant's observed monthly rate exceeds twice baseline. For monthly settled transaction counts of 500, 2,000, 10,000 and 50,000, simulate the false-alarm probability per merchant-month under the baseline, and the power to detect a merchant whose true rate is 30 per 10,000. Then, for a portfolio of 4,000 merchants split 60, 25, 10 and 5 percent across those four counts, give the expected number of false alarms per month.

Approach

1. Recognise the rule is a threshold on an integer count, not on a continuous rate. At n = 500, twice baseline is 24 per 10,000, so the first observable value above it is 2 chargebacks, or 40 per 10,000. Derive the trigger count for every n before simulating anything.
2. Draw binomial counts with numpy at p = 0.0012 and take the share at or above the trigger for the false-alarm rate, then repeat at p = 0.0030 for power. Use at least 200,000 draws per cell so a probability near 0.001 has a usable standard error.
3. Cross-check every simulated cell against the Poisson approximation with lambda = n*p, which is tight here because p is tiny. A mismatch almost always means the trigger count is off by one.
4. Weight the per-merchant false-alarm probabilities by the portfolio mix, and report the share of expected alerts contributed by each size band rather than only the total.
5. Close on the operating consequence: a fixed multiplicative threshold is not a constant false-alarm rate across merchant sizes, so either the threshold scales with n or small merchants need a minimum volume before the rule applies.

Worked solution (30 min)

1. For each n, compute trigger = floor(2 * 0.0012 * n) + 1 and print the four values before simulating.
2. Simulate 200,000 binomial draws per n at p = 0.0012 and take the share at or above the trigger.
3. Repeat at p = 0.0030 and record power for the same triggers.
4. Compute the Poisson tail 1 - CDF(trigger - 1, lambda = n*p) for both p values and confirm agreement within Monte Carlo error.
5. Multiply the false-alarm probabilities by 2400, 1000, 400 and 200 merchants and sum.

Expected result: Trigger counts of 2, 5, 25 and 121. False-alarm probability roughly 12 percent at n = 500, roughly 10 percent at n = 2,000, under 0.1 percent at n = 10,000 and effectively zero at n = 50,000. Power against 30 per 10,000 roughly 44, 72, 85 and above 99 percent. Expected false alarms about 390 per month, with over 99 percent of them coming from the two smallest bands.

Verify it yourself

• The four trigger counts are derived, not guessed: each is the smallest integer strictly above 0.0024n, giving 2, 5, 25 and 121. Derive them in exact or integer arithmetic, because 20.0012*10000 evaluates to 23.999999999999996 in binary floating point and floor-plus-one silently returns 24.
• Simulated probabilities match the Poisson tail at lambda = n*p within about two Monte Carlo standard errors.
• Power exceeds the false-alarm rate at every n; if it does not, the null and alternative rates are swapped.
• Doubling the draw count moves each probability by less than its own simulated standard error.

Follow-up

• How would you set a threshold that holds the false-alarm rate roughly constant across merchant size?
• The rule reads the transaction month, but disputes arrive for up to 120 days afterwards. What does that do to the alert and how would you fix it?
• What does a month of these false alarms cost, and how would you decide whether it is worth paying?

Bootstrap a fraud loss rate that clusters within merchant

Medium · bootstrap · clustered resampling · heavy tails

You have a per-transaction frame with auth_id, merchant_id, settled_amount_reporting and net_loss_reporting, both already in one reporting currency. Most rows carry zero loss, a few carry large ones, and losses cluster within merchant. Using only numpy's random generator and no resampling helper from any library, write a bootstrap that returns a 95 percent interval for net fraud loss in basis points of settled volume, resampling merchants with replacement and taking all rows belonging to each drawn merchant. Also produce the naive row-level interval and state which you would report.

Approach

1. State the estimator before writing it: total net loss divided by total settled volume, times 10,000. It is a ratio of sums, so each replicate recomputes both sums. Averaging per-transaction loss rates instead would weight a five-unit transaction like a five-thousand-unit one.
2. Pre-aggregate loss and volume to merchant level once. For a ratio of sums, drawing merchants and taking all their rows is arithmetically identical to drawing merchant-level (loss_sum, volume_sum) pairs, so a replicate becomes one integer draw plus two vectorised sums rather than a groupby inside the loop.
3. Draw B replicates of M merchant indices with replacement, where M is the observed merchant count, compute the ratio per replicate, and take the 2.5th and 97.5th percentiles. Say explicitly that this is a percentile interval and that BCa would correct the skew-induced bias if the decision is close.
4. Repeat with independent row draws for the naive interval and compare widths on the same replicate count.
5. Report the clustered interval. Rows within a merchant share an acceptance profile, a category code and a fraud exposure, so they are not independent, and the row-level interval understates variance by roughly the design effect.

Follow-up

• Your clustered interval is three times wider. How do you explain that to someone who wanted a tighter number?
• One merchant accounts for 40 percent of losses. What does that do to the interval, and what would you do about it?
• How does this change if the question is whether two months differ rather than what this month's rate is?

How would you use SQL window functions to calculate a moving average or identify rank within a specific grouping?

Medium · sql and data manipulation

Approach

1. Check whether any join is one-to-many before aggregating, or the sums inflate.
2. Compute rates by summing numerator and denominator separately, never by averaging rates.
3. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

Follow-up

• How would you verify this result without re-running the same query?
• How does the query change if the join becomes one-to-many?

Given a dataset with missing values, what is your systematic approach to identifying the cause and imputing or handling them?

Medium · sql and data manipulation

Approach

1. State the window function and its partition and ordering out loud before writing it.
2. Check whether any join is one-to-many before aggregating, or the sums inflate.
3. Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

• How would you verify this result without re-running the same query?
• How does the query change if the join becomes one-to-many?

How do you optimize a query that is performing poorly on a large, complex table?

Medium · sql and data manipulation

Approach

1. Compute rates by summing numerator and denominator separately, never by averaging rates.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

• What breaks if events arrive late or out of order?
• How does the query change if the join becomes one-to-many?

Count-weighted and dollar-weighted approval rates on one currency

Easy · aggregation · denominators · minor units

Using fct_payment_authorization, report the trailing 7-day authorization approval rate two ways for transaction_currency = 'EUR': count-weighted, and dollar-weighted on amount_minor. Exclude is_reversal = true, exclude incremental authorizations (parent_auth_id not null), and exclude zero-amount account verifications. auth_result = 'approved' is the numerator; the four declined_* values make up the rest of the denominator. Return channel, attempts, approved_attempts, approval_rate_count and approval_rate_value. State every exclusion and its reason before you write the SELECT.

Approach

1. Say the denominator out loud first: attempts on a single transaction currency, excluding reversals, incremental authorizations and zero-amount verifications, because none of those is a purchase attempt a merchant is trying to get approved.
2. Filter requested_at against a half-open interval (>= start AND < end) so the boundary day is neither dropped nor double counted.
3. Compute both rates in one pass with FILTER clauses: COUNT() FILTER (WHERE auth_result = 'approved') over COUNT(), and SUM(amount_minor) FILTER (WHERE auth_result = 'approved') over SUM(amount_minor).
4. Cast one side of each ratio to numeric before dividing, since amount_minor and the counts are integers and integer division silently truncates to zero.
5. Group by channel and sort by the value-weighted rate, then read the gap between the two rates as a statement about where the declines sit rather than as noise.

Worked solution (20 min)

1. Write the exclusion list as comments above the query: is_reversal = false, parent_auth_id is null, amount_minor > 0, transaction_currency = 'EUR'.
2. Build a single aggregate query over fct_payment_authorization with a half-open requested_at predicate and those four filters.
3. Emit attempts, approved_attempts, approval_rate_count and approval_rate_value with FILTER clauses and a numeric cast on the numerator.
4. Group by channel, order by approval_rate_value ascending so the worst channel is on top.

Expected result: One row per channel present in the window, with attempts and approved_attempts as integers and both rates as numerics strictly between 0 and 1. approval_rate_value is typically lower than approval_rate_count on ecommerce and moto, because declines concentrate in higher-value card-not-present traffic.

Verify it yourself

• SUM(attempts) across channels equals a separate COUNT(*) run with the same four filters and no GROUP BY.
• Dropping the amount_minor > 0 filter raises attempts and moves approval_rate_count while leaving approval_rate_value unchanged, which confirms the zero-amount rows were only ever affecting the count-weighted figure.
• Both rates are strictly less than 1 unless a channel had no declines at all in the window.

Follow-up

• The two rates diverge by four points on the ecommerce channel but agree on card_present. What does that tell you, and what would you cut next?
• How would you extend this to all currencies without summing amount_minor across them?
• Which of the four decline reasons belong in the denominator of a rate you would put in front of a risk team, and which are really the network's problem?

How do you balance long-term user value with short-term engagement metrics?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. State what result would change your recommendation, so the answer is falsifiable.
3. Fix the population and the time window before naming any metric.

Follow-up

• Which segment would you cut first, and what would that rule out?
• What would you do if the primary metric and the guardrail moved in opposite directions?

How would you design a set of success metrics for a new internal research tool or product?

Medium · product sense and metrics

Approach

1. Fix the population and the time window before naming any metric.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. State what result would change your recommendation, so the answer is falsifiable.

Follow-up

• What would you do if the primary metric and the guardrail moved in opposite directions?
• Which segment would you cut first, and what would that rule out?

If you notice a sudden, unexpected metric drop in your daily dashboard, what is your step-by-step process for diagnosing the root cause?

Medium · product sense and metrics

Approach

1. Name one primary metric, then the guardrail that stops it being gamed.
2. Restate the decision this analysis has to support, and who acts on the answer.
3. State what result would change your recommendation, so the answer is falsifiable.

Follow-up

• Which segment would you cut first, and what would that rule out?
• How would you detect that the metric is being gamed rather than genuinely improving?

If an experiment shows a positive result but the overall product metric does not improve, how do you investigate the discrepancy?

Medium · a/b testing and experimentation

Approach

1. Name the guardrails that would stop a launch even on a positive primary result.
2. Name the randomisation unit first; it decides the variance and what the test can detect.
3. State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

• What would you conclude if the result is positive but the test is underpowered?
• What would you do if you could not randomise at all?

What are the most common experimentation pitfalls that could lead to biased results?

Medium · a/b testing and experimentation

Approach

1. Say whether units interfere with each other, and switch design if they do.
2. Decide the analysis before seeing data, including how long it runs and when you look.
3. Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

• How would you handle interference between treated and control units?
• What would you do if you could not randomise at all?

Estimate the marginal effect of approval at a score cutoff

Hard · regression discontinuity · instrumental variables · selection

Policy auto-approves applications with bureau_score at or above 660 and routes 640 to 659 to manual review, which approves about 35 percent of them. You cannot randomise approvals. Using fct_loan_application and a bureau-sourced panel that reports 12-month serious delinquency on any trade line for approved and declined applicants alike, estimate the effect of approval at the margin. State the identifying assumptions you would test rather than assert, the estimator and its tuning choices, and exactly what the estimate does and does not license.

Approach

1. Set the design up as a fuzzy regression discontinuity, because crossing 660 changes approval probability sharply but not from zero to one. Let Z = 1{bureau_score >= 660} be the instrument, approval be the treatment, and 12-month serious delinquency be the outcome. The estimate is the ratio of the jump in the outcome to the jump in approval probability, which is two-stage least squares with Z as the instrument and identifies a local average treatment effect for compliers at the cutoff.
2. Insist on the bureau panel as the outcome source. Funded loans exist on both sides of 660 only because manual review approves some applicants below it, and those are selected on whatever the reviewer saw; an outcome defined only on funded loans would reintroduce exactly the selection the design is meant to remove.
3. Test the assumptions instead of listing them. Check density continuity of bureau_score at 660 with a McCrary-style or local-polynomial density test, since brokers and applicants can sometimes trigger a bureau refresh and a heaped density at the threshold kills the design. Check that pre-determined covariates, including declared_annual_income_minor, channel, product_code and model_version, are continuous at the cutoff. Check policy_rule_hits and the pricing table for any other rule that fires at exactly 660, because a second discontinuity at the same point is not separable from the first.
4. Choose the estimator deliberately. Local linear regression with a triangular kernel and an MSE-optimal bandwidth, with bias-corrected robust confidence intervals. Do not fit a high-order global polynomial, which puts weight on observations far from the cutoff and produces artefacts at the boundary. Because bureau_score is an integer, the running variable has mass points, so cluster standard errors by score value or use an approach designed for discrete running variables.
5. Respect outcome maturity. Include only application cohorts with a full 12 months of bureau observation; a recent cohort with partial observation will look cleaner and will drag the estimate.
6. State the limits in the deliverable. The estimate is the effect at 660 on applicants whose approval status is determined by the threshold. It does not license moving the cutoff to 620, because both the first stage and the outcome relationship differ away from 660 and because a policy change at scale shifts the applicant mix. Report bandwidth sensitivity alongside the point estimate.

Worked solution (40 min)

1. Assemble the application-level panel: bureau_score, decision, funded status, and the bureau-sourced 12-month serious delinquency flag, restricted to cohorts with 12 full months of outcome observation.
2. Plot the first stage, being approval probability against bureau_score in one-point bins, and confirm a visible jump of roughly 0.65 at 660 (about 1.00 above against about 0.35 below).
3. Plot the reduced form, being delinquency against bureau_score in the same bins, and read the jump at 660 off a local linear fit on each side.
4. Estimate the Wald ratio as reduced form over first stage, equivalently two-stage least squares with Z = 1{score >= 660}, using a triangular kernel, an MSE-optimal bandwidth, bias-corrected robust intervals, and standard errors clustered by integer score.
5. Run the assumption battery: density continuity at 660, covariate continuity, placebo cutoffs at 650 and 670, and bandwidth sensitivity at 0.5x and 2x the selected bandwidth.
6. Write the result as a complier average effect at 660 with its interval, its bandwidth sensitivity table, and an explicit sentence on what it does not license.

Expected result: A fuzzy RDD estimate delivered as the Wald ratio, jump in 12-month serious delinquency divided by a first-stage jump in approval probability of about 0.65, fitted by local linear regression with a triangular kernel at an MSE-optimal bandwidth, with robust bias-corrected intervals and standard errors clustered on the integer score. It is reported as a local average treatment effect for compliers at 660 only, accompanied by the density test, the covariate-continuity table and the bandwidth sensitivity, with an explicit statement that it does not support moving the cutoff.

Verify it yourself

• Reduced form divided by first stage must equal the two-stage least squares coefficient to numerical precision; if it does not, the sample or the bandwidth differs between the two fits.
• Placebo cutoffs at 650 and 670 should show no jump in either the first stage or the outcome.
• Pre-determined covariates should be continuous at 660; a jump in income or channel mix at the threshold means applicants or brokers are sorting and the design is compromised.
• The point estimate should be stable when the bandwidth is halved and doubled; large movement means the local linear fit is being driven by observations far from the cutoff.

Follow-up

• Manual reviewers below the cutoff see documents the score does not. What does that do to the monotonicity assumption, and how would you look for defiers?
• You are offered a randomised approval band across 650 to 659 for one quarter. What does it buy you that the discontinuity does not, and how would you price the expected loss of running it?
• The density test shows a modest pile-up just above 660. What are the possible mechanisms, and which of them leave the design usable?

Trailing thirty day volume per customer drops week over week

Hard · seasonality · day of week · window alignment

The trailing 30-day settled volume per active customer is down 7 percent against the same metric seven days earlier. Nothing shipped. You have fct_payment_authorization with requested_at, channel, captured_at, captured_amount_minor, settled_at, settlement_amount_minor, plus dim_customer with is_current, kyc_status, onboarded_at and closed_at for the active denominator. Before anyone writes a retention narrative, decide how much of the 7 percent is calendar structure, and hand back a calendar-robust version of the comparison.

Approach

1. Do the window arithmetic first. Thirty days is four whole weeks plus two days, so exactly two weekdays appear five times and the other five appear four times. Sliding the window by seven days changes which two, and card-present and card-not-present volumes differ sharply by weekday.
2. Rebuild on a 28-day window, which contains exactly four of every weekday, and see how much of the 7 percent survives. That single change removes the weekday composition effect with no modelling and no assumptions.
3. Count the structural events inside each window: public holidays, and the billing anchor days that recurring authorizations cluster on. A window holding one fewer month boundary loses a block of recurring volume that has nothing to do with customer behaviour.
4. Decompose by channel, since recurring, card_present and ecommerce have different calendar signatures. A drop concentrated in recurring points at anchor-day placement; one spread evenly across channels does not.
5. Compare year over year at a 364-day lag rather than 365, which preserves weekday alignment, and only then read the residual.
6. Check the denominator on its own. Active customer counts on a trailing window carry their own calendar structure, and a ratio can move because either side moved.

Follow-up

• Which window goes on the executive dashboard, and what do you give up by choosing it?
• How would you handle a holiday that moves between years, so that a 364-day lag still misaligns it?
• If a genuine 2 percent residual survives, what is the smallest cut that tells you whether it is breadth or depth?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

• Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
• Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
• Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

• Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
• Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
• Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

• Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
• Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
• Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

• Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
• Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
• Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

• Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
• Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
• Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

• Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
• Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
• Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

• Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
• Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
• Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

Being wrong in public

A number you shipped turned out to be wrong, and someone had already acted on it. That is one of the most useful stories a data person can carry. What is being scored is how fast you noticed, who you told first, and what you changed in the process so the same class of error could not repeat quietly.

Describe a time you had to explain a complex technical finding to a non-technical stakeholder.

Medium · behavioural and stakeholder questions

Approach

1. State the situation in two sentences and spend the rest on your reasoning.
2. Pick a story where you drove the decision, not one where you observed it.
3. Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

• How did you know the outcome was caused by your change?
• What would you do differently if you ran that project again?

Explain an incomplete dispute chart to a non-technical executive

Easy · dispute maturity · stakeholder communication · right-censoring

A finance lead is looking at first-chargeback rate by transaction month, built from fct_card_dispute joined to fct_payment_authorization on auth_id and attributed to requested_at. The last three months slope sharply down and the lead wants to announce a fraud improvement at tomorrow's review. Consumer dispute rights commonly run around 120 days from the transaction or expected delivery date, so those months are not complete. In five minutes, with no statistics vocabulary, explain why the decline is not yet evidence and say exactly what you would put on the slide instead.

Approach

1. Lead with the mechanism in the listener's own terms, not with the statistical name for it: a dispute is attributed to the month the transaction happened, but it can be filed up to roughly 120 days later, so recent months contain only the disputes filed so far.
2. Show completeness rather than arguing about the rate: for each transaction month, plot the share of its eventual disputes already filed, estimated from months that are fully matured. The last three months will sit visibly below 100 percent.
3. Replace the chart with two artefacts: a matured series that stops 120 days back and is labelled final, and a development-factor estimate for the immature months drawn as a dashed range and labelled an estimate.
4. Hand over one sentence the executive can repeat without you in the room: the recent months look better because the disputes have not arrived yet, not because fewer will arrive.
5. Offer a weekly signal they can watch instead, such as the risk-score mix of approved volume or the decline-rule hit rate, and state up front what it does and does not predict.

Follow-up

• The deck ships tomorrow regardless. What exactly goes on the slide, and what wording do you insist on?
• How would you estimate the development factors, and how would you notice if they had shifted?

Allocate one analyst-week across three competing risk requests

Medium · prioritisation · decision deadlines · stakeholder negotiation

Three requests land in the same week and you have one analyst-week. Payments wants a merchant-level decline teardown before a contract renewal in nine days. Credit wants a swap-set analysis on a cutoff change scheduled to ship in six weeks. Insurance wants accident-quarter loss ratios at 12 months development for a reserving review with no fixed date. Each sponsor believes theirs is first, and each has escalated before. Produce the allocation, the reasoning you would say out loud to all three at once, and what you explicitly drop.

Approach

1. Score each request on the decision it unblocks rather than on effort or on how loudly it arrived: what changes if it is late, and is that change reversible.
2. Separate deadline from value. The nine-day renewal is a hard, irreversible date with a bounded prize; the six-week cutoff has slack but a much larger downside if it ships unmeasured; the reserving number has no date but feeds external reporting, which is its own kind of hard.
3. Hunt for the cheap partial in each: a decline teardown restricted to the top merchants by declined value usually answers the contract question at a fraction of the full cut.
4. Sequence by hard date first, then by largest irreversible downside, and deliver the trade-off to all three sponsors in one message rather than three, so nobody negotiates privately against a version you told someone else.
5. Name what is dropped and who now owns that consequence, in writing, so the trade-off is visible rather than silently absorbed by you.

Follow-up

• The credit sponsor escalates to your manager. What do you change, and what do you refuse to change?
• How would you make this allocation reproducible so the next contested week is a rule application rather than a negotiation?