Interview guides · BCG Digital Ventures

BCG Digital Ventures Data Scientist Interview Questions & Guide 2026

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

The 60-second brief

As a Data Scientist at BCG Digital Ventures (BCG DV), you are not just analyzing data; you are acting as a core builder of entirely new businesses. BCG DV operates at the unique intersection of corporate incubation and startup agility, partnering with some of the world’s largest corporations to invent, build, and scale new digital products and ventures. In this role, your work directly shapes the trajectory of these new ventures, transforming raw data into foundational product features, strategic insights, and scalable machine learning solutions.

SQL is seldom the hardest round and is often the one that eliminates people. The working bar is usually window functions, correct deduplication, and joins that do not silently fan out rows, rather than obscure syntax.

Focus areas

Separate contracted seats from actively used seats
Read NRR on a fixed account cohort
Measure churn only on renewal-eligible accounts

Overview

Your impact spans the entire venture lifecycle. During the incubation phase, you will use data to validate market opportunities and shape the Minimum Viable Product (MVP). As the venture scales, you will design and deploy the algorithms that power the core product experience. This is a role that demands both deep technical rigor and exceptional commercial acumen, as you will frequently collaborate with corporate partners, product managers, and venture architects who rely on your insights to make high-stakes business decisions.

What makes this position uniquely compelling is the sheer variety and scale of the problem spaces you will encounter. One month you might be optimizing supply chain logistics for a global manufacturer, and the next, you could be building a predictive health-tech algorithm or a fintech risk model. If you thrive in fast-paced, ambiguous environments and want to see your technical work directly manifest into living, breathing companies, this role offers an unparalleled platform for your skills.

The BCG Digital Ventures interview process

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

HR and Background Screen

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 to demonstrate

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.

Technical Rounds

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

What to demonstrate

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

How to prepare

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

Data Visualization Round

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

What to demonstrate

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

How to prepare

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

Technical Case Study

This round runs as a working session, so part of what it decides is whether you are useful to think with. The interviewer will interrupt: a hint that the data you assumed does not exist, a challenge to your metric, a nudge toward a branch you skipped. Treating those as interference is the common failure. Reason out loud while your thinking is still provisional so there is something to react to, and when a redirect arrives, take it instead of defending the path you had already started down.

What to demonstrate

Whether your reasoning is audible while it is still unsettled, or only after you have privately decided
What you do with a hint: absorb it and adjust, or argue for the original route
Whether your clarifying questions have answers that would change your approach, as opposed to filling silence
Whether you can be wrong about something in the middle of the case and keep moving without restarting

How to prepare

Run practice cases with a partner instructed to interrupt twice: once to remove a data source you assumed existed, once to reject the metric you chose. Practise absorbing both without going back to the start.
Before each practice case, write down the clarifying questions you plan to ask, then check afterwards whether any answer actually changed what you did. Drop the ones that did not.
Explain an analysis you already know well to someone outside the field and have them stop you at every point where the reasoning jumped a step.

Where candidates lose points

Randomising an experiment at the user level when users share an account

Two problems fire at once. Colleagues in one workspace see each other's work and talk to each other, so a treated user changes the behaviour of a control user in the same account, which violates the no-interference assumption and biases the estimate toward zero. Separately, outcomes within an account are strongly correlated, so the effective sample size is roughly n / (1 + (m - 1) * rho) for m users per account and intra-class correlation rho, not n. With rho around 0.3 and twenty users per account that is a design effect near 6.7, meaning a user-level confidence interval is about two and a half times narrower than it should be and results cross significance thresholds on noise alone. Randomise the account and cluster the standard errors.

Reading consumption metrics before the metering lag window has closed

Usage pipelines land late and correct themselves, which is exactly what is_restated and restated_at record. A dashboard queried on day T sees a partially populated tail for the last several days, so the most recent points always slope downward and always look like a regression. Analysts then explain the artefact, and sometimes ship a change to fix it. Establish the empirical settling time by measuring how much a given usage_date's total moves between first_written_at and its final value, exclude that many trailing days from every reportable figure, and never compare a fresh period against a settled one.

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.

