Interview guides · Gallagher

Gallagher Data Scientist Interview Questions & Guide 2026

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

The 60-second brief

A Data Scientist at Gallagher occupies a critical position at the intersection of complex risk management and advanced analytics. You are responsible for transforming high-volume customer and industry data into actionable insights that drive business strategy and operational efficiency. Because Gallagher operates in a highly regulated and data-intensive landscape, your work directly influences how the firm assesses risk, optimizes product offerings, and maintains its competitive advantage.

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

Cluster inference at the account, not the engagement
Reconstruct pipeline stage history from mutable rows
Separate bookings, recognised revenue and collected cash

Overview

The role demands a balance between rigorous statistical methodology and practical product-sense. You will spend your time navigating large datasets, designing experiments to validate hypotheses, and building models that translate into real-world impact. While the work is technically demanding, the most successful Data Scientists here are those who can communicate complex technical findings to non-technical stakeholders, ensuring that data-driven decisions are understood and implemented across the organization.

The Gallagher interview process

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

Recruiter Screen

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 Assessment

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

What to demonstrate

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

How to prepare

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

Managerial Round

Rounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.

What to demonstrate

Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options

How to prepare

Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.

Where candidates lose points

Computing days-to-pay or proposal cycle time over completed records only

At any snapshot date, invoices that have already been paid are disproportionately the fast ones, and proposals that already have a decision are disproportionately the quick ones. Averaging over the completed set alone biases both numbers downward, and the bias grows exactly when the business is deteriorating, because the slow cases are the ones still open. Unpaid and undecided records are right-censored; use Kaplan-Meier or a restricted mean up to a fixed horizon, and never fill paid_at with a placeholder.

Trending utilisation or revenue on work_date without accounting for timesheet backfill

Time entries are created days to weeks after the work happens, and the backfill tail often runs two to six weeks. A dashboard keyed on work_date therefore shows the most recent weeks as a decline that reverses on every refresh. The fix is either to hold the reporting window back past the observed backfill tail (measure the tail with the timesheet submission lag metric rather than guessing) or to report an as-of-entered_at snapshot so the series is internally consistent, and to state which one you used.

Reporting a p-value with no effect size or interval

Give the estimated difference with a confidence interval in the units the business cares about, then say whether that whole interval is worth acting on. A p-value only addresses whether you can rule out exactly zero; it says nothing about magnitude.

Interpreting a change before checking data quality and logging

Spend the first pass on row volume by day, null rates, duplicate keys, and whether the step change lands on a release or tracking-migration date. A discontinuity that coincides with a deploy is an instrumentation hypothesis before it is a behavioural one.

Questions & practice

How do you balance the trade-off between model accuracy and business interpretability?

Medium · machine learning and modelling

Approach

Set a baseline first, so any model has something honest to beat.
Check what information would not exist at prediction time, and exclude it.
Frame the prediction: the label, the moment of prediction, and the action it triggers.

Follow-up

How would you choose the decision threshold, and who owns that choice?
What would you monitor after launch to know the model is still valid?

Kaplan-Meier days to payment with unpaid invoices censored

Hard · survival analysis · censoring · kaplan-meier

invoice_lines has invoice_line_id, engagement_id, line_type, issued_at, due_date, paid_at (null when unpaid), amount_usd and status in draft, issued, partially_paid, paid, disputed, written_off. At a given snapshot_date, estimate the median days from issue to full payment. Implement Kaplan-Meier yourself; no lifelines or equivalent. Treat issued, partially_paid and disputed as right-censored at snapshot_date, and decide and justify what to do with written_off. Report the naive mean over paid lines alongside your estimate and state the sign of its bias.

Approach

