Interview guides · alternative investment manager

alternative investment manager Data Scientist Interview Questions & Guide 2026

Data scientist interview prep for alternative investment manager: original practice on experiment design, SQL and metric reasoning, with worked solutions.

The 60-second brief

The Data Scientist role at alternative investment manager is a high-impact position situated at the intersection of complex financial data and actionable product strategy. You will be responsible for transforming raw, high-dimensional datasets into clear narratives that guide investment decisions and operational efficiency. Unlike roles in consumer tech, your work here directly impacts portfolio performance, risk assessment, and the underlying financial products that define the firm’s market presence.

Allocate prep to your weakest link rather than your favourite topic. Of the three things that usually gate the outcome (SQL that is correct under messy joins, sound reasoning about experiments, and structured framing of an open-ended problem), candidates tend to over-invest in modelling theory and under-invest in framing.

Focus areas

Decompose expected loss into PD, LGD, EAD
Set fraud thresholds by expected cost
Read vintage curves, not blended portfolio averages

Overview

You will function as a strategic partner to investment teams, product managers, and engineering squads. Your day-to-day work involves rigorous product metric design, diagnosing sudden shifts in performance through metric drop diagnosis, and designing robust A/B testing frameworks to validate new investment hypotheses. Success in this role requires not just technical proficiency, but the ability to translate complex statistical findings into clear, persuasive recommendations for non-technical stakeholders.

You will be expected to balance academic rigor with business pragmatism. While the firm values deep statistical knowledge, they prioritize candidates who can identify when a "perfect" model is less valuable than a "fast, directional" insight.

The alternative investment manager interview process

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

Technical Screening

A handful of shapes account for most of what gets asked in this format: a ranking or deduplication inside groups, a running or rolling total, a period-over-period comparison, and a cohort tracked forward over time. Recognising the shape quickly is most of the speed here; deriving it from scratch while a clock runs is where the time goes. Know that a window function keeps every row while a GROUP BY collapses them, and know which one the question needs. If the exercise is in Python instead of SQL, the same shapes arrive as groupby with transform, shift and merge, and the same grain mistakes are available.

What to demonstrate

Whether you reach the right construct without a detour, such as ROW_NUMBER over a partition to deduplicate instead of a self-join against a MAX subquery
Whether you know what your window frame actually is, since adding ORDER BY inside OVER changes the default frame and silently changes a running total
Whether the thing runs. A near-miss that throws an error scores below a plainer query that returns the right rows.

How to prepare

Write each of the four shapes once from memory against a small schema and keep the working version somewhere you will reread it: dedupe with ROW_NUMBER, a running total, a month-over-month change with LAG, and a retention table
Compute one running total twice on data with tied timestamps, once on the default frame and once with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and look at where the two disagree
If Python is on the table, rebuild the dedupe and the running total with groupby and cumsum, then assert the two implementations return identical rows

Project Deep Dive

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Team-Based Assessment

Before anything else, this round is a reading test. You are given a small schema and a question phrased in business language, and most of the difficulty sits in the gap between them. Who counts as an active user, does a refunded order still count as an order, is that date column an event time or a load time. Weak answers start typing immediately and compute something precise about the wrong population. Strong ones pin the definition in one sentence, name the column that encodes it, then write the query. On a timed assessment with nobody to tell, write the definition in a comment anyway.

What to demonstrate

Whether an ambiguous term becomes a specific column and filter before any computation happens
Whether you read the schema for keys and cardinality rather than only for column names
Whether the result answers the question at the grain it was asked at, per user or per session or per day

How to prepare

Take three metrics you already use and write down the exact filter and exact grain behind each, then practise stating one of them in a single sentence out loud
On a schema you have never seen, spend the first minute writing what one row of each table means and which key it is unique on, then predict which joins can duplicate rows
Rehearse a version where the definition changes halfway through, and edit the query you have instead of starting over

Where candidates lose points

Averaging delinquency across a book that is growing

