Hilti Group Data Scientist Interview Questions & Guide 2026

The 60-second brief

A Data Scientist at Hilti Group plays a pivotal role in bridging the gap between complex industrial data and actionable business strategy. As a global leader in construction technology, Hilti Group generates vast amounts of information across its supply chain, digital tool services, and direct-to-customer sales channels. You will be responsible for transforming this data into insights that optimize internal operations, enhance product performance, and improve the overall customer experience.

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

Focus areas

• Separate novelty effects from durable behaviour change
• Pick a randomisation unit that respects interference
• Turn a vague request into a measurable question

Overview

This role is both technically rigorous and highly collaborative. You will work alongside product managers, software engineers, and domain experts to solve real-world challenges, such as predictive maintenance for tools or optimizing logistics networks. Because Hilti Group values precision and long-term reliability, your work must be grounded in robust statistical methods and clear, communicable results. You are not just building models; you are building the intelligence that drives one of the most respected brands in the construction industry.

The Hilti Group interview process

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

Initial 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

Technical Assessment

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

• Whether your row counts survive each join, and whether you notice on your own when they do not
• Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
• Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
• Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

• Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
• Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
• Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Behavioral Assessment

This round decides whether you owned a decision or watched one happen nearby. Interviewers for data roles listen for the point where the analysis stopped being a report and started changing what someone did, so build each story around that hinge: what was going to happen by default, what you found, and what happened instead. The most common weakness is a story that ends at delivery. If you can name the decision your work changed and the number that moved because of it, most follow-ups become easy.

What to demonstrate

• Whether the decision was yours to influence, or whether you are narrating a team outcome in the first person
• The counterfactual: what would have been done without your analysis, and why that default was worse
• How far your involvement ran past the handoff, and whether you checked that the change did what you predicted

How to prepare

• Pick three projects and write one sentence for each naming the decision-maker, the choice in front of them, and what they chose after seeing your work. If you cannot name a person and a choice, the story is not ready for this round.
• Reconstruct the baseline for your strongest project from the original query or dashboard rather than memory, so the before-number survives a follow-up asking where it came from.
• Prepare an honest version of a project where your recommendation was overruled, including what you did with the analysis afterwards.

Two-Way Conversation

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

What to demonstrate

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

How to prepare

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

Where candidates lose points

Reading a pooled rate that moved because the mix moved, not because any behaviour changed

A pooled conversion rate is a weighted average, and a shift in the weights can move it in the opposite direction to every one of its parts. A paid campaign that brings low-converting traffic drops overall signup conversion even if desktop, mobile web and app conversion each rose that week, which is Simpson's paradox and it is the single most common cause of an inexplicable dashboard move. The discipline is to decompose before explaining: recompute the rate holding last period's segment weights fixed, and compare that counterfactual to the actual, so the mix effect and the rate effect are separated numerically rather than argued about. Segment on the dimensions that actually reweight, which in this domain are almost always device_type, referrer_channel, country and new versus returning.

Slicing a flat experiment until a segment reaches significance

Testing one metric across twenty segments at a nominal 5% level produces a significant result about two thirds of the time when nothing is happening anywhere, and the segment that surfaces is by construction the one with the most favourable noise. The reported effect in that slice is then badly overstated, because selection on significance conditions the estimate on being large. What makes it dangerous rather than merely wrong is that a post-hoc segment always has a plausible story attached, so it survives the meeting. The controls are declaring the small number of segments of interest before launch, correcting across the ones tested, and treating anything discovered afterwards as a hypothesis that needs its own adequately-powered test rather than a finding.

Explaining an aggregate move without decomposing the mix shift

Split the change in the aggregate into within-segment movement and movement in segment weights before you explain it. Every segment's rate can fall while the overall rate rises, purely because volume shifted toward segments that already had higher rates.

Reading a dozen metrics with no multiplicity control