Build the duration and event table explicitly. Drop draft lines, which have no clock. For status paid, duration = (paid_at - issued_at).days with event = 1. For issued, partially_paid and disputed, duration = (snapshot_date - issued_at).days with event = 0.
Handle written_off as a competing event rather than a censor. Censoring it makes the estimator answer 'time to payment if written-off invoices could still pay', which overstates collection. Either report a cumulative-incidence version alongside, or censor them and say plainly that the result is conditional on eventual collection.
Implement the estimator directly: sort unique event times, at each t take n_i as the count with duration >= t and d_i as the payments at exactly t, and accumulate S(t) = product of (1 - d_i / n_i). Censored rows leave the risk set without causing a drop, which is the whole mechanism and the reason the answer differs from any completed-case average.
Read the median as min{t : S(t) <= 0.5}. If S never reaches 0.5 within observed follow-up, report 'not reached'; interpolating past the last observation invents data that the snapshot does not contain.
Add Greenwood's formula for Var(S(t)) to put a band on the curve, then invert the band at 0.5 for an interval on the median rather than quoting a point estimate alone.
Compare against the mean over paid lines only and name the direction: at any snapshot the paid set over-represents fast payers, so the naive mean is biased low, and the bias widens exactly when collections deteriorate.

Follow-up

A large account moved to a monthly payment run. Is administrative censoring still independent of payment time, and what would you check?
Finance wants one DSO number against a target. What do you give them, and what do you refuse to give them?
Stratify by line_type. Do milestone lines behave like fees lines, and what would it mean for the firm if they do not?

Build a dense consultant-week hours spine with zero weeks

Easy · calendar spine · groupby · missing rows

You are given time_entries (time_entry_id, consultant_id, engagement_id, charge_code, work_date as datetime64, hours, is_billable, status) and a list of consultant_ids in scope plus a start and end date. Produce a DataFrame with exactly one row per consultant per Monday-anchored week in that range, with columns consultant_id, week_start, billable_hours, total_hours. Weeks in which a consultant logged nothing must appear as zeros rather than be absent. Count only rows with status == 'approved'. Do not loop over consultants and do not use resample.

Approach

Anchor weeks arithmetically: week_start = work_date - pd.to_timedelta(work_date.dt.weekday, unit='D'). Avoid dt.isocalendar().week, whose numbers restart each January and do not sort across a year boundary.
Construct the spine from the inputs, not from the data: pd.date_range(start, end, freq='W-MON') crossed with the in-scope consultant_ids via pd.MultiIndex.from_product, so its size is fixed before any aggregation runs.
Aggregate approved entries once with a single groupby on [consultant_id, week_start], producing total hours and billable hours (hours.where(is_billable, 0).sum()) in the same pass.
Left-merge the aggregate onto the spine, fillna(0.0) on both hour columns, and keep float dtypes so later ratios do not integer-divide.
Assert the shape before returning: len(out) == n_consultants * n_weeks, and out.total_hours.sum() equals the sum of hours over the filtered input inside the window.

Worked solution (20 min)

Filter to status == 'approved' and to work_date within [start, end], then derive week_start by subtracting the weekday offset.
Build weeks = pd.date_range(start_monday, end, freq='W-MON') and spine = pd.MultiIndex.from_product([consultant_ids, weeks], names=['consultant_id','week_start']).to_frame(index=False).
agg = filtered.assign(billable_hours=filtered.hours.where(filtered.is_billable, 0.0)).groupby(['consultant_id','week_start'], as_index=False)[['hours','billable_hours']].sum().
out = spine.merge(agg, how='left', on=['consultant_id','week_start']).fillna({'hours':0.0,'billable_hours':0.0}).rename(columns={'hours':'total_hours'}).
Sort by consultant_id then week_start and run the two assertions on row count and hour conservation.

Expected result: A frame of exactly len(consultant_ids) x len(weeks) rows, sorted by consultant then week, in which total_hours sums to the approved hours inside the window and zero-hour weeks are present as 0.0 rather than missing.

Verify it yourself

Row count equals n_consultants * n_weeks exactly, for a consultant list that includes someone with no entries at all.
Total hours in the output equal total approved hours in the filtered input, to the cent.
billable_hours <= total_hours on every row.
A week containing a year boundary lands in one week bucket only, which is the test that catches isocalendar-based bucketing.

Follow-up

Utilisation gets computed on this spine. What breaks for a consultant hired mid-window or terminated mid-window, and where would you fix it?
How would you extend this to consultant x engagement x week without the spine exploding to a row count nobody can hold in memory?

How do you optimize a query that is performing poorly on a table with millions of rows?

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
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.

Follow-up

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

How would you use SQL window functions to calculate a rolling average of customer churn rates over the last six months?

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
State the window function and its partition and ordering out loud before writing it.
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 would you verify this result without re-running the same query?

