CDC Foundation Data Scientist Interview Questions & Guide 2026

The 60-second brief

The Data Scientist role at CDC Foundation is pivotal in harnessing data analytics to inform public health decisions and strategies. By employing statistical methodologies and machine learning techniques, you will analyze complex datasets to uncover insights that directly impact health initiatives and policies. This role is not only about crunching numbers; it’s about transforming raw data into actionable knowledge that can lead to improved health outcomes across communities.

Coding rounds for this role are usually data-manipulation shaped rather than data-structure shaped: group-bys, joins, time windows, ranking within a partition. Confirm the format before spending a week on graph traversal.

Focus areas

• Measure churn only on renewal-eligible accounts
• Power experiments for heavy-tailed account revenue
• Separate contracted seats from actively used seats

Overview

As a Data Scientist, you will work closely with interdisciplinary teams, including epidemiologists, public health officials, and program managers. Your contributions will be instrumental in projects aimed at addressing critical public health challenges, such as disease prevention and health promotion. The complexity and scale of the data you will handle enhance the strategic influence of your work, making it both impactful and rewarding.

Expect to engage in meaningful projects that resonate with the mission of the CDC Foundation, driving improvements in health equity and access to care. The role is not just a job; it's an opportunity to make a significant difference in people's lives through data-driven insights.

The CDC Foundation interview process

CDC Foundation candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Phone Screen

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

What to demonstrate

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

How to prepare

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

Team Interviews

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.

Where candidates lose points

Computing monthly churn against the entire customer base when contracts are annual

An annual contract has no opportunity to churn except at its renewal date, so an account that is eleven months from renewal is in the denominator while being incapable of appearing in the numerator. The resulting rate is smaller than the real one by roughly the ratio of the base to the renewal-eligible base, and it oscillates with the seasonality of when deals were originally signed rather than with anything about the customers. The corresponding trap on the other side is counting a churn on the date the record was updated rather than on term_end_date, which shifts losses into whichever month the operations team did its paperwork.

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.

Defining the cohort on a post-treatment condition

Ask how rows entered the table. Filtering on something that treatment itself influences, such as users who finished onboarding or accounts still active at ninety days, breaks comparability between arms; define the population at an entry point that precedes exposure and keep everyone in it.

Answering a product-sense question with a list of features

Answer with a decision and the measurement that would settle it: the hypothesis, the primary metric, the guardrails, and the result that would make you not ship. A feature brainstorm cannot be wrong, which is exactly why it earns no points.

Questions & practice

Can you describe a project where you implemented a machine learning algorithm? What was the outcome?

Medium · machine learning and modelling

Approach

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

Follow-up

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

Quantify billable volume created by retries after server errors

Hard · telemetry-hygiene · pandas · unit-economics

From fct_api_request (request_id, account_id, endpoint, idempotency_key, is_retry, http_status, request_at, billable_units), measure how much billable volume in a 28-day window is retry traffic that followed a 5xx. Group requests into attempt chains by (account_id, endpoint, idempotency_key) ordered by request_at; a request is error-driven if any earlier attempt in its chain returned 5xx. Requests with a null idempotency_key cannot be chained, so report them as their own class rather than assuming each is unique. Return billable_units split into first-attempt, error-driven retry, other retry and unchainable, per account.

Approach

1. Split the population before measuring anything. A null idempotency_key is not a chain of one, it is an unknown; report its share of billable_units first, because if it is 40% of volume then the headline estimate is a lower bound and the deliverable has to say so.
2. Within chainable rows, sort by (account_id, endpoint, idempotency_key, request_at) and derive 'any earlier attempt failed' with arithmetic rather than a per-group lambda: with is5 = (http_status >= 500), the per-chain cumsum minus the row's own value is positive exactly when an earlier attempt in that chain returned 5xx. A groupby-apply gives the same answer and is unusable at five million rows.
3. Do not take is_retry as the definition. It is set by the client whenever an idempotency_key is resent, which covers retries after client-side timeouts and after 4xx as well; compute the flag yourself and then cross-tabulate it against is_retry, because the disagreement is a finding in its own right.
4. Aggregate billable_units by (account_id, class) and assert the classes sum to each account's total. The spine sets billable_units to zero on 5xx responses, so the failed attempt contributes nothing and the whole inflation sits in the successful retry that follows it.
5. Report the per-account share and look at its distribution, not the fleet total. One account in a retry storm dominates any blended figure, which is the same failure that makes a fleet-wide error rate useless.

