Interview guides · ADM

ADM Data Scientist Interview Questions & Guide 2026

ADM data scientist prep on data pitfalls: selection bias, target leakage, and checking an outlier's provenance before you drop it.

The 60-second brief

As a Data Scientist at ADM, you play a pivotal role in transforming complex scientific data into actionable insights that drive innovation in carbohydrate-based and biobased materials. This position is crucial for developing both food and non-food applications, such as advanced materials and packaging, thereby enhancing the functionality and commercial viability of ingredients. You will be at the intersection of research and application, directly impacting ADM’s ability to create sustainable solutions that meet evolving market demands.

Most of the loop measures decision-making under uncertainty rather than recall. You are scored on whether you state your assumptions, commit to an estimate you can defend, and say explicitly what evidence would change it.

Focus areas

Set fraud thresholds by expected cost
Separate authorization, settlement and dispute outcomes cleanly
Report only matured cohorts for loss metrics

Overview

In this role, you will work closely with cross-functional teams, leveraging your deep understanding of carbohydrate chemistry to develop innovative ingredients and functional solutions. Your contributions will not only influence the products we bring to market but also enhance the value we provide to our customers. This strategic influence makes the position both challenging and rewarding, as you will continually monitor and integrate new scientific developments into ADM’s product lines.

The ADM interview process

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

Initial Screening

Whoever runs this call is usually not a practitioner. They take notes, and a hiring manager skims those notes later, so the real question is whether your work survives being written down by someone outside the field. Test every project sentence against that: could a non-specialist repeat it correctly without knowing what a propensity score is? Carry a plain-language version of each project and one reason you want this particular role that you could not copy onto another application. Vagueness at this stage reads as inexperience, even when the underlying work was genuinely deep.

What to demonstrate

Whether a non-specialist can restate your projects accurately, since their paraphrase is what reaches the hiring manager
Whether your reason for wanting the role points at the work itself rather than the company's reputation
Whether your language signals the level being screened for: what you decided yourself versus what you were handed

How to prepare

Write a two-sentence, jargon-free version of each major project: the question nobody could answer, and the decision your work changed. Read it to someone outside data and have them repeat it back
Point your 'why this role' answer at something concrete in the job description or the product surface you would be working on, and keep it to two sentences
Have two questions ready about measurement: which metric the team is held to, and who acts on an analysis once it lands

Technical Assessments

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

Behavioral Interviews

Rounds of this kind usually include one question about work that did not go well, and it is the part that carries the most information. Anyone can narrate a shipped win. What the interviewer learns from a project that stalled is how you behave without a result to hide behind: whether you noticed the problem yourself, how long it took, and who you told. Answers that route the failure onto a data pipeline or a reorganisation close the topic without answering it, and the follow-up comes back to your own part.

What to demonstrate

Whether you found the error yourself or someone else found it, and how long it sat before anyone knew
What you changed afterwards, stated as a check you now run rather than a lesson you now believe
Whether the mistake you choose has real cost attached, such as a quarter of misdirected roadmap or a metric that was reported upward, instead of one that flatters you

How to prepare

Choose a failure you caught yourself and be ready to say what tipped you off. A story where someone else caught it is still usable, but you will be asked why you missed it.
Write down the check you added afterwards and where it lives now, so the correction is a concrete artefact rather than a resolution.
Rehearse saying the cost out loud. Candidates shrink the number by instinct once the interviewer is in the room.

Case Studies

A case has a fixed clock, and a good deal of what is being scored is how you spend it. Thirty to forty-five minutes buys one pass across the whole problem or a deep read of one part of it, and choosing between those is the work rather than a compromise forced on you. Announce the shape early: the structure you are using, the branch you think carries the decision, and what you are setting aside. An answer that is thorough for the first third and silent on the recommendation reads worse than one that is rougher throughout and lands.

What to demonstrate

Whether a visible structure appears in the opening minutes and survives the rest of the case
Whether the depth goes to the branch that carries the decision, rather than the branch you find most comfortable
Whether you say what you are leaving out and why, instead of quietly omitting it and hoping nobody asks

How to prepare

After each practice case, write down the branches you chose not to open and the reason for each, then check whether you said any of them out loud while the case was running. A branch you only cut privately reads to the interviewer as one you missed.
Redo a case you have already worked in half the time, deciding in advance which single branch you keep, then compare which version a listener would find more useful.
Write a two-sentence opening you can reuse, holding the restated question and your plan for the available time, and deliver it within the first ninety seconds of every practice run.

Final Interviews