Find signed engagements with no approved billable hour

Medium · anti-join · null semantics · not exists

fct_engagement holds engagement_id, start_date and status. fct_time_entry holds time_entry_id, consultant_id, engagement_id, charge_code, work_date, hours, is_billable and status, and engagement_id is NULL on bench, leave, training and internal rows. Return engagements with start_date in the trailing quarter and status IN ('active','closed_delivered','closed_early') that have no approved billable time entry with work_date within 45 days of start_date, giving engagement_id, start_date and days elapsed. Then explain why NOT IN over a subquery selecting fct_time_entry.engagement_id is the wrong instrument here.

Approach

Express the exclusion as NOT EXISTS with a correlated predicate covering all four conditions at once: te.engagement_id = e.engagement_id AND te.is_billable AND te.status = 'approved' AND te.work_date < e.start_date + INTERVAL '45 days'. NOT IN can only carry a single column, so a multi-condition anti-join forces a contorted subquery even before the NULL problem.
State the NULL semantics precisely: NOT IN (SELECT engagement_id FROM fct_time_entry) evaluates to NULL rather than TRUE for every candidate as soon as one NULL is present in the list, because x <> NULL is unknown. The WHERE clause keeps only TRUE, so the query returns zero rows and reads as a clean bill of health. This table guarantees NULLs, since bench and leave rows carry no engagement.
Note the equivalent LEFT JOIN ... WHERE te.engagement_id IS NULL form. It is correct, but it materialises a join only to discard most of it; NOT EXISTS states the intent and most planners execute both as the same anti-join.
Guard the window against truncation: an engagement that started 10 days ago cannot yet have failed a 45-day test, so require start_date <= current_date - 45 or report the too-early set separately rather than counting them as failures.
Decide on the late-timesheet risk explicitly. A missing entry may mean unstaffed work or an unsubmitted timesheet, so either hold the cutoff back past the measured submission lag or label the recent tail as provisional.

Worked solution (20 min)

Count engagements matching the status and start_date predicates; this is the denominator.
Write the NOT EXISTS version and record its row count.
Write the NOT IN version on the same data and observe zero rows, then confirm with SELECT COUNT(*) FROM fct_time_entry WHERE engagement_id IS NULL that NULLs are present.
Cross-check by counting engagements that DO have a qualifying entry and verifying the two counts sum to the denominator.
Open one engagement from the result and look at its raw time entries to see whether the cause is no staffing or unapproved timesheets.

Expected result: A short list of engagement_id, start_date and days_since_start for engagements that started at least 45 days ago and carry no approved billable hour in their first 45 days. The NOT IN variant returns zero rows on the same data.

Verify it yourself

Count of flagged engagements plus count of engagements with at least one qualifying entry equals the count of engagements matching the status and date filters.
Every returned engagement has start_date at or before current_date - 45.
Spot-checking one flagged engagement in fct_time_entry shows either no rows or only unapproved or non-billable rows.
Replacing NOT EXISTS with LEFT JOIN ... IS NULL returns an identical set.

Follow-up

Turn this into a time-to-first-billable-hour distribution. What do you do with engagements that never recorded one?
Should client_pursuit hours count as the engagement being staffed, and how does that choice change the list?
How would you prove the empty result from the NOT IN version is a bug rather than good news?

If you notice a sudden, significant drop in a key engagement metric, what is your step-by-step process for diagnosing the root cause?

Medium · product sense and metrics

Approach

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

Follow-up

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

What metrics would you track to determine if a new insurance product feature is resonating with users?

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.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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

What steps do you take if an A/B test shows a positive result, but the long-term retention metrics are declining?

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you design a success metric for a new risk-assessment tool?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Fix the population and the time window before naming any metric.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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

How do you determine the required sample size for an A/B test to achieve statistical significance?

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Name the randomisation unit first; it decides the variance and what the test can detect.
State the primary metric and the minimum effect worth shipping, then size the test.

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?

What are the most common experimentation pitfalls you have encountered, and how do you mitigate them?

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Name the randomisation unit first; it decides the variance and what the test can detect.
State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

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

Define competitive win rate for a quarterly pipeline review

Easy · win rate · censoring · denominators