Worked solution (40 min)

1. unchainable = df.idempotency_key.isna(); report df.loc[unchainable].groupby('account_id').billable_units.sum() before proceeding.
2. keys = ['account_id','endpoint','idempotency_key']; c = df[~unchainable].sort_values(keys + ['request_at'], kind='mergesort'); c['is5'] = (c.http_status >= 500).astype(int)
3. c['attempt_no'] = c.groupby(keys, sort=False).cumcount(); c['prior_5xx'] = (c.groupby(keys, sort=False).is5.cumsum() - c.is5) > 0
4. c['cls'] = np.where(c.attempt_no == 0, 'first_attempt', np.where(c.prior_5xx, 'error_driven_retry', 'other_retry')); out = pd.concat([c, df[unchainable].assign(cls='unchainable')]).groupby(['account_id','cls']).billable_units.sum().unstack(fill_value=0)

Expected result: A per-account table of four columns summing exactly to that account's total billable_units in the window. error_driven_retry is zero for any account with no 5xx; the fleet-level error-driven share is typically low single digits while a small number of accounts sit far above it.

Verify it yourself

• out.sum(axis=1) equals df.groupby('account_id').billable_units.sum() for every account.
• Every row classed error_driven_retry has attempt_no >= 1.
• An account with no http_status >= 500 in the window has error_driven_retry == 0.
• Cross-tabulating cls against is_retry: no first_attempt row should carry is_retry True, and any that do mark chains truncated by the window boundary.

Follow-up

• An account's error-driven share is 22%. Is that the platform's fault or the client's, and what do you look at next?
• How would you define a consumption-based north-star metric that an outage cannot inflate?
• Chains straddle the 28-day boundary. How large is that bias and in which direction?

Audit daily usage rows for grain and arithmetic violations

Easy · data-quality · pandas · grain

You are handed fct_usage_daily as a pandas DataFrame with account_id, workspace_id, sku_code, usage_date, billable_quantity, included_quantity_applied, overage_quantity, list_amount_cents, discount_amount_cents, net_amount_cents, cogs_cents, is_restated, first_written_at and restated_at. The declared grain is one row per (account_id, workspace_id, sku_code, usage_date). Write audit(df) returning a DataFrame with one row per failing check: check name, failing row count, and one example key. Cover at minimum grain duplication, negative quantities or amounts, the identity net = list - discount, billable = included + overage, and rows where is_restated is true but restated_at is null.

Approach

1. Check the grain before anything else with df.duplicated(subset=key, keep=False), and count rows rather than groups so a key appearing twice contributes 2 — if the grain is broken every arithmetic count below it is uninterpretable.
2. Express each invariant as a boolean Series over the whole frame. The cent columns are integers and compare exactly, so use !=; the numeric(18,6) quantity columns need np.isclose with atol=1e-6 because included + overage is a decimal sum.
3. Handle null as its own failure mode. Comparisons against NaN return False, so a check written as rows_that_pass = (a == b - c) silently files every null-amount row wherever the negation happens to land; build each check as violations = ~condition | column.isna().
4. Collect the checks as a list of (name, mask) pairs and assemble the output in one pass, so adding a check is one line and every check reports in the same shape.
5. Order the output with structural failures (grain, null keys) above arithmetic failures, and report zero-count checks too — a check that silently disappears when it passes is indistinguishable from a check that was never run.

Follow-up

• Which of these should block a dashboard refresh and which should only warn?
• Rows with is_restated = true legitimately change value after first write. How do you make yesterday's audit result reproducible?
• How would you extend this to catch a partition that is missing entirely rather than wrong?

Explain the difference between supervised and unsupervised learning.

Medium · sql and data manipulation

Approach

1. Compute rates by summing numerator and denominator separately, never by averaging rates.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. 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?

What programming languages are you proficient in, and how have you applied them in your previous roles?

Medium · sql and data manipulation

Approach

1. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
2. Check whether any join is one-to-many before aggregating, or the sums inflate.
3. State the window function and its partition and ordering out loud before writing it.

