Zapier Data Scientist Interview Questions & Guide 2026

The 60-second brief

At Zapier, a Data Scientist plays a pivotal role in democratizing automation and helping millions of users streamline their workflows. Unlike traditional machine learning research roles, data science here is deeply embedded in product analytics, business intelligence, and growth. You will focus on extracting actionable insights from massive datasets of user activity, helping product, engineering, and marketing teams understand how users interact with "Zaps" (automated workflows), where they experience friction, and how to optimize retention.

Learn the economics of the product category before the loop. Marketplaces, subscription products and ad-supported products turn on different core quantities (match rate and liquidity, retention and churn, fill rate and yield) and fail in different characteristic ways.

Focus areas

• Strip CI, retry and synthetic traffic first
• Separate contracted seats from actively used seats
• Measure churn only on renewal-eligible accounts

Overview

The impact of this position is immediate and widespread. Because Zapier operates on a product-led growth model, your analyses directly influence product roadmaps, feature launches, and user onboarding flows. You will work with complex, high-volume event data generated by integrations between thousands of third-party applications. To succeed, you must be comfortable translating raw, sometimes unstructured user activity logs into clear business recommendations that drive strategic growth.

This role is ideal for analytical professionals who enjoy solving business problems, building robust data transformations, and communicating findings to cross-functional stakeholders. While machine learning and predictive modeling are occasionally utilized, the core of the work centers on SQL execution, data pipelines, metric definition, and visualization.

The Zapier interview process

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

Application Questionnaire

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

• Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
• Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
• Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

• For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
• Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
• For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Recruiter Screen

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

• 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

Hiring Manager Meeting

This conversation decides whether you can be handed a problem nobody has finished defining and left alone with it for a few weeks. The manager is listening for how you behave when the brief is thin: what you clarify before starting, and what you settle on your own rather than escalating. Most candidates over-index on technical depth here and under-describe the decisions they actually owned. Say who wanted the work, what you chose not to do, and where you would have stopped and asked. A clean account of your own judgement carries this round further than a longer project list.

What to demonstrate

• Whether you can name a decision that was yours alone, as opposed to one the team arrived at
• How you respond to a request that arrives with no success metric attached to it
• Whether the effort you estimate for a piece of work matches the work you just described doing
• What you escalate, and how long you sit on a problem before you do

How to prepare

• For each project you plan to raise, write one sentence saying what would not have happened if you had not been on it, and check that the sentence is about an outcome rather than an artefact
• List the decisions in your last project that were genuinely yours, and for each one write down the option you rejected and why
• Prepare the project that went badly: the point at which you knew, who you told, and what it cost before it was caught

Take-Home Assignment

Your submission is read asynchronously by someone who cannot ask you a clarifying question, and who will usually skim it before reading it properly. That changes what good looks like. The conclusion belongs near the top with the supporting analysis beneath it, and the code should run start to finish on a clean machine without a manual step you forgot to document. A reviewer forced to reconstruct your reasoning from the order of notebook cells is already discounting the work. The submissions that land are the ones where a busy reader gets the answer immediately and can verify it if they want to.

What to demonstrate

• Whether the answer arrives before the methodology, so a reader who stops after the first page still has the recommendation
• Whether the code runs end to end from the submitted files, with dependencies and data paths declared rather than assumed
• Whether each chart is legible on its own, carrying axis labels and units, and exists to support a claim made in the text

How to prepare

• Restructure a past analysis so the opening paragraph holds the recommendation and the number behind it, then check that nothing later in the document quietly contradicts it
• Copy your own submission into an empty directory, run it in a clean environment, and fix everything that breaks; hidden local state is caught here or by the reviewer
• For every chart, write the one sentence it is meant to prove, and delete the chart if you cannot write that sentence

Where candidates lose points

Reporting a mean over accounts when account revenue is heavy-tailed

When a small number of accounts hold most of the revenue, the sample mean is dominated by whichever of them happens to be in the sample, and the sample variance keeps growing as more data arrives instead of stabilising. In that regime the usual central-limit-based confidence interval understates uncertainty, and a single renewal or a single large account's batch job can flip the sign of a measured effect. The fixes are to pre-register a winsorisation or capping rule before looking at the outcome, to report account counts crossing a threshold alongside the revenue figure, or to define the estimand on a bounded transform. Choosing the cap after seeing the result is a separate and worse problem, because the cap then encodes the answer.

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.

