Interview guides · Canadian Tire

Canadian Tire Data Scientist Interview Questions & Guide 2026

Timed data scientist practice for Canadian Tire: short SQL and probability sets run end to end, then scored against a rubric.

The 60-second brief

A Data Scientist at Canadian Tire sits at the intersection of retail innovation and advanced analytics. You will work within one of Canada’s most iconic retail ecosystems, leveraging vast datasets—from supply chain logistics and inventory management to digital marketing and customer loyalty programs—to drive strategic business decisions. Your work directly impacts how millions of Canadians interact with Canadian Tire products, whether through personalized recommendations, optimized pricing strategies, or predictive models that streamline store operations.

In modelling rounds the live question is usually why this model class for this problem, and how you would know six weeks after launch that it is still working. Deriving gradients by hand is rarely what is being probed.

Focus areas

Report only matured cohorts for loss metrics
Separate authorization, settlement and dispute outcomes cleanly
Read vintage curves, not blended portfolio averages

Overview

This role is both technically rigorous and highly collaborative. You will not be working in a silo; you will partner with product managers, engineers, and retail stakeholders to translate complex business problems into actionable data products. Because of the scale of Canadian Tire, you will face unique challenges related to high-volume transaction data and multi-channel retail environments. Success in this role requires a balance of statistical depth, a product-first mindset, and the ability to communicate technical findings to non-technical stakeholders across the organization.

The Canadian Tire interview process

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

Recruiter Screen

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical 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

Interviews with Managers

An added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.

What to demonstrate

Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact

How to prepare

Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.

Where candidates lose points

Using written premium as the denominator of a loss ratio

Premium is written at inception and earned pro rata across the exposure period, so in a growing book written premium runs ahead of earned premium and the loss ratio comes out too low, with the error reversing when the book shrinks. The numerator has the mirror-image problem if it omits incurred-but-not-reported reserves, since recent accident periods then look profitable twice over. Both sides must refer to the same exposure period, which is what an accident-period view at a fixed development age enforces.

Recalibrating an underwriting cutoff on approved and funded applicants only

Rejected applicants have no repayment outcome, and they were rejected because the incumbent model scored them badly, so the missingness depends directly on the outcome being modelled. Reject inference by augmentation or parcelling fills the gap using the incumbent model's own assumptions, which means it can confirm those assumptions but cannot test them. The only genuinely new information about the reject region comes from bureau performance on rejects who borrowed elsewhere, or from a deliberately randomised approval band around the cutoff.

Solving silently instead of narrating the reasoning

Say which branch you are taking and why you chose it over the alternative, for example checking the denominator first because it changes what the comparison means. A correct answer that arrives with no visible path scores below a rigorous one that needed a hint.

Crediting a treatment for regression to the mean

Selecting a group because it is extreme (lowest-engagement users, accounts having their worst month, the bottom decile of a score) moves that group's expected next-period value back toward the average even under no treatment, by exactly as much as the selecting measure is imperfectly correlated with its own later value. Compare against units that met the same selection rule and went untreated, or use two pre-periods so the bounce-back is visible before the intervention starts. A pre-post number on a group chosen for being extreme measures the selection rule, not the treatment.

Questions & practice

How have you handled a situation where your model’s performance was lower than expected?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Frame the prediction: the label, the moment of prediction, and the action it triggers.
Set a baseline first, so any model has something honest to beat.

Follow-up

How would you choose the decision threshold, and who owns that choice?
What would you monitor after launch to know the model is still valid?

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?

Measure calibration of a twelve-month default probability from scratch

Hard · calibration · brier score · binning

fct_loan_application gives application_id, model_pd_12m, model_version, decision, funded_at and loan_id. fct_loan_performance_monthly gives loan_id, months_on_book, days_past_due and charge_off_flag. Define the outcome as ever 90 or more days past due, or charged off, by months_on_book = 12. Without sklearn or scipy, build an equal-count binned reliability table, the expected calibration error, the Brier score and its reliability, resolution and uncertainty components, and report the residual the binned identity leaves behind. Restrict to cohorts that have actually reached 12 months on book.

Approach

