What makes it hard is the absence of a physical constraint to anchor definitions. A supply chain has units that either arrived or did not; a product funnel has whatever the tracking plan happened to record, sessionised by a rule somebody chose, joined on an identity that resets when a browser clears its cookies. Two competent analysts can produce different conversion rates from the same warehouse and both be right, which means the defensible answer is the one whose assumptions are written down. On top of that, the effects being measured are small: a genuine improvement to a mature funnel is often a fraction of a percentage point in absolute terms, comfortably inside the noise of a week, so most of the skill is in variance control and sizing rather than in modelling.
The skills that carry the most weight are SQL across mismatched grains, experiment design, and the judgement to say what a result does not support. Event, session, subscription-period and exposure data all live at different grains, and the standard failure is a join that fans revenue across rows or an inner join that deletes exactly the population the question was about. Experiment work is judged less on whether a test statistic was computed correctly than on whether the randomisation unit matched the interference structure, whether the horizon was fixed in advance, and whether the analysis population was the exposed set rather than the assigned set. Above all of this sits communication: a correct analysis delivered as a table of numbers with no stated decision is treated as unfinished.
The work in general product and business analytics is mostly deciding what a number should mean and then defending that decision. A typical week contains a metric that moved and nobody can explain, an experiment readout where the headline is flat but three segments look interesting, a request to define success for a feature that ships next month, and a forecast or sizing input for a planning cycle. Very little of it arrives as a well-posed question; the first deliverable is usually a restatement of the question in terms that have a numerator, a denominator and a window. The output is a decision about shipping, pricing, spending or stopping, and it has to survive somebody else recomputing it from the same tables.
Metric definition and metric-tree construction
editorialPreparation area covering the translation of a loosely worded goal into a metric with a stated numerator, denominator, window and exclusion rule, and the assembly of those metrics into a tree where each layer explains the one above it.
What to demonstrate
- Whether a proposed metric is actually computable from the available tables, and whether the candidate says so when it is not
- Whether the candidate names the window, the lag before publication and the excluded populations rather than leaving them implied
- Ability to explain how a metric would be gamed and to name the specific guardrail that catches that behaviour
- Judgement about the grain a metric belongs at, particularly the distinction between a per-user and a per-account question
How to prepare
- Write out each metric in the tree above from memory, including its lag rule, until the definitions are recall rather than reconstruction
- Take three vague goals such as 'improve engagement' and produce two defensible metrics for each, with the argument for choosing between them
- Practise stating the failure mode of a ratio metric whose denominator is the thing that moved, and how you would detect that in the data
- Rehearse the difference between a count, a rate and a rate-of-rates, and which of the three survives being rolled up across periods
Warehouse SQL across event, session, subscription and exposure grains
editorialPreparation area for queries that span an append-only event log, derived sessions, billing periods and experiment exposures, where a naive join fans out revenue and an inner join removes exactly the rows that carry the answer.
What to demonstrate
- Correct handling of fan-out: pre-aggregating one side in a CTE before joining, and being able to demonstrate what the unaggregated join would have done to the total
- Deliberate treatment of NULL foreign keys, including signed-out events with no user_id, sessions with consent_state = 'denied' and accounts with no subscription row
- Fluency with window functions for first-event identification, cohort indexing, running counts and deduplicating overlapping billing periods
- Whether rates are rolled up by re-summing numerator and denominator rather than by averaging daily or weekly rates
How to prepare
- Build the full visitor-to-signup-to-activation funnel as one query using left joins forward, so that lost stages stay visible as NULLs instead of disappearing
- Write MRR at the start and end of a month from fct_subscription_period, handling mid-period plan changes without double-counting the account
- Practise a retention triangle from dim_user and fct_event with both window bounds enforced, and verify the row count at each grain before aggregating
- Reconstruct a per-attempt funnel from flow_instance_id, and be able to say what breaks when that id is missing
Experiment design, sizing and readout discipline
editorialPreparation area for choosing a randomisation unit, powering a test before it launches, and reading it out in a way that survives scrutiny, including the cases where the honest answer is that the test could not have detected the effect in question.
What to demonstrate
- Whether the randomisation unit matches the interference structure, and whether the standard error is computed on that unit rather than on the row
- Ability to size a test from first principles: for a proportion, roughly 16 p(1-p) divided by the squared absolute lift per arm, at 5% two-sided alpha and 80% power with equal arms
- Recognition that the analysis population is the exposed set, that assignment without exposure dilutes the estimate toward zero, and that a sample-ratio mismatch invalidates the readout rather than being a footnote
- Whether novelty and primacy effects are addressed, and whether the candidate distinguishes a metric that recovered from one that was never affected
How to prepare
- Memorise the proportion sizing shortcut and practise computing a minimum detectable effect from a realistic weekly traffic figure in under a minute
- Be able to state the design effect for clustered randomisation, variance inflated by 1 + (m-1) rho with m the mean cluster size and rho the intraclass correlation, and what it does to required sample size
- Practise explaining why a per-session metric randomised on users needs a delta-method or bootstrap standard error rather than a naive one
- Prepare a short account of variance reduction with a pre-period covariate, including the fact that it removes a share of variance equal to the squared correlation between covariate and outcome
Causal inference when randomisation is unavailable
editorialPreparation area for questions where an experiment is impossible, illegal or already too late, covering the quasi-experimental designs, the assumption each one rests on, and how to argue that assumption is plausible in the case at hand.
What to demonstrate
- Whether the candidate names the identifying assumption rather than the method, for instance parallel counterfactual trends rather than 'difference-in-differences'
- Ability to choose between difference-in-differences, a geo or time-based holdout, regression discontinuity and matching on the basis of what the data actually supports
- Awareness of what matching cannot fix, namely selection on anything unobserved, and willingness to say so instead of presenting a balanced covariate table as proof
- Whether a placebo or pre-trend check is proposed before the estimate is believed
How to prepare
- Practise writing the parallel-trends check you would run before a difference-in-differences estimate, and what you would conclude if the pre-period trends diverge
- Prepare one worked case where a launch was staggered across regions and explain how the staggered timing itself provides the comparison
- Rehearse the argument for a geo holdout as the cheapest credible read on channel incrementality, including how you would size it on total conversions
- Be able to state in two sentences why observational channel comparisons overstate the channels closest to conversion
Diagnosing a metric movement and landing the decision
editorialPreparation area for the open-ended case where a number moved and the task is to find out why and say what to do, working from a decomposition rather than from a list of guesses.
What to demonstrate
- Whether the first move is to verify the metric itself, covering instrumentation changes, a late-arriving data partition, a client release and a definition edit, before any product hypothesis
- Whether the decomposition is systematic, splitting the metric into its factors and then across segment, platform, geography, tenure and channel rather than sampling hypotheses at random
- Ability to size the contribution of each candidate cause, so that a segment moving sharply but carrying two percent of volume is correctly set aside
- Whether the answer ends in a recommendation with its uncertainty stated, and whether the candidate says plainly when the data cannot yet support one
How to prepare
- Rehearse a fixed opening sequence: confirm the movement is real and outside normal weekly variation, confirm the pipeline and tracking plan did not change, then decompose
- Practise the multiplicative decomposition of a funnel metric so that a drop can be attributed to a specific stage rather than to the funnel in general
- Build the habit of quantifying every candidate cause in the units of the headline metric, so contributions can be ranked and the remainder made explicit
- Practise delivering the finding in three lines, the movement, the cause with its share, and the decision, before any supporting detail
PracHub editorial advice for the preparation topics above.
Comparing cohort retention curves of different maturities, or building the curve from users who are still present
A cohort four weeks old has no week-8 value, so an average taken across cohorts silently drops young cohorts from the later columns and keeps them in the earlier ones. The curve then bends upward at the tail, and the reading that 'retention is improving over time' is an artefact of which cohorts survived to be measured. The same error appears in the denominator when retention is computed over users active in the current period rather than over the full original cohort, which conditions on survival and guarantees a flattering number. The fix is a triangle: fix the cohort at signup, bound every window on both sides, and only compare cells where every cohort has had the full elapsed time, publishing the rest as blank rather than as a partial average.
Counting on an identity key that changes underneath the metric
visitor_id is per browser and per device, and it resets on cookie clearance, private browsing and platform privacy changes, so the distinct-visitor count drifts upward for reasons unrelated to reach. Any rate with visitors in the denominator therefore decays over time even when behaviour is constant, and any rate with visitors in the numerator inflates. The stitching at signup makes it worse in both directions: a user who signed up on mobile and returns on desktop is two visitors and one user, while a shared device is one visitor and several users. Decide which key each metric is counted on, write it into the definition, and when comparing a period before and after a platform privacy change, expect a level shift in every visitor-keyed metric and do not attribute it to the product.
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.
Ignoring interference between units in a marketplace experiment
Ask whether one unit's treatment can change another unit's outcome through shared inventory, a matching pool, a social graph or a common budget. Where it can, randomise at a level that contains the spillover, such as region or time slice, and say explicitly what that costs you in statistical power.
Choose a category, try a prompt, then open its approach, worked solution or follow-up when you need it.
Sessionise an event stream with gap and midnight rules
Sessionise a raw event stream. Input: a DataFrame with visitor_id, user_id (often NULL), occurred_at_utc and event_name, unsorted, up to 5 million rows. A session breaks when the gap from that visitor's previous event exceeds 30 minutes, and is force-closed at UTC midnight so no session spans two calendar dates. A gap of exactly 30 minutes does not break. Emit one row per session with session_id, visitor_id, the user_id as of the last event in the session, started_at_utc, ended_at_utc, session_date, duration_seconds and event_count. Vectorise; do not loop per visitor.
Approach
- Sort by ['visitor_id', 'occurred_at_utc', 'event_id'] once, then express the whole problem as one boolean vector: a row starts a new session when the visitor changed, or the gap exceeds 30 minutes, or the UTC date differs from the previous row's UTC date. Cumsum that vector and you have the session key.
- Get the comparison direction right on the gap: the rule is strictly greater than 1800 seconds, so an event at exactly 1800 seconds continues the session. Write it as gap > pd.Timedelta(minutes=30), and make the tie a test case rather than an assumption.
- Derive the midnight break from the date change, not from inserting synthetic boundary rows. A date change implies a break even when the gap is two seconds, which is precisely the force-close rule and is why the two conditions are ORed rather than one subsuming the other.
- Aggregate with a single groupby on the session key: min and max of occurred_at_utc, size for event_count, and last for user_id, which is correct because the frame is already sorted so 'last' is the final event in the session. That is the identity-as-of-session-end rule.
- Compute duration_seconds as (max - min).dt.total_seconds(), which makes a single-event session 0 seconds. Say so explicitly, because a downstream mean session duration is sensitive to whether single-event sessions are 0 or excluded.
Worked solution 35 min
- df = df.sort_values(['visitor_id','occurred_at_utc','event_id']).reset_index(drop=True).
- new_visitor = df.visitor_id.ne(df.visitor_id.shift()); gap = df.occurred_at_utc.diff(); new_day = df.occurred_at_utc.dt.date.ne(df.occurred_at_utc.dt.date.shift()).
- is_start = new_visitor | (gap > Timedelta(minutes=30)) | new_day; df['session_key'] = is_start.cumsum().
- g = df.groupby('session_key'); out = g.agg(visitor_id=('visitor_id','first'), user_id=('user_id','last'), started_at_utc=('occurred_at_utc','min'), ended_at_utc=('occurred_at_utc','max'), event_count=('event_id','size')).
- out['session_date'] = out.started_at_utc.dt.date; out['duration_seconds'] = (out.ended_at_utc - out.started_at_utc).dt.total_seconds(); assign session_id from the sorted index.
Follow-up
- Sessions are used as the denominator of a conversion rate. How does moving the inactivity gap from 30 to 45 minutes move that rate, and in which direction?
- A visitor's clock is 40 minutes ahead, so their events arrive with future occurred_at values. What does your sessioniser do, and what would you rather it did?
- The same person signs up mid-session on mobile and continues on desktop. How many sessions and how many users does your output show, and is that the right answer?
Cluster bootstrap for a per-session rate randomised on users
An experiment randomised on user_id reports a per-session conversion rate, so sessions inside a user are correlated. Input: one row per session with user_id, variant in {control, treatment} and converted in {0,1}. Write a cluster bootstrap from scratch: resample users with replacement within each arm, keep every session of a drawn user, recompute each arm's ratio of converted sessions to sessions, and take the difference. Return the point estimate, a 95 percent percentile interval from at least 2,000 resamples, the naive session-level interval that ignores clustering, and the ratio of their widths.
Approach
- Name the estimand precisely: it is a ratio of sums, sum(converted) over sum(sessions) within an arm, not the mean of per-user rates. Those differ whenever session counts vary across users, and the ratio is what the reported metric is.
- Resample the cluster, not the row. Draw n_users user ids with replacement inside each arm and take every session belonging to each draw, including duplicate draws of the same user. Keeping the user count fixed per arm rather than the session count is what preserves the sampling design.
- Precompute per-user (converted_sum, session_count) once, so each resample is two vector lookups and a division rather than a repeated filter over the session frame. That turns 2,000 resamples from minutes into under a second.
- Take the 2.5th and 97.5th percentiles of the 2,000 differences for the interval, and report the point estimate from the full data rather than from the bootstrap mean, since the bootstrap mean carries the resampling bias.
- Compute the naive interval from the session-level binomial standard error and compare widths. The expected inflation is roughly sqrt(1 + (m-1)*rho), with m the mean sessions per user and rho the intraclass correlation of converted within users, so a computed ratio far from that value points at a bug in one of the two intervals.
Follow-up
- Users average 3.4 sessions and the intraclass correlation is 0.12. What width ratio do you predict before running it, and does your bootstrap land there?
- Give the delta-method standard error for this ratio and say when you would prefer it to the bootstrap.
- Half the users in the treatment arm have exactly one session. What does that do to the cluster bootstrap's coverage, and how would you check it?
Simulate the false positive cost of repeated peeking
Quantify the cost of peeking. Simulate a two-arm experiment with no true effect: each arm accumulates Bernoulli conversions at a base rate of 0.10 up to 40,000 units per arm. Run a two-sided two-proportion z-test at alpha 0.05 at ten equally spaced interim points, and record whether the test ever crossed. Report the false positive rate over at least 10,000 replications, alongside the rate for a single look at the final sample only. Use a fixed seed and report a Monte Carlo standard error on both figures.
Approach
- Generate each replication as two cumulative sums of Bernoulli draws, then read the interim points off the cumulative arrays. Regenerating data at each look would make the looks independent, which destroys exactly the dependence the exercise is about: later looks share data with earlier ones.
- Use the pooled-variance two-proportion z: p_pool = (x1+x2)/(n1+n2), z = (p1-p2) / sqrt(p_pool*(1-p_pool)*(1/n1 + 1/n2)), reject when |z| > 1.96. State that the normal approximation is fine here because the smallest look has roughly 400 expected conversions per arm.
- Vectorise across replications rather than looping: draw a (reps, n) array of uniforms, threshold at 0.10, cumsum along axis 1 and slice the ten look indices. A per-replication loop at 10,000 by 40,000 is unnecessarily slow.
- Record the any-cross indicator per replication, take the mean, and compute the Monte Carlo standard error as sqrt(p*(1-p)/reps) so the reported figure comes with its own precision.
- Report the single-look rate in the same run as a control. If it does not land near 0.05, the bug is in the test statistic and not in the peeking argument.
Follow-up
- Re-run with 40 looks instead of 10. Why does the curve flatten rather than continue rising linearly?
- Among the replications that crossed, what is the mean observed lift, and why is it not zero?
- What does an O'Brien-Fleming boundary or an always-valid confidence sequence change about this simulation, and what does each cost in power?
Find reactivation gaps in account paid-period history
fct_subscription_period holds account_id, subscription_id, period_start_utc, period_end_utc, period_status and change_reason. A mid-period plan or seat change closes one row and opens another, so a single continuous paid tenure is often many rows, and an account may hold two overlapping subscriptions. Collapse rows with period_status in ('active','past_due') into continuous tenures per account, treating gaps of three days or less as continuous. Return account_id, tenure_start, tenure_end, and for every tenure after the first, the gap in days that preceded it.
Approach
- Filter to paid rows only: period_status IN ('active','past_due'). Trialing periods are not tenure, and including them turns every trial that never converted into a one-period tenure followed by a fake churn.
- Order by period_start_utc and take a running maximum of all prior ends: MAX(period_end_utc) OVER (PARTITION BY account_id ORDER BY period_start_utc, period_end_utc, subscription_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). Those three columns are the only stable ordering this schema exposes, so check first that they are unique within an account; if rows tie on all three, the island numbering is order-dependent between runs and you need a real row key before the result is reproducible.
- LAG on its own is wrong here because with overlapping or nested periods the immediately preceding row by start date is not the one that ends latest, so the running maximum is the part that cannot be shortcut.
- Flag a new island when prior_max_end IS NULL OR period_start_utc > prior_max_end + interval '3 days', then number islands with a running SUM of the flag over the same ordering and an explicit ROWS frame.
- Group to (account_id, island) taking MIN(period_start_utc) and MAX(period_end_utc), then LAG(tenure_end) OVER (PARTITION BY account_id ORDER BY tenure_start) to compute the preceding gap in days for every tenure after the first.
- Sanity-check with change_reason, which is the only lineage this schema carries: list its distinct values first, then confirm that rows recording a plan or seat change sit inside a tenure rather than opening one, and that every tenure after the first opens on a row whose reason records a restart rather than an ordinary renewal. Do not reconcile against a churn timestamp on dim_account, which this schema does not define; and where such a column does exist, a cancellation timestamp records when the request was made and routinely sits weeks before the period it ends.
Follow-up
- Why three days of grace? What do 0 and 30 days each do to the count of accounts classed as reactivated?
- An account runs two concurrent subscriptions for different teams. One tenure or two, and what does the revenue reader expect?
- How would you turn these tenures into a monthly gross logo churn series without double-counting an account that churned and returned in the same month?
Read an experiment from first exposure, not assignment
fct_experiment_exposure holds experiment_id, unit_type, unit_id, variant, user_id, assigned_at_utc, first_exposed_at_utc, is_in_analysis_population and planned_end_utc. fct_event holds user_id, occurred_at_utc, is_core_action, and carries events up to a known data cut, :data_cut_utc. For one experiment randomised on unit_type = 'user', return per variant: exposed units, units with at least one core action in the seven days after that unit's own first exposure, the rate, and the variant share of exposed units. Only units whose seven-day window has fully elapsed as of the data cut belong in the readout. Units appearing under more than one variant are excluded from both arms and counted separately.
Approach
- Run the contamination pass as an aggregate, not a window: SELECT unit_id FROM fct_experiment_exposure WHERE experiment_id = :exp GROUP BY unit_id HAVING COUNT(DISTINCT variant) > 1, then anti-join it away. PostgreSQL rejects COUNT(DISTINCT variant) OVER (PARTITION BY unit_id) outright, since DISTINCT is not implemented for window functions; if you want the test inline, MIN(variant) OVER (PARTITION BY unit_id) <> MAX(variant) OVER (PARTITION BY unit_id) is the equivalent that does run.
- Do not resolve contamination by keeping the earliest variant. A unit that saw both arms carries treatment from both, so assigning it to either one biases that arm.
- Define the population as is_in_analysis_population = TRUE AND unit_type = 'user' AND first_exposed_at_utc < planned_end_utc AND first_exposed_at_utc + interval '7 days' <= :data_cut_utc. The horizon filter is what makes the readout reproducible next week instead of drifting with every re-run; the data-cut filter is the one that actually buys seven days of follow-up, since a unit exposed an hour before the horizon otherwise contributes an hour of observation to a seven-day rate.
- Measure the outcome on a per-unit relative window: LEFT JOIN fct_event on user_id with is_core_action = TRUE and occurred_at_utc in [first_exposed_at_utc, first_exposed_at_utc + interval '7 days'). LEFT JOIN so units with no outcome stay in the denominator at zero rather than being deleted by an inner join.
- Check the sample ratio before reading the effect: variant share of exposed units against the intended split, tested as a binomial. Run it on the truncated population as well as on the full exposed set, because if one arm exposes later on average the data-cut filter removes more of that arm and can manufacture a ratio mismatch the randomisation did not have. A mismatch on the full set means the exposure data is not a valid randomisation and invalidates the readout rather than being a footnote under it.
- Report the per-variant rate, the absolute difference, and the fact that the variance unit is unit_id. That is straightforward here only because the grain is already one row per user; a per-session outcome under user randomisation would need a delta-method or bootstrap standard error instead.
Worked solution 35 min
- Count units per variant and units with more than one variant; report the contaminated count before anything else.
- Build the exposed population CTE with both the horizon filter and the seven-days-before-data-cut filter, assert one row per unit_id, and record per variant how many units the second filter removed.
- LEFT JOIN the bounded outcome events and flag conversion with a boolean aggregate.
- Aggregate per variant to exposed, converted, rate and share, and run the binomial check on the share, both before and after the truncation.
- Re-run with a fixed calendar window from experiment_start_utc and record the difference as the size of the dilution you avoided.
Follow-up
- Some units were assigned days before they were exposed. What does analysing the assigned set instead do to the estimated effect, and in which direction?
- The split is 51/49 on 400,000 exposed units. Do you read the result?
- The treatment arm exposes on average two days later than control. What does that do to a fixed calendar outcome window, and which arm does it favour?
Measure whether self-serve help actually answered the question
A help widget opens in-product. Leadership asks for the percentage of users who got their answer. You have fct_event (event_name, user_id, session_id, occurred_at_utc, is_core_action, properties JSONB carrying article_id), fct_session, and a support ticket table joined on user_id. Nothing records whether the answer was correct or whether the user was satisfied, and no such field is being added. Propose the metric you would publish, state the proxy plainly, name the direction and rough size of its bias, and say which decisions it can and cannot support. Deliverable: the definition plus the bias statement.
Approach
- Say first that the target quantity is unobserved and will stay unobserved: nothing in the stream distinguishes a user who was helped from one who gave up, and both leave the same trace, so every candidate metric here is a proxy and the only question is which bias you prefer.
- Build the proxy to remove the largest identifiable error: among widget-open sessions, the share with no ticket from that user within 72 hours and at least one is_core_action = TRUE event after the widget opened in the same session. The downstream action requirement strips out most of the silent abandonment that a no-ticket rule alone scores as success.
- State the residual bias with a direction and a bound: it still over-counts, because a user who gave up and went elsewhere files no ticket and may still complete an unrelated core action later in the session; bound it using the observable population of widget-open sessions that end within two minutes with no further event.
- Publish a directly-measured companion with its own weakness in the same sentence: article thumbs up/down reported with its response rate, and the note that a single-digit response rate missing non-randomly toward annoyed users is exactly why it cannot be the headline.
- Write the use statement, because a proxy without one gets reused for the wrong decision: valid for ranking articles against each other and for detecting a week-on-week break, invalid as an absolute deflection rate or as an input to a cost-saved figure.
Follow-up
- How would you validate this proxy once, and what would you do if the validation said it over-counts by 20 points?
- Support asks for a dollars-saved number from this metric. What do you say, and what would you need before saying anything else?
- Ticket volume drops the week support hours change. How do you keep that out of the series?
Randomise a shared workspace feature without contaminating control
A feature changes a collaborative surface inside a workspace: when one member uses it, other members of the same account see the result in their own view. You have dim_user (user_id, account_id, is_internal), dim_account (account_id, seats_assigned, lifecycle_status) and fct_event. Among active accounts the mean seats_assigned is 6, the coefficient of variation of that count is 1.5, and the intraclass correlation of the weekly core-action rate within an account is 0.10. Choose the randomisation unit, quantify what that choice costs in sample, and specify how you would compute inference.
Approach
- State the interference before choosing anything: a treated user changes what an untreated colleague sees, so user-level randomisation puts both arms inside one account and biases the contrast toward zero. Randomise on account_id.
- Price the clustering properly. With equal clusters the design effect is 1 + (m - 1) rho = 1 + 5(0.10) = 1.5. Sizes here are far from equal, so use 1 + ((CV^2 + 1) m - 1) rho = 1 + (3.25 x 6 - 1)(0.10) = 2.85. The equal-size shortcut understates the cost by nearly half.
- Decide the estimand before the estimator. An account-weighted mean gives every workspace one vote; a user-weighted mean lets the largest workspaces dominate. With this size skew the two can move in opposite directions, so pick the one the decision needs and write it down.
- Compute standard errors on the account, not the user: cluster-robust on account_id, or collapse each account to a single number and test those. Below roughly 40 clusters per arm, cluster-robust errors are biased downward, so use a wild cluster bootstrap or randomisation inference over the assignment.
- Buy back variance where you can. Stratify assignment by seat band and lifecycle_status before randomising, and decide in advance how the handful of very large accounts are handled, since one enterprise workspace can carry more users than a hundred single-seat ones.
Worked solution 30 min
- Write the interference down: the outcome for user i depends on the treatment of other users in account(i), so the no-interference assumption fails at the user level and holds at the account level.
- Compute both design effects, 1.5 equal-size and 2.85 unequal-size, and use 2.85.
- Take the user-level sample requirement from the proportion shortcut, multiply by 2.85, then divide by the mean of 6 users per account to express it in accounts per arm.
- Specify the analysis: collapse to one row per account, regress the account-level outcome on variant with stratum fixed effects, and use a wild cluster bootstrap for inference.
- State the stopping rule up front: if the required account count exceeds the eligible population, the test is not runnable, and the alternatives are a longer window, a larger target effect, or a non-experimental read.
Follow-up
- Suppose the feature is not workspace-scoped but changes a globally shared ranking model, so no clean cluster exists. What design gets you a causal read, and what does it cost you?
- You have 900 eligible active accounts in total. Given the design effect, what absolute lift can this test detect, and is the honest answer 'do not run it'?
- The intraclass correlation is an estimate from last quarter. What happens to your sizing if the true value is 0.25?
A completion rate the owning team can move without fixing anything
A team's target is core-flow completion rate: distinct fct_event.flow_instance_id with a 'flow_completed' event within 30 minutes of its 'flow_started' and no 'error_shown' carrying the same flow_instance_id in between, over distinct flow_instance_id with a 'flow_started' in the window, split by surface and app_version. The same team owns the client that emits those events and the tracking plan that defines them. List the ways this rate rises without any user completing more flows, then redefine the metric and its guardrails so those routes are closed. Deliverable: the hardened definition.
Approach
- Work the emission side first, because that is what the team controls: delay minting flow_started until after the first screen so the highest-dropping attempts leave the denominator; stop emitting or rename error_shown; mint a fresh flow_instance_id on each retry so one failed attempt becomes several attempts whose last one completes; move flow_completed earlier in the flow.
- Sort those moves by where they are visible. None of them shows in the rate itself; three of them show only in volume, which is why the denominator has to be published on the same chart as the rate.
- Re-anchor the numerator on something outside the flow's own instrumentation: require a downstream is_core_action = TRUE event for the same user_id within 30 minutes of flow_completed, so a completion only counts when it produced the thing the flow exists to produce.
- Add the guardrail that catches the retry route specifically: mean and p90 flow_instance_id per user per day, with the rule written down that a rising completion rate alongside rising attempts per user is a regression and not a win.
- Make definition changes visible instead of forbidden: stamp a tracking-plan version on the series and re-base the history when event semantics or the 30-minute window change, rather than splicing two definitions into one line.
Follow-up
- How do you distinguish a genuine instrumentation fix from gaming, given both look like a step change confined to one release?
- The jump appears in exactly one app_version. Does that exonerate the team or implicate it?
- What do you do with eighteen months of history once the definition is hardened?
Attribute a flow-completion drop to one client build
Core-flow completion rate on surface = 'ios', defined as distinct flow_instance_id reaching 'flow_completed' within 30 minutes of 'flow_started' with no 'error_shown' on the same id in between, fell from 78% to 71% over four days. A new iOS build began a staged rollout on day one. Using fct_event columns flow_id, flow_instance_id, app_version, event_name, user_id and occurred_at_utc, produce the completion rate per build per day, size the loss in absolute completions, and say whether the fix is a rollback. Treat the possibility that the instrumentation changed rather than the flow.
Approach
- Cut the rate by app_version and day, each build against its own denominator. A pooled series during a staged rollout is a weighted average whose weights move daily, so it declines in proportion to the rollout share even when the old build is perfectly flat; confirming that proportionality is itself the evidence the regression is build-specific rather than environmental.
- Test the denominator before believing the rate. Compute flow_started events per distinct user_id on each build: if the new build re-mints flow_instance_id on retry, or fails to carry the id from start to completion, the denominator inflates and the numerator falls with nothing changing for the user.
- Normalise to a user-level outcome that is immune to the id question, namely completions per distinct user_id per day on each build. This is the number that says whether anyone actually failed to finish.
- Check the error path: pull error_shown counts and their properties for flow_instance_id values on the new build. A genuine regression produces errors; an instrumentation break produces missing completions with no corresponding error volume, and the two prescribe different fixes.
- Size the real loss as the per-user completion gap multiplied by daily users on the new build, and give the rollback recommendation conditional on the error evidence rather than on the rate.
Follow-up
- The rollout is at 45% and product wants to go to 100% tomorrow. What do you say, and what would you need by when?
- If the flow_instance_id is genuinely being re-minted, what is the correct historical treatment of the four affected days?
- How would you have caught this on day one instead of day four?
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.
Prepare, practise & reflect
One practical outcome each day. Spend longer where you need it.
0 / 7 done01Breadth 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.
Practice prompt ↗Practice prompt ↗Worked solution ↗02Breadth 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.
Practice prompt ↗Practice prompt ↗03Breadth 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.
Practice prompt ↗Practice prompt ↗Worked solution ↗04Breadth 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.
Practice prompt ↗Practice prompt ↗05Depth, 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.
Practice prompt ↗Practice prompt ↗Worked solution ↗06Depth, 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.
Practice prompt ↗07Integration 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.
Practice prompt ↗Worked solution ↗Expand any day for tasks and deliverables. Your progress is saved on this device.
Sometimes the honest read is that the initiative did not work, and the person who commissioned the analysis was hoping otherwise. Interviewers want to know whether you softened it. Prepare the case where you delivered an unwelcome result, how you presented the uncertainty without hiding behind it, and what the team did next.
Disagree with a product manager's roadmap claim using data
A product manager proposes building a feature on the argument that accounts connecting an integration in week one retain three times better at week four. The figure is correctly computed from dim_user and fct_event, and it has already been shown to leadership. You have one scheduled 1:1 before the roadmap locks. Deliver the specific analysis you would run to test whether the relationship is causal, the result that would change your own mind, and how you open the conversation so that the PM is not put in the position of defending the number in public.
Approach
- Recognise what is being probed: whether you can separate a number being right from an inference being wrong, and do it without costing the PM face. The generic answer recites that correlation is not causation; the strong one names the specific confound and proposes the cheapest design that could distinguish the explanations.
- State the alternative concretely. Accounts that connect an integration in week one are accounts that already have a workflow and a technical owner, so week-one intent plausibly drives both the connection and week-four retention. The selection is on intent, which no amount of post-hoc adjustment observes.
- Order the discriminating analyses by cost. First, condition on pre-connection activity by comparing retention within strata of week-one core-action count, which removes the crude version of the confound but not unobserved intent. Second, look for variation in integration availability that was unrelated to intent, such as a staggered release or an outage window. Third, an encouragement design that randomises a prompt to connect and reads the intent-to-treat effect on week-four retention, which is the only version that identifies an effect.
- Run the timing check, because it is nearly free and it is the most persuasive single piece of evidence. If the retention advantage among connectors is already visible before any of them connected, the causal story is largely finished.
- Pre-commit to what would change your mind and say it before you show anything: if the gap survives stratification and the encouragement arm moves week-four retention at all, the feature has a case and you will say so.
- Open the 1:1 by agreeing with the true part, that the correlation is real and worth chasing, then ask what effect size the roadmap plan assumes. That makes the size of the claim the topic instead of its authorship.
Follow-up
- The encouragement test needs six weeks and the roadmap locks in two. What do you recommend in the interim?
- Stratifying on week-one activity closes half the gap. What do you conclude, and what do you still not know?
- How would you word this in the roadmap document so the PM's original number is reframed rather than deleted?
Choose between three teams' requests with one analyst-week
You have one analyst-week. Three requests land the same morning. A growth team wants a paid-channel readout before a Friday spend decision. A billing team wants gross monthly revenue churn rebuilt, because the current figure recognises cancellation at canceled_at_utc rather than period_end_utc and is therefore wrong. A product team wants a dashboard for a feature launching in six weeks. All three sponsors are peers of your manager. Deliver your ranking, the explicit rule that produced it, and the message you send to the two teams you defer.
Approach
- Recognise what is being probed: whether you prioritise on decision value and reversibility or on who asked most recently and most loudly. The generic answer sorts by importance; the strong one states a rule, applies it, and accepts the ranking it produces even where that is uncomfortable.
- Score each request on three statable things: the decision it unblocks and the date that decision is made, the cost of being wrong in the meantime, and whether the work is one-off or compounding. A wrong published churn figure compounds, because it is quoted downstream and enters forecasts; the channel readout has a fixed date that cannot move; the dashboard has six weeks of slack.
- Notice the tension between value and urgency rather than resolving it by feel. The churn defect is the most valuable item and the least urgent one, which is exactly the shape of work that never gets done.
- Break the churn item in two. A one-hour severity check, sizing the gap between the two recognition points in MRR, is cheap enough to do before ranking anything and may promote the item outright. Do that first, then rank.
- Make the deferrals concrete. Each deferred team gets a date, a reason expressed as another team's decision deadline rather than as relative importance, and the smallest thing you can hand them immediately.
Follow-up
- The dashboard team escalates to your manager. What do you say in that conversation?
- Your severity check shows churn is overstated by 15%. Does the ranking change, and does anybody need to be told today regardless of the ranking?
- A fourth request arrives Wednesday with a Thursday deadline. What comes off the list, and who do you tell first?
Walk through an analysis you got wrong and what changed
Describe an analysis of yours that turned out to be wrong after somebody had already acted on it. You have four minutes. The account must name the defect mechanically, the join, the filter, the window or the identity key, rather than describing it as a communication problem. It must also say who did what because of the wrong number, how the error surfaced, how long it stood, and what control you put in place so that class of error cannot reach a decision again. Do not pick an error nobody acted on.
Approach
- Recognise what is being probed: whether you can be specific about your own failure without minimising it or performing contrition. The discriminator is whether the defect has a mechanism the listener could reproduce in their own warehouse.
- Choose the case by blast radius rather than by comfort. An error nobody acted on tests nothing, and picking one signals that you are managing the interview instead of answering it.
- Structure the account in six beats: the number, the decision it drove, the defect, the detection, the correction, the control. Keep the defect to one reproducible sentence, for example an inner join to fct_subscription_period that dropped accounts with no subscription row and so computed retention over payers only.
- State the direction of the bias, not only its existence. A filter or join that removes rows usually moves a metric predictably, and knowing which way shows you diagnosed the mechanism rather than patched the symptom.
- Be exact about detection and elapsed time. 'A colleague noticed' and 'the row-count assertion failed before publication' are different answers about the same organisation, and the second one is the one your control is supposed to produce next time.
- End on the control, its cost, whether it has fired since, and one thing it does not cover.
Follow-up
- What did the control cost, and has it fired since? If it never has, how do you know it works?
- How long did the wrong number stand before anyone questioned it, and what does that say about the review path it went through?
- What is the equivalent mistake you are most likely to make in this role, given the tables you would be working in?
- 01
A product manager proposes building a feature on the argument that accounts connecting an integration in week one retain three times better at week four. The figure is correctly computed from dim_user and fct_event, and it has already been shown to leadership. You have one scheduled 1:1 before the roadmap locks. Deliver the specific analysis you would run to test whether the relationship is causal, the result that would change your own mind, and how you open the conversation so that the PM is not put in the position of defending the number in public.
- 02
You have one analyst-week. Three requests land the same morning. A growth team wants a paid-channel readout before a Friday spend decision. A billing team wants gross monthly revenue churn rebuilt, because the current figure recognises cancellation at canceled_at_utc rather than period_end_utc and is therefore wrong. A product team wants a dashboard for a feature launching in six weeks. All three sponsors are peers of your manager. Deliver your ranking, the explicit rule that produced it, and the message you send to the two teams you defer.
- 03
Describe an analysis of yours that turned out to be wrong after somebody had already acted on it. You have four minutes. The account must name the defect mechanically, the join, the filter, the window or the identity key, rather than describing it as a communication problem. It must also say who did what because of the wrong number, how the error surfaced, how long it stood, and what control you put in place so that class of error cannot reach a decision again. Do not pick an error nobody acted on.
Are these confirmed Zuora interview questions?
No. Every prompt here is an original PracHub practice exercise written for the Data Scientist role and for product and business analytics data problems. This guide does not claim to reproduce Zuora's interview questions, rounds or hiring timeline. Confirm the actual format, team and scope with your recruiter.
PracHub Data Scientist practice ↗How should I handle probability and brainteaser questions?
Set up the sample space explicitly before computing anything, and say what you are conditioning on. Most errors come from an unstated independence assumption or from swapping P(A|B) and P(B|A). If the exact answer gets messy, describe the simulation that would produce it. The recurring shapes are conditional probability, expected value via linearity, waiting times, and collision or birthday problems. A setup the interviewer can follow matters more than speed.
PracHub Data Scientist practice ↗Do I need deep learning for a product data scientist role?
Depth in experimentation, causal inference and metric design pays far more than neural network internals. Know enough to say when a learned model beats a heuristic, what overfitting looks like, and how you would evaluate a classifier and choose a threshold. If the posting names ranking, recommendations or forecasting, go one level deeper on that family. The job description is usually an honest signal about which half of the field is being tested.
PracHub Data Scientist practice ↗How should I open an ambiguous case question?
Spend the first two minutes narrowing rather than solving. Restate the question in your own words, ask what decision the answer feeds, then state the scope you are choosing and the assumptions you are making. Say your plan out loud before executing it. Most case failures are a competent answer to a question nobody asked. Write the scoped question down so both of you can check the final answer against it.
PracHub Data Scientist practice ↗Sources & methodology 2 sources ↗
Official role evidence, timestamped platform data and clearly labeled preparation advice.
- 01PracHub Data Scientist practice ↗
Cross-company practice questions for this role. Not an employer question record.
platform · Accessed 2026-09-22 - 02PracHub interview preparation framework ↗
The shared preparation framework these checkpoints and the seven-day plan follow.
platform · Accessed 2026-09-22
Explore related engineering environments and compare the preparation focus.
Zoox Data Scientist Interview Questions & Guide 2026
Case practice for the Zoox data scientist interview, where the prompt is deliberately underspecified and your first move is scoping it into something measurable.
ZoomInfo Data Scientist Interview Questions & Guide 2026
Product-sense practice for a ZoomInfo data scientist interview: define a product and business analytics metric, pick its guardrails, and argue why a tempting alternative would mislead you.
Zoom Communications Data Scientist Interview Questions & Guide 2026
SQL sets for data scientist prep ahead of Zoom Communications, covering window functions, funnel and retention queries, and the joins that quietly drop the rows you needed.
Zomato Data Scientist Interview Questions & Guide 2026
SQL sets for data scientist prep ahead of Zomato, covering window functions, funnel and retention queries, and the joins that quietly drop the rows you needed.