Follow-up

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

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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)

1. 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.
2. 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.
3. Materialise base = arr_asof(M-12) filtered to arr_cents > 0, and curr = arr_asof(M).
4. LEFT JOIN curr onto base on account_id and COALESCE(curr.arr_cents, 0) AS arr_now.
5. 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.
6. 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 your methodology for conducting a gap analysis in a health program.

Medium · product sense and metrics

Approach

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

Follow-up

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

What steps would you take to design a study to evaluate a new health intervention?

Medium · product sense and metrics

Approach

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

Follow-up

• Which segment would you cut first, and what would that rule out?
• How would you detect that the metric is being gamed rather than genuinely improving?

How do you prioritize tasks when faced with multiple deadlines?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. State what result would change your recommendation, so the answer is falsifiable.
3. Fix the population and the time window before naming any metric.

Follow-up

• Which segment would you cut first, and what would that rule out?
• How would you detect that the metric is being gamed rather than genuinely improving?

How would you optimize a data processing workflow for efficiency?

Medium · product sense and metrics

Approach

1. Decompose the metric into the rates that drive it, and say which one you would check first.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

• Which segment would you cut first, and what would that rule out?
• How would you detect that the metric is being gamed rather than genuinely improving?

How do you ensure the integrity and validity of your data?

Medium

Approach

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

Follow-up

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

Cut variance with pre-period usage before the test starts

Medium · cuped · variance-reduction · stratification

You are planning an account-randomised test on 2,800 paying accounts. The outcome is a 28-day sum of billable_quantity for sku_code = 'compute_hours' from fct_usage_daily, and the same account's 28-day pre-period sum correlates 0.80 with it. 420 accounts were created inside the pre-period and have partial or no history. Specify the variance-reduction plan: the adjusted estimator and where its coefficient comes from, how assignment is stratified, how the 420 incomplete accounts are handled, and what must change in the pre-period window given that fct_usage_daily rows are restated after first write.

Approach

1. Write the estimator explicitly: Y_adj = Y - theta * (X - mean(X)), with theta = Cov(X, Y) / Var(X). Estimate theta from pre-experiment history or pooled across arms, never separately by arm and never from post-treatment outcomes. Fitting theta on treatment-arm outcomes folds the effect being measured into the adjustment and biases the result toward whatever the treatment did.
2. Quantify the gain and convert it into the currency the team cares about. The residual variance multiplier is 1 - 0.80^2 = 0.36, so the standard error falls to 0.60 of its unadjusted value and the MDE falls with it. That is the same precision as running with 1 / 0.36, about 2.8 times as many accounts, which matters because the account population is fixed and cannot be bought with a longer run.
3. Stratify assignment on pre-period usage decile crossed with the three paid plan tiers, giving 30 cells at roughly 93 accounts each, and collapse any cell below about 20. Use the same strata in the analysis through strata fixed effects or post-stratification: stratified assignment analysed pooled discards much of the gain, and stratified analysis without stratified assignment risks empty cells in the top decile, which is precisely where the revenue sits.
4. Handle the 420 incomplete accounts by imputing X at the stratum mean and adding a binary indicator for missing pre-period, rather than dropping them. Dropping silently redefines the population to established accounts, which is usually the opposite of the segment a new feature targets, and it makes the result non-generalisable in a way the readout will not disclose.
5. Fix the window against restatement. Measure the empirical settling time by comparing a usage_date's total at first_written_at against its value after restated_at has stopped moving, then end the pre-period that many days before assignment. A pre-period whose last days are still settling carries recency-correlated measurement error in the covariate, which both weakens rho and can correlate with assignment date.
6. Pre-register the whole plan before assignment: the estimation set for theta, the strata definition and collapsing rule, the imputation rule, the winsorisation cap and the trailing exclusion. Every one of these can be tuned after the fact to move a p-value, which is why they are worth nothing if decided afterwards.

Worked solution (30 min)