Generalising beyond the population the sample actually supports

State the frame the sample was drawn from and where it diverges from the population you want to talk about: time window, platform, geography, opt-in. If a group is excluded from the frame, either weight to known margins or narrow the claim rather than quietly extending it.

Questions & practice

If your dataset has 100 features but only 1,000 rows, how would you approach feature selection to avoid overfitting?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Pick an evaluation metric that matches the cost of each error type, not a default.
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?
Where could label leakage enter this setup?

Walk me through the end-to-end process of building a churn prediction model.

Medium · machine learning and modelling

Approach

Check what information would not exist at prediction time, and exclude it.
Say how the offline result would be validated online before it is trusted.
Set a baseline first, so any model has something honest to beat.

Follow-up

Where could label leakage enter this setup?
What would you monitor after launch to know the model is still valid?

Sessionise an API event stream with a 30-minute inactivity gap

Medium · sessionisation · event-streams · pandas

fct_api_request arrives as a DataFrame with account_id, user_id, request_at (tz-aware UTC), traffic_class and http_status, roughly 5 million rows. Assign a session_id to every human-attributable request: drop rows where user_id is null or traffic_class is in ('ci','synthetic_monitor','load_test'), then open a new session whenever the gap since that user's previous remaining request exceeds 30 minutes. Return the filtered frame plus session_id, and a per-session summary with user_id, account_id, session start, session end and request count. Do not loop over rows.

Approach

Settle the filter-then-gap ordering before writing code. Removing CI and synthetic rows changes the gaps, so sessionising the raw stream and filtering afterwards is a different answer; the definition given filters first, and the two diverge most for accounts whose CI runs every ten minutes.
Sort once by (user_id, request_at) with a stable kind, then gap = df.groupby('user_id', sort=False).request_at.diff(). The first row of each user yields NaT, which is exactly the boundary condition you want rather than a special case to patch.
new_session = gap.isna() | (gap > Timedelta(minutes=30)); session_id = new_session.cumsum(). The cumsum runs over the whole sorted frame and therefore produces globally unique ids in one pass; a per-user cumcount collides across users and forces a composite key on every downstream join.
Build the summary with a single groupby('session_id').agg(...). user_id and account_id can be carried with 'first' only because the sort key groups them — state that dependency, since it silently breaks if someone later re-sorts the frame.
Decide explicitly what a session means when one user_id holds memberships in several accounts: either add account_id to the sort and group keys, or document that sessions may cross accounts. Leaving it undecided produces sessions whose account_id is whichever row sorted first.

Worked solution (30 min)

human = df[df.user_id.notna() & ~df.traffic_class.isin(['ci','synthetic_monitor','load_test'])].sort_values(['user_id','request_at'], kind='mergesort').reset_index(drop=True)
gap = human.groupby('user_id', sort=False).request_at.diff(); human['session_id'] = (gap.isna() | (gap > pd.Timedelta(minutes=30))).cumsum()
summary = human.groupby('session_id').agg(user_id=('user_id','first'), account_id=('account_id','first'), start=('request_at','min'), end=('request_at','max'), n_requests=('request_at','size')).reset_index()
assert summary.n_requests.sum() == len(human) and human.groupby('session_id').user_id.nunique().max() == 1

Expected result: human gains a dense session_id increasing in (user_id, request_at); summary has exactly one row per session, n_requests sums to len(human), and every within-session consecutive gap is at most 30 minutes while every same-user between-session gap exceeds it.

Verify it yourself

summary.n_requests.sum() == len(human); no request is lost or double-counted.
human.groupby('session_id').user_id.nunique().max() == 1.
human.groupby('session_id').request_at.diff().max() <= pd.Timedelta(minutes=30).
Shifting every timestamp by a constant offset leaves the session count unchanged.

Follow-up

Where does 30 minutes come from, and how would you pick it from this data instead of from convention?
An engineer reused their personal key for a nightly batch job, so machine traffic carries a human user_id. How would you detect that, and should those requests form sessions?
How much does the session count change if you sessionise before dropping CI traffic rather than after?