Nominate one primary metric before launch and treat the rest as guardrails or exploratory, with Bonferroni or Benjamini-Hochberg applied when you intend to make claims from them. Twenty independent tests at 0.05 under the null produce at least one false positive about 64 percent of the time.

Questions & practice

Permutation test for a difference in conversion rates

Medium · permutation test · resampling · inference

Write a two-sided permutation test from scratch for a difference in conversion rates, using no scipy hypothesis function. Input: a DataFrame with unit_id, variant in {control, treatment} and converted in {0,1}, one row per randomisation unit. Compute the observed difference in proportions, then build the null distribution by reshuffling the variant labels while holding each arm's size fixed. Report the p-value as (1 + the count of permuted statistics at least as extreme in absolute value) / (B + 1) with B at least 10,000, and return the permutation distribution.

Approach

1. Name the null being tested: the sharp null that each unit's outcome is the same under either label. That is what licenses permuting labels, and it is stronger than the null of equal means, which matters when someone asks whether the test is valid under unequal variances.
2. Extract converted to a single numpy array of 0s and 1s and record n_treatment. Every permutation is then just a reshuffle of one array, and the treatment mean is the mean of the first n_treatment entries of the shuffled array.
3. Vectorise the B permutations with rng.permuted on a tiled 2-D array, or with argsort of a (B, n) random matrix. A Python loop calling np.random.shuffle B times is correct but roughly an order of magnitude slower and often runs past the time limit.
4. Use the +1 correction in both numerator and denominator. Without it a p-value of exactly 0 is reportable, which is false: the observed labelling is itself one of the permutations, so the smallest attainable p-value is 1/(B+1).
5. Compare the resulting p-value against a two-proportion z-test as a sanity check. At these sample sizes they should agree closely; a large divergence means the statistic or the shuffle is wrong, not that the permutation test found something subtle.

Worked solution (25 min)

1. y = df['converted'].to_numpy(); n_t = (df['variant'] == 'treatment').sum(); obs = y[treat_mask].mean() - y[~treat_mask].mean().
2. Build the null: for B draws, shuffle y and take the mean of the first n_t entries minus the mean of the rest.
3. p = (1 + (np.abs(null) >= abs(obs) - 1e-12).sum()) / (B + 1), with the small tolerance so exact ties count as at least as extreme.
4. Return obs, p and the null array; plot or describe the null to confirm it is centred at 0.

Expected result: The observed difference, a p-value in [1/(B+1), 1], and a null distribution whose mean is approximately 0 and whose standard deviation approximately matches the analytic pooled standard error sqrt(p_pool*(1-p_pool)*(1/n1 + 1/n2)).

Verify it yourself

• The null distribution's mean is within a few Monte Carlo standard errors of 0; if it is centred on the observed difference, the code bootstrapped instead of permuted.
• The null's standard deviation agrees with the analytic pooled standard error to within a couple of percent.
• The p-value agrees with a hand-computed two-proportion z-test p-value to within about 0.01 at these sample sizes.
• Setting converted identical in both arms by construction yields a p-value near 1, and a planted large effect yields the floor of 1/(B+1).

Follow-up

• The arms are 200 and 20,000 units. Does the permutation test stay valid, and what happens to its resolution at B = 10,000?
• Give a 95 percent confidence interval for the difference. Can you get it from this permutation distribution, and if not, what would you run instead?
• The randomisation unit is user_id but the outcome is per session. What breaks, and what is the fix?

Cluster bootstrap for a per-session rate randomised on users

Hard · cluster bootstrap · randomisation unit · variance

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

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

Implement seven-day activation from its written definition

Medium · metric definition · cohorts · pandas · joins

Implement the seven-day activation rate. Inputs: dim_user with user_id, account_created_at_utc and is_internal; fct_event with user_id, occurred_at_utc and is_core_action. A user activates when core-action events carrying a non-NULL user_id fall on at least two distinct UTC dates inside [account_created_at_utc, account_created_at_utc + 7 days). The denominator is every non-internal user whose account_created_at_utc lands in the cohort week, including users with no events at all. Return one row per cohort week with numerator, denominator and rate, publishing only weeks whose last signup is at least eight days old.