Reading an observational correlation as a causal effect

Name the confounder you are most worried about and the design that would remove it: an experiment, a difference-in-differences with a checked pre-period trend, an instrument, or a regression discontinuity. When none is available, state which direction the bias likely runs and bound the claim accordingly.

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

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.

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?

Simulate how the renewal calendar distorts monthly churn rates

Medium · simulation · churn · numpy · cohorts

Simulate 1,200 accounts on annual contracts. Draw each account's renewal month from a deliberately lumpy calendar: 30% renew in January and the remaining 70% are spread evenly over the other eleven months. At each renewal an account churns with probability 0.18, independent of month; survivors renew and come back twelve months later. Run 24 simulated months. For each month compute two rates: churned accounts over all live accounts, and churned accounts over accounts whose term ended that month. Report the mean and the month-to-month standard deviation of each series, and state which one belongs in an executive summary.

Approach

1. Build the panel with numpy state arrays rather than a per-account loop: a next_renewal_month vector, an alive boolean vector, and a loop over the 24 months only. Looping over 24 months is fine; looping over 1,200 accounts inside it is what makes the simulation too slow to iterate on.
2. Maintain the live set honestly. An account that churned in month m must leave the denominator from m+1 onward and can never be renewal-eligible again; if it stays, the naive rate drifts downward for reasons that have nothing to do with churn and the calendar effect gets buried.
3. Compute both series over the same months and compare dispersion, not only level. The eligible-base rate should sit near 0.18 with binomial noise scaled by that month's renewal count; the naive rate spikes in January and collapses in thin months.
4. Quantify the gap instead of describing it: the ratio of the two means is roughly the reciprocal of the average monthly renewal-eligible fraction, and the naive series' standard deviation is driven by the signing calendar rather than by customer behaviour.
5. Check against the closed form before trusting the output. With the live set maintained correctly the eligible-base rate is an unbiased estimator of 0.18 in every month, so a systematic offset means the bookkeeping is wrong, not that the simulation found something.

Worked solution (30 min)

1. rng = np.random.default_rng(0); p = [0.30] + [0.70/11]*11; next_renewal = rng.choice(12, size=1200, p=p); alive = np.ones(1200, bool)
2. For m in range(24): eligible = alive & (next_renewal == m); churn = eligible & (rng.random(1200) < 0.18); record churn.sum(), eligible.sum(), alive.sum() at month start; alive &= ~churn; next_renewal[eligible & ~churn] += 12
3. naive = churned / live_at_start; eligible_rate = churned / eligible, left as NaN where eligible == 0.
4. Report naive.mean(), naive.std(ddof=1), np.nanmean(eligible_rate), np.nanstd(eligible_rate, ddof=1) and the ratio of the two means.

Expected result: The eligible-base rate averages close to 0.18 (roughly 0.17 to 0.19 at this sample size) with no January spike. The naive rate averages near 0.015, about one twelfth of it, and its January values run several times a typical month, so its month-to-month standard deviation is a large fraction of its own mean.

Verify it yourself

• The 24 monthly churn counts sum to 1200 minus the final alive count.
• Switching the calendar to uniform (p = 1/12 everywhere) collapses the naive series' dispersion while leaving the eligible-base mean unchanged.
• Setting the churn probability to 0 makes both series identically zero and leaves 1,200 accounts alive.
• Quadrupling the account count roughly halves the standard deviation of the eligible-base rate without moving its mean.

Follow-up

• Compounded over twelve months the naive rate lands close to the true annual churn. Does that rescue it?
• How would you report churn in a month where only nine accounts were renewal-eligible?
• Eighteen-month terms are now being sold alongside annual ones. What breaks?

Implement seven-day activation rate from its written definition

Easy · metrics · pandas · activation

Given dim_account (account_id, created_at, is_internal, is_current) and fct_api_request (account_id, request_at, http_status, api_key_id, traffic_class), implement activation_rate(accounts, requests, week_start). Definition: the numerator is accounts whose first request with http_status < 400, api_key_id not null and traffic_class != 'synthetic_monitor' occurs no later than 168 hours after created_at; the denominator is non-internal accounts created during the ISO week starting week_start. All timestamps are tz-aware UTC. Return the rate and both counts, and refuse to report a week until every account in it has had its full 168 hours.

Approach