A loop is not scored one interview at a time. The people you meet compare notes afterwards, usually in a meeting you are not in, and the outcome turns on what each of them can say about you when asked. That rewards something other than survival: every room needs one specific thing worth repeating, and none of them can contradict another. The common way to lose is to tell the same project four times with different numbers in it, or to be uniformly fine in a way that leaves nobody with anything to argue for.

What to demonstrate

Whether your account of a project survives being told twice, with the same scale, the same metric definition and the same numbers each time
Whether each interviewer leaves with one concrete claim they could make on your behalf later, rather than an absence of complaints
Whether a question you already answered in an earlier room gets the same answer at the same depth, without visible impatience

How to prepare

Write a one-page fact sheet for your two or three main projects that fixes the numbers you will quote: rows of data, the metric as a single sentence, the effect you measured and how long the work took. Say them aloud from the sheet until they come out identical every time
For each kind of room you expect, decide the one sentence you want that interviewer repeating in a debrief, then check during the mock that you said it outright instead of implying it
Rehearse answering the same project question twice in one sitting, the second time as though you had not just answered it, because the thing that needs fixing is the flatness that creeps into a repeated story

Where candidates lose points

Counting authorizations instead of weighting them, and summing amounts across currencies

Declines skew toward high-value, cross-border and card-not-present transactions, so an unweighted approval rate can sit flat while approved value falls. Merchant retry logic also turns one declined purchase into several rows, inflating the denominator by an amount that varies by merchant and by decline reason. Amounts are held in the minor unit of the transaction currency and that unit is not always two decimals, since some currencies have none and some have three, so summing amount_minor across currencies produces a figure with no interpretation at all.

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.

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.

Reporting a p-value with no effect size or interval

Give the estimated difference with a confidence interval in the units the business cares about, then say whether that whole interval is worth acting on. A p-value only addresses whether you can rule out exactly zero; it says nothing about magnitude.

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

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.

Worked solution (35 min)

Filter out reversals and zero-amount rows, then sort by the chain key and requested_at.
Compute the within-key time difference, derive the chain start flag and the chain id, and take the last row per chain with groupby(chain_id).tail(1) after sorting.
Join the exponent and daily rate tables, compute value_reporting, and assert no nulls remain after the join.
Aggregate approved value and total value to a daily grain by channel and issuer_country, reindex to a complete date range per group so missing days are zero rather than absent.
Take 7-day rolling sums of both columns and divide, then confirm one hand-picked group-day against a direct filter.

Expected result: A DataFrame keyed by date, channel and issuer_country with approved_value, total_value and approval_rate. The collapsed attempt count is materially below the raw row count, with the gap concentrated in declined ecommerce rows, and ecommerce sits below card_present.

Verify it yourself

Within every chain, all consecutive requested_at gaps are under 15 minutes; across chains with the same key, at least one gap is not.
Chain count is strictly below raw attempt count, and the difference is concentrated in declined rows rather than spread evenly.
Total converted value on a single-currency single-day subset equals the raw amount_minor sum divided by that currency's exponent power, times that day's rate.
The rolling rate on one chosen day matches a direct filter-and-divide over the preceding seven days.

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?

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

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.
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.
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.
Repeat with independent row draws for the naive interval and compare widths on the same replicate count.
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?

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

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

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?

Customers with no credit application, avoiding the NOT IN trap

Medium · null semantics · anti-join · scd type 2

Count current customers who have never submitted a credit application, broken out by segment. dim_customer is a slowly changing dimension type 2, so restrict to is_current = true, kyc_status = 'verified' and closed_at null. In fct_loan_application, customer_id is null for applicants who were not customers when they applied. Write the anti-join, return segment and customer_count, and state in one line what NOT IN (SELECT customer_id FROM fct_loan_application) returns against this table and why.

Approach

Pin the dimension to one row per customer first: is_current = true already guarantees that, but say so out loud, because forgetting it multiplies every count by the number of attribute versions a customer has accumulated.
Write the anti-join as NOT EXISTS with a correlated predicate on customer_id, which evaluates per row and is unaffected by nulls anywhere in the applications table.
Name the failure explicitly: NOT IN against a nullable column compares each candidate to a set containing NULL, the comparison yields UNKNOWN rather than TRUE, and the whole predicate is therefore never satisfied, so the query returns zero rows.
If NOT IN is required for some reason, add WHERE customer_id IS NOT NULL inside the subquery, which restores the intended semantics, and note that a LEFT JOIN with an IS NULL filter is equally safe.
Group by segment and sanity-check the total against the unfiltered current-customer count minus the count of distinct applying customers.

Follow-up

Rewrite it as a LEFT JOIN with IS NULL and say when you would prefer that form to NOT EXISTS.
How does the answer change if you want customers who never applied as of a historical date rather than today?
The applications table has 40,000 rows with a null customer_id. What are those rows, and are they a data quality problem or a product fact?