Approach

1. Build the denominator first, from dim_user alone, filtered on is_internal = False. Deriving it from the join is the standard way to lose every user who never fired an event, which is exactly the population the metric is about.
2. Join events to users on user_id with a left join from the user side, then apply the window as a half-open interval: occurred_at >= created AND occurred_at < created + 7 days. The right bound is exclusive, so an event at exactly created + 7 days does not count.
3. Count distinct UTC dates per user, not distinct events. Floor occurred_at_utc to date before the nunique, and do it in UTC rather than local time so the threshold does not move with the user's country.
4. Apply the >= 2 threshold, aggregate to cohort week, and compute the rate by re-summing numerator and denominator per week rather than averaging any per-user or per-day rate. Fix the week anchor explicitly: cohort_week is the Monday of the signup week in UTC, which is what Postgres DATE_TRUNC('week') returns and what any SQL version of this metric will produce. In pandas, subtract dt.weekday days from the floored timestamp. If you reach for periods instead, the anchor that matches is to_period('W') (equivalently 'W-SUN'), whose weeks end Sunday and therefore start Monday; to_period('W-MON') labels weeks that end on Monday, so it runs Tuesday through Monday and its start_time is a Tuesday. Mixing the two shifts every cohort label by one day and silently moves Mondays into the previous week.
5. Suppress immature weeks: drop any cohort week whose maximum account_created_at_utc is within 8 days of the data cut, and return them as absent rather than as a partial number.

Follow-up

• The threshold is 2 distinct days. What changes in the reported history if someone moves it to 3, and how would you publish that change?
• Invited seats and SSO-provisioned users have no pre-signup session. Should they be in this denominator at all, and what does including them do to the rate for sales-assisted accounts?
• How would you produce the same metric at account grain, and which of the two would you put on the dashboard?

How would you use SQL window functions to calculate a running total or a moving average?

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. Say which table is the grain you start from, and join outward from it.

Follow-up

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

Given a table of transactions, how would you find the time difference between consecutive purchases?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Weekly visit-to-signup conversion split by acquisition channel

Easy · funnel · conversion · aggregation

From fct_session (session_id, visitor_id, started_at_utc, referrer_channel, is_bot_flagged, consent_state) and fct_event (visitor_id, occurred_at_utc, event_name), compute visit-to-signup conversion for one ISO week, split by channel. session_id is the unique key of fct_session. Denominator: distinct visitor_id with a session starting in the week, is_bot_flagged = FALSE and consent_state <> 'denied'. Numerator: those visitors with a 'signup_completed' event in the same week. Label each visitor with the referrer_channel of their first session in the window. Return channel, visitors, signups and rate, plus one all-channel total row.

Approach

1. Build a visitor spine that is one row per visitor: filter sessions to the week, drop is_bot_flagged and consent_state = 'denied', then take the first session per visitor with ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY started_at_utc, session_id) = 1 to carry the channel label. Collapsing to one row here is what makes the channel buckets mutually exclusive and the totals additive.
2. session_id is the unique key, so that ordering is total and the label is reproducible. If the table carried no unique key you would have to write an explicit tie rule instead, because two sessions on different channels at the identical timestamp would otherwise label the visitor differently between runs.
3. Attach the outcome as a semi-join (EXISTS on a signup_completed event for that visitor inside the same week) rather than a join to the event table, so a visitor who fires the event twice does not count twice and inflate the numerator past the denominator.
4. Aggregate with COUNT() as visitors and COUNT() FILTER (WHERE signed_up) as signups, and compute the rate as signups::numeric / NULLIF(visitors, 0) so an empty channel returns NULL rather than a division error.
5. Produce the total with GROUP BY GROUPING SETS ((channel), ()), which re-sums numerator and denominator for the total row. Averaging the channel rates gives a different and wrong number whenever channel volumes differ, which they always do.
6. Verify the spine before trusting the output: COUNT(*) must equal COUNT(DISTINCT visitor_id), and the per-channel visitor counts must sum to the total row.

