Interview guides · Aircall

Aircall Data Scientist Interview Questions & Guide 2026

Causal inference drills for Aircall data scientist prep: diff-in-diff, matching, instruments, and the limits of observed data.

The 60-second brief

As a Data Scientist at Aircall, you are a foundational pillar of the company’s decision-making engine. In a high-growth environment where voice, digital channels, and AI converge, your work directly informs how over 22,000 businesses globally optimize their customer communications. You are not just crunching numbers; you are a strategic partner to product and engineering leaders, helping to define the roadmap for customer acquisition, engagement, and long-term retention.

SQL is seldom the hardest round and is often the one that eliminates people. The working bar is usually window functions, correct deduplication, and joins that do not silently fan out rows, rather than obscure syntax.

Focus areas

Decompose a metric move by segment and mix
Define numerator, denominator and window precisely
Turn a vague request into a measurable question

Overview

This role is uniquely challenging because it sits at the intersection of complex product strategy—such as pricing, packaging, and self-service motions—and the fast-paced world of AI-powered communication. You will be expected to translate ambiguous business problems into rigorous analytical frameworks, design high-impact A/B tests, and communicate findings to C-level stakeholders. Success at Aircall requires a "builder mentality," a bias toward action, and the ability to turn raw data into a compelling narrative that influences the company’s trajectory.

The Aircall interview process

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

Cultural Alignment

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

What to demonstrate

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

How to prepare

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

Technical Alignment

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

Practical Assessments

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

Collaborative Meetings

An added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.

What to demonstrate

Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact

How to prepare

Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.

Where candidates lose points

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.

Counting on an identity key that changes underneath the metric

visitor_id is per browser and per device, and it resets on cookie clearance, private browsing and platform privacy changes, so the distinct-visitor count drifts upward for reasons unrelated to reach. Any rate with visitors in the denominator therefore decays over time even when behaviour is constant, and any rate with visitors in the numerator inflates. The stitching at signup makes it worse in both directions: a user who signed up on mobile and returns on desktop is two visitors and one user, while a shared device is one visitor and several users. Decide which key each metric is counted on, write it into the definition, and when comparing a period before and after a platform privacy change, expect a level shift in every visitor-keyed metric and do not attribute it to the product.

Solving silently instead of narrating the reasoning

Say which branch you are taking and why you chose it over the alternative, for example checking the denominator first because it changes what the comparison means. A correct answer that arrives with no visible path scores below a rigorous one that needed a hint.

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

Split a pooled conversion drop into rate and mix

Medium · mix shift · decomposition · pandas

You have weekly visit-to-signup counts by segment: a DataFrame with week, device_type, referrer_channel, visitors and signups. The pooled rate fell 0.84 percentage points between two consecutive weeks while several individual segments rose. Write a function that, for a caller-supplied list of segment columns, splits the pooled change into a rate effect, a mix effect and an interaction term that sum exactly to the observed change. Return those three scalars plus a per-segment contribution table sorted by absolute contribution, so the largest single driver can be named.

Approach