1. Reduce dim_account to one row per account_id before joining anything. It is a type 2 dimension, so several versions of the same account exist; joining the versioned table to requests multiplies the denominator by the number of plan changes an account happened to make.
2. Build the denominator first and freeze it: is_internal == False and week_start <= created_at < week_start + 7 days. Everything after this is a filter on the numerator only, because an account that never sent a request must still sit in the bottom of the fraction.
3. Filter requests to qualifying rows and only then take groupby('account_id').request_at.min(). The first qualifying request is not the same object as the global first request filtered afterwards, and the two answers differ for every account whose first call was a 4xx.
4. Left-join the first qualifying timestamp onto the cohort and test (first_ok - created_at) <= Timedelta(hours=168). NaT propagates to False in that comparison, which is the behaviour you want, but assert it rather than assume it.
5. Guard reportability explicitly: if week_start + 7 days + 168 hours exceeds the maximum request_at in the data, the week is censored and will read as a drop, so return None or raise rather than emit a number.

Follow-up

• Median time-to-first-call is more informative. What breaks if you take the median over activated accounts only, and what estimator fixes it?
• How would you decide whether 168 hours is the right window rather than 72 or 336?
• An account signs up, does nothing for 20 days, then integrates heavily. Where does it land in this metric, and is that what you want?

Given a table of user logins and a table of task executions, write a query to find which user segments have the highest ratio of tasks executed per login.

Medium · sql and data manipulation

Approach

1. Check whether any join is one-to-many before aggregating, or the sums inflate.
2. State the window function and its partition and ordering out loud before writing it.
3. Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

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

Write a query to find the daily active users (DAU) who successfully completed at least three automated tasks within a 24-hour window.

Medium · sql and data manipulation

Approach

1. Check whether any join is one-to-many before aggregating, or the sums inflate.
2. Say which table is the grain you start from, and join outward from it.
3. 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?

Consecutive qualifying weeks before renewal, as a ranked worklist

Hard · gaps and islands · window functions · date boundaries

fct_api_request carries account_id, workspace_id, environment, request_at (timestamptz), http_status and traffic_class. fct_subscription_period carries account_id, term_end_date, auto_renew and is_current. A qualifying week for an account is an ISO week with at least 50 successful production requests in traffic_class ('interactive','batch'). Over the last 52 whole ISO weeks, and for accounts whose current term ends within 90 days, return the current qualifying-week streak length, the week it began, and the longest earlier streak. An account whose streak has broken must appear with a current length of zero.

Approach

1. Bucket weeks as date_trunc('week', request_at AT TIME ZONE 'UTC'). request_at is a timestamptz, so an unpinned date_trunc silently uses the session time zone, weeks start at a local midnight, and Monday-morning traffic lands in the previous week for part of the fleet. Pinning UTC also makes the seven-day arithmetic below exact across daylight-saving transitions.
2. Apply the exclusions before counting: environment = 'production', http_status < 400, traffic_class IN ('interactive','batch'). Then apply the volume floor and drop the current partial week, which can never meet a floor calibrated on whole weeks.
3. Build islands with the row-number anchor: ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY week_start) as rn, then week_start - rn * interval '7 days' is constant inside a run of consecutive weeks. Group by that anchor to get each streak's start, end and length.
4. The current streak is the island whose end equals the last whole week; if none does, the account's current streak is zero and that is the interesting case. The longest earlier streak is the maximum length among the remaining islands.
5. Join the renewal filter from the current subscription row and LEFT JOIN the streak summary so an account with no qualifying week at all still appears, rather than vanishing from the risk list precisely because it went quiet.
6. Finish with an operating point. The list is worked by a team with finite capacity, so order it and cut it at that capacity, and say what happens to the accounts below the line.

Worked solution (40 min)

1. Build weekly_qualified: filter the fact on environment, status and traffic_class, group by account_id and date_trunc('week', request_at AT TIME ZONE 'UTC'), keep groups with count(*) >= 50, and exclude the in-progress week and anything older than 52 whole weeks.
2. Add rn = ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY week_start) and anchor = week_start - rn * interval '7 days'.
3. Group by (account_id, anchor) to get streak_start = min(week_start), streak_end = max(week_start), streak_len = count(*).
4. Per account, take current_len as the streak_len where streak_end = the last whole week else 0, current_start from the same island, and longest_prior as max(streak_len) over the other islands.
5. Join to fct_subscription_period on is_current with term_end_date <= current_date + 90, LEFT JOIN the streak summary, and order by current_len ascending then term_end_date ascending.

