GHD Data Scientist Interview Questions & Guide 2026

The 60-second brief

At GHD, the Data Scientist role sits at the intersection of complex engineering challenges and high-impact business decision-making. You will be responsible for translating raw data into actionable insights that drive efficiency and innovation across a global professional services firm. Whether you are working on simulation models, predictive maintenance, or operational optimization, your work directly informs how GHD delivers value to its clients in the engineering, architecture, and construction sectors.

If the team owns experimentation, expect depth past a two-sample test: minimum detectable effect and its roughly inverse-square-root dependence on sample size (holding power, significance level and variance fixed), variance reduction from pre-period covariates, interference between units, and when a sequential design is the right call.

Focus areas

• Separate novelty effects from durable behaviour change
• Pick a randomisation unit that respects interference
• Decompose a metric move by segment and mix

Overview

This role is critical because it requires more than just technical proficiency; it demands a product-centric mindset. You will often operate in ambiguous environments, identifying which metrics truly move the needle and designing experiments to validate your hypotheses. Success at GHD means you are as comfortable discussing the nuances of SQL window functions with a data engineer as you are explaining the business impact of an A/B test result to non-technical stakeholders.

The GHD interview process

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

HR Screening

A screening call is a matching exercise run by someone who will not evaluate your statistics. They are checking that the work described on your resume is work you personally did, and that its scope matches the level the role is written for. Logistics get settled in the same half hour so nobody spends an interviewer's afternoon on a mismatch. The answer that fails is the one narrated in the plural. If every sentence is 'we built' and 'the team decided', there is nothing specific to write down about you. Name the piece that was yours, the decision you made inside it, and what changed after.

What to demonstrate

• Whether the ownership implied by your resume survives one round of follow-up about who actually did which part
• Whether your described scope (data size, stakeholders, what shipped) matches the seniority the role is written at
• Whether timeline, location and compensation expectations make the rest of the loop worth scheduling

How to prepare

• Rewrite your top three resume bullets in the first person singular, each with the decision you made and what moved afterwards, then say them out loud once so the 'we' does not return under pressure
• Attach one number to each project: the baseline, the change, and the window it was measured over. Where impact was never measured, say that plainly rather than inventing a figure
• Settle your compensation range before the call and give it as a range with a reason behind it, such as current total comp or a competing timeline, instead of deflecting the question twice

Technical Rounds

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 Rounds

Behavioural answers from data candidates get audited in a way that answers from other roles do not. When you say a model lifted retention, the next question is the denominator, the window, and how you knew the lift was not seasonal. So attach the measurement to each claim while you tell it: what the metric was before, over what period, and against what comparison. Numbers with no baseline read as rounded-up memory, and one unsupported figure tends to make the rest of the story sound rehearsed.

What to demonstrate

• Whether each impact number arrives with a baseline, a window and a comparison, or as a bare percentage
• Whether you can name the method that attributed the effect to your work (an experiment, a staged rollout, a seasonal control) or concede the link was correlational
• Whether the magnitudes stay internally consistent when the interviewer multiplies them against the scale you described earlier

How to prepare

• For each story, write the impact line as metric, value before, value after, window, and how attribution was established. Any line missing two of those five is a follow-up you will answer badly.
• Re-derive one headline number from the source table rather than the deck that reported it. Resume numbers drift upward across retellings.
• Decide in advance which figures you cannot share, and prepare the ratio or relative change you can give instead, so a confidentiality limit does not read as evasion.

Business Case Discussions

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

What to demonstrate

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

How to prepare

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

Where candidates lose points

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.

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.

SQL that silently fans out on a one-to-many join

State the grain of each table and the grain you want in the result before writing the join. Pre-aggregate the many side to the join key, or use EXISTS or a window function, and verify with a row count against COUNT(DISTINCT id) rather than trusting that the numbers look plausible.

Ending an analysis without a recommendation or next step

Close with what you would do and what would change your mind, stated as a condition you can check later. If the evidence is genuinely inconclusive, recommend the specific next measurement and say what it costs in time or exposure.

Questions & practice

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?

Sessionise an event stream with gap and midnight rules

Hard · sessionisation · vectorisation · event streams

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

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

1. df = df.sort_values(['visitor_id','occurred_at_utc','event_id']).reset_index(drop=True).
2. 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()).
3. is_start = new_visitor | (gap > Timedelta(minutes=30)) | new_day; df['session_key'] = is_start.cumsum().
4. 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')).
5. 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.

Expected result: One row per session where session_count equals is_start.sum(), event_count sums exactly to len(input), every session's started_at and ended_at share one UTC date, and duration_seconds is at least 0 and strictly less than 86400.

Verify it yourself

• out['event_count'].sum() equals len(df); a mismatch means rows were lost to a join or a dropna.
• started_at_utc.dt.date equals ended_at_utc.dt.date on every row.
• For a planted visitor with events at 12:00:00 and 12:30:00 exactly, one session is emitted; move the second to 12:30:01 and two are emitted.
• For a planted visitor with events at 23:59:50 and 00:00:10, two sessions are emitted on consecutive dates despite a 20-second gap.

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?