State the algebra before coding: the pooled rate is r = sum over segments of w_s * r_s, with w_s the segment's share of the denominator. Then r1 - r0 decomposes exactly into sum(w_s0 * (r_s1 - r_s0)) for rate, sum((w_s1 - w_s0) * r_s0) for mix, and sum((w_s1 - w_s0) * (r_s1 - r_s0)) for interaction. The identity is per-segment, so it holds for any numbers you put in the four slots.
Pivot both weeks onto a common segment index with an outer join so a segment that appeared or vanished is kept rather than dropped, then decide what rate to give a segment with no visitors in one of the weeks, and document the choice. The identity stays exact either way because the missing week's weight is 0, but the attribution does not. Filling the missing rate with 0 sends an appearing segment's entire w_s1 * r_s1 into the interaction term, since w_s0 = 0 makes both the rate term and the mix term (w_s1 - w_s0) * r_s0 identically zero; a vanishing segment then splits as -w_s0 * r_s0 in rate, -w_s0 * r_s0 in mix and +w_s0 * r_s0 in interaction.
The convention used below instead imputes the missing week's rate as that week's pooled rate. A vanishing segment then lands wholly in mix at -w_s0 * r_s0, with rate and interaction cancelling; an appearing segment puts w_s1 * r_pooled0 in mix (volume arriving at the average rate) and only w_s1 * (r_s1 - r_pooled0) in interaction (its rate differing from that average). Impute by which week the segment is missing from, never by argument order, or the swap identities below stop holding.
Guard the division where visitors is 0 so no NaN enters the vectors, because a single NaN poisons every sum. A segment with zero visitors in both weeks contributes exactly 0 and can be dropped; a segment missing from only one week does not contribute 0, and where its contribution lands is settled by the convention above, not by the guard.
Compute the three components as vectors over segments, then sum. Keep the vectors, because the per-segment contribution table is what turns the decomposition into an explanation.
Assert that the three components sum to the observed pooled change within floating-point tolerance. This identity is exact, so a mismatch means an implementation bug, not a modelling judgement.

Follow-up

The mix effect accounts for 0.71 of the 0.84 point drop, driven by paid_social volume. What is your recommendation, and what would change it?
Why is a two-way split into a counterfactual rate and a residual also exact, and when would you prefer it to the three-way version?
Segmenting on device and channel leaves a large interaction term. What does that tell you about the choice of segments?

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.

Worked solution (30 min)

rng = np.random.default_rng(seed); for memory, batch the replications in chunks and accumulate the any-cross count across chunks.
Per chunk: draw (chunk, 40000) uniforms per arm, x = (u < 0.10).cumsum(axis=1), slice columns at indices 3999, 7999, ..., 39999.
Compute the ten z statistics vectorised over the chunk, take crossed = (np.abs(z) > 1.96).any(axis=1).
Aggregate: peek_rate = total_crossed / reps; single_rate = mean of |z_final| > 1.96; mc_se = sqrt(p*(1-p)/reps) for each.

Expected result: The single-look rate lands at 0.05 within about 0.005 at 10,000 replications. The ten-look rate lands near 0.19, which with 10,000 replications has a Monte Carlo standard error of about 0.004, so anything in roughly 0.18 to 0.20 is consistent and anything near 0.40 indicates independent redraws.

Verify it yourself

The single-look control recovers 0.05 within two Monte Carlo standard errors; if it does not, the z statistic is wrong and the headline number is meaningless.
The crossing rate is monotone in the number of looks: 1 look near 0.05, 2 near 0.08, 5 near 0.14, 10 near 0.19.
Halving the base rate to 0.05 leaves the peeking rate essentially unchanged, since the result is about the test's random walk and not about the conversion level.
Re-running with the same seed reproduces the figure exactly.

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?

Walk me through a time you had to optimize a slow-performing SQL query. What was the bottleneck?

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 write a SQL query to identify "power users" based on call volume and integration usage?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Can you explain the difference between a left join and an inner join in the context of merging CRM data with voice logs?

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
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.

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?

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?

What metrics would you prioritize if you were testing a change to our pricing page, and why?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Restate the decision this analysis has to support, and who acts on the answer.
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?

If you notice a sudden drop in a key metric, how do you investigate the root cause?

Medium · product sense and metrics

Approach

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

How would you design an A/B test to evaluate the impact of a new AI-powered feature on user retention?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Decide the analysis before seeing data, including how long it runs and when you look.
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?
How would you handle interference between treated and control units?

How do you address the issue of "novelty effect" in long-term product experiments?

Medium · a/b testing and experimentation

Approach

Name the guardrails that would stop a launch even on a positive primary result.
Name the randomisation unit first; it decides the variance and what the test can detect.
Decide the analysis before seeing data, including how long it runs and when you look.

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?

Measure whether self-serve help actually answered the question

Medium · proxy metrics · bias · support

