Interview guides · Industry-Leading National Security Consultancy

Industry-Leading National Security Consultancy Data Scientist Interview Questions & Guide 2026

Experiment-design practice for Industry-Leading National Security Consultancy data scientist candidates, covering randomisation units, power, minimum detectable effect and the traps that invalidate a result.

The 60-second brief

The Data Scientist role at Industry-Leading National Security Consultancy serves as a critical bridge between complex data architecture and actionable intelligence. You will be responsible for transforming raw, high-stakes data into meaningful insights that support national security initiatives. Your work directly impacts how the organization evaluates operational risks, optimizes resource allocation, and solves high-dimensional problems that require both technical rigor and strategic foresight.

When randomisation is off the table, the skill being checked is naming an identification strategy together with the assumption it rests on: parallel trends for difference-in-differences, relevance and exclusion for an instrument, overlap and conditional ignorability for matching. Say the assumption out loud and say how you would try to break it.

Focus areas

Decompose a metric move by segment and mix
Size an experiment before anyone launches it
Turn a vague request into a measurable question

Overview

Operating in this environment requires more than just analytical ability; it demands a deep commitment to precision and integrity. You will collaborate with cross-functional teams to design robust experiments, develop predictive models, and maintain the analytical infrastructure that keeps the organization at the forefront of its field. This is a role for those who thrive when working on mission-critical projects where the scale of data is matched only by the importance of the outcomes.

The Industry-Leading National Security Consultancy interview process

Industry-Leading National Security Consultancy candidates report 5 rounds · ≈ 4-6 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical Evaluation

Much of what gets scored here happens out loud while you type. Nobody can see your reasoning inside a half-written query, so five silent minutes read as being stuck even when they are not. State the plan in plain language first: which tables, what grain you are aggregating to, and the one filter that defines the population. Then write it. The narration doubles as insurance, because a wrong plan gets caught early and cheaply while a wrong query gets caught at the end with no time left to redo it. A timed statistics section, where one exists, is a separate test with its own clock.

What to demonstrate

Whether the query you write matches the plan you just described
What you do with a hint, meaning whether the correction gets absorbed or the first approach gets defended
Whether you can debug your own wrong output by reading the result set and naming which part of the query produced the anomaly

How to prepare

Solve three problems while screen-sharing into a recording, then watch it back and mark every stretch longer than thirty seconds where you said nothing
Practise compressing the plan into one sentence before typing, then check afterwards whether the finished query actually matched it
Time yourself on statistics questions that carry a business reading, such as what a confidence interval does and does not claim, rather than re-reading notes without a clock

Behavioral Assessment

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.

Case Studies

A case has a fixed clock, and a good deal of what is being scored is how you spend it. Thirty to forty-five minutes buys one pass across the whole problem or a deep read of one part of it, and choosing between those is the work rather than a compromise forced on you. Announce the shape early: the structure you are using, the branch you think carries the decision, and what you are setting aside. An answer that is thorough for the first third and silent on the recommendation reads worse than one that is rougher throughout and lands.

What to demonstrate

Whether a visible structure appears in the opening minutes and survives the rest of the case
Whether the depth goes to the branch that carries the decision, rather than the branch you find most comfortable
Whether you say what you are leaving out and why, instead of quietly omitting it and hoping nobody asks

How to prepare

After each practice case, write down the branches you chose not to open and the reason for each, then check whether you said any of them out loud while the case was running. A branch you only cut privately reads to the interviewer as one you missed.
Redo a case you have already worked in half the time, deciding in advance which single branch you keep, then compare which version a listener would find more useful.
Write a two-sentence opening you can reuse, holding the restated question and your plan for the available time, and deliver it within the first ninety seconds of every practice run.

Take-home Assessments

A take-home is graded as an argument, not as a notebook. Somebody reads the submission without you in the room, so every choice has to survive on the page: why the question was framed this way, and what was deliberately left out. The gap between a strong and a weak submission is almost never model quality. It is whether the writeup names the specific question it answers and commits to a recommendation, including what evidence would overturn it. A high-accuracy model attached to no conclusion reads as effort that stopped before the decision.