1. Estimate rho on a historical pair of adjacent 28-day windows, applying exactly the traffic-class and is_internal filters the live experiment will use.
2. Compute the variance multiplier 1 - 0.80^2 = 0.36 and translate it to a standard-error multiplier of 0.60 and an effective-sample multiplier of about 2.8.
3. Build 30 strata from ten pre-period usage deciles crossed with three paid plan tiers, inspect the minimum cell count and collapse cells below about 20 accounts.
4. Measure the metering settling time from first_written_at against restated_at and shift the pre-period window back by that many days.
5. Write the pre-registration: theta source, strata, imputation for the 420 incomplete accounts, winsorisation cap, trailing exclusion.

Expected result: Standard-error multiplier 0.60 and effective-sample multiplier about 2.8 at rho = 0.80, with 30 strata collapsed where thin, the 420 incomplete accounts retained through stratum-mean imputation plus a missingness indicator, and the pre-period ending one measured settling window before assignment.

Verify it yourself

• Adjusted and unadjusted point estimates must be close under valid randomisation, because this reduces variance rather than moving the estimate. A large shift means theta was fitted on post-treatment data or that randomisation itself failed.
• The multiplier 1 - rho^2 is monotone in the absolute correlation and equals 1 at rho = 0, so at rho = 0.30 the standard error can only fall by about 5%. Any larger claimed reduction at that correlation is arithmetically wrong.
• Computing the pre-period sum twice, a week apart, should return the same number once the settling window is excluded. If it does not, the exclusion window is too short.

Follow-up

• Once continuous-integration and synthetic traffic are excluded, rho turns out to be 0.45 rather than 0.80. What is the revised variance reduction, and is the added complexity still worth it?
• How would you extend this to more than one covariate, and what stops you from adding twenty?
• Does this adjustment repair an imbalance you discover after assignment, or only reduce variance? Be precise about the difference.

Activation drops six points starting at a deploy hour

Medium · instrumentation · release analysis · metric inputs

Seven-day activation, defined as an account's first request with http_status < 400, api_key_id not null and traffic_class <> 'synthetic_monitor' within 168 hours of created_at, fell six points for sign-up cohorts after a Tuesday. A client SDK major version shipped that morning. From fct_api_request (account_id, api_key_id, sdk_name, sdk_version, http_status, traffic_class, request_at) and dim_account (account_id, created_at, is_internal), decide whether activation actually fell or the metric's inputs changed, and state in advance what evidence would convince you of each.

Approach

1. Decompose the definition and recompute activation under each relaxation: status only, status plus traffic_class, then the full definition. If the entire drop lives in the api_key_id clause, this is an instrumentation question rather than a behavioural one.
2. Measure the null rate of api_key_id by sdk_version and by hour. A stamping change appears as a step at the deploy boundary confined to the new version; a behavioural change appears as a ramp that grows with adoption and leaves old-version traffic untouched.
3. Hold the cohort's SDK mix fixed before comparing. New sign-ups adopt the newest version first, so a cohort-level drop can be pure composition even when no individual version changed at all.
4. Corroborate with a source the release did not touch: whether the same cohorts appear in fct_usage_daily with non-zero billable_quantity, and whether their fct_support_ticket rows with category = 'onboarding' rose.
5. Write the decision rule down before looking at the answer. An instrumentation artefact predicts unchanged downstream usage and a version-confined null step; a real regression predicts falling downstream usage and more onboarding tickets in the same cohorts.

Follow-up

• Old-version and new-version populations are not exchangeable, because new accounts adopt the new version first. How would you build a comparison that is not confounded by cohort age?
• What backfill or metric-versioning policy keeps the historical series interpretable once you fix the stamping?

Suggested practice checklist

Roughly 90 minutes a night on weekdays with one longer weekend block. The plan deliberately cuts scope rather than compressing everything, on the assumption that finishing one thing a night beats half-starting four.

Day 1 — Fix the scope and set a baseline

• Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list for everything else and keep it visible all week.
• Take one 20-minute SQL prompt and one 10-minute metric question cold, and write the single sentence that says what blocked each attempt, since that sentence is what decides which two topics get the most evenings.
• Set the week's one rule: one problem finished to completion every night, including the night you only have 40 minutes.

Deliverable: A one-page scope with an explicit not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One query pattern, written three times

• Choose the single pattern most likely to appear (a cohort retention grid, or a funnel counted by user) and write it three times from a blank file rather than editing the previous attempt.
• On the third attempt, write the grain of every CTE as a comment before writing its body.
• Stop at 90 minutes even if the third version is imperfect, and write the one thing you would fix with another hour.