Simulate the false positive cost of repeated peeking

Medium · simulation · peeking · type i error

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

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

How would you identify duplicate records in a large dataset using SQL?

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. State the window function and its partition and ordering out loud before writing it.
3. Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

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

Write a query using SQL window functions to calculate a rolling average of project costs over the last six months.

Medium · sql and data manipulation

Approach

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

Follow-up

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

Attribute account MRR to first-touch channel without seat fan-out

Medium · fan-out · joins · revenue · attribution

dim_user holds user_id, account_id, first_touch_channel, account_created_at_utc. dim_account holds account_id, lifecycle_status. fct_subscription_period holds account_id, period_start_utc, period_end_utc, period_status, mrr_cents_constant_fx. For a given as-of date, report active MRR by the first-touch channel of the account's earliest-created user. An account has many users and many periods. The channel totals must sum exactly to total active MRR on that date, and accounts whose label is NULL must appear as their own bucket.

Approach

1. Reduce the label side to one row per account before any join: ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY account_created_at_utc, user_id) = 1 over dim_user, carrying first_touch_channel. Joining dim_user to the period table directly repeats each revenue row once per seat.
2. Reduce the money side to one row per account too: the period where period_start_utc <= :as_of AND period_end_utc > :as_of AND period_status IN ('active','past_due'). If more than one row survives that filter the account holds overlapping subscriptions, which must be deduped or deliberately summed, and either way the choice is stated rather than left to the join.
3. Join the two one-row-per-account CTEs and SUM(mrr_cents_constant_fx). Use the constant-FX column, since the same query run in two quarters otherwise reports currency moves as channel performance.
4. Bucket NULL first_touch_channel as 'unattributed' with COALESCE instead of letting it drop. Roughly the share of users with no observed touch is not small, and dropping it understates the total and inflates every named channel's share.
5. Reconcile: the SUM over the joined result must equal the SUM over the period CTE alone. That single equality check catches fan-out immediately, which is why it is worth writing before presenting the number.

Worked solution (30 min)

1. Compute total active MRR on the as-of date from the period table alone and write the number down as the target.
2. Build the label CTE and assert COUNT(*) = COUNT(DISTINCT account_id).
3. Build the as-of period CTE and assert the same, investigating any account with more than one surviving row.
4. Join, COALESCE the channel, aggregate, and compare the total to the target.
5. Deliberately run the naive dim_user join and record the inflation factor, which is the MRR-weighted mean seat count.

Expected result: One row per first_touch_channel plus an 'unattributed' bucket, whose MRR values sum exactly to the independently computed total active MRR for the as-of date.

Verify it yourself

• Channel total equals the standalone total from the period CTE, to the cent.
• Both CTEs satisfy COUNT(*) = COUNT(DISTINCT account_id).
• The unattributed bucket is present and non-zero rather than silently absent.
• The naive join version produces a strictly larger total, and you can state the ratio.

Follow-up

• Which user should own the account's label: earliest created, the 'owner' role, or the one who converted? What does each choice systematically favour?
• What does this table entitle you to say about the value of a channel, and what does it not?
• Some accounts have a parent_account_id. How does rolling those up change both the numerator and the channel mix?

If a primary engagement metric drops by 10% overnight, how would you go about diagnosing the root cause?

Medium · product sense and metrics

Approach

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

Follow-up

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

How do you prioritize your work when you have multiple competing requests from different project leads?

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. State what result would change your recommendation, so the answer is falsifiable.
3. Fix the population and the time window before naming any metric.

Follow-up

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

How do you balance short-term metric optimization with long-term user value?

Medium · product sense and metrics

Approach

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

Follow-up

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

What key product metrics would you prioritize if we launched a new digital service for our clients?

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. Name one primary metric, then the guardrail that stops it being gamed.

Follow-up

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

What are the most common experimentation pitfalls you have encountered in your previous roles?

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. 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?
• How would you handle interference between treated and control units?

If you have a low sample size, how do you adjust your statistical approach to ensure validity?

Medium · a/b testing and experimentation

Approach

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

Follow-up

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

Define success for a rebuilt first-run onboarding checklist

Easy · activation · metric tree · cohorts

A rebuilt first-run checklist ships to all new signups next month. You have dim_user (user_id, account_created_at_utc, signup_surface, is_internal) and fct_event (user_id, occurred_at_utc, is_core_action, event_name, surface). Propose a primary metric with an explicit numerator, denominator, window and publication lag, plus two guardrails and one diagnostic you would refuse to treat as success. The team wants a number it can read weekly, and the constraint is that everything must be computable from those two tables alone. Deliver the metric tree from the north star down to the metric you chose.

Approach