Expected result: One row per renewal-eligible account with current_streak_weeks, current_streak_start_week and longest_prior_streak_weeks, where an account with no qualifying week in the last whole week returns current_streak_weeks = 0 and still appears, ordered so the quietest imminent renewals sit at the top.

Verify it yourself

• Per account, the sum of all island lengths equals the count of qualifying weeks for that account.
• current_streak_weeks > 0 if and only if the last whole week is a qualifying week for that account.
• No island contains a gap: re-deriving consecutive differences inside an island returns exactly 7 days everywhere.
• An account qualifying in all 52 weeks returns a single island of length 52 and a null-free current streak start.

Follow-up

• The floor of 50 requests was picked for this exercise. How would you calibrate it from data, and what would force a recalibration?
• A regional holiday week drops several accounts below the floor at once. How do you keep that out of the risk list?
• How would you evaluate whether contacting these accounts actually changed renewal, given that coverage is assigned deliberately?

How would you measure the success of a newly launched app integration on our ecosystem?

Medium · product sense and metrics

Approach

1. State what result would change your recommendation, so the answer is falsifiable.
2. Decompose the metric into the rates that drive it, and say which one you would check first.
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?
• Which segment would you cut first, and what would that rule out?

Looking at this set of bar charts showing user task usage over the last quarter, what key trends do you observe, and what business recommendations would you make based on them?

Medium · product sense and metrics

Approach

1. State what result would change your recommendation, so the answer is falsifiable.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. Fix the population and the time window before naming any metric.

Follow-up

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

If we notice a sudden 10% drop in user onboarding completion, what steps would you take to diagnose the issue?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Name one primary metric, then the guardrail that stops it being gamed.
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?
• What would you do if the primary metric and the guardrail moved in opposite directions?

What metrics would you define to measure the health and engagement of our premium plan subscribers?

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. 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?
• Which segment would you cut first, and what would that rule out?

How would you design an experiment to test whether a new dashboard layout increases the number of active Zaps per user?

Medium · a/b testing and experimentation

Approach

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

Follow-up

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

What visualization types would you choose to represent user drop-off points throughout a multi-step signup funnel, and why?

Medium

Approach

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

Follow-up

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

Measure a repackaging that moves usage between metered SKUs

Hard · mix shift · sku migration · metering lag · unit economics

A repackaging moves work previously billed as compute_hours into build_minutes at a different rate. Early readouts show compute_hours down 30% and total net_amount_cents flat. You have fct_usage_daily (account_id, sku_code, usage_date, billable_quantity, net_amount_cents, cogs_cents, is_restated, first_written_at, restated_at) and fct_api_request (account_id, environment, traffic_class, http_status, compute_ms, egress_bytes, request_at). Design the metric that says whether real consumption changed rather than merely moving between SKUs, state the settling window you will wait for and how you measured it, and name the unit-economics guardrail. Deliver the metric definition and the readout you would take to a pricing decision.

Approach

1. Refuse to compare SKU-level quantity series across the change. billable_quantity carries a different unit per sku_code, so a 30% fall in compute_hours cannot be added to or netted against a rise in build_minutes. Any metric that sums billable_quantity across sku_code produces a number with no unit and no meaning.
2. Build a SKU-invariant measure of delivered work from fct_api_request instead: successful, non-synthetic, non-load-test production requests with sum(compute_ms) and sum(egress_bytes) per account per week. That series is unaffected by which SKU the billing system attributed the work to, which is exactly the property this question needs.
3. Measure the settling window before reading anything. For a sample of historical usage_date values, compare the total as first written against the settled total after restatement, and exclude as many trailing days as it takes for the delta to fall under a stated tolerance. The tail of every usage series slopes downward for this reason alone and is routinely misread as a regression, then explained, then fixed.
4. Compare like for like at the account grain against each account's own pre-period. Split accounts by whether their workload was in scope of the repackaging, and read the change in delivered work per account relative to its own 8-week pre-period level, reporting the median and interquartile range. A fleet mean is dominated by whichever account happened to scale one batch job, because account consumption is heavy-tailed.
5. Guardrail the economics rather than the revenue line. Compute gross margin per account from net_amount_cents and cogs_cents and report it as a distribution. Flat revenue with identical work moved onto a SKU carrying a higher cogs_cents allocation is a real margin loss that a flat revenue line conceals completely.

Worked solution (40 min)