A loan three months old cannot be 90 days past due, so a portfolio with many recent originations reports a low blended 90+ rate purely from age mix. The blended rate falls fastest exactly when originations grow fastest, which is precisely when credit quality most needs watching, so the metric moves in the reassuring direction during the riskiest period. Only comparisons at equal months on book are valid, which is what a vintage or roll-rate view enforces.

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.

Writing SQL without stating NULL and tie-breaking behaviour

Before calling a query finished, say what it does with NULLs, ties and empty groups. NOT IN against a subquery containing a single NULL returns no rows at all, and RANK, DENSE_RANK and ROW_NUMBER differ precisely on ties, so name which one the question requires.

Averaging per-user rates to produce a population rate

Decide which quantity you want: the mean of per-user ratios and the ratio of summed numerator to summed denominator are different estimands, and heavy users dominate one but not the other. For a ratio metric, aggregate numerator and denominator separately and use the delta method for its variance.

Questions & practice

Write integrity checks for the authorization and settlement lifecycle

Easy · data quality · minor units · fx reconciliation

You are given fct_payment_authorization as a pandas DataFrame with auth_id, requested_at, amount_minor, transaction_currency, auth_result, decline_reason_code, is_reversal, parent_auth_id, captured_at, captured_amount_minor, settled_at, settlement_amount_minor, settlement_currency and settlement_fx_rate. Write a function returning one row per integrity check with the check name, failing row count, failing share and up to five example auth_id values. Cover at least six checks, one of which reconciles captured_amount_minor against settlement_amount_minor through settlement_fx_rate. Partial capture, zero-amount verification and a decline with no capture are all legitimate and must not be flagged.

Approach

Separate contract violations from observations before writing any code: an approved row carrying a decline_reason_code is structurally impossible, while a capture two days after requested_at is merely slow and belongs in a different severity tier.
Express each check as a boolean mask over the whole frame and collect the masks in a dict, so the summary table is one comprehension over mask.sum() rather than a row loop.
For the reconciliation, leave minor units before comparing: expected = captured_amount_minor / 10exponent[transaction_currency] * settlement_fx_rate * 10exponent[settlement_currency]. Build the exponent table covering zero-decimal and three-decimal currencies instead of assuming two everywhere.
Guard the legitimate cases explicitly so each mask fires only on the genuine contradiction: captured_amount_minor below amount_minor is partial capture, amount_minor of zero on an approved row is account verification, a null captured_at on a declined row is correct.
Sort the output by failing share times a stated severity weight, because a check firing on 0.01 percent of rows can still be the one that breaks a ledger reconciliation.

Worked solution (25 min)

Assert auth_id is unique, then build a currency exponent lookup that includes the zero-decimal and three-decimal currencies present in the data.
Define masks for: approved with non-null decline_reason_code; declined with non-null captured_at; captured_amount_minor above amount_minor with parent_auth_id null; captured_at before requested_at; settled_at before captured_at; is_reversal true with parent_auth_id null; settlement_currency differing from transaction_currency while settlement_fx_rate is null.
Add the exponent-aware reconciliation mask with a tolerance of one minor unit plus a small relative term.
Assemble a frame of check_name, n_failing, pct_failing and up to five sample auth_id values, ordered by severity then share.
Read five flagged rows per check by hand and confirm each is genuinely contradictory before reporting any counts.

Expected result: A DataFrame of at least eight rows, one per check, each with n_failing, pct_failing and example auth_id values. The reconciliation check should fire on a whole currency at once rather than on scattered rows, because an exponent error is systematic while an FX error is not.

Verify it yourself

Every mask returns false on three hand-constructed legitimate rows: a partial capture, a zero-amount verification and a decline with no capture.
Each pct_failing uses the denominator that check actually applies to, with reversals and verifications excluded where they are out of scope.
The reconciliation residual is centred at zero with spread under one minor unit; residuals clustered at exactly 100x or 0.01x mean an exponent is wrong, not a rate.

Follow-up

Which of these would you run as a blocking pipeline assertion and which as a monitored metric, and why?
The FX check fails on 3 percent of rows, all in one settlement currency. How do you decide between a data bug and a rounding convention?
How would you detect that a currency's minor-unit exponent is wrong in your reference table, using only the transaction data?