Deliverable: Three independent versions of the same query plus a note on what changed between them.

Day 3 — Only the statistics you will be asked to defend

• Write, in under 200 words, how you would decide whether a difference between two groups is real: the test, its assumptions, and what you would switch to when an assumption fails.
• Compute a 95 percent confidence interval for a difference in proportions by hand on realistic numbers, then write in one sentence what changes if the two samples are paired rather than independent.
• Write your answer to "what does a p-value mean", check it against a definition, and delete the version that describes it as the probability the hypothesis is true.

Deliverable: A 200-word written answer and one hand-computed interval you can reproduce under pressure.

Day 4 — One case, and the assumptions holding it up

• Answer one product case aloud in 20 minutes with a recording running, then listen back with a pen and mark every claim you asserted without saying what it rested on: an assumed user behaviour, an assumed data source, an assumed baseline rate, an assumed grain.
• Pick the three assumptions the recommendation actually depends on, write how you would check each one against data, and say which one being wrong would flip the recommendation rather than merely weaken it.
• Write the four-step structure you used onto a card small enough to hold in working memory when you are nervous.

Deliverable: One recording, three load-bearing assumptions each with a written check, and a four-step structure card.

Day 5 — Your own work, timed

• Write a 90-second version and a four-minute version of your main project, and time both out loud rather than reading them.
• Prepare answers to the two follow-ups that always come: what you would do differently, and how you knew it worked.
• Put one number in the first sentence and be able to say exactly where that number came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in a longer weekend block

• Run a 60-minute mock covering query work, a case and a behavioural question in a single sitting with no breaks, because sustained attention is the thing evenings have not trained.
• Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
• Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

• Write the 20-minute warm-up you will actually do on the morning of the interview: one query you can already write from a blank file, one metric you can define out loud, and nothing you have never seen before.
• Re-read only your own notes from this week, and open no new material.
• Write down the logistics: the tool you will be asked to work in, whether lookups are allowed, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the case structure, the project numbers, and the logistics.

Bring numbers, including the ones you are unsure of

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

Describe a situation where you faced a significant challenge in a project. How did you handle it?

Medium · behavioural and stakeholder questions

Approach

1. Close with what you would do differently, concretely.
2. Quantify the outcome, including what you would not claim credit for.
3. State the situation in two sentences and spend the rest on your reasoning.

Follow-up

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

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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?

Allocate one analyst week across three competing requests

Easy · prioritisation · stakeholders · scoping

Three requests arrive the same morning and you have one week. Finance wants per-account gross margin from fct_usage_daily for a pricing review in three weeks. Sales wants a renewal-risk list for accounts with term_end_date inside 60 days. A product manager wants an experiment readout for a decision being taken on Thursday. Produce your allocation with hours attached, what you say to whoever receives less, and one thing you refuse to do this week, with the reason each decision is defensible to the person it costs.

Approach

1. The interviewer is probing whether you prioritise on decision timing and reversibility or on who asked most forcefully. Sort by the date each decision is actually taken and by what the default outcome is if nothing arrives.
2. Apply that sort concretely. The Thursday readout has a hard irreversible deadline and no value afterwards. The pricing review has three weeks of slack. The renewal list has a rolling deadline set by term_end_date, so part of it is urgent this week and the rest is not, which means it can be split rather than deferred whole.
3. Find the cheapest sufficient version of each request rather than the full version. The readout goes in full. The renewal list ships as a filtered query over renewal-eligible accounts ranked by two inspectable signals rather than as a model. The margin work is scoped to the accounts that dominate the pricing decision, since revenue is heavily skewed and the tail will not change the conclusion.
4. Make the trade visible in one written note to all three at once, with dates. Telling each person separately that they are the priority is how an allocation becomes a credibility problem.
5. Refuse something explicitly and say why. The model version of the renewal list is the usual candidate, because it cannot be evaluated without a holdout nobody has agreed to yet, and building it this week forecloses that.
6. Leave slack. A plan with none is a plan to miss the one deadline that cannot move.

Follow-up

• The sales leader escalates to your manager. What did you already do that makes that a short conversation?
• Which of the three deadlines would you push back on, and what exactly would you ask for?
• What would you change about how these requests reach you so next week is not the same?