1. Measure the settling window: for 30 historical usage_date values, compute total net_amount_cents as first written against the current settled value, and take the number of trailing days after which the delta falls below a stated tolerance such as 0.5%.
2. Build weekly per-account delivered work from fct_api_request: successful request count, sum(compute_ms) and sum(egress_bytes), filtered to traffic_class in ('interactive','batch') and environment = 'production'.
3. Split accounts into in-scope and out-of-scope for the repackaging and compute the change in delivered work per account relative to its own 8-week pre-period, reporting median and interquartile range rather than a mean.
4. Compute per-account monthly gross margin before and after from net_amount_cents and cogs_cents, and report the count and revenue share of accounts whose margin crossed below zero.
5. Reconcile the two sources and state why request-derived work and fct_usage_daily quantities will not tie exactly, naming retries, failed requests and restatement.

Expected result: A three-part readout: the empirically measured settling window in days, the median per-account change in delivered work (successful production requests and compute_ms) for in-scope against out-of-scope accounts measured relative to each account's own pre-period, and the per-account margin distribution before and after. The conclusion states whether consumption actually changed or merely moved between SKUs, and at no point adds quantities across sku_code.

Verify it yourself

• Out-of-scope accounts should show no change in delivered work; movement there means the scope split is wrong or something else shipped in the same window.
• The settling window must be measured rather than assumed; re-running the readout a week later must not move the final week's figures by more than the stated tolerance.
• Total net_amount_cents across all SKUs before and after must reconcile to the invoiced total; a gap means restated rows were read mid-settlement.

Follow-up

• Revenue is flat and delivered work is flat. Name the one thing that could still have got worse, and exactly where you would see it.
• Packaging changes are usually applied to everyone at once. How would you design this as an experiment rather than a before-and-after, and what would you give up?

Billable units per account jumped while nothing shipped

Medium · retries · idempotency · guardrail metrics

Billable units per paying account rose 22 percent month over month with no pricing or packaging change. From fct_api_request (account_id, endpoint, http_status, is_retry, idempotency_key, traffic_class, billable_units, request_at) and fct_usage_daily (account_id, sku_code, usage_date, billable_quantity), determine how much of the rise is delivered value and how much is duplicated work. Deliverable: the decomposed figure, the accounts it concentrates in, and a recommendation on whether to report the 22 percent at all.

Approach

1. Check the guardrail before the headline: compute the customer-visible server error rate per account for both months, numerator http_status >= 500 and denominator excluding synthetic_monitor and load_test. Metered volume rising alongside an error rate is the known failure mode in this domain.
2. Deduplicate logical work by counting billable_units once per (account_id, idempotency_key) at the first successful request rather than once per row. Rows with a null idempotency_key cannot be deduplicated, so report their share as an explicit uncertainty band instead of assuming they are all unique.
3. Split by traffic_class before interpreting anything, because one change to a continuous-integration configuration can multiply request volume overnight without a human deciding anything about the product.
4. Test concentration: compute the per-account distribution of the increase and its top-decile share. A rise carried by a few accounts scaling one batch job is a different finding from a broad shift and gets a different recommendation.
5. Reconcile against fct_usage_daily for the same accounts and dates, and be ready to explain the expected gap in two sentences: request rows include retries and failures carrying zero billable_units, and the usage table restates after first write.

Follow-up

• A client retrying a request the server already completed produces duplicate billed work. What protocol or product change removes that, and what would you measure to confirm it worked?
• If the duplicated volume was genuinely invoiced, what should finance do, and how does that change what belongs in the metric?

Suggested practice checklist

Instead of guessing where the week should go, day one measures it under a fixed rubric and allocates the remaining hours in proportion to the gaps. The method is deliberately rigid: the allocation is written down before any studying starts and is not renegotiated when a topic turns out to be unpleasant.

Day 1 — Diagnostic, scored before you study anything

• Sit a 100-minute timed diagnostic in four blocks: 30 minutes of SQL across three prompts, 25 minutes of short-answer statistics, 25 minutes on one modelling or case prompt, and 20 minutes delivering one behavioural story aloud.
• Score each block from 0 to 3 on a fixed rubric where 3 is correct and fluent, 2 is correct but slow or prompted, 1 is partially correct, and 0 is stuck, grading the output rather than how the attempt felt.
• Allocate the hours for days two to five roughly in proportion to 3 minus the score in each block, write the allocation down, and commit to not revising it midweek.