What to demonstrate

Whether the question you answered is stated outright, and whether it is the question the prompt posed rather than an easier neighbour of it
Whether the recommendation is specific enough to act on, with the uncertainty attached to it instead of parked in a caveats section at the end
Whether analytical choices such as the metric definition, the population filter and the time window are justified in the prose, not merely visible in code

How to prepare

Take a dataset you have already worked with, write the one-paragraph conclusion first, then check whether the analysis you were planning actually supports it and cut whatever does not
Practise stating a metric in one sentence that fixes the population, the time window and the denominator, then confirm your query computes exactly that sentence and nothing adjacent to it
Hand a draft to someone outside the problem and ask them to tell you back what you recommended and why; anything they cannot recover is not on the page yet

Where candidates lose points

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.

Treating last-touch attribution as the causal value of a channel

The attribution label on dim_user is the output of a rule that assigns full credit to whichever touch happened to be recorded last inside a lookback window, and that rule systematically rewards channels that sit close to the conversion, especially branded search and retargeting, which largely intercept demand that already existed. Reallocating spend on those labels moves budget toward the channels that are best at being last, which is why attributed return on ad spend often improves while total signups do not. Nothing in the touchpoint data can settle this, because the counterfactual of not running the channel was never observed. The credible reads are a geo holdout or a scheduled pause, sized in advance on the total-signups metric rather than on the attributed one, and the honest framing in the meantime is that the label describes correlation with conversion and not incremental contribution.

Accepting a metric definition without asking about the denominator

Pin down the denominator, the eligibility filter and the time window before computing anything: conversion rate per session, per user, per eligible user and per new user are four different numbers with different behaviour. Restate the definition in one sentence and get agreement before you analyse.

Comparing periods without accounting for seasonality or day-of-week

Compare whole weeks against whole weeks and check whether the same swing appeared in prior cycles or prior years before attributing it to anything you changed. Weekday and weekend populations often differ enough that a Tuesday-to-Saturday comparison is meaningless.

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

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.
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.
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.
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).
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)

y = df['converted'].to_numpy(); n_t = (df['variant'] == 'treatment').sum(); obs = y[treat_mask].mean() - y[~treat_mask].mean().
Build the null: for B draws, shuffle y and take the mean of the first n_t entries minus the mean of the rest.
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.
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

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

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

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?

Describe how you would handle missing values in a dataset before performing a primary analysis.

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
State the window function and its partition and ordering out loud before writing it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

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 rolling averages of activity over a 30-day window.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Check whether any join is one-to-many before aggregating, or the sums inflate.
Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

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

How would you join multiple disparate datasets to identify patterns in operational logs?

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

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

Read an experiment from first exposure, not assignment

Hard · experiments · window-functions · srm

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.

Expected result: One row per variant with exposed, converted, rate and share, plus a scalar count of excluded contaminated units. Every retained unit was first exposed at least seven days before the data cut, so each one has a full seven days of observation and the rates are directly comparable.

Verify it yourself

Exposed counts across variants sum to the population count minus the contaminated units.
MAX(first_exposed_at_utc) among retained units is at least seven days before :data_cut_utc.
Variant shares sum to 1.
No unit_id contributes to two variant rows.
The truncation drops a similar share of each arm; if it does not, note the imbalance alongside the SRM result rather than reading the effect.
Swapping the relative window for a calendar one changes the rates; if it does not, exposure timing is balanced and you can say so.

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?

If you notice a sudden drop in a key performance metric, walk me through your diagnosis process.

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.

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 would you design a product metric for a system tracking regional security threats?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.

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?

Describe how you would measure the success of a new internal data-sharing tool.

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.

Follow-up

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

Explain the concept of statistical significance and how you determine it in an A/B test.

Medium · a/b testing and experimentation

Approach

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

Follow-up

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

What are the most common experimentation pitfalls that lead to false positives?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Name the randomisation unit first; it decides the variance and what the test can detect.
Say whether units interfere with each other, and switch design if they do.

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?

