Airwallex Data Scientist Interview Questions & Guide 2026

Interviewing at Airwallex

• Practice bank: 13+ questions
• Rounds: 2
• Typical prep: 1–2 weeks
• Interview reports: 3

Interview rounds

Round	Questions
Technical Screen	3
Onsite	10

How hard is the Airwallex Data Scientist interview?

Difficulty	Share	Questions
Easy	8%	1
Medium	92%	12
Hard	0%	0

What Airwallex actually tests for

Topic	Share	Questions
Analytics & Experimentation	54%	7
Data Manipulation (SQL/Python)	23%	3
Behavioral & Leadership	15%	2
Coding & Algorithms	8%	1

The 60-second brief

As a Data Scientist at Airwallex, you sit at the intersection of complex financial infrastructure and hyper-growth global expansion. You serve as a critical strategic partner to executive leadership, regional business leads, and product teams, turning massive volumes of transactional and user behavior data into actionable roadmaps. Your work directly influences how Airwallex scales its unified payments, treasury, and spend management platform for over 200,000 businesses worldwide.

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

What a Data Scientist does at Airwallex

The scope of this role is broad and impactful, spanning product-led growth, risk optimization, forecasting, and Go-To-Market (GTM) strategies. Whether you are building causal inference models to understand macroeconomic impacts on cross-border payments, designing robust experimentation frameworks for new feature rollouts, or developing automated forecasting tools, your contributions drive core business decisions. You will operate across the entire modern data stack, leveraging advanced analytics to shape the future of global fintech.

Expect a fast-paced environment that demands both technical rigor and commercial curiosity. You will tackle ambiguous, high-visibility challenges from first principles, balancing speed with analytical precision. While the expectations are high, you will work alongside exceptional builders and engineers who value end-to-end ownership and proactive critical thinking.

The Airwallex interview process

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

Recruiter Screening

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What it evaluates

• Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
• Whether you name what you have not done instead of stretching to cover every line of the posting
• Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

• Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
• Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
• Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Technical Evaluation

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 it evaluates

• 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

Business 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 it evaluates

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

Causal Inference Deep Dive

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What it evaluates

• Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
• Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
• How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

• Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
• Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
• Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

Cross-Functional Collaboration

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 it evaluates

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

Questions Airwallex candidates report

Product sense and metrics

Every question in this group starts from the same move: name the outcome the product is supposed to produce, then name the number that would show it happening. Work forward from the mechanism, not backward from a metric you already like. Ask what behaviour changes, for whom, and how soon, and only then pick the measure that sits closest to that behaviour without being trivially satisfied by it. What usually separates answers is the counterfactual. A candidate who says what result would make them call the change a failure has defined success; one who only describes what going well looks like has not.

• If a regional executive wants to track success via a vanity metric, how would you guide them toward a more actionable alternative?
• How would you design a product metric framework to measure user activation and long-term retention for a new spend management tool?
• Extract month-over-month user retention cohorts using advanced grouping and date manipulation.
• Is the metric good enough? What is the problem of the metric?

SQL and data manipulation

A window function earns its place when a question needs a per-row value that depends on other rows without giving up the rows themselves: rank inside a group, the previous value, a running total, a share of the group total. A grouped aggregate collapses, a window keeps. The detail most answers miss is the frame. Adding ORDER BY inside OVER switches the default frame to everything from the start of the partition through the current row, and that default is range-based in standard SQL, so rows tied on the ordering value all enter together. If ties matter, write ROWS yourself.

• These tests assess your fluency in data retrieval, schema navigation, and advanced querying techniques.
• Given the provided schema for transaction logs and user accounts, write a query using SQL window functions to find the rolling 30-day active volume per merchant.

A/B testing and experimentation

The standard analysis assumes one unit's treatment cannot affect another unit's outcome. Say that assumption out loud, then ask where it breaks in the setting you were handed: shared supply, a shared budget, a ranking that reorders when other people click, any two-sided market where the arms compete for the same inventory. Interference does not merely add noise, it biases the estimate, and where the arms compete for scarce supply it usually flatters the treatment because the control group absorbs the cost. The answer that lands names the channel the spillover travels through, then changes the design rather than the test.

• These scenarios test your understanding of online controlled experiments, traffic allocation, and common pitfalls.
• How do you evaluate A/B testing results when sample ratios mismatch?

Statistics and probability

Probability questions here tend to be short, and they reward setup over cleverness. Define the random variable in words, say what it ranges over, and only then compute. Two tools cover most of what gets asked. Linearity of expectation lets you break a total into indicators and add them, and it holds whether or not those pieces are independent, which is precisely why it beats enumeration. Conditioning on the first step turns a repeating process into an equation you solve for the answer. Weak answers start multiplying numbers before the sample space is pinned down, then cannot locate where it went wrong.

• These questions gauge your command of core statistical principles and inference methods.

Behavioural and stakeholder questions