Pipeline reporting currently shows win rate as won proposals divided by all proposals created. Using fct_proposal (proposal_id, expected_value_usd, stage, loss_reason, is_competitive, created_at, submitted_at, decided_at), write a definition you would defend to a finance lead: numerator, denominator, window and every exclusion. Decide whether to weight by count or by expected_value_usd, and justify the choice. State what happens to proposals still in 'qualifying', 'scoping' or 'submitted' at the snapshot date, and why dropping them is not a neutral act.

Approach

Cohort on decided_at, not created_at. A created_at cohort mixes decided and undecided proposals, so the rate for a recent quarter is computed over whichever deals resolved fastest and drifts upward on every refresh.
Set the denominator to stage IN ('won','lost'). Excluding 'withdrawn' and 'no_decision' is defensible only if you report their count and value alongside, because they are not missing at random: they skew toward deals that were heading for a loss.
Filter is_competitive = TRUE. Sole-sourced follow-on work closes at near certainty and mechanically inflates the rate, so it belongs on its own line rather than inside the headline number.
Report count-weighted and value-weighted side by side. They diverge when large proposals lose more often than small ones, which is the diagnostically useful case; the value-weighted version is the one that ties to revenue.
Treat open proposals as right-censored rather than absent. Either restrict to cohorts old enough that most proposals have decided, picking that horizon off a Kaplan-Meier cycle-time curve, or publish the rate with the undecided count and value attached to it.

Worked solution (25 min)

Compute the naive rate (won / all created) and the proposed rate (won / won+lost, is_competitive = TRUE, cohorted on decided_at) for the same four quarters.
For each quarter, count and sum the value of proposals in 'withdrawn' and 'no_decision', and of proposals still open at the snapshot.
Compute the value-weighted version and compare it with the count-weighted one.
Re-run the most recent quarter as it would have looked 30 days ago and record how much each definition moved.

Expected result: Two numbers per trailing quarter, count-weighted and value-weighted competitive win rate, each with the denominator predicate written out in full, cohorted on decided_at, plus a reported count and value for withdrawn, no_decision and still-open proposals. The horizon is chosen so the undecided share is small rather than set to a round number of days.

Verify it yourself

The value-weighted and count-weighted rates differ. If they are identical, expected_value_usd is either constant or lost in a join.
Re-running a given quarter two weeks later moves the decided_at-cohorted number by much less than it moves the created_at version.
Withdrawn and no_decision appear as reported counts, not as a silent exclusion.

Follow-up

Win rate rose four points while proposal volume fell by a third. Which do you report first, and what single query separates a genuine quality shift from a volume shift?
How would you detect partners reclassifying likely losses as 'withdrawn' to protect the rate?

Fees per billable head fell nine percent; rank the causes

Hard · decomposition · seasonality · clustered inference

Recognised fees per billable head fell about 9% quarter over quarter and leadership wants a ranked cause by Friday. You have fct_time_entry, fct_invoice_line, fct_engagement and dim_consultant, plus a workday and regional holiday calendar. Decompose the move into available hours per head, billable utilisation and realised dollars per billable hour, separate calendar effects from behaviour, split within-segment change from mix, and deliver a ranked attribution with uncertainty. Name the one further cut you would run if the leading term is ambiguous.

Approach

Write the identity before computing anything: fees per head = (available hours per head) x (billable utilisation) x (realised dollars per billable hour), where the last term is recognised fees divided by approved billable hours. Verify the product reproduces the reported 9% exactly; if it does not, the scope of the three inputs disagrees and nothing downstream is trustworthy.
Decompose in logs: d ln(fees per head) = d ln(available per head) + d ln(utilisation) + d ln(realised rate). The log form is exactly additive for a product, so contributions sum to the total without an interaction residual and cannot attribute more than the whole move.
Strip the calendar first, because it is the term nobody can act on: available hours per head moves with scheduled workdays, regional public holidays, leave and any change in fte_fraction mix. Quantify how many of the nine points are calendar and quote the remainder as the behavioural move.
Split each surviving term by practice_area, level, region and pricing_model, and within each split separate within-segment change from mix using the weighted three-term decomposition, since a shift toward lower-rate levels reads as a rate decline that no individual rate produced.
Attach uncertainty at the unit that generates it: anything fee-side clusters at client_id because engagements in one account share a partner, a rate card and a team. With fewer than roughly forty clusters use a wild cluster bootstrap or a CR2 correction, since cluster-robust standard errors are downward-biased in that regime.
If realised rate leads and is ambiguous, the disambiguating cut is discount versus write-off versus fixed-fee overrun: compare invoiced fees to the standard-rate value of hours worked, and read written_off_hours and scope overrun by pricing_model.