Build the label first and name the population it covers out loud: only funded loans have outcomes, so this measures calibration on the approved population. The declined region is unmeasured, and no binning scheme repairs that.
Restrict to applications whose loans have reached months_on_book = 12. A cohort observed at 8 months has a mechanically lower default rate and will read as systematic over-prediction that is really just immaturity.
Bin by equal count, deciles of model_pd_12m through a rank-based cut, not equal width. The PD distribution is heavily right-skewed, so equal-width bins put most of the mass in the first bin and leave the risky bins with single-digit counts whose observed rates mean nothing.
Per bin compute n, mean predicted, observed rate, and the binomial standard error sqrt(o(1-o)/n) so a gap can be read against noise. ECE is the count-weighted mean absolute gap between mean predicted and observed.
Compute Brier directly as the mean squared error, then reliability = sum of n_k (pbar_k - obar_k)^2 over N, resolution = sum of n_k (obar_k - obar)^2 over N, uncertainty = obar(1 - obar). Report residual = Brier - (reliability - resolution + uncertainty). That identity is exact only for discrete forecasts, so with binned continuous scores the residual is the within-bin spread of the score; a large one means the bins are too wide to support the decomposition.
Split by model_version. A mixed-version population can look well calibrated in aggregate while each version is biased in opposite directions.

Follow-up

AUC is unchanged after a population shift but the reliability curve has moved. What happened, and what do you do about it?
How would you recalibrate without retraining, and what would you check afterwards?
The top decile shows observed default well above predicted. Is that a calibration problem or a policy problem?

Describe how you would use the LAG function to compare month-over-month sales performance.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
State the window function and its partition and ordering out loud before writing it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

Follow-up

What breaks if events arrive late or out of order?
How would you verify this result without re-running the same query?

How would you use SQL window functions to calculate a moving average of sales over the last 30 days?

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.
Say which table is the grain you start from, and join outward from it.

Follow-up

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

Reconcile captured authorizations against the daily settlement total

Easy · reconciliation · currency · rounding

fct_payment_authorization holds captured_amount_minor in transaction_currency, and settlement_amount_minor in settlement_currency with settlement_fx_rate applied at settlement rather than at authorization. The rate is quoted in major units of settlement_currency per major unit of transaction_currency, and dim_currency.minor_unit_exponent carries the ISO 4217 exponent for each code (0, 2 or 3 depending on the currency). Produce a daily reconciliation: for each settled_at date and settlement_currency, return settled_count, total settlement_amount_minor, and the sum of captured_amount_minor converted into settlement minor units. Flag any date and currency pair whose two totals differ by more than one minor unit per settled authorization. Do not sum amounts across currencies anywhere in the output.

Approach

Restrict to rows that actually settled: settled_at is not null and settlement_amount_minor is not null, which is a smaller population than captured rows because a capture can still be in flight.
Truncate settled_at to a date with an explicit time zone so the cut matches the ledger's cut, since settled_at is timestamptz and date_trunc on timestamptz silently uses the session time zone.
Join dim_currency twice, once on transaction_currency and once on settlement_currency, so both exponents are on the row. Minor units are not a common scale: a bare captured_amount_minor * settlement_fx_rate is correct only when the two exponents are equal, and a zero-decimal currency settling into a two-decimal one is wrong by a factor of 100.
Convert per row as ROUND(captured_amount_minor::numeric / POWER(10::numeric, exp_txn) * settlement_fx_rate * POWER(10::numeric, exp_settle)) — minor units to major in the transaction currency, apply the major-per-major rate, then back to minor units in the settlement currency. The collapsed form ROUND(captured_amount_minor::numeric * settlement_fx_rate * POWER(10::numeric, exp_settle - exp_txn)) is the same expression. Round per row and then sum, not SUM(...) * an average rate, because the rate varies row by row and rounding per row is what the settlement file did.
Group by the settlement date and settlement_currency together, never by date alone, and carry the currency into every output column name or row.
Compare the two totals with a tolerance scaled by settled_count, since per-row rounding accumulates linearly in the number of rows rather than being a fixed constant.

Worked solution (25 min)

Filter to settled rows and derive settlement_date from settled_at with an explicit time zone.
Join dim_currency on transaction_currency and again on settlement_currency to pick up exp_txn and exp_settle; fail the run if either is null rather than defaulting to 2.
Aggregate by settlement_date and settlement_currency: COUNT(*), SUM(settlement_amount_minor), and SUM(ROUND(captured_amount_minor::numeric / POWER(10::numeric, exp_txn) * settlement_fx_rate * POWER(10::numeric, exp_settle))).
Add a derived difference column and a boolean flag where ABS(difference) > settled_count.
Order by the flag first and then by settlement_date so the exceptions surface at the top.