Implement accident-quarter loss ratio at twelve months development

Medium · loss ratio · development age · earned premium

fct_policy_period_monthly arrives as a stack of month-end snapshots: each row carries valuation_month alongside as_of_month, policy_id, product_line, written_premium_minor, earned_premium_minor, paid_loss_minor, case_reserve_minor, ibnr_reserve_minor and loss_adjustment_expense_minor. Compute the accident-quarter loss ratio at exactly 12 months of development: incurred losses over earned premium, both taken from rows whose as_of_month falls in the accident quarter, read from the snapshot 12 months after that quarter closes. Report quarters that cannot reach that age as incomplete rather than dropping them.

Approach

Derive accident_quarter from as_of_month, then define the evaluation snapshot per quarter as valuation_month equal to the quarter's final month plus twelve months. Every figure in the ratio comes from that one snapshot, not from whichever snapshot happens to be newest.
Numerator is paid_loss_minor plus case_reserve_minor plus ibnr_reserve_minor over the accident quarter's rows in that snapshot. Loss adjustment expense may be included or not, but the choice applies to every quarter and is named in an output column.
Denominator is earned_premium_minor over the same rows. Written premium is booked in full at inception, so in a growing book it runs ahead of earned premium and drags the ratio down, with the error reversing when the book shrinks.
Left-join the full quarter list against available valuation months so a quarter with no 12-month snapshot yields status incomplete and a null ratio, instead of disappearing and shortening the series without saying so.
Split by product_line, since both the loss ratio level and the speed of development differ by line, and a blended series moves with mix as much as with experience.

Follow-up

The most recent complete quarter came in four points better than the one before. What do you check before calling it an improvement?
How would you estimate the 12-month figure for a quarter that is only 6 months developed, and how would you label the estimate?
Why can an expense ratio legitimately use a different denominator from the loss ratio in the same presentation?

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

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.
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.
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.
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.
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?

Given a table of transaction logs, how would you identify the first and last transaction for each user?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Write a query using SQL window functions to calculate the rolling 30-day average of asset returns.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Compute rates by summing numerator and denominator separately, never by averaging rates.
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?
What breaks if events arrive late or out of order?

Monthly delinquency roll rates with honest exiting-account denominators

Hard · roll rates · window functions · self joins

From fct_loan_performance_monthly, compute month-over-month roll rates: for each as_of_month_end and delinquency_bucket, the share of loans in that bucket that are in a worse state at the following month end. Use this disposition convention and apply it without exception: charge-off is the terminal state one rank past dpd_90_plus, so a loan that charges off has rolled forward, not exited; loans that leave the book any other way — prepaid in full, sold, matured, or simply absent at the next month end — are exited. Bucket order is current, dpd_1_29, dpd_30_59, dpd_60_89, dpd_90_plus, then charged_off. Return from_month, from_bucket, accounts, rolled_forward, cured, stayed, exited and roll_rate, and show that the four dispositions sum to accounts on every row.

Approach

Map the five delinquency_bucket values to ranks 0 through 4 in a small inline VALUES list, and reserve rank 5 for charged_off, because 'worse' is an ordering statement and enum text does not sort that way reliably. Read charge-off from charge_off_flag on the next month's row rather than from delinquency_bucket, which keeps carrying whatever delinquency value the loan held when it was written off.
Use LEAD(as_of_month_end), LEAD(bucket_rank) and LEAD(charge_off_flag) OVER (PARTITION BY loan_id ORDER BY as_of_month_end), then verify the leading month end is exactly one month after the current one, since a loan missing a month would otherwise appear to cure or roll across a gap it never traversed.
Classify each loan-month into exactly one of four labels off the adjacent next row, whose rank is 5 when its charge_off_flag is true and its mapped bucket rank otherwise. rolled_forward: the next rank is strictly greater. cured: strictly lower. stayed: equal. exited: there is no adjacent next row at all, which is where prepayment, sale, maturity and a gap in the monthly history land.
Define rolled_forward as 'any strictly worse rank', not 'rank plus one'. Under normal monthly aging the only reachable worse state is the next bucket, so the two definitions coincide — except at charge-off, where a loan can go from dpd_60_89 straight to rank 5. A strict plus-one test leaves those loan-months matching no branch and breaks the exhaustiveness invariant.
Be explicit about why charge-off is a roll rather than an exit: routed to exited, dpd_90_plus has no worse state left, so its roll_rate is identically zero by construction — arithmetic, not a credit finding. At rank 5 the dpd_90_plus row reports roll-to-loss, which is the number a loss forecast actually consumes.
Aggregate by from_month and from_bucket and require that rolled_forward + cured + stayed + exited equals accounts on every row, which is the invariant that proves the classification is exhaustive and mutually exclusive.