Worked solution (20 min)

1. Write the filtered session CTE and check its row count against an unfiltered count, so you know how much volume the bot and consent filters removed.
2. Add the ROW_NUMBER first-session pick and assert one row per visitor.
3. Add the EXISTS outcome flag and aggregate with FILTER.
4. Add GROUPING SETS for the total and format the rate to four decimal places.
5. Spot-check one channel by hand: pull its visitor list, count signups directly, compare.

Expected result: One row per referrer_channel plus a total row, with visitors, signups and a decimal rate. Channels with zero signups appear with rate 0, not missing. The total rate lies strictly between the minimum and maximum channel rate unless one channel holds all the volume.

Verify it yourself

• Per-channel visitor counts sum exactly to the total row's visitor count.
• COUNT(*) equals COUNT(DISTINCT visitor_id) on the spine CTE.
• Replacing the EXISTS with a plain inner join changes the signup count only if duplicate signup_completed events exist; if it does change, say by how much.
• Every rate is between 0 and 1.
• Re-running the first-session pick with session_id removed from the ORDER BY returns the same labels; if it does not, visitors hold same-timestamp sessions on different channels and the tie rule is doing real work.

Follow-up

• The denominator is distinct visitors. If a browser release shortens cookie lifetime, what happens to this rate, and how would you tell that apart from a genuine drop?
• A visitor's first session is direct and their signup session is paid search. Your label says direct. When is that the wrong answer for the decision being made?
• How do you roll four weeks into a month, and why is averaging the four weekly rates wrong?

How do you balance short-term optimization with long-term product health?

Medium · product sense and metrics

Approach

1. Fix the population and the time window before naming any metric.
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?
• What would you do if the primary metric and the guardrail moved in opposite directions?

How do you prioritize tasks when you have competing requests from multiple teams?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Fix the population and the time window before naming any metric.
3. Decompose the metric into the rates that drive it, and say which one you would check first.

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?

How would you design the key performance metrics for a new IoT-enabled power tool?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Fix the population and the time window before naming any metric.
3. Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

• Which segment would you cut first, and what would that rule out?
• What would you do if the primary metric and the guardrail moved in opposite directions?

If you observe an unexpected metric drop after a product launch, what is your step-by-step diagnostic process?

Medium · product sense and metrics

Approach

1. Decompose the metric into the rates that drive it, and say which one you would check first.
2. Restate the decision this analysis has to support, and who acts on the answer.
3. Fix the population and the time window before naming any metric.

Follow-up

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

How do you account for novelty effects or sample ratio mismatch in your experiments?

Medium · a/b testing and experimentation

Approach

1. State the primary metric and the minimum effect worth shipping, then size the test.
2. Decide the analysis before seeing data, including how long it runs and when you look.
3. Say whether units interfere with each other, and switch design if they do.

Follow-up

• How would you handle interference between treated and control units?
• What would you conclude if the result is positive but the test is underpowered?

How do you determine the statistical significance of an A/B test result?

Medium · a/b testing and experimentation

Approach

1. State the primary metric and the minimum effect worth shipping, then size the test.
2. Say whether units interfere with each other, and switch design if they do.
3. Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

• What would you do if you could not randomise at all?
• What would you conclude if the result is positive but the test is underpowered?

Measure a paid channel without a user-level experiment

Hard · difference-in-differences · geo holdout · incrementality · clustered inference

Finance needs the incremental contribution of paid social before next year's budget. It carries 18% of last-touch-attributed signups in dim_user.last_touch_channel, but the ad platform will not randomise users and unexposed users are unobservable. The ad platform targets at market level, your warehouse resolves a market label onto fct_session, and 40 markets are eligible with 12 months of weekly signups derivable from dim_user. Design the read, size it on total signups rather than attributed ones, and state the identifying assumption and the check that would falsify it.