A help widget opens in-product. Leadership asks for the percentage of users who got their answer. You have fct_event (event_name, user_id, session_id, occurred_at_utc, is_core_action, properties JSONB carrying article_id), fct_session, and a support ticket table joined on user_id. Nothing records whether the answer was correct or whether the user was satisfied, and no such field is being added. Propose the metric you would publish, state the proxy plainly, name the direction and rough size of its bias, and say which decisions it can and cannot support. Deliverable: the definition plus the bias statement.

Approach

Say first that the target quantity is unobserved and will stay unobserved: nothing in the stream distinguishes a user who was helped from one who gave up, and both leave the same trace, so every candidate metric here is a proxy and the only question is which bias you prefer.
Build the proxy to remove the largest identifiable error: among widget-open sessions, the share with no ticket from that user within 72 hours and at least one is_core_action = TRUE event after the widget opened in the same session. The downstream action requirement strips out most of the silent abandonment that a no-ticket rule alone scores as success.
State the residual bias with a direction and a bound: it still over-counts, because a user who gave up and went elsewhere files no ticket and may still complete an unrelated core action later in the session; bound it using the observable population of widget-open sessions that end within two minutes with no further event.
Publish a directly-measured companion with its own weakness in the same sentence: article thumbs up/down reported with its response rate, and the note that a single-digit response rate missing non-randomly toward annoyed users is exactly why it cannot be the headline.
Write the use statement, because a proxy without one gets reused for the wrong decision: valid for ranking articles against each other and for detecting a week-on-week break, invalid as an absolute deflection rate or as an input to a cost-saved figure.

Worked solution (25 min)

Write the unobservable target in one sentence with the observable set beside it, so the gap is visible on the page rather than assumed away.
Define the published proxy with exact numerator and denominator, including the session scoping of the downstream core action.
Bound the bias from data you already have: compute the share of widget-open sessions ending within two minutes with no further event.
Publish the thumbs signal beside it with its response rate and the direction of its missingness.
Write the two-line use statement covering what the number supports and what it does not.

Expected result: A named proxy with numerator and denominator, a stated bias direction (over-counts success) with an observed magnitude bound, and an explicit list of the decisions the number does and does not support.

Verify it yourself

The proxy and the raw no-ticket rate should differ by roughly the abandonment share computed in step three. If they are nearly identical, the downstream-action requirement is not binding and adds nothing.
A change in support hours or ticket-form availability moves the no-ticket rate with no change in help quality, so the series must carry annotations for support operational changes.
Sessions with consent_state = 'denied' cannot be joined forward to a user_id and therefore cannot enter this metric at all; the denominator definition has to say so.

Follow-up

How would you validate this proxy once, and what would you do if the validation said it over-counts by 20 points?
Support asks for a dollars-saved number from this metric. What do you say, and what would you need before saying anything else?
Ticket volume drops the week support hours change. How do you keep that out of the series?

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

Roughly 90 minutes a night on weekdays with one longer weekend block. The plan deliberately cuts scope rather than compressing everything, on the assumption that finishing one thing a night beats half-starting four.

Day 1 — Fix the scope and set a baseline

Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list for everything else and keep it visible all week.
Take one 20-minute SQL prompt and one 10-minute metric question cold, and write the single sentence that says what blocked each attempt, since that sentence is what decides which two topics get the most evenings.
Set the week's one rule: one problem finished to completion every night, including the night you only have 40 minutes.

Deliverable: A one-page scope with an explicit not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One query pattern, written three times

Choose the single pattern most likely to appear (a cohort retention grid, or a funnel counted by user) and write it three times from a blank file rather than editing the previous attempt.
On the third attempt, write the grain of every CTE as a comment before writing its body.
Stop at 90 minutes even if the third version is imperfect, and write the one thing you would fix with another hour.

Deliverable: Three independent versions of the same query plus a note on what changed between them.

Day 3 — Only the statistics you will be asked to defend