Worked solution (45 min)

CTE ranks: an inline VALUES mapping of the five delinquency_bucket values to ranks 0 through 4, joined onto the monthly rows. Rank 5 is not in the map; it is assigned during classification.
CTE leads: add LEAD(as_of_month_end), LEAD(bucket_rank) and LEAD(charge_off_flag) partitioned by loan_id ordered by as_of_month_end.
CTE classified: derive next_rank as CASE WHEN next_charge_off_flag THEN 5 ELSE next_bucket_rank END, then a CASE expression producing exactly one label per loan-month — exited when the next month end is null or is not the current month end plus one month, otherwise rolled_forward / cured / stayed by comparing next_rank to bucket_rank.
Aggregate to from_month and from_bucket with COUNT(*) and four FILTER counts, then compute roll_rate as rolled_forward over accounts with a numeric cast.
Assert the exhaustiveness invariant in a final HAVING or a separate verification query before trusting any number.

Expected result: One row per month and originating bucket, with the four disposition counts summing to accounts on every row and roll_rate between 0 and 1. Roll rates climb across current, dpd_1_29, dpd_30_59 and dpd_60_89, because each successive bucket holds loans that have already failed to cure. Do not expect that climb to continue into dpd_90_plus: that bucket is a catch-all holding several monthly cohorts at once until the charge-off policy retires them, so its roll-to-loss rate is set by the policy horizon — under a 180-day policy roughly a third of the bucket rolls in a given month — and usually sits below the dpd_60_89 rate. Cure out of dpd_90_plus is well under ten percent.

Verify it yourself

rolled_forward + cured + stayed + exited equals accounts for every row, with no exceptions.
SUM(accounts) for a given from_month equals the count of loan rows at that month end, so no loan-month was dropped or counted twice.
Removing the adjacency test changes the cure and roll counts, and the size of that change is exactly the number of loans with a gap in their monthly history.
The rolled_forward count for from_bucket dpd_90_plus in month M is at least the number of loans whose first charge_off_flag = true row falls at month end M+1. If it is materially lower, charge-off is being routed into exited.
roll_rate for dpd_90_plus is strictly greater than zero in any month with charge-offs. A flat zero on that bucket across every month is the signature of charge-off classified as an exit, not of an unusually stable book.
No loan-month is counted as both rolled_forward and exited: a loan with a charged-off row at M+1 has an adjacent next row, so it can never reach the exited branch.

Follow-up

Project the next three months of dpd_90_plus inflow from these roll rates. What assumption does that projection make, and when does it break?
A restructure resets days_past_due to zero. What does that do to the cure rate out of dpd_60_89, and how would you separate a real cure from a reset?
Argue the other convention: prepayment in exited but charge-off also in exited, with roll rates reported only for the four non-terminal buckets. What does that series answer better, and what does it lose?
The charge-off policy changed from 180 to 120 days. Show where that appears in this table and what it does to the dpd_90_plus roll rate specifically.

If we observed a sudden 10% drop in user engagement on our platform, how would you go about diagnosing the root cause?

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
Name one primary metric, then the guardrail that stops it being gamed.
Restate the decision this analysis has to support, and who acts on the answer.

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?

What metrics would you use to evaluate the quality of a new data feed?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
What would you do if the primary metric and the guardrail moved in opposite directions?

What are the most common experimentation pitfalls you have encountered in your past projects?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Name the guardrails that would stop a launch even on a positive primary result.
Say whether units interfere with each other, and switch design if they do.