Approach

1. Say why the attributed number cannot answer the question. Last-touch assigns full credit to whichever touch was recorded nearest the conversion, which systematically rewards channels that intercept demand that already existed, and the counterfactual of not running the channel is nowhere in the touchpoint data.
2. Choose a geo holdout: randomise markets into a paused arm and a spend-as-usual arm, and measure total signups per market. The metric has to be the total, because the question is whether demand is created or merely relabelled, and attributed signups fall to zero in a paused market by construction.
3. Size on markets, since the market is the randomisation unit and therefore the variance unit. Forty markets give at most twenty per arm, so form matched pairs on twelve months of pre-period weekly signups and randomise within pair, which removes the between-market level differences that would otherwise dominate the residual variance.
4. Fix the identifying assumption in plain words: absent the pause, treated and control markets' signup trends would have moved in parallel. Falsify it by running the same estimator on placebo cut dates inside the pre-period; a non-zero placebo estimate or visibly diverging pre-trends kills the design as specified.
5. Estimate with market and week fixed effects and cluster standard errors on market. Below about forty clusters the cluster-robust error is biased downward, so use a wild cluster bootstrap or randomisation inference over the pair assignment. If only one or two markets can be paused, switch to synthetic control: weight untreated markets to match the treated market's pre-period path and use in-space placebos for inference.

Worked solution (40 min)

1. Aggregate weekly signups per market for twelve months from dim_user with is_internal = FALSE, and drop markets whose history is too short or too volatile to pair.
2. Rank markets on pre-period mean and trend, form twenty matched pairs, and randomise one of each pair into the paused arm.
3. Derive the MDE from the between-pair residual standard deviation of weekly signups, not the within-market one, because power is set by the number of pairs; express it as a percentage of total signups and compare it with the effect finance cares about.
4. Run placebo estimates at three pre-period cut dates before committing; re-pair or abandon if any is significant.
5. After the pause, estimate difference-in-differences with market and week fixed effects, report a wild cluster bootstrap interval, and convert the point estimate into incremental cost per signup against the spend withheld.

Expected result: A matched-pair geo holdout across twenty pairs, read on total signups with market and week fixed effects and market-clustered inference, yielding an incrementality ratio of incremental signups to attribution-credited signups. The identifying assumption is parallel counterfactual trends, checked with pre-period placebo cuts. The MDE is set by twenty pairs rather than by millions of sessions, and is usually far coarser than the requester expects.

Verify it yourself

• Pre-period placebo estimates should centre on zero with intervals covering it. A systematically non-zero placebo means the pairing failed and the main estimate inherits that bias.
• Treated plus control market signups in the pre-period must reconcile with the total the finance dashboard reports, otherwise the market label is silently dropping sessions.
• An incrementality ratio above 1 implies the channel drives conversions it never touched. That is possible through view-through effects the model never sees, but it demands a stated mechanism rather than a shrug.

Follow-up

• Campaign commitments mean the pause starts in different weeks in different markets. What does staggered timing do to a two-way fixed effects estimate when effects vary across cohorts?
• How long do you pause, given that paid social has a delayed conversion tail, and how do you keep that tail out of the pre-period?
• The estimate says the channel is 40% incremental. What happens to the last-touch number on the dashboard after that?

Attribute a flow-completion drop to one client build

Medium · release-regression · instrumentation · staged-rollout

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

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

Suggested practice checklist

For someone who can already write the query and train the model but stalls when asked what to measure or whether a change is worth making. Metric definition and case structure come first; the technical work is kept as maintenance rather than the centre of the week.

Day 1 — Metric anatomy