A test that won on day four and lost by day twelve

Medium · peeking · sequential testing · multiple comparisons · winner's curse

A two-week test was checked every morning. On day four the primary metric crossed p = 0.03 and the team shipped. By day twelve, with logging still running, the estimate had fallen to a 0.2% lift at p = 0.61. The day-four deck also reported a significant 9% lift among paid_search visitors on mobile_web, one of twenty segments inspected. Explain both patterns quantitatively, say what the day-four evidence was actually worth, and specify the monitoring rule that prevents a repeat.

Approach

Price the peeking. A fixed-sample test controls type I error at one pre-declared look. Ten daily looks at a nominal 5% raise the false positive rate to roughly 0.15 to 0.20, because the running test statistic takes a random walk that will eventually cross the boundary under the null.
Price the winner's curse separately, because it is a different error. Conditioning an estimate on having crossed the threshold biases it away from zero, by a factor of roughly 3 when true power is 20% and only about 1.1 at 80% power. Day four sits at roughly 29% of planned information, which is exactly the low-power case.
Read day twelve as the better estimate rather than as a contradiction. Nothing regressed; the day-four value was an extreme draw that was selected precisely for being extreme.
Price the segment claim. Across twenty segments at a nominal 5%, at least one crosses about 64% of the time under a global null, since 1 - 0.95^20 = 0.64, and the slice that surfaces is by construction the one with the most favourable noise.
Specify the rule instead of scolding. Either fix the horizon and look once, or use group-sequential boundaries such as O'Brien-Fleming that spend almost no alpha early, or use always-valid confidence sequences that stay correct under continuous monitoring. Pre-register the small set of segments, correct across the ones tested, and treat anything found afterwards as a hypothesis needing its own powered test.

Worked solution (25 min)

Count the looks actually taken, twelve daily, and state the inflated alpha band of 0.15 to 0.20 for ten looks, rising with more.
Place day four on the information fraction, about 4/14 = 29% of planned sample, and apply the exaggeration factor for low power.
Compute 1 - 0.95^k for the number of segments actually inspected; with k = 20 this is 0.64.
Report the day-twelve interval as the estimate and say plainly that the experiment supports no effect at any size the team would ship for.
Write the rule into the experiment template: horizon and segments declared at launch, sequential boundaries if interim looks are needed at all.

Expected result: The day-four result is consistent with pure noise: ten looks inflate the false positive rate to roughly 0.15 to 0.20, and an estimate conditioned on crossing at about 29% information is exaggerated by roughly a factor of three at low power. The segment finding appears about 64% of the time across twenty slices under a global null. The supportable conclusion is a 0.2% lift whose interval covers zero.

Verify it yourself

1 - 0.95^20 = 0.6415. If your family-wise number lands near 0.05 you have computed the per-test rate instead.
The day-twelve standard error should be roughly sqrt(12/4) = 1.7 times smaller than day four's. If the interval did not narrow, sample was not accumulating and there is a second problem.
A Bonferroni threshold for twenty segments is 0.0025; check whether the mobile_web result would have survived it.

Follow-up

Under O'Brien-Fleming with five planned looks, roughly what nominal p-value would the first look have needed to stop?
The feature already shipped. What analysis can you still run, and what can it not tell you?
If mobile_web paid_search were genuinely the only affected segment, how would you design the confirmatory test and how large would it have to be?

Pooled signup conversion fell while every segment rose

Medium · mix-shift · simpsons-paradox · decomposition

Weekly visit-to-signup conversion, counted on distinct fct_session.visitor_id with is_bot_flagged = TRUE and consent_state = 'denied' sessions excluded, fell from 4.4% to 3.9% week over week. Split by device_type and referrer_channel, all twelve cells are flat or up. A paid_social campaign launched on Monday. Using fct_session and fct_event, quantify how much of the 0.5-point fall is mix and how much is within-segment rate, then state what you would tell the growth lead.

Approach