Follow-up

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

How do you account for seasonality in your experimental design?

Medium · a/b testing and experimentation

Approach

Name the randomisation unit first; it decides the variance and what the test can detect.
Say whether units interfere with each other, and switch design if they do.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

What would you conclude if the result is positive but the test is underpowered?
How would you handle interference between treated and control units?

How do you define "success" for a new automated portfolio rebalancing tool?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
Work from the decision backwards to the evidence you would need.
State your assumptions explicitly before working the problem.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

Judge a rate increase without letting price flatter the ratio

Hard · mix shift · adverse selection · loss development

A rating change raises premium on one product_line's renewal book. Using fct_policy_period_monthly (earned_premium_minor, written_premium_minor, exposure_units, paid_loss_minor, case_reserve_minor, ibnr_reserve_minor, loss_adjustment_expense_minor, rating_tier, policy_status, renewal_flag, policy_term_end), the accident-period loss ratio improves two points over the following year. Explain why that number alone cannot tell you the change worked, decompose the movement into the effects you would separate, and specify the primary metric and guardrails you would commit to before the next rating change.

Approach

Start with the arithmetic. A rate increase raises earned premium per exposure unit, so the loss ratio falls even if every policyholder behaves identically and every claim is unchanged. Part of the two points is mechanical and carries no information about risk at all.
Switch the risk measure to one price cannot move: pure premium, being incurred losses (paid_loss_minor plus case_reserve_minor plus ibnr_reserve_minor, with loss_adjustment_expense_minor included or excluded consistently and the choice stated) divided by exposure_units, evaluated by accident period at a fixed development age. Flat pure premium alongside an improved loss ratio means the improvement was entirely price.
Decompose the loss ratio movement into three named components: the price effect at constant exposure and mix, the mix effect from which rating_tiers renewed and which lapsed, and the residual change in pure premium within tier. Only the third is evidence about risk selection, and it is usually the smallest.
Name the adverse-selection risk directly. Price sensitivity and loss propensity are not independent, and the policyholders most able to leave after a rate rise are often the ones worth keeping. Retention by rating_tier is therefore a guardrail with teeth, and it has to be read at tier level because a flat blended retention hides tiers moving in opposite directions.
Add prior-period reserve development as the second guardrail. The same two points are producible by setting case reserves or IBNR light, which surfaces only later as adverse development, so the reserve guardrail is what stops the primary metric being satisfiable by an accounting choice.
Commit the primary before the next change: underwriting margin per exposure unit, being earned premium minus incurred losses minus loss adjustment expense minus allocated expense, over exposure_units, at a fixed development age, reported by accident period and by rating_tier with exposure volume printed beside it so that improving margin by shrinking the book is visible in the same table.

Worked solution (40 min)

By accident quarter at a fixed twelve-month development age, compute both the loss ratio (incurred over earned_premium_minor) and the pure premium (incurred over exposure_units), before and after the change.
Build the three-way decomposition: move price only at constant mix and exposure, then move the tier mix to the post-change distribution at constant price, then take the remainder as the within-tier pure premium change.
Compute renewal retention by rating_tier from policies reaching policy_term_end, excluding cancelled_midterm and terms where no renewal offer was made, and cross each tier's retention with its prior pure premium.
Pull prior-period reserve development for the periods used and state whether the improvement survives it.

Expected result: A decomposition splitting the two-point improvement into price, mix and within-tier pure premium components that reconcile to the total, alongside retention by rating_tier and an explicit statement of whether the within-tier component is distinguishable from zero.

Verify it yourself

The three components reconcile to the observed change up to the interaction term. Decide in advance where the interaction is allocated and say so on the chart.
Pure premium contains no price term by construction, so a step change in it exactly at the rate effective date points to an exposure or earning-pattern error rather than to a risk effect.
Recent accident periods have no value at the fixed development age, which means unavailable rather than favourable, so any period younger than that age is excluded from the comparison.
Retention measured at tier level reconciles to the blended figure once terms with no renewal offer are excluded from both.

Follow-up