A large share of these land on disagreement: someone wanted a different answer, a different metric, or a different date. The weak version of the answer makes the other person wrong and the data right. The strong version treats their position as a constraint you had not priced in, usually a cost, a deadline, or a commitment made outside your view. Before you say what changed their mind, say what evidence would have changed yours. That one sentence separates someone who argues with numbers from someone who reasons with them, and it is the sentence that survives the follow-up.

• These prompts explore your collaboration style, communication skills, and resilience under pressure.

Other topics

A few of these have no retrievable answer at all. They ask what you would prioritise with one analyst and two weeks, whether a model is good enough to ship, whether a data source is appropriate to use. Answer by naming the decision, then the cost of being wrong in each direction. A false positive that sends an email and a false positive that suspends an account do not deserve the same threshold, and any argument about the threshold only makes sense once those costs are on the table. Say what you would do, then say what evidence would change it.

• Your core conversion rate dropped by 15% week-over-week. How would you structure a root-cause diagnosis?

Practice exercises with worked solutions

The prompts above tell you what gets asked.

Customers with no credit application, avoiding the NOT IN trap

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.

How a strong candidate works it

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

Expected result (20 min): One row per segment present in the current dimension (retail, sme, corporate), each with a non-zero customer_count. The NOT EXISTS and the null-guarded NOT IN forms agree exactly; the unguarded NOT IN form returns no rows at all.

Check yourself

• COUNT(DISTINCT customer_id) equals COUNT(*) on the filtered dimension, which confirms is_current really collapsed the type 2 versions.
• The total across segments plus COUNT(DISTINCT customer_id) from fct_loan_application restricted to current verified open customers equals the full current verified open customer count.
• Swapping NOT EXISTS for the unguarded NOT IN changes the result from a populated set to an empty one, which is the whole point of the exercise.

Follow-ups

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

Write integrity checks for the authorization and settlement lifecycle

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.

How a strong candidate works it

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

Follow-ups

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

Simulate false alarms in a merchant chargeback monitoring rule

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.

How a strong candidate works it

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

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

Check yourself

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

Follow-ups

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

Estimate the marginal effect of approval at a score cutoff

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

How a strong candidate works it

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

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

Check yourself

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

Follow-ups

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

Trailing thirty day volume per customer drops week over week

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

How a strong candidate works it

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

Follow-ups

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

Defend a vintage finding that contradicts the portfolio dashboard

The lending dashboard shows blended 90-plus days-past-due falling for four consecutive quarters while originations grew 60 percent. Using fct_loan_performance_monthly, you build a vintage view keyed on origination_month by months_on_book and find the three most recent vintages are worse than their predecessors at the same age. The business lead presents that dashboard weekly and pushes back hard, suggesting you picked favourable cohorts. You get one meeting and the vintage table. Present the finding so it survives the cherry-picking objection and ends in a decision.

How a strong candidate works it

• Reconcile before you contradict: show that aggregating your vintage table along the calendar diagonal reproduces the published blended series, so the disagreement is about age mix rather than about data quality.
• Make the mechanism arithmetic rather than rhetorical: a loan cannot reach 90 days past due before it is 90 days old, so rapid origination growth shifts weight onto young months-on-book where the rate is structurally near zero.
• Show every vintage rather than a selected pair, all indexed at months_on_book equal to 12, with cohort sizes printed beside each curve so nobody can claim the divergence rests on a thin cohort.
• Handle restructuring explicitly, because restructured_flag resets days_past_due: count each loan on its worst pre-restructure state, or recent vintages will look better than they are.
• Close on the decision rather than the chart: state what the divergence implies for the cutoff or the channel mix, and state in advance what evidence would make you withdraw the claim.

Follow-ups

• Two cohorts differ at month 12. How do you separate a seasoning effect from a genuine credit-quality effect?
• Someone argues the recent vintages are simply a broker-channel mix shift. How do you test that, and what would confirm it?

Disagree with a product manager over an approval-rate target

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.

How a strong candidate works it

• 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-ups

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

Where candidates lose points

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.

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.

Naming a model class before naming the deployment constraints

Set out the latency budget, the label delay, the retraining cadence, the interpretability requirement and the number of labelled examples, then pick the model that fits them. A boosted-tree answer to a problem where each decision must be explained to the affected user is a well-executed answer to the wrong question.

SQL that silently fans out on a one-to-many join

State the grain of each table and the grain you want in the result before writing the join. Pre-aggregate the many side to the join key, or use EXISTS or a window function, and verify with a row count against COUNT(DISTINCT id) rather than trusting that the numbers look plausible.

SQL first, for a candidate whose queries have gone rusty

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

Day 1 — Build a fixture you can check answers against

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

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

Day 2 — Joins, filters and NULL semantics

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

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

Day 3 — Window functions and frames

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

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

Day 4 — The four analytical query patterns

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

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

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

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

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

Day 6 — One day for everything that is not SQL

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

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

Day 7 — Full loop rehearsal

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

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