Follow-up

Two of the nine points are calendar. How do you say that to an executive without sounding like you are explaining the number away?
Utilisation is up and realised rate is down. What single operational behaviour produces both, and how would you confirm it?
What would you need to see to claim this is a demand problem rather than a delivery or pricing one?

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.

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.

How do you handle situations where there is conflicting information regarding project scope or requirements?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Explaining a censored days-to-pay number on a board slide

Easy · censoring · kaplan-meier · executive communication

A finance lead wants average days to get paid for the last four quarters, for a board slide. In fct_invoice_line, rows with status in ('issued','partially_paid','disputed') have paid_at NULL. The mean of (paid_at - issued_at) over paid invoices is 38 days. A Kaplan-Meier median, right-censoring the open invoices at snapshot_date minus issued_at, is 51 days. You get two sentences and one chart. Explain the number you put on the slide, the gap between the two figures, and what will make that number move next quarter.

Approach

Name the probe: whether you can give a non-technical decision-maker one number, the direction of the error in the alternative, and the reason, without teaching survival analysis.
Explain the mechanism in business language. Invoices that have been paid are disproportionately the ones that pay fast; the slow ones are still open and therefore missing from the 38-day average. The error is one-directional and it grows as collections get worse, which is exactly when the number matters.
Commit to one headline. Either the Kaplan-Meier median, or a restricted mean days-to-pay capped at a fixed horizon such as 90 days, which is easier for a finance audience to audit. Say which you used and why, and do not put both headline numbers on the slide.
Make the chart the survival curve or a simple share-paid-by-day-k curve rather than a bar of averages, because the audience question is really when cash arrives, not a single moment.
State the forward behaviour before you are asked: the figure for a recent quarter will rise or fall as open invoices resolve, so the slide carries the snapshot date and the share of invoices still open.

Follow-up

The exec asks why the number printed on last quarter's slide no longer reproduces. What is your answer, and what would have prevented the question?
How would you report this by account_tier without putting six survival curves on one slide?

Scoping a one-line question about practice profitability in twenty minutes

Medium · scoping · metric definition · exclusions

A practice leader asks, in one line, whether the data_and_ai engagements are profitable. You have fct_engagement, fct_time_entry and fct_invoice_line, and twenty minutes before they travel. Some engagements are retainer or outcome_based, so contracted_hours is NULL; some invoices are unpaid; pursuit hours sit on charge_code = 'client_pursuit' with engagement_id NULL. Produce the scoping conversation: the questions you ask, the metric you commit to in writing, the exclusions you will have to make, and what you deliver by when.

Approach

Name the probe: whether you convert an ambiguous one-liner into a decision with a metric attached, or start querying and return a number nobody can act on.
Ask what changes based on the answer. Repricing the rate card, changing the staffing pyramid, and deciding whether to keep selling fixed_fee are three different questions, and they need different cuts of the same data. Pick the one the leader actually has authority over.
Commit the metric in writing on the spot: fees plus milestone plus credit_note lines from fct_invoice_line as the revenue side, the sum of hours times cost_rate_usd over all approved entries, billable and non-billable alike, as the cost side, inception to date, reported separately by pricing_model.
List what you will not answer and why: overrun on retainer and outcome_based work, because contracted_hours is NULL there and needs an explicit predicate rather than a silent NULL drop; cash collected, because unpaid invoices are censored; and pursuit cost, which has no engagement_id and must either be allocated on a stated rule or reported as a separate line.
Commit two deliverables with dates rather than one vague one: the stratified margin table within two days, the mix decomposition within a week. Name the single assumption that, if wrong, flips the conclusion.

Follow-up

They insist on one number for a partner meeting. Which single number do you give, and what sentence goes with it?
If you had to allocate client_pursuit hours to engagements, what rule would you use and how would you show the answer is not sensitive to it?