Write the pooled rate explicitly as the sum over segments of weight times segment rate, and materialise both weeks' weights and rates into one table. Until that table exists there is nothing to decompose, only opinions.
Compute three quantities and report all three: the rate effect holding the prior week's weights fixed, the mix effect holding the prior week's rates fixed, and the interaction residual. Reporting only the first two hides a term that can be material when both weights and rates move a lot.
Rank segments by their individual mix contribution, computed as the change in that segment's weight multiplied by its prior-period rate. This is what lets you say one cell caused the move rather than gesturing at the campaign.
Verify the new traffic is human and countable before accepting the mix story: check is_bot_flagged coverage on the new channel, the distribution of duration_seconds and event_count for its sessions, and whether its consent_state profile differs from the rest.
Deliver the conclusion as a definition change rather than a diagnosis: a pooled rate over a mix that moves is not comparable week over week, so the recurring report should carry per-channel rates plus absolute signups, with the pooled figure demoted or dropped.

Follow-up

Paid social converts at roughly a quarter of organic but absolute signups rose. Is the campaign working, and what would you need to answer that properly?
Would you reach the same conclusion if the campaign had moved the mix by two points instead of sixteen? Where is your threshold and why?
How would you present this to someone who has been watching the pooled number in a weekly meeting for a year?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

Being wrong in public

A number you shipped turned out to be wrong, and someone had already acted on it. That is one of the most useful stories a data person can carry. What is being scored is how fast you noticed, who you told first, and what you changed in the process so the same class of error could not repeat quietly.

Tell me about a time you had to influence a stakeholder who disagreed with your data-driven recommendation.

Medium · behavioural and stakeholder questions

Approach

Name the disagreement or constraint, and how you resolved it with evidence.
State the situation in two sentences and spend the rest on your reasoning.
Quantify the outcome, including what you would not claim credit for.

Follow-up

What did you decide not to do, and why?
How did you know the outcome was caused by your change?

Defend a flat experiment readout against a post-hoc segment

Medium · experiments · segmentation · pushback

A feature you evaluated is flat on seven-day activation: +0.05pp with a 95% interval of [-0.47pp, +0.57pp], from 61,000 exposed users per arm in fct_experiment_exposure joined to dim_user and fct_event. Baseline activation is 32%. The launch team asks you to drop every surface except mobile_web, where the point estimate is +1.1pp, and re-run. You have ten minutes in their planning meeting. Deliver a spoken position: what you will and will not do, and the decision you recommend.

Approach

Recognise what is being probed: whether you hold a statistical position under social pressure without becoming either rigid or apologetic. A generic answer says the segment is not significant; a strong one separates the request into a question that is answerable (is the mobile_web number real?) and one that is not (can we ship on it?), and answers both.
Price the multiplicity out loud. The slice was chosen after seeing the results, so its estimate is selected on favourable noise and is biased away from zero. With k independent looks at a nominal 5% level, the chance of at least one false positive is 1 - 0.95^k: 26% at six segments, 64% at twenty. Quote the k you actually inspected, not the k you reported.
Use the arithmetic already in front of you. On the point estimates, a +1.1pp mobile_web effect combined with a pooled +0.05pp implies the remaining surfaces average negative in proportion to mobile_web's share of exposures. State that as a testable implication of their story rather than as a rebuttal of it.
Ask the one question that settles the category: was mobile_web named in the analysis plan before launch? If it was, it is a planned comparison and gets a corrected reading. If it was not, it is a hypothesis, and the honest move is to size the test that would confirm it.
Convert the refusal into a cost. Size a mobile_web-only confirmatory test at the claimed effect, state the weeks of mobile_web traffic it needs, and close with the recommendation: do not ship this as a lift, and note that the interval already rules out anything at or above +0.6pp, which is itself a useful input to the roadmap.

Follow-up

The confirmatory test you sized needs nine weeks of mobile_web traffic and the team has three. What do you recommend instead?
Suppose mobile_web was pre-registered. How does your reading change, and what correction do you apply?
Your interval excludes +0.6pp. Is that the same as saying the feature does nothing?

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

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.
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.
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.
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.
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.
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?