How would you write a query to identify the top 3 selling products in each category, ranked by revenue?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Write a SQL query to calculate the month-over-month retention rate for a cohort of users.

Medium · sql and data manipulation

Approach

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.
Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

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

How do you handle duplicate records and null values when joining two large tables in SQL?

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Check whether any join is one-to-many before aggregating, or the sums inflate.
Say which table is the grain you start from, and join outward from it.

Follow-up

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

Net revenue retention on a cohort frozen twelve months back

Hard · retention · as-of join · cohort arithmetic

fct_subscription_period carries subscription_period_id, account_id, arr_cents, plan_code, term_start_date, term_end_date, booked_at, amendment_type, superseded_by_id (the subscription_period_id of the version that replaced this one, null on the live version of a lineage) and is_current. Compute net revenue retention for month M: the summed arr_cents at M for the set of accounts holding arr_cents > 0 at M-12, divided by that same set's arr_cents at M-12. An account can hold more than one live subscription, a churned account contributes zero rather than dropping out, and nothing signed after M-12 may enter either side. Return the ratio plus the expansion, contraction and churn components in cents.

Approach

Write one reusable as-of ARR snapshot parameterised by a date: rows whose term brackets the date AND whose booked_at is at or before the date, then only the version of each lineage that is still live at that date, then sum arr_cents per account. The booked_at guard matters because an amendment signed in advance otherwise co-exists with the term it replaces and double counts the account.
Collapse the lineage on superseded_by_id, not on any attribute of the contract. Keep a row when superseded_by_id IS NULL, or when the successor it points at was booked after the date. Ranking with ROW_NUMBER() OVER (PARTITION BY account_id, plan_code ...) instead is wrong in both directions: an amendment that moves the account from one plan_code to another puts the old and new versions in different partitions, so both are rank 1, both bracket the date, and the account's arr_cents is counted twice; and two genuinely concurrent subscriptions that happen to share a plan_code land in one partition, so one of them is deleted.
Resolve the successor with a LEFT JOIN back to fct_subscription_period on subscription_period_id, and treat a missing successor as not superseded. An inner join would silently delete an account's ARR on a dangling pointer, which is a data-quality bug in the source, not a retention movement.
Never use is_current for the M-12 side. is_current describes today; using it at the historical snapshot backdates the present contract onto last year's cohort and makes retention look like 100 percent by construction.
Freeze the cohort from the M-12 snapshot where arr_cents > 0, then LEFT JOIN the M snapshot onto it and COALESCE the missing side to zero. An inner join deletes exactly the churned accounts, which is the single largest way this number gets overstated.
Return a ratio of sums, not a mean of per-account ratios. The two are different estimands: contraction is floored at zero while expansion is unbounded, so the mean of ratios is both biased relative to the aggregate and far noisier on a skewed revenue base.
Decompose per account on the delta: positive delta is expansion, negative delta with a non-zero M value is contraction, a zero M value is churn. The three components must reconcile to numerator minus denominator.
Prove no leakage: any account whose first contract began after M-12 must be absent from both sides, and the cohort row count must be identical in the numerator and denominator.

Worked solution (45 min)

Write arr_asof(d) as a CTE or lateral: from fct_subscription_period s take rows with term_start_date <= d AND term_end_date >= d AND booked_at <= d, LEFT JOIN fct_subscription_period succ ON succ.subscription_period_id = s.superseded_by_id, keep the row when s.superseded_by_id IS NULL OR succ.subscription_period_id IS NULL OR succ.booked_at > d, then sum arr_cents per account_id across every surviving version.
Sanity-check the lineage rule on one amended account before going further: at a date after the amendment, the account must contribute exactly one version per lineage even when the amendment changed plan_code, term dates or both.
Materialise base = arr_asof(M-12) filtered to arr_cents > 0, and curr = arr_asof(M).
LEFT JOIN curr onto base on account_id and COALESCE(curr.arr_cents, 0) AS arr_now.
Compute nrr = sum(arr_now)::numeric / NULLIF(sum(base.arr_cents), 0), and the three components with SUM(...) FILTER on the sign of arr_now - base.arr_cents and on arr_now = 0.
Reconcile: assert sum(arr_now) - sum(base.arr_cents) = expansion - contraction - churn, and assert the cohort account count is identical on both sides.