Expected result: One row per settlement_date and settlement_currency, carrying settled_count, settlement_total_minor, converted_capture_total_minor, their signed difference, and a boolean exception flag. On clean days the difference is small relative to settled_count and has no consistent sign. Pairs with unequal exponents reconcile exactly as well as equal-exponent pairs; if they do not, the exponent terms are missing rather than the FX rate being wrong.

Verify it yourself

The difference divided by settled_count stays well under one minor unit on unflagged days, which is the expected size of per-row rounding.
Restricted to rows where exp_txn = exp_settle, the exponent-aware conversion and a bare captured_amount_minor * settlement_fx_rate agree exactly; restricted to rows where the exponents differ by 2, the bare form is smaller by exactly a factor of 100. That is the direct test that the dim_currency join is load-bearing.
Re-running with SUM over all currencies at once produces a number that changes when a zero-decimal currency's volume moves, which demonstrates why the cross-currency total is meaningless.
settled_count summed over all dates equals COUNT(*) on the same settled-row filter with no grouping.

Follow-up

A partial capture means captured_amount_minor is less than amount_minor. Where does that show up in this reconciliation, and where does it not?
On one currency pair the converted total is consistently about one hundredth of the settlement total, on every date, while the other pairs reconcile. Which two columns do you inspect first, and what single change fixes it?
The rate is documented as major-per-major. If a feed started publishing it minor-per-minor instead, which pairs would still reconcile and which would break?
How would you present a total across currencies to a finance partner who has asked for one number?

How would you measure the incremental uplift of a marketing campaign?

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.
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?
How would you detect that the metric is being gamed rather than genuinely improving?

How do you determine which two product sales patterns are correlated?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.
Fix the population and the time window before naming any metric.

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?

If you noticed a sudden drop in online conversion rates, 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.
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?

How would you design a metric to measure the success of a new loyalty program feature?

Medium · product sense and metrics

Approach

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.
Name one primary metric, then the guardrail that stops it being gamed.

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?

How do you decide on the sample size required for a statistically significant experiment?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Decide the analysis before seeing data, including how long it runs and when you look.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

What are the most common pitfalls you encounter when designing an A/B test?

Medium · a/b testing and experimentation

Approach

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.
Say whether units interfere with each other, and switch design if they do.

Follow-up

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

Read a segment-sliced result with a fading first-week lift

Medium · multiple comparisons · novelty effects · mix shift

A three-week test of an in-app credit-limit-increase offer had one pre-registered primary metric. The readout also slices 24 secondary segment-by-metric cells, of which three are significant at 0.05 with p-values 0.011, 0.028 and 0.041. The treatment effect on the primary was plus 8.1 percent in calendar week one, plus 3.4 percent in week two and plus 1.5 percent in week three. Say which claims survive, what the decay does and does not prove, and what you tell the sponsor.

Approach

Compute what chance alone produces before interpreting any cell. With 24 tests at 0.05 the expected number of false positives is 1.2, the probability of at least one is 1 - 0.95^24 = 0.708, and the probability of three or more under an independence approximation is about 0.12. Three hits is an unremarkable outcome, not a signal.
Apply a stated correction rather than a vibe, and be precise about which one the reported numbers can settle. Holm and Bonferroni both begin at 0.05 / 24 = 0.00208, so the smallest p-value alone decides them. Benjamini-Hochberg sorts all m p-values ascending and finds the largest k with p(k) <= k * q / m; at q = 0.10 and m = 24 the rank-k threshold k / 240 starts at 0.00417 and rises to 0.10 at k = 24, so it is a step-up test over the whole family and its verdict cannot be read off the three cells that happened to fall below 0.05. Ask for all 24 p-values, report the surviving set, which may be empty, and demote the rest to hypotheses for a future test.
Refuse to call the decay novelty until the competing explanations are ruled out. Three things produce a falling weekly effect: a genuine novelty response from existing users that reverts, a mix shift because week three's exposed population is not week one's, and an effect that is correctly one-shot because a limit increase is taken once per customer.
Separate them with concrete cuts. Plot the effect against time since first exposure instead of calendar week, which removes the mix shift. Compare users onboarded during the test, who never saw the previous experience and therefore cannot show novelty, against existing users. Hold back a small fraction of traffic for a long-run read.
Fix the estimand to match the mechanism. For a one-shot offer the meaningful quantity is cumulative take-up per exposed user over a fixed horizon, not a weekly rate, and under that estimand a declining weekly rate is exactly what a working feature looks like.
Give the sponsor one number with its interval, name the decision it supports, and list the exploratory cells separately under a heading that says they are not results.