Retention is flat overall but fell nine points in the lowest-loss tier. What do you expect next year's pure premium to do?
Why not use written premium as the denominator, and where would you still legitimately see it used?
How would you separate a genuine underwriting improvement from a year of mild weather?

Settled volume jumps after a new acceptance corridor launches

Medium · minor units · currency conversion · reconciliation

Settled payment volume per active customer rose 12 percent in the month a new acceptance corridor went live. The pipeline sums settlement_amount_minor from fct_payment_authorization and divides by 100 to get major units. Columns available: amount_minor, transaction_currency, captured_amount_minor, settlement_amount_minor, settlement_currency, settlement_fx_rate, settled_at, is_reversal, issuer_country, acquirer_country. Confirm or refute the 12 percent, and specify exactly the conversion the metric should be using.

Approach

Split the month-over-month increase by settlement_currency. If one currency carries nearly all of it while its transaction count barely moved, the problem is arithmetic rather than demand, and that is a two-minute check.
Look up each currency's ISO 4217 exponent. Dividing a three-decimal currency by 100 instead of 1000 overstates it tenfold; dividing a zero-decimal currency by 100 understates it hundredfold. Neither is a rounding issue.
Rewrite the conversion to scale settlement_amount_minor by ten to the power of that currency's exponent, then convert to the reporting currency. Do not reuse settlement_fx_rate for this; it is the rate applied between transaction and settlement currency at settlement time, not a reporting-currency rate.
Check the other half of the series too. settlement_amount_minor is denominated in settlement_currency while amount_minor is in transaction_currency, so any series that mixes the two is uninterpretable regardless of the exponent fix.
Exclude reversals and refunds, recompute, then reconcile the corrected total to the settlement ledger for the same period and show the tie-out in the write-up.
Recompute the per-customer denominator on the corrected month, since a new corridor adds customers as well as volume and the ratio can move either way once the numerator is right.

Follow-up

Where else in the warehouse does a hardcoded divide-by-100 appear, and how would you find every instance?
What test would fail the build the next time a currency with a different exponent is onboarded?
The reporting-currency rate is itself a choice. Which rate, on which date, and why does finance care about the answer?

Suggested practice checklist

For someone who can already write the query and train the model but stalls when asked what to measure or whether a change is worth making. Metric definition and case structure come first; the technical work is kept as maintenance rather than the centre of the week.

Day 1 — Metric anatomy

For three products you use daily, write one primary metric, two input metrics that plausibly move it, and one guardrail that would catch a cheap way of moving the primary at the cost of the product.
For one of them, specify the metric precisely enough that two analysts would return the same number: numerator, denominator, unit of observation, time window, and how returning and deleted accounts are treated.
Pick a ratio metric and write what happens to it when the denominator shrinks for reasons unrelated to the numerator, with a concrete example of that happening.

Deliverable: A one-page metric tree for one product, with the primary metric written as an unambiguous spec.

Day 2 — Diagnosing a drop without guessing

Take the prompt "weekly active users fell 8 percent week over week" and write the segmentation plan before proposing any cause: platform, region, tenure cohort, acquisition channel, and whether the movement sits in the numerator or in a changed denominator.
List the instrumentation failures that manufacture fake drops (a client release that stopped firing an event, a bot filter change, a shifted date boundary or timezone) and write the query that rules out each one.
Rehearse stating the boring explanations first, seasonality and day-of-week composition, before reaching for a product cause.

Deliverable: A drop-diagnosis checklist short enough to recite from memory in under a minute.

Day 3 — Should we build it

Take a feature idea and write it as a bet: what you believe is true, what would have to be true for it to pay off, the metric that would confirm it, and the effect size that would justify the engineering cost.
Size the opportunity top-down and bottom-up, then reconcile the two numbers in writing instead of quoting whichever is friendlier.
Write the counter-metric that would make you kill the feature even if it wins on the primary metric.

Deliverable: A one-page product memo ending in a decision rather than a list of considerations.

Day 4 — The places aggregate numbers lie