Expected result: A single row with nrr as a ratio, plus expansion_cents, contraction_cents and churn_cents as positive magnitudes satisfying numerator minus denominator equals expansion minus contraction minus churn, over a cohort whose membership is fixed at M-12.

Verify it yourself

At each snapshot date, the surviving row count per account equals the number of distinct live subscription lineages on that account, not the number of contract versions bracketing the date.
For an account whose amendment changed plan_code, the snapshot returns one version and its snapshot ARR equals the amended arr_cents; swapping in a ROW_NUMBER partition on (account_id, plan_code) returns two versions for that account and inflates the side it sits on, which is the fastest way to see the bug.
The three components reconcile to the numerator-denominator delta to the cent.
The distinct account count is the same in the numerator and the denominator.
No account with a first term_start_date after M-12 appears anywhere in the output.
Re-running after new accounts sign leaves the ratio unchanged, which is the defining property of a frozen cohort.

Follow-up

Net revenue retention can rise while the business shrinks. Show one mechanism and name the guardrail that catches it.
How do you handle an account that co-terms two subscriptions into one mid-window, so the subscription count changes but the money does not?
Finance computes this from invoiced amounts and gets a different number. Which is right for which question?

Describe a complex Power BI dashboard you created. What were the key metrics, and how did you design the layout?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
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 choose between a bar chart, a line graph, and a scatter plot when presenting financial data to an executive?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.

Follow-up

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

Stop metering retried requests: design the metric that decides it

Medium · guardrail conflict · billing integrity · retries · expected value

The platform meters accepted requests. A proposal carries three clauses: stop metering fct_api_request rows where is_retry = true, stop metering rows with a 4xx status, and stop metering rows with http_status >= 500. Its author has attached one figure to all three together, roughly 4% of requests_thousands volume. You have fct_api_request (account_id, is_retry, idempotency_key, http_status, traffic_class, billable_units, request_at) and fct_usage_daily (billable_quantity, net_amount_cents, cogs_cents). Size each clause separately before arguing about any of them, then define the primary metric, the guardrail that genuinely conflicts with it, and how you resolve that conflict for a decision that has to be made this quarter. Revenue falls this quarter with certainty; any benefit appears at renewals up to twelve months out.

Approach

Size the three clauses before accepting the headline 4%, because one of them is a no-op. billable_units is defined as zero for requests that failed with a 5xx, so the third clause removes no metered volume at all. Confirm that in the data rather than trusting the column comment: if sum(billable_units) over rows with http_status >= 500 is not zero, the metering pipeline contradicts its own definition and that is a billing defect to file before any pricing conversation happens. The two live clauses are 4xx failures, which are metered in full, and retries that did not themselves end in a 5xx.
Size the two live clauses as a union, not a sum. A retry can return 4xx, so the clauses overlap and adding their volumes counts that intersection twice. Partition the trailing 90 days into four mutually exclusive buckets instead: clean (is_retry = false, http_status < 400), non-retry 4xx, retry with http_status < 500, and http_status >= 500. Report the removable share per account as a distribution; if the mass sits in a handful of accounts this is a commercial conversation with those accounts rather than a platform-wide pricing change.
State the conflict rather than dissolving it. The primary metric, net metered revenue per paying account, and the integrity guardrail, the share of metered volume that is retried or failed traffic, move in opposite directions by construction. No redefinition removes that. The job is to price the trade-off, not to make it disappear.
Show the perverse coupling with data, and be exact about its mechanism. Because a 5xx already carries zero billable_units, the platform is not paid directly for its own failures; it is paid for the retries and the client-side 4xx traffic those failures provoke, which is one step removed and therefore easy to miss. Cross-tab each account's trailing 28-day 5xx rate against its metered volume in the same window. If metered volume rises with error rate, that indirect coupling is the actual argument for the change.
Resolve on expected value with the uncertainty stated. The revenue loss is computable and near-certain; the renewal benefit is not, so invert it and state the break-even: how many basis points of gross logo retention on the renewal-eligible base would offset the loss. That converts an argument about values into an argument about one number. Then propose the measurement that would settle it instead of claiming a readout you do not have: stage the rollout by renewal cohort so accounts whose terms end soonest are treated first, read out on gross logo retention on the renewal-eligible base, and say honestly whether the number of annual renewals in the window can support that estimate at all.