Deliverable: A scored rubric and a fixed hour allocation for the rest of the week.

Day 2 — Largest gap: find the boundary rather than the subject

• Break the weakest area into five named sub-skills (for query work: grain control, window frames, date arithmetic, set logic with NULLs, and reading a query plan) and rate each one, so the rest of the week targets a sub-skill instead of a subject.
• Solve three problems chosen to sit just above where the rating drops off, and for each write the first move you failed to make.
• Re-solve one of them from memory four hours later, on paper, with nothing open.

Deliverable: A five-item sub-skill map with the two blocking sub-skills circled.

Day 3 — Largest gap: drill the blocking sub-skill

• Do eight short repetitions of the same shape rather than eight different problems, so what you practise is the pattern and not the puzzle.
• Write the rule you now hold in one sentence, then test it against a case built to break it: a ranking function over a column with ties, or a two-sample test on observations that are obviously dependent.
• Have someone else read your one-sentence rule and find the precondition you left out.

Deliverable: One rule statement with its preconditions attached and one counterexample that would have caught the incomplete version.

Day 4 — Second gap, plus maintenance on your strongest area

• Run the same sub-skill map and boundary protocol on the second-largest gap, compressed into half the day.
• Spend 25 timed minutes on your strongest area to stop it decaying, choosing the hardest problem you can still finish rather than an easy warm-up.
• Compare how the two areas fail: whether you lose time on recall, on setup, or on arithmetic, because the fix differs for each.

Deliverable: A second sub-skill map plus a one-line diagnosis of how each area fails you.

Day 5 — The gap that is not a skill

• Record yourself answering one technical and one behavioural prompt, then count two things in the playback: how many seconds before your first clarifying question, and how many sentences you started without knowing where they ended.
• Rewrite your three most-used stock phrases into shorter versions, and practise saying "I do not know, here is how I would find out" without softening it into a guess.
• Deliver one answer again with a hard 90-second limit to force structure before detail.

Deliverable: Two recordings with a counted improvement in time-to-first-question.

Day 6 — Retest under day-one conditions

• Sit the same 100-minute diagnostic structure with new prompts of comparable difficulty and score it on the identical rubric.
• Compare block by block, and for any block that did not move, change the method rather than adding hours: a block stuck at 1 usually means the practice was too varied, not too short.
• Write which single block you would still lose the offer on.

Deliverable: A second scored rubric placed next to the first, with one named remaining risk.

Day 7 — Full loop under interview conditions

• Run a 60-minute mock covering the two blocks that moved least, with an interviewer instructed to interrupt and change direction.
• Write your recovery script for the moment you go blank: restate the question, state your assumption, name the first thing you would check.
• Reduce the week to the rule statements you wrote, each with its preconditions attached, then say every one of them out loud without reading it and cut any you cannot state in a single sentence, since a rule you have to reconstruct mid-answer will not survive being interrupted.

Deliverable: A one-page card holding the recovery script and only the rules you could state from memory.

What the behavioural round is actually scoring

Interviewers here are not checking whether you can describe a project. They want the decision you made, why you made it under the information you had, and what changed afterwards that someone else could measure. A story that ends at 'I built a model' has no ending. Say what the model caused, or what you stopped doing because of it.

State the measured impact of your own work honestly

Hard · impact · causal inference · self-assessment

You are asked for the business impact of a renewal-risk worklist you shipped nine months ago. Customer success used it, and renewals in the covered segment came in 6 points above the prior year. Coverage was assigned by the team itself: they worked the top of your list and also the accounts they were already worried about. Produce the impact claim you are willing to defend, the number you refuse to claim, and the design you would have asked for at the start.

Approach

1. The interviewer is probing whether you can separate what you shipped from what you caused, and whether you would have built the measurement in rather than reconstructing it afterwards. Both halves are being scored.
2. Name the confound precisely. Coverage assignment is doubly selected: the largest accounts get an owner because they are valuable, and distressed accounts get one because they are at risk. The naive covered versus uncovered comparison mixes a strong positive selection with a strong negative one and can come out with either sign depending on which rule dominated. Matching on account size does not fix it, because the risk signal that triggered coverage is the same signal that predicts the outcome.
3. Split the claims by what each needs to be true. Ranking quality is defensible from precision at k on out-of-time renewals. Adoption is defensible from timestamps showing what share of listed accounts were contacted. The outcome claim is not defensible without a design, and saying so is the point of the exercise.
4. Look for identification before giving up on it. A capacity cut-off, a territory boundary, or a period in which the list existed but was unstaffed can assign coverage for reasons unrelated to account health, and any of those supports a bounded estimate.
5. State the design you would ask for now and its price: a randomly withheld slice of the list, held for two renewal quarters, with the expected cost in renewals stated openly. That cost is what it takes to be able to answer this question at all.
6. Give a bounded number rather than none. Six points with an explicit statement of how much of it you can attribute is more useful than either claiming the whole figure or declining to quantify anything.