Write, in under 200 words, how you would decide whether a difference between two groups is real: the test, its assumptions, and what you would switch to when an assumption fails.
Compute a 95 percent confidence interval for a difference in proportions by hand on realistic numbers, then write in one sentence what changes if the two samples are paired rather than independent.
Write your answer to "what does a p-value mean", check it against a definition, and delete the version that describes it as the probability the hypothesis is true.

Deliverable: A 200-word written answer and one hand-computed interval you can reproduce under pressure.

Day 4 — One case, and the assumptions holding it up

Answer one product case aloud in 20 minutes with a recording running, then listen back with a pen and mark every claim you asserted without saying what it rested on: an assumed user behaviour, an assumed data source, an assumed baseline rate, an assumed grain.
Pick the three assumptions the recommendation actually depends on, write how you would check each one against data, and say which one being wrong would flip the recommendation rather than merely weaken it.
Write the four-step structure you used onto a card small enough to hold in working memory when you are nervous.

Deliverable: One recording, three load-bearing assumptions each with a written check, and a four-step structure card.

Day 5 — Your own work, timed

Write a 90-second version and a four-minute version of your main project, and time both out loud rather than reading them.
Prepare answers to the two follow-ups that always come: what you would do differently, and how you knew it worked.
Put one number in the first sentence and be able to say exactly where that number came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in a longer weekend block

Run a 60-minute mock covering query work, a case and a behavioural question in a single sitting with no breaks, because sustained attention is the thing evenings have not trained.
Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

Write the 20-minute warm-up you will actually do on the morning of the interview: one query you can already write from a blank file, one metric you can define out loud, and nothing you have never seen before.
Re-read only your own notes from this week, and open no new material.
Write down the logistics: the tool you will be asked to work in, whether lookups are allowed, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the case structure, the project numbers, and the logistics.

The project you argued against

Saying no well is a senior skill and it is rarely rehearsed. Think of a time you told someone their analysis was not worth doing, or that the experiment could not answer their question at the sample size available. Explain what you offered instead. Refusal without an alternative reads as obstruction rather than judgement.

How do you handle a situation where your data contradicts the intuition of a product manager?

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
Name the disagreement or constraint, and how you resolved it with evidence.
Close with what you would do differently, concretely.

Follow-up

What would you do differently if you ran that project again?
What did you decide not to do, and why?

Walk through an analysis you got wrong and what changed

Easy · postmortem · data quality · self-assessment

Describe an analysis of yours that turned out to be wrong after somebody had already acted on it. You have four minutes. The account must name the defect mechanically, the join, the filter, the window or the identity key, rather than describing it as a communication problem. It must also say who did what because of the wrong number, how the error surfaced, how long it stood, and what control you put in place so that class of error cannot reach a decision again. Do not pick an error nobody acted on.

Approach

Recognise what is being probed: whether you can be specific about your own failure without minimising it or performing contrition. The discriminator is whether the defect has a mechanism the listener could reproduce in their own warehouse.
Choose the case by blast radius rather than by comfort. An error nobody acted on tests nothing, and picking one signals that you are managing the interview instead of answering it.
Structure the account in six beats: the number, the decision it drove, the defect, the detection, the correction, the control. Keep the defect to one reproducible sentence, for example an inner join to fct_subscription_period that dropped accounts with no subscription row and so computed retention over payers only.
State the direction of the bias, not only its existence. A filter or join that removes rows usually moves a metric predictably, and knowing which way shows you diagnosed the mechanism rather than patched the symptom.
Be exact about detection and elapsed time. 'A colleague noticed' and 'the row-count assertion failed before publication' are different answers about the same organisation, and the second one is the one your control is supposed to produce next time.
End on the control, its cost, whether it has fired since, and one thing it does not cover.

Follow-up

What did the control cost, and has it fired since? If it never has, how do you know it works?
How long did the wrong number stand before anyone questioned it, and what does that say about the review path it went through?
What is the equivalent mistake you are most likely to make in this role, given the tables you would be working in?

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?