Worked solution (25 min)

Compute the chance baseline: 0.95^24 = 0.292, so P(at least one false positive) = 0.708 and the expected count is 1.2.
Settle the corrections the reported numbers can settle. Holm and Bonferroni both require the smallest p-value to clear 0.05 / 24 = 0.00208; 0.011 does not, so neither rejects anything, and that conclusion needs only the one number. Benjamini-Hochberg is different: with m = 24 and q = 0.10 the rank-k threshold is k / 240, which runs 0.00417, 0.00833, 0.0125 for the three reported cells (all three fail their own thresholds) but passes 0.05 at k = 13 (0.05417) and reaches 0.10 at k = 24. Because BH rejects the k smallest hypotheses whenever any p(k) clears its threshold, one of the 21 unreported p-values sitting between 0.05 and its own rank threshold would make BH reject every cell down to rank 1, the three included. Demand the full ordered vector of 24 p-values before stating a BH verdict.
Rebuild the weekly series on time since first exposure and overlay new versus existing users, then state which of novelty, mix shift and one-shot mechanics the pattern is consistent with.
Recompute the primary as cumulative take-up per exposed user at a fixed 21-day horizon and report that with its confidence interval as the headline.
Write the sponsor summary: one confirmatory number, the exploratory cells listed as non-results pending the full p-value vector, and a proposed follow-up test for the single most plausible segment hypothesis.

Expected result: Holm and Bonferroni reject nothing, and that follows from the reported numbers alone: the smallest p-value, 0.011, exceeds 0.05 / 24 = 0.00208. Benjamini-Hochberg at q = 0.10 is not decidable from three p-values, because it is a step-up test over all 24 whose rank-k threshold k / 240 passes 0.05 at k = 13, so the correct move is to demand the full ordered vector and name the condition that would flip it rather than assert that nothing survives. Until that vector arrives the three cells are exploratory, not findings. Only the pre-registered primary is confirmatory, restated as cumulative take-up over a fixed horizon. The 8.1 to 1.5 percent decay is not established as novelty; the correct answer names mix shift and the one-shot nature of a limit increase as competing explanations and gives the cuts that separate them.

Verify it yourself

Binomial sanity check: P(3 or more hits out of 24 at 0.05) is about 0.12 under independence, so three hits is unremarkable; correlation among the cells makes the true probability higher still.
BH self-check: the rank-k thresholds k * 0.10 / 24 must run from 0.00417 at k = 1 to 0.10 at k = 24 and cross 0.05 between k = 12 and k = 13. Any BH conclusion that uses only the p-values below 0.05 is ignoring the ranks where the threshold is loosest.
The cumulative take-up curve per exposed user should be monotone increasing and flatten, while the weekly rate falls. Both are the same data, which is the point.
New users during the test should show no decay if the decay is novelty. If they decay the same way, novelty is not the explanation.
Re-run the segment slice on an A/A period: if it also produces two or three significant cells, the slicing procedure itself is the finding.

Follow-up

The three significant cells are all in the same segment. Does that change your reading, and how would you test whether it is a real interaction rather than three correlated slices of one population?
Design the long-term holdback: what size, for how long, and what does it cost you in foregone treatment?
If cumulative take-up is the estimand, what is the right horizon and how do you keep it from being chosen after seeing the data?

Approval rate fell but approved value did not

Easy · approval rate · retry chains · denominators

Over ten days the count-weighted 7-day approval rate on fct_payment_authorization fell from 91 to 86 percent, while captured value in the reporting currency is flat. Available columns: auth_id, card_token_id, merchant_id, mcc, channel, requested_at, amount_minor, transaction_currency, auth_result, decline_reason_code, risk_score, is_reversal, parent_auth_id, captured_at, captured_amount_minor, issuer_country. In twenty minutes, decide whether approved value is actually at risk, and hand back a corrected rate together with its denominator and every exclusion written down.

Approach