1. Fix the grain and the cohort key first: the checklist is seen by users, so count on user_id with account_created_at_utc as the cohort key, and say out loud that the north star is account-grained so the tree crosses grains here deliberately rather than by accident.
2. Take seven-day activation as the primary — core action on at least 2 distinct UTC dates inside [account_created_at_utc, +7 days) — because the two-distinct-days predicate cannot be satisfied by the single checklist-completion click the feature itself produces.
3. Demote checklist completion rate to a diagnostic and give the reason: it is an output of the feature, so it is near-perfectly correlated with having shipped the feature and cannot fall when the feature is bad.
4. Pick guardrails by the failure each one catches, not by what is easy to query: week-4 signup-cohort retention catches an activation gain that does not persist, and p50 minutes to first core action catches a checklist that adds steps to a path users already completed.
5. State the operational rules explicitly: is_internal = FALSE, an 8-day publication lag, and signup_surface values 'invite' and 'sso_provisioned' split out because those users arrive through an administrator rather than a self-serve signup and may not be shown the checklist at all.

Worked solution (20 min)

1. Write the primary in full: numerator = cohort users with is_core_action = TRUE events on at least 2 distinct UTC dates in [account_created_at_utc, +7 days); denominator = all dim_user rows in the cohort week with is_internal = FALSE.
2. Draw the tree downward: weekly active accounts completing a core action, then seven-day activation rate multiplied by weekly signups, then checklist step completion and time-to-first-core-action labelled as diagnostics.
3. Attach the timing rules: 8-day lag, and a cohort with fewer than seven full elapsed days published blank rather than partial.
4. Attach each guardrail to its named failure mode in one line each, so the guardrail list reads as a list of specific risks rather than a list of metrics.
5. Write the exclusion rule and the surface split, and state what you would do if 'invite' users turn out to be a third of the cohort.

Expected result: A primary metric stated as numerator, denominator, window, lag and exclusions; two guardrails each tied to a named failure mode; and checklist completion explicitly demoted to a diagnostic with the reason given.

Verify it yourself

• The denominator includes cohort users who never returned. If it is built from users with any event, the metric is conditioned on survival and will read several points too high.
• Recomputing a cohort's activation rate three days after first publication must return the same number. If it moves, the right-hand window bound or the late-arrival rule is missing.
• The per-surface cohort counts must sum to the unsplit cohort count, so confirm signup_surface IN ('invite','sso_provisioned') is broken out rather than dropped. Note the limit while you are there: neither dim_user nor fct_event carries a pre-signup identifier, so whether those users were ever shown the checklist is not answerable from these two tables — the surface split is the only handling available, and the metric definition has to say that rather than imply exposure was verified.

Follow-up

• Activation rises three points but week-4 retention is flat. What do you tell the team, and what would you need to distinguish a real gain from pulled-forward activity?
• The checklist ships on web only. What changes in the denominator, and what breaks if you leave every surface in?
• Signup mix shifted toward 'invite' the same week. How would you show whether the activation move was mix or behaviour?

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.

What the behavioural round is actually scoring

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

Describe a time you had to explain a complex technical concept to a stakeholder who did not have a data background.

Medium · behavioural and stakeholder questions

Approach

1. Name the disagreement or constraint, and how you resolved it with evidence.
2. Close with what you would do differently, concretely.
3. State the situation in two sentences and spend the rest on your reasoning.

Follow-up

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

Handle a request for numbers supporting a decision already made

Hard · integrity · framing · stakeholder

A senior leader has already decided to sunset a plan tier and asks you for the analysis showing it is the right call. Accounts on that tier carry 6% of MRR at constant FX and have the highest licensed-seat utilisation in the book. The leader's support matters to your next review cycle, and the decision is being presented in four days. Deliver what you produce, what you decline to produce, and the exact sentence you will say in the meeting where the number appears on a slide.

Approach

1. Recognise what is being probed: whether you can find the legitimate request inside an illegitimate framing instead of either complying or refusing on principle. The generic answer promises to push back; the strong one produces something genuinely useful and states its limits in the room, without ambushing anybody.
2. Separate the decision from the justification. Sunsetting the tier may be correct for reasons the data does not hold, such as support cost, roadmap surface area or sales motion. What you decline is a one-sided document. What you produce is the case read both ways, which also happens to be more useful to the leader.
3. Build the symmetric analysis: MRR at risk at constant FX, the share of affected accounts with a plausible migration path given seats_licensed and billing_term, the recovery rate assumed for that migration and where it came from, and the downside case in which high-utilisation accounts treat the sunset as a reason to re-evaluate the vendor entirely.
4. Surface the inconvenient fact privately and early. The highest seat utilisation in the book is a retention signal, and the leader should hold it before the room does, so they can incorporate it rather than be caught by it.
5. Agree the meeting sentence in advance with the leader, so that nobody is surprised. Something to the effect that the tier is 6% of MRR and its accounts are the most heavily used in the book, and that the case for sunsetting rests on cost and focus rather than on revenue. That is true, it supports the decision on its real grounds, and it stops the deck claiming the numbers endorse it.
6. Decide your own line before you need it: what you will not put your name to, and that the route if asked anyway is your own manager rather than a confrontation in the meeting.

Follow-up

• The deck circulates with your analysis included and the downside case removed. What do you do, and by when?
• What changes if the honest analysis says the sunset is clearly the wrong call?
• How do you write the same memo when the leader is your skip-level and the meeting is tomorrow?

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?