Worked solution (30 min)

Test the third clause first: over the trailing 90 days compute count(*) and sum(billable_units) from fct_api_request where http_status >= 500. The sum must be zero, because billable_units is defined as zero for 5xx failures. If it is zero the clause removes nothing and drops out of the analysis; if it is not, stop and raise a metering defect, because every volume figure downstream of that column is then suspect.
Compute trailing-90-day metered volume per account in the four mutually exclusive buckets: clean, non-retry 4xx, retry with http_status < 500, and http_status >= 500. Roll the total up to the requests_thousands SKU and reconcile it against fct_usage_daily billable_quantity for the same window.
Convert the two removable buckets to money using each account's realised rate, net_amount_cents / billable_quantity from fct_usage_daily, because list rate overstates revenue for every discounted account.
Annualise the revenue at risk and divide it by the ARR of the renewal-eligible base to express the break-even as an improvement in gross logo retention, in basis points.
Cross-tab account 28-day 5xx rate deciles against metered volume per account to establish whether the error-to-revenue coupling, which can only run through retries and 4xx rather than through the failed requests themselves, is real or a story.

Expected result: Three clause sizes rather than one blended figure: zero for the 5xx clause, since billable_units is already zero there, and separate volumes for 4xx failures and for retries that did not 5xx, combined as a union so the retry-and-4xx intersection is counted once. On top of that, a single break-even figure stated as the improvement in gross logo retention on the renewal-eligible base needed to offset the metered revenue actually removed, and the per-account concentration of that removable volume. The answer says that the primary metric and the integrity guardrail conflict by construction, and resolves it with the break-even number and a staged rollout rather than by moving a definition.

Verify it yourself

sum(billable_units) over rows with http_status >= 500 must be exactly zero; any other result contradicts the documented definition of the column and invalidates the volume figures derived from it.
The four buckets must be mutually exclusive and must sum to total billable_units, which must in turn reconcile to fct_usage_daily billable_quantity within the known restatement tolerance; a large gap means the request-to-SKU mapping is wrong.
The two removable buckets must be reported as a union; their arithmetic sum will exceed the union by exactly the retry-and-4xx intersection, and a write-up that quotes the sum has overstated the revenue at risk.
Requests sent with no idempotency_key cannot carry is_retry = true, so the measured retry share is a lower bound and the write-up must say so.

Follow-up

Suppose the two live clauses turn out to remove 2.6% of consumption revenue. How much improvement in gross logo retention on an annual-contract base pays that back, and over what horizon does the payback land?
A retry sent without an idempotency_key cannot be flagged as a retry. Which direction does that bias your estimate of the removable volume, and how can you bound it?

Net revenue retention jumps sixteen points in one month

Medium · versioned tables · fan-out · metric definition

Trailing-twelve-month net revenue retention printed around 108 percent for months and now reads 124 percent, with no unusual deals closed. The query sums arr_cents from fct_subscription_period (account_id, arr_cents, term_start_date, term_end_date, amendment_type, superseded_by_id, is_current, booked_at) filtered on is_current = true at month M, across accounts holding arr_cents > 0 at month M-12. Find the defect, correct the number, and rewrite the definition so the next person cannot reintroduce it.

Approach