Restate the denominator before querying anything. The current one counts every row, so reversals (is_reversal = true), incremental authorizations (parent_auth_id not null) and zero-amount verification attempts (amount_minor = 0) are all sitting in it.
Chart numerator and denominator separately by day. If approved counts are flat and total attempts rose, the rate moved because the denominator grew, which is a different investigation from a rule change and points at a different owner.
Group declines by decline_reason_code and merchant_id. Retry-driven inflation concentrates in a few soft decline codes at a few merchants; a genuine policy change spreads across merchants within one code family.
Collapse retry chains: partition by (card_token_id, merchant_id, amount_minor), keep one attempt per 15-minute window taking the best outcome, and recompute both count-weighted and dollar-weighted rates on the collapsed set.
Convert amount_minor to one reporting currency using each currency's ISO 4217 exponent before any dollar weighting, then cut by channel and issuer_country to confirm nothing is hiding underneath a flat total.
Report both rates side by side with the exclusion list attached, and state which definition the alert should have been built on.

Follow-up

How would you pick the retry-collapsing window when merchants retry on different schedules?
Flat captured value could itself be hiding a mix shift. How do you rule that out?
What monitor would have caught denominator inflation on the day it started, rather than ten days later?

Suggested practice checklist

For a candidate whose interviews will centre on A/B testing, metric movement and causal claims. Design comes before arithmetic, arithmetic before analysis, and the week ends by rehearsing the readout rather than the derivation.

Day 1 — Design one test end to end on paper

Take a single feature change and write the full design: randomization unit, the exact point of exposure, the primary metric with its grain, guardrails, allocation, planned duration, and the decision rule committed before any data exists.
Write why the randomization unit must sit at or above the level where treatment can spill over, and give one case where user-level randomization is still contaminated (shared accounts or devices, or two participants in the same marketplace).
State in advance what you will do if the primary metric is flat while a secondary metric is significant.

Deliverable: A one-page test design with a decision rule written before launch.

Day 2 — Power arithmetic until it is automatic

Compute required sample size per arm for a binary metric with the normal approximation, n is approximately 2 times (z for alpha/2 plus z for power) squared times p(1 minus p) divided by delta squared, for baselines of 2, 10 and 40 percent at a 5 percent relative lift, and note that for a fixed relative lift the requirement falls as the baseline rises because delta grows proportionally with p.
Redo the calculation for a continuous metric using variance in place of p(1 minus p), and show why a heavy-tailed quantity such as revenue per user needs either far more traffic or a capped version with a stated cap.
Convert one of the results into weeks given a weekly eligible traffic figure, then list the two honest ways to shorten it (accept a larger detectable effect, or reduce variance) and write why quietly lowering the power target is a decision to miss more real wins, not a speedup.

Deliverable: A small script or sheet that maps baseline, minimum detectable effect, alpha and power to sample size and weeks, cross-checked against a published calculator.

Day 3 — Variance and the unit-of-analysis problem

Take a ratio metric whose denominator is not the randomization unit (clicks per session, randomized by user) and compute the standard error twice, once naively at session level and once by the delta method or a user-level bootstrap, then record how much the naive version understates it.
Implement CUPED on simulated data: choose a pre-period covariate X measured before assignment, estimate theta as Cov(Y, X) divided by Var(X), and analyse Y minus theta times (X minus its mean) in place of Y. Confirm the variance of the adjusted outcome equals the raw variance multiplied by one minus the squared correlation between Y and X, so a correlation of 0.45 removes about 20 percent of the variance and not 80.
Now run that simulation a few hundred times and confirm the adjusted effect estimate is unbiased for the same effect rather than numerically identical to the raw one. Within any single run the two differ, sometimes by a large fraction of the true effect, because the two arms' pre-period covariate means never coincide exactly in a finite sample; they agree in expectation, which is the property that matters and the one to state out loud.

Deliverable: A notebook showing the adjusted estimator with a measurably smaller variance than the raw one, plus a repeated-simulation table showing the two estimators agreeing on average while differing run by run.

Day 4 — Validity threats you can actually test for

Run a sample ratio mismatch check as a chi-square goodness-of-fit test against the intended allocation, and write the three causes you would chase first (assignment logged before exposure, an arm-specific redirect or load failure, bot filtering applied asymmetrically).
Simulate peeking: generate A/A data, test daily at alpha 0.05 across 14 looks, record the inflated false positive rate, then apply an alpha-spending boundary or commit to a fixed horizon and confirm the rate returns to nominal.
Write how you would separate a novelty effect from a durable lift using the treatment effect plotted against days since first exposure, and what shape would change your recommendation.

Deliverable: One table showing the peeking false positive rate before and after correction, plus a written SRM triage list.

Day 5 — When randomization is not available