• For three products you use daily, write one primary metric, two input metrics that plausibly move it, and one guardrail that would catch a cheap way of moving the primary at the cost of the product.
• For one of them, specify the metric precisely enough that two analysts would return the same number: numerator, denominator, unit of observation, time window, and how returning and deleted accounts are treated.
• Pick a ratio metric and write what happens to it when the denominator shrinks for reasons unrelated to the numerator, with a concrete example of that happening.

Deliverable: A one-page metric tree for one product, with the primary metric written as an unambiguous spec.

Day 2 — Diagnosing a drop without guessing

• Take the prompt "weekly active users fell 8 percent week over week" and write the segmentation plan before proposing any cause: platform, region, tenure cohort, acquisition channel, and whether the movement sits in the numerator or in a changed denominator.
• List the instrumentation failures that manufacture fake drops (a client release that stopped firing an event, a bot filter change, a shifted date boundary or timezone) and write the query that rules out each one.
• Rehearse stating the boring explanations first, seasonality and day-of-week composition, before reaching for a product cause.

Deliverable: A drop-diagnosis checklist short enough to recite from memory in under a minute.

Day 3 — Should we build it

• Take a feature idea and write it as a bet: what you believe is true, what would have to be true for it to pay off, the metric that would confirm it, and the effect size that would justify the engineering cost.
• Size the opportunity top-down and bottom-up, then reconcile the two numbers in writing instead of quoting whichever is friendlier.
• Write the counter-metric that would make you kill the feature even if it wins on the primary metric.

Deliverable: A one-page product memo ending in a decision rather than a list of considerations.

Day 4 — The places aggregate numbers lie

• Construct a Simpson's paradox numerically: two segments where the treatment wins within each segment yet loses overall, and identify the shift in segment weights that causes it.
• Take a heavy right-tailed quantity such as revenue per user and write why the mean is the wrong summary, which percentile you would report instead, and what a moving mean with a stable median tells you.
• Write your definition of a session for the product from day one, then name two real behaviours it misclassifies.

Deliverable: One page holding a worked Simpson's paradox table and a session definition with its two known failure cases.

Day 5 — Technical maintenance, aimed at metrics

• Solve four timed SQL prompts that all end in a ratio metric, so the question of grain stays live in every answer.
• Compute a 95 percent confidence interval for a proportion on a small sample, and state why the normal approximation is unreliable when either np or n(1 minus p) falls below roughly 10, along with which interval you would use instead.
• Take one metric from your day-one tree, write the query that computes it correctly, then write the query that computes it wrong in the most plausible way and explain how you would notice.

Deliverable: Four solved prompts plus a matched correct and plausible-wrong query for one metric.

Day 6 — Turning engineering work into data science stories

• Write three project stories as situation, decision, trade-off, outcome, each carrying one number and one thing you got wrong.
• For the story you will lead with, prepare an answer to "what would you do differently" that names a decision you made, not a constraint you were handed.
• Practise the sentence that reframes a systems project as a question project: the question the work answered, ahead of the pipeline it shipped.

Deliverable: Three written stories with the lead story delivered aloud and timed under four minutes.

Day 7 — Mock case and gap list

• Run a 40-minute mock case with someone playing a product manager who pushes back on your metric choice, and record it.
• Listen back and mark every moment you proposed a solution before the success metric existed.
• Rewrite those moments as the question you should have asked, and rehearse the first 90 seconds of the case until scoping comes before solving.

Deliverable: A recorded case plus a rewritten opening 90 seconds.

Ownership when the result is inconvenient

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.

How do you handle missing values or nulls in a large dataset using SQL?

Medium · behavioural and stakeholder questions

Approach

1. Quantify the outcome, including what you would not claim credit for.
2. Pick a story where you drove the decision, not one where you observed it.
3. Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

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

Choose between three teams' requests with one analyst-week

Medium · prioritisation · stakeholder · judgement

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

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

Disagree with a product manager's roadmap claim using data

Medium · causal inference · selection bias · stakeholder

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

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