Audit the grain before the arithmetic: count account_ids holding more than one row with is_current = true and superseded_by_id null. A versioned contract table that double counts one amendment batch inflates the numerator while leaving the denominator untouched.
Replace is_current with an as-of selection on both dates, taking the version whose term_start_date and term_end_date bracket the reporting date and tie-breaking on latest booked_at. The numerator is read as of M and the denominator as of M-12; neither uses today's live version.
Verify the cohort is frozen. The account set is fixed at M-12 and nothing acquired since may enter the numerator, so check that no join to a current-period table quietly re-admits new accounts.
Confirm the estimand is a ratio of sums rather than a mean of per-account ratios. Contraction is floored at zero while expansion is unbounded, so the two constructions differ systematically and the second is far noisier.
Reissue the definition with the failure modes written into it: exactly one row per account per date by construction, cohort frozen at M-12, churned accounts contributing zero rather than dropping out of the numerator.

Follow-up

A churned account should contribute zero rather than disappear. What does the ratio do under each treatment, and which one is correct?
How would you unit-test this metric so a future amendment batch with the same defect fails a check instead of reaching a board slide?

Suggested practice checklist

Four days spend equal time on query work, statistics, modelling and product judgement at deliberately shallow depth, which produces a scored map of where you actually stand. The last three days spend everything on the two areas the role weights most, and close by re-running day one to measure movement.

Day 1 — Breadth pass: query fluency

Solve six prompts spanning aggregation, joins, window functions and date arithmetic in 60 minutes total, stopping at 10 minutes each whether or not it works, and mark every prompt as solved, solved slowly, or stuck.
For each unsolved prompt write the single blocking sentence (I lost the grain, I did not know the frame clause, I could not express the date boundary) instead of reading the solution.
Translate one pandas transformation you know well into SQL and one SQL query into pandas, checking that both return the same row count and the same totals.

Deliverable: A scored six-row table, one line per prompt, saved for the day-seven re-run.

Day 2 — Breadth pass: statistics and inference

Answer ten short questions in writing with nothing open: what a p-value is conditional on, what a 95 percent interval covers across repeated samples, when a paired test is the right one, what the bootstrap estimates, why multiple comparisons inflate false positives, how controlling the family-wise error rate differs from controlling the false discovery rate, what power depends on, what a missed real effect costs a product, the three situations where the central limit theorem does not rescue you (small n, very heavy tails, dependent observations), and what a standard error is the standard deviation of.
Grade yourself against a reference and count only the answers that were exactly right, not the ones that were nearly right.
Rewrite the two weakest answers the following morning from memory in full sentences.

Deliverable: Ten graded answers with an honest count of exact hits.

Day 3 — Breadth pass: modelling