Collapse merchant retry chains before measuring authorization approval

Medium · window functions · gaps and islands · sessionisation

Merchant retry logic turns one declined purchase into several rows in fct_payment_authorization. Collapse attempts into chains: inside a partition of (card_token_id, merchant_id, amount_minor), consecutive rows whose requested_at gap is 15 minutes or less belong to the same chain. Return one row per chain with chain_started_at, attempt_count, amount_minor, channel, and chain_outcome set to 'approved' when any attempt in the chain was approved. Then report a 7-day rolling dollar-weighted approval rate by channel over chains rather than attempts. Exclude is_reversal rows.

Approach

In a CTE, take LAG(requested_at) OVER (PARTITION BY card_token_id, merchant_id, amount_minor ORDER BY requested_at) and set is_new_chain when the previous value is null or the gap exceeds 15 minutes.
Turn the flag into a chain identifier with SUM(is_new_chain::int) OVER (same partition, same order, rows unbounded preceding to current row), which is the standard gaps-and-islands construction.
Aggregate to one row per chain: MIN(requested_at), COUNT(*), and BOOL_OR(auth_result = 'approved') as the chain outcome, because the purchase succeeded if any attempt in the chain did.
Roll the chains to a daily grain per channel, then apply the rolling window as PARTITION BY channel ORDER BY chain_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW. An interval-offset RANGE frame is value-based: it admits every row in the partition whose chain_date falls in the closed window [D - 6 days, D], so a channel with no chains on some dates still gets the right seven-day sums — sparsity is exactly what this construct tolerates. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is the one that needs a dense grain, because it counts rows and on a sparse grain reaches back further than seven days.
Left-join a dense date spine only if the output needs a row on days with no chains. That is a presentation requirement, not a correctness one, and it leaves every rolling sum unchanged. The RANGE frame's preconditions are that chain_date is a date or timestamp (an interval offset needs an ordering column it can be added to) and that the aggregation left one row per (channel, chain_date).
Divide approved chain value by total chain value inside the rolling frame, never the average of daily rates, since averaging rates weights a quiet day the same as a heavy one.

Worked solution (40 min)

CTE 1: filter out is_reversal rows, then compute prev_requested_at with LAG and the is_new_chain flag.
CTE 2: build chain_id with a running SUM of the flag over the same partition and order.
CTE 3: group by partition keys plus chain_id to emit chain_started_at, attempt_count, amount_minor, channel and chain_outcome.
CTE 4: aggregate chains to (chain_date, channel) with approved value and total value. Add the date spine here only if the report must show empty days.
Final SELECT: two rolling SUMs over PARTITION BY channel ORDER BY chain_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, divided at the end.

Expected result: A rolling series with one row per date and channel, where the chain count is strictly below the attempt count on ecommerce and recurring, and the chain-level approval rate is at or above the attempt-level rate for every channel, since collapsing removes failed attempts that later succeeded.

Verify it yourself

SUM(attempt_count) over all chains equals the filtered attempt row count exactly; if it does not, the chain identifier is not partitioned the same way as the LAG.
Every chain has exactly one row whose is_new_chain flag is true, so COUNT(DISTINCT chain_id) equals the number of flagged rows.
A chain whose attempt_count is 1 must have chain_outcome equal to that single row's approved status.
The rolling denominator on day D equals the sum of the daily denominators over every date present between D-6 and D inclusive — seven terms on a dense grain, fewer on a sparse one, and the same total either way.
Adding or removing the date spine leaves every rolling sum identical, because spine rows contribute zero. Swapping RANGE for ROWS changes them on any channel whose grain has a gap, which is the direct test of which construct tolerates sparsity.

Follow-up

This definition chains off the previous attempt, so a chain can span an hour if attempts arrive every 14 minutes. When is that right, and when would you anchor the window to the first attempt instead?
Two genuine purchases of the same amount at the same merchant, four minutes apart, collapse into one chain. How large is that error and how would you bound it?
A channel goes dark for three days mid-window. Which of the two frame types would silently widen, and what would the resulting rate look like?
What changes if the merchant retries with a slightly different amount to dodge a velocity rule?

Describe a challenging research problem you encountered. How did you approach it?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
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?

How would you prioritize multiple projects with tight deadlines?

Medium · product sense and metrics

Approach

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

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 steps would you take to translate scientific insights into a commercial product?

Medium · product sense and metrics

Approach

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.
Fix the population and the time window before naming any metric.

Follow-up

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