Construct a Simpson's paradox numerically: two segments where the treatment wins within each segment yet loses overall, and identify the shift in segment weights that causes it.
Take a heavy right-tailed quantity such as revenue per user and write why the mean is the wrong summary, which percentile you would report instead, and what a moving mean with a stable median tells you.
Write your definition of a session for the product from day one, then name two real behaviours it misclassifies.

Deliverable: One page holding a worked Simpson's paradox table and a session definition with its two known failure cases.

Day 5 — Technical maintenance, aimed at metrics

Solve four timed SQL prompts that all end in a ratio metric, so the question of grain stays live in every answer.
Compute a 95 percent confidence interval for a proportion on a small sample, and state why the normal approximation is unreliable when either np or n(1 minus p) falls below roughly 10, along with which interval you would use instead.
Take one metric from your day-one tree, write the query that computes it correctly, then write the query that computes it wrong in the most plausible way and explain how you would notice.

Deliverable: Four solved prompts plus a matched correct and plausible-wrong query for one metric.

Day 6 — Turning engineering work into data science stories

Write three project stories as situation, decision, trade-off, outcome, each carrying one number and one thing you got wrong.
For the story you will lead with, prepare an answer to "what would you do differently" that names a decision you made, not a constraint you were handed.
Practise the sentence that reframes a systems project as a question project: the question the work answered, ahead of the pipeline it shipped.

Deliverable: Three written stories with the lead story delivered aloud and timed under four minutes.

Day 7 — Mock case and gap list

Run a 40-minute mock case with someone playing a product manager who pushes back on your metric choice, and record it.
Listen back and mark every moment you proposed a solution before the success metric existed.
Rewrite those moments as the question you should have asked, and rehearse the first 90 seconds of the case until scoping comes before solving.

Deliverable: A recorded case plus a rewritten opening 90 seconds.

Explaining a method to someone who will not follow the maths

Half of this section is about translation. Be ready to describe how you explained a result to someone who did not want the method, only the implication, and what you did when the simplified version started being repeated in a way that overstated it. Correcting your own simplification is a strong beat.

Describe a project you worked on that you are particularly proud of. What was your specific contribution?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Quantify the outcome, including what you would not claim credit for.
Close with what you would do differently, concretely.

Follow-up

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

Tell me about a time you had to explain a complex technical concept to a non-technical team member.

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
State the situation in two sentences and spend the rest on your reasoning.
Pick a story where you drove the decision, not one where you observed it.

Follow-up

What did you decide not to do, and why?
What would you do differently if you ran that project again?

Recommend a decision whose true outcome matures a year later

Hard · decision under censoring · leading indicators · staged rollout

An underwriting rule change must be decided in six weeks. Its real outcome, the vintage 90-plus rate at months_on_book 12 in fct_loan_performance_monthly, matures in a year. The executive wants a yes or no, not a range. Randomising the credit decision across the whole population is not available. Name the leading indicator you would accept, state its bias and the direction of that bias, define the decision rule and stopping condition before any rollout starts, and say what reading would make you recommend reversing the change.

Approach

Fix the readout before the rollout, because a readout chosen after the data arrives is a story rather than a decision rule: indicator, window, threshold and reversal condition all go in writing first.
Choose the leading indicator on its measured relationship to the matured outcome in historical vintages rather than on availability. Early delinquency, typically the share reaching dpd_1_29 or missing a first scheduled payment by months_on_book 3, is the usual candidate, and you quantify how well it predicted the 12-month rate across past cohorts.
State the bias and its direction plainly: early delinquency under-represents default that emerges later and is contaminated by servicing and payment-date effects, so treat it as a floor on risk rather than an estimate of it.
Buy identification where full randomisation is unavailable: a narrow randomised approval band around the cutoff, or a staged rollout by channel or region read as a difference-in-differences, with the parallel-trends assumption stated and checked in the pre-period rather than assumed.
Give the executive the binary they asked for with the trigger attached in the same sentence: yes, conditional on the month-3 indicator staying inside a stated band, with an automatic hold if it breaches.

Follow-up

How would you validate that the month-3 indicator predicts the 12-month outcome, and what evidence would invalidate it mid-rollout?
Compliance refuses a randomised band. What is your next-best identification strategy, and what precision do you lose by taking it?