Follow-up

• Your manager wants the 6 points in a promotion packet. What wording do you accept, and what do you strike?
• What would have had to be true for the naive covered versus uncovered comparison to be valid?
• If the holdout costs the team real renewals, how do you justify asking for it, and to whom?

Scope an open-ended request to predict account churn

Medium · scoping · churn modelling · operating point

A customer success director asks for a list of accounts about to churn. You know only that the team has six people and that contracts are annual. Available data is fct_subscription_period, fct_usage_daily, fct_api_request, fct_support_ticket and dim_account. Before writing any code, produce the questions you need answered, a proposed definition of about to churn, and the shape of the artefact you would hand back, including the operating point that turns a score into a decision.

Approach

1. The interviewer is probing whether you convert a vague request into a decision with a capacity constraint attached. A candidate who starts talking about model families has already failed the exercise.
2. Pin the event and the horizon first. Churn is only possible at term_end_date, so the population is accounts renewing in the next 60 to 90 days, not the whole base. Ask explicitly whether contraction and downgrade count as churn or only full non-renewal, because the three have different base rates and different interventions.
3. Pin the action and the capacity. Six people times a realistic number of meaningful interventions per week gives k, and k is what the list is ranked to. Evaluate on precision at k rather than a global AUC over accounts that will never be contacted.
4. Audit leakage before choosing features. Every feature needs a timestamp proving it existed before the prediction date. A downgrade amendment, a churn reason code, and a ticket opened after the renewal conversation started are all leaks that will make the offline number look excellent and the live list useless.
5. Ask for the counterfactual now rather than later. Coverage is assigned deliberately, so without a held-out slice agreed at the start the intervention can never be evaluated, and you will be asked for its impact in nine months regardless.
6. Propose the smallest artefact that closes the loop: a weekly ranked list sized to capacity with two or three inspectable reasons per row, plus a stated policy for accounts below the line.

Follow-up

• The director insists all accounts are in scope, not only those renewing soon. How do you answer without simply refusing?
• Historical non-renewals number about 30 a year. At what point do you tell them a model is the wrong tool and a rules list is better?
• Which candidate features would you drop purely because you cannot date them?

Walk through an analysis you later discovered was wrong

Easy · data quality · error ownership · metering

Six weeks ago you reported that consumption fell 9 percent in the last week of the month, and a team spent a sprint investigating the cause. The fall was an artefact: rows in fct_usage_daily land late and are restated in place, and you queried before the tail had settled. Describe how you found the error, what you told the people who acted on it, and the control you put in place so this class of mistake cannot reach a dashboard again. Be specific about how the settling window was measured.

Approach

1. The interviewer is probing whether you self-report errors before someone else finds them, and whether your fix is structural rather than a promise to be more careful. Say plainly that the number was wrong and that a sprint was spent on it, before describing any diagnosis.
2. Establish the artefact quantitatively instead of asserting that data lands late. For each usage_date, compare the total as of first_written_at against the settled total and read the settling time off that curve, for example 97 percent of final by day three and 99.5 percent by day five.
3. Correct the record the same day, in the channel the original number went out in, to the same audience. The cost of the wasted sprint belongs in the correction, not in a footnote.
4. Make the fix structural: exclude a trailing lag window from every reportable figure, and make the reporting view return no rows inside that window rather than returning partial ones. A dashboard that shades unsettled days still gets read as a decline.
5. State what generalises. Any fact table restated in place has this failure mode, so the guard belongs at the source rather than on the one dashboard that embarrassed you. A strong answer ends with the class of error closed; a generic one ends with a lesson learned.

Follow-up

• How did you choose the completeness threshold behind the lag window, and what would make you recalibrate it?
• What did you say to the team that lost the sprint, and what did they say back?
• Is there a legitimate case for showing the unsettled tail at all, and to whom?