Provide an example of how you innovated a solution based on scientific data.

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.
Decompose the metric into the rates that drive it, and say which one you would check first.

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?

How would you design an experiment to test the functionality of a new carbohydrate ingredient?

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
State the primary metric and the minimum effect worth shipping, then size the test.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

Can you discuss a project where you applied statistical tools to optimize experimental designs?

Medium · a/b testing and experimentation

Approach

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

Follow-up

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

What analytical techniques do you find most useful in carbohydrate characterization?

Medium

Approach

State your assumptions explicitly before working the problem.
Say what you would check first and why it is the highest-information step.
Work from the decision backwards to the evidence you would need.

Follow-up

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

A dispute rate that does not fall at the right edge

Medium · right-censoring · development factors · cohort maturity

Your chargeback chart slopes downward over the last four months every time it is refreshed. It is built from fct_card_dispute counted by opened_at, divided by settled transactions in the same month. Using auth_id, requested_at, dispute_stage, opened_at, network_reason_code and outcome, explain what the chart is actually showing, then specify the metric you would publish: attribution, denominator, maturity rule, and how immature periods appear on the chart. State the condition under which you would publish a development-factor estimate instead of a blank.

Approach

Separate the two dates. A dispute belongs to the month of the transaction that caused it, which is requested_at on the linked authorization, not to the month the case was filed. Counting by opened_at mixes old transactions into new months and makes the series unreadable in both directions at once.
Once attribution is fixed, name the censoring. Recent transaction months have not had time to accumulate their disputes, so the right edge falls by construction. The slope will still be there next month, one month further right, which is the diagnostic that distinguishes it from a real improvement.
Set the maturity threshold empirically rather than by folklore. From completed transaction months, compute the share of the final dispute count filed by 30, 60, 90, 120 and 180 days, split by reason-code family. Consumer dispute rights commonly run around 120 days from the transaction or the expected delivery date, but several reason codes run materially longer, so one global cut-off under-covers the slow families.
Specify the published metric: disputes reaching dispute_stage 'first_chargeback' or later, attributed to the transaction month, over settled transactions in that same month, with only months past the measured threshold shown as final.
Decide how immature months appear. Either omit them, or show a chain-ladder style estimate with an interval in visibly different styling, never as a plain point on the same line as final months. Publish the estimate only when the development pattern is stable across recent completed cohorts; if the reason-code mix or the dispute process has shifted, the factors are estimated on a pattern that no longer applies.
Note that the count series and the value series mature at different speeds, since net_loss_minor is signed and known only after the case resolves, so a loss-value chart is stale for longer than a case-count chart of the same data.

Worked solution (30 min)

Join fct_card_dispute to fct_payment_authorization on auth_id, rebuild the series by requested_at month, and plot both versions together.
For transaction months older than twelve months, compute the cumulative share of final disputes filed within 30, 60, 90, 120 and 180 days, split into fraud and non-fraud categories.
Choose the maturity threshold as the age at which cumulative share reaches a stated coverage level, for example 95 percent, for the slowest family you intend to report, and write that level into the definition.
Recompute the last twelve months, marking anything below the threshold as incomplete, and write one line saying what the chart no longer claims.

Expected result: Two series over the same data, one by opened_at and one by transaction month, plus a maturity table of cumulative filing share by age and reason-code family from which a single threshold is chosen and justified, with the most recent months marked incomplete rather than plotted low.

Verify it yourself

Cumulative filing share is non-decreasing in age. If it falls anywhere, cases are being re-dated or re-staged and the stage history log needs checking.
A mature transaction month's rate does not move when the query is re-run a month later, while an immature one rises. If a month you declared mature still moves materially, the threshold is too short.
Fraud-category disputes reach the coverage level at a younger age than delivery-related consumer disputes, so a threshold calibrated on the fast family leaves the right edge still sloping.

Follow-up

Your development factors were estimated over a period containing an authentication policy change. What do you do?
How do you present a month that is 60 days mature to an audience that wants a single number?
Would you attribute a dispute to the authorization month or the settlement month, and when does the difference matter?

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.

Bring numbers, including the ones you are unsure of

An answer without a quantity is hard to interrogate, so interviewers keep probing until they find one. Come with the baseline, the change, the window it was measured over, and how confident you were. If the effect never got measured, say so and say what you would have measured. Fabricated precision is worse than an honest gap.

What does sustainability mean to you in the context of product development?

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
Pick a story where you drove the decision, not one where you observed it.
Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

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

How do you stay informed about new developments and innovations in your field?

Medium · behavioural and stakeholder questions

Approach

State the situation in two sentences and spend the rest on your reasoning.
Close with what you would do differently, concretely.
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 did you decide not to do, and why?

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

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