Take one tabular dataset end to end in 90 minutes: a leakage-safe split, a baseline that is not a model (majority class or historical mean), one regularized linear model, one gradient-boosted tree, and a single evaluation metric chosen before you look at any result.
Write why that metric fits the cost structure: precision at a fixed recall for alerting, calibration for anything feeding a price or a threshold, ranking metrics for retrieval, and note that area under the ROC curve is insensitive to class balance in a way that can flatter a rare-positive problem.
Name the leak you were most likely to introduce (an encoding fit on all rows before splitting, or a feature computed after the label's timestamp) and write the check that would have caught it.

Deliverable: A notebook whose first cell states the metric and the baseline, plus two lines on what beat what and by how much.

Day 4 — Breadth pass: product judgement

Answer three case prompts aloud at 15 minutes each, timing how long passes before you state a success metric.
For one case write the first segmentation you would run and the row counts you expect per segment, so that a tiny segment cannot quietly drive the conclusion.
Take a metric definition you did not write, from a public dashboard, a textbook, or documentation you already have open, and list every place two analysts implementing it would diverge: which rows the denominator admits, whether the unit is an account or a person, what the time window is anchored to, and what happens to data that arrives late. Then write the one question that would close the largest of those gaps.

Deliverable: Three recorded case answers plus an ambiguity list for a metric someone else defined, ending in the single question you would ask about it.

Day 5 — Depth, first area

Rank the four areas by how many bullet points in the role description each one covers, pick the top one, and spend the entire day inside it.
Work the six hardest problems you can find in that area and for each write the generalizable move you should have reached for first, rather than the answer.
Re-solve the two you failed the same evening with notes closed.

Deliverable: Six generalizable moves written as instructions to yourself, not as solutions.

Day 6 — Depth, second area, and the seam between them

Repeat the depth protocol on the second-ranked area with the same six-problem structure.
Construct one problem that requires both areas at once, for example a metric redefinition whose effect you must validate with a test whose readout you then have to query.
Solve your own combined problem end to end and note where the handoff between the two areas cost you time.

Deliverable: One combined problem, solved end to end, with the handoff failure written down.

Day 7 — Integration and re-measurement

Re-run the six prompts from day one under the same clock and compare both correctness and time.
Run a 60-minute mixed mock that moves between areas without warning, since switching cost is what breadth passes do not train.
Write the two areas you would still fail on, and the sentence you will use in the interview when you hit one of them.

Deliverable: A before-and-after score table plus a written plan for the two remaining gaps.

Explaining a method to someone who will not follow the maths

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

If a stakeholder asks for a metric that you believe is misleading, how do you handle the conversation and what alternatives do you propose?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
State the situation in two sentences and spend the rest on your reasoning.
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?
How did you know the outcome was caused by your change?

Describe a situation where you had to learn a completely new industry or domain very quickly to deliver a project.

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
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 would you do differently if you ran that project again?

Defend a churn number twelve times the one in the board deck

Hard · stakeholder management · retention metrics · definitions

You recompute logo churn on the renewal-eligible base from fct_subscription_period, counting only accounts whose term_end_date fell in the month and allowing a 45-day grace for late paperwork. Annualised, about 14 percent of accounts that reach a renewal date do not renew. A revenue leader has been quoting 1.2 percent to the board for two quarters, computed by dividing non-renewed accounts by the entire customer base in each month and printing that monthly figure with no period attached. You have 20 minutes with that leader and the finance lead. Decide which number is reported from now on, and what happens to the two quarters already published.

Approach

The interviewer is probing whether you can hold a correct definition under social pressure without turning it into a competence dispute. Open by reproducing their 1.2 percent exactly, with their denominator and their months, so the disagreement is arithmetic both sides can see rather than a claim about who was careless.
Separate the two defects, because they are different in kind. The denominator is wrong: on annual contracts only about one twelfth of the base reaches a renewal date in any month, so an account eleven months from renewal sits in the denominator while being structurally incapable of entering the numerator, which suppresses the rate by a factor near twelve. The period is merely unstated: a monthly figure printed beside annual revenue targets gets read as an annual rate.
Say out loud that those two defects nearly cancel in the level, before the leader finds it. Twelve times 1.2 percent is about 14 percent, which is your number. That is the strongest thing you can say in the room, because it proves both figures rest on the same non-renewal count and moves the meeting onto which denominator and which period get published rather than onto whose query is right.
The level is recoverable; the series is not. Non-renewals in a month are the eligible base for that month times the churn rate, so dividing by a fixed whole base makes the published line proportional to how many contracts happen to come up that month. Where signings cluster at quarter ends, the eligible base in a quarter-end month can be several times a quiet month's, and the month-over-month moves the board has been reading as satisfaction are the signing calendar.
Separate the measurement change from a business change. Nothing got worse this week; the loss rate was always this. Bring net revenue retention over the same period as a ratio of sums on a cohort frozen twelve months earlier, because logo churn concentrated in small accounts can sit beside healthy revenue retention, and that combination is the actual story.
Offer a migration path rather than a correction. Report both rates for one quarter with a written bridge, restate the prior two quarters in an appendix instead of silently, and pin the definition, including the period it is stated over, somewhere finance and product both read it. Concede the limits of your own number: the 45-day grace means the most recent 45 days are not reportable, and churn must be dated on term_end_date rather than on updated_at. A strong answer volunteers this; a generic one only defends.

Follow-up

The leader multiplies their monthly figure by twelve, lands on your annual number, and concludes nothing was ever wrong. What do you say?
The leader says publishing the corrected rate costs the team its credibility with the board this quarter. What do you do?
Gross logo retention worsened while net revenue retention improved. Which do you lead with, and what does the combination tell you about who is leaving?