Write the identifying assumption for difference-in-differences (parallel trends in the absence of treatment), then plot pre-period trends for two candidate control groups and justify rejecting one of them.
Design a switchback test for a change where user-level randomization would leak across participants, choosing a time-block length against the carryover you expect and saying how you would detect carryover in the data.
List what an interrupted time series or a synthetic control buys you and the one thing neither can rule out: an unobserved shock that coincides with the launch.

Deliverable: A one-page memo recommending a single quasi-experimental design and naming its weakest assumption explicitly.

Day 6 — The readout query

Write the assignment-to-exposure join that returns exactly one row per unit per experiment, and handle units appearing in both arms by excluding and counting them rather than silently keeping one.
Compute the per-arm metric, its variance and the relative lift with a confidence interval in SQL, then reproduce the identical numbers in a notebook as a cross-check.
Add a segment breakdown and write the sentence that keeps it from being p-hacking: segments declared in advance, everything else reported as exploratory and corrected for multiplicity.

Deliverable: A single query that outputs the full readout table, matched to a notebook recomputation.

Day 7 — Present it to someone who will not read the appendix

Give a 10-minute readout of a real or simulated experiment in the order decision, number, uncertainty, caveat.
Have your listener ask "can we ship it" in the case where the primary is flat and a guardrail moved, and answer with a recommendation rather than a request for more data.
Rewrite your opening line so the recommendation lands before any methodology.

Deliverable: A one-page readout whose first line is the recommendation.

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.

Explain the concept of a p-value to a non-technical stakeholder.

Medium · behavioural and stakeholder questions

Approach

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

Disagree with a product manager over an approval-rate target

Medium · metric design · denominators · influence without authority

A product manager proposes a quarterly goal of raising card authorization approval rate by 150 basis points, measured as approved authorizations divided by all authorizations in fct_payment_authorization. You believe that metric can be hit with no customer benefit, because merchant retry chains, zero-amount verification authorizations, incremental authorizations and reversals all sit in the denominator, and declines skew toward high-value cross-border ecommerce. You support the underlying goal. In one working session, change the metric without killing the initiative, and name the guardrail you would accept.

Approach

Separate the goal from the metric out loud and agree with the goal first, so the disagreement stays narrow and technical rather than becoming positional.
Demonstrate the failure rather than asserting it: compute the proposed metric and the dollar-weighted collapsed version over the same recent window, and find a period where they moved in opposite directions.
Propose the replacement precisely: sum of approved amount_minor over sum of attempted amount_minor, after collapsing retries to one attempt per card_token_id, merchant_id and amount_minor within a 15-minute window, excluding is_reversal rows and zero-amount verifications, with everything converted to one reporting currency before summing.
Attach the guardrail that makes the target honest: matured first-chargeback rate and net fraud loss in basis points of settled volume, read only on transaction months carrying at least 120 days of maturity.
Give the product manager something back: the replacement metric cuts cleanly by channel and issuer_country, which makes a roadmap of merchant-specific and authentication fixes legible in a way the blended rate never was.

Follow-up

How do you identify a retry chain when the merchant varies the amount slightly between attempts?
The product manager wants a weekly read on the guardrail. What is the earliest defensible signal, and how do you label it?

Retract a published number after finding a currency bug

Medium · error disclosure · minor units · process repair

Two weeks ago you published an interchange and fraud analysis that summed amount_minor across fct_payment_authorization without converting currencies. Minor units are not two decimals everywhere: some currencies carry none and some carry three, so the sum has no interpretation. A pricing decision is already in flight on the back of it. You now have corrected figures. Produce the retraction: what you send, to whom, in what order, and what you change in the process so this class of error is caught next time rather than trusted next time.

Approach

Size the error before announcing it, because saying the number is wrong without a magnitude and a direction forces every reader to assume the worst case.
Check whether the conclusion actually flips: if the ranking that drove the pricing decision is unchanged, that belongs in the first sentence beside the correction rather than buried at the end.
Tell the person acting on it first and directly, then the wider distribution, using the same text, so nobody learns about it secondhand.
Write the correction as four parts: the old number, the cause in one clause, the effect on the pending decision, and the new number. Leave out self-flagellation, which makes the reader do emotional work instead of acting.
Fix the class rather than the instance: a rule that a sum over amount_minor either groups by transaction_currency or passes through both conversion steps, exponent scaling and then a dated rate into one named reporting currency, plus a standing reconciliation of the settled subset to the settlement ledger inside each settlement_currency.

Follow-up

The corrected figures do not change the decision. Do you still send the correction, and what does that choice signal?
What automated check would have caught this, where would it live, and what would it cost in false alarms?