Interview guides · Blue Data Consulting

Blue Data Consulting Data Scientist Interview Questions & Guide 2026

SQL practice for Blue Data Consulting data scientist candidates: window functions, funnel and retention queries, joins that quietly drop rows.

The 60-second brief

As a Data Scientist at Blue Data Consulting, you will serve as a critical bridge between raw data and actionable business strategy. The role suits professionals who have both the technical rigor to manipulate complex datasets and the product intuition to translate those findings into meaningful improvements for Blue Data Consulting's clients. You will be expected to move beyond simple reporting to drive experimentation, optimize product metrics, and provide evidence-based recommendations that shape the direction of the firm's consulting engagements.

In modelling rounds the live question is usually why this model class for this problem, and how you would know six weeks after launch that it is still working. Deriving gradients by hand is rarely what is being probed.

Focus areas

Segment margin by pricing model before comparing
Reconstruct pipeline stage history from mutable rows
Correct late-entered timesheets before trending recent weeks

Overview

This position demands a high level of autonomy and a proactive mindset. You will work within diverse project teams, requiring you to communicate complex statistical concepts to non-technical stakeholders clearly and persuasively. Whether you are diagnosing a sudden drop in a core product metric or designing a robust A/B test to validate a new feature, your work will directly influence how the firm's clients make decisions. The environment is fast-paced, intellectually demanding, and focused on delivering high-impact, data-driven solutions in an ambiguous consulting landscape.

The Blue Data Consulting interview process

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

Initial Screening

Whoever runs this call is usually not a practitioner. They take notes, and a hiring manager skims those notes later, so the real question is whether your work survives being written down by someone outside the field. Test every project sentence against that: could a non-specialist repeat it correctly without knowing what a propensity score is? Carry a plain-language version of each project and one reason you want this particular role that you could not copy onto another application. Vagueness at this stage reads as inexperience, even when the underlying work was genuinely deep.

What to demonstrate

Whether a non-specialist can restate your projects accurately, since their paraphrase is what reaches the hiring manager
Whether your reason for wanting the role points at the work itself rather than the company's reputation
Whether your language signals the level being screened for: what you decided yourself versus what you were handed

How to prepare

Write a two-sentence, jargon-free version of each major project: the question nobody could answer, and the decision your work changed. Read it to someone outside data and have them repeat it back
Point your 'why this role' answer at something concrete in the job description or the product surface you would be working on, and keep it to two sentences
Have two questions ready about measurement: which metric the team is held to, and who acts on an analysis once it lands

Technical Rounds

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

What to demonstrate

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

How to prepare

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

Behavioral Interviews

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

What to demonstrate

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

How to prepare

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

Where candidates lose points

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.

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.

Analysing at a different unit than the one randomised

Say out loud what was randomised (user, device, account, cluster) and make the analysis unit match, or account for the clustering with cluster-robust standard errors, the delta method, or aggregation up to the randomised unit. Randomising users and then running a test over sessions understates variance and inflates the false-positive rate.

Generalising beyond the population the sample actually supports

State the frame the sample was drawn from and where it diverges from the population you want to talk about: time window, platform, geography, opt-in. If a group is excluded from the frame, either weight to known margins or narrow the claim rather than quietly extending it.

Questions & practice

Simulate the chance a capped engagement crosses its cap

Medium · monte carlo · bootstrap resampling · pricing caps

A capped time-and-materials engagement has not_to_exceed_usd = 400000, has billed 250000 to date, and bills at a blended bill_rate_usd of 250. Fourteen delivery weeks remain before planned_end_date. You have that engagement's last twenty weekly totals of approved billable hours as a pandas Series. Estimate the probability that cumulative billable value crosses the cap before the planned end, and the expected unbillable hours if it does. Resample the observed weeks; do not assume normality. Report a Monte Carlo standard error and justify the number of draws.

Approach

Reduce the deterministic part first: the cap still affords (400000 - 250000) / 250 = 600 hours, so the whole question is the distribution of the sum of fourteen future weekly hour totals against a fixed threshold of 600.
Before resampling, check the twenty observed weeks for trend and lag-1 autocorrelation. Independent resampling is defensible only if the weeks are exchangeable; on a ramping engagement use a moving-block bootstrap or model the ramp, because i.i.d. draws understate the upper tail, which is exactly the tail the question is about.
Draw one (B, 14) array with np.random.default_rng().choice(observed, size=(B,14), replace=True), sum along axis 1, and compute p_hat = mean(total > 600) in one vectorised pass.
Report expected unbillable hours two ways: unconditional mean(maximum(total - 600, 0)) for expected loss, and the mean conditional on a breach for how bad a breach is when it happens. The second is the number that drives a change-order conversation.
Attach se(p_hat) = sqrt(p_hat(1 - p_hat)/B) and pick B from the precision you need: plus or minus one point at 95% confidence needs about 1.96^2 * 0.25 / 0.01^2, roughly 9600 draws at the worst case p = 0.5.
State the assumptions that would flip the answer: constant blended rate, no scope change, no holiday weeks inside the fourteen, and a cap that applies to fees rather than to fees plus expenses.

Follow-up

Hours past the cap still cost money. Restate the result as expected gross margin rather than a probability.
Two weeks of time are entered but not yet approved. How do you fold them in without double-counting?
A change order that raises the cap is judged 60% likely. How does that change the number you present, and to whom?

Measure the timesheet backfill curve and pick a reporting cutoff

Easy · late-arriving data · data quality · cohort curves

time_entries has work_date (date), entered_at (timezone-aware UTC timestamp), hours and status. Given a snapshot_date, restrict to work_date in [snapshot_date - 180 days, snapshot_date - 60 days] so every cohort is fully observed. For k = 0..45, compute F(k): the share of a work_date cohort's final hours that already existed as of work_date + k days, pooled across cohorts. Return the 46-point curve and the smallest k with F(k) >= 0.99. Some rows are entered before the work date; those lags are real, not errors.

Approach

Compute lag = (entered_at converted to the reporting timezone and taken as a date) - work_date in whole days, then clip negative lags to 0 instead of dropping them; leave and planned time are routinely entered ahead of the work date and dropping them deflates the early curve.
Take cohort totals as groupby(work_date).hours.sum() over the restricted window. These are final only because the window stops 60 days short of the snapshot, which is why the restriction is in the prompt.
Build the numerator by summing hours per (work_date, lag), sorting by lag, taking a per-cohort cumsum, then reindexing each cohort onto the full 0..45 lag grid and forward-filling, so a cohort with no entries at a given lag holds its previous level rather than disappearing.
Pool as sum(numerators) / sum(denominators) at each k, not as the mean of per-cohort shares. Holiday weeks are tiny cohorts and would otherwise carry the same weight as a full week.
Read k* off the pooled curve and report F(45) with it: if F(45) is below about 0.995 the tail runs past the grid and k* is a lower bound, not the answer.

Follow-up

The dashboard refreshes daily. Would you hold the window back past k*, or publish an as-of-entered_at series instead, and what does each choice cost the reader?
One practice area has a tail twice as long as the rest. Does that change the firm-wide cutoff, or does it change what you publish per practice area?

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.

Worked solution (40 min)

Assemble a frame of (duration_days, event) per non-draft line, with snapshot-based durations for the three open statuses and an explicit, documented rule for written_off.
times = np.sort(df.loc[df.event == 1, 'duration_days'].unique()); for each t, n = (df.duration_days >= t).sum() and d = ((df.duration_days == t) & df.event).sum().
S = np.cumprod(1 - d / n) aligned to times; prepend S(0) = 1.0 so the curve starts where it should.
median = times[np.argmax(S <= 0.5)] if (S <= 0.5).any() else 'not reached'; compute Greenwood variance as S(t)^2 * cumsum(d / (n * (n - d))).
naive = df.loc[df.event == 1, 'duration_days'].mean(); report both, plus the censored share, since the gap between the two scales with it.

Expected result: A step function starting at S(0) = 1.0 that drops only at observed payment days, an integer median or an explicit 'not reached', a Greenwood-based interval, and a naive completed-case mean that sits below the Kaplan-Meier median on data with material censoring.

Verify it yourself

S is non-increasing, starts at 1.0, and never goes below 0; the number at risk at the first event time equals the count of all non-draft lines.
Re-running with every censored row deleted makes the Kaplan-Meier median equal the empirical median of paid durations, since with no censoring the estimator reduces to the empirical survival function.
A six-row toy example worked by hand reproduces the implementation's S values exactly.
The gap between the naive mean and the Kaplan-Meier median grows when you move snapshot_date earlier, which raises the censored share.

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?

Can you explain a time you had to optimize a complex query for a large-scale dataset?

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
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?

Write a SQL query to sort one of the columns in a table based on the length of the given strings.

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

Follow-up

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

How would you use a SQL window function to identify the top three performing segments in a dataset?

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

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

Build a client cohort revenue table from invoice lines

Easy · cohorts · date truncation · revenue recognition

dim_client holds client_id and first_engagement_date. fct_engagement maps engagement_id to client_id. fct_invoice_line holds engagement_id, line_type, period_start_date, amount_usd and status; recognised fees are lines with line_type IN ('fees','milestone','credit_note') and status <> 'draft', and credit notes carry negative amount_usd. Return one row per (cohort_month, months_since_first) giving distinct clients billed and net recognised fees. cohort_month is the month of first_engagement_date; months_since_first is the whole-month gap between period_start_date and first_engagement_date. Long format, no pivot. Decide explicitly what to do with clients whose first_engagement_date is NULL.

Approach

Filter fct_invoice_line to recognised lines first (line_type, status <> 'draft'), and key recognition on period_start_date rather than issued_at or paid_at. Billing period, issue date and payment date are three different clocks, and mixing them produces a cohort table nobody can reconcile against finance.
Join invoice lines to fct_engagement to reach client_id, since invoice lines carry engagement_id only, then to dim_client for first_engagement_date.
Truncate both dates to month, then compute months_since_first as 12 * (year(bill_month) - year(cohort_month)) + (month(bill_month) - month(cohort_month)). Doing it on truncated months is what keeps a client billed on day 2 of the third month in bucket 2 instead of bucket 1.
Aggregate to (cohort_month, months_since_first) with SUM(amount_usd) and COUNT(DISTINCT client_id), keeping credit_note lines inside the sum so the figure is net rather than gross.
Handle NULL first_engagement_date as an explicit exclusion and report how many clients and how much revenue that removes, rather than letting the join drop them silently. That exclusion is why the output's total is smaller than the filtered line set's total, so carry the excluded figure as a named number rather than discovering the gap later.

Worked solution (25 min)

Write the recognised-lines CTE with the line_type and status predicates, and check its row count against the unfiltered table so you know how much you excluded.
Join through fct_engagement to dim_client and confirm the join does not multiply rows: a line has exactly one engagement, an engagement exactly one client.
Add the two date_trunc expressions and the integer month arithmetic, then eyeball ten rows against their source dates.
Group and aggregate, then sort by cohort_month and months_since_first.
Run a separate count of clients with NULL first_engagement_date, and a separate sum of their recognised fees, and note both beside the output.

Expected result: A long-format table of (cohort_month, months_since_first, active_clients, net_fees_usd), plus two scalars reported beside it: the count of clients with a NULL first_engagement_date and the recognised fees attached to them. months_since_first is a non-negative integer on every row; a negative value means an invoice period precedes the recorded first_engagement_date, which is a data-quality finding to report, not something to clamp to zero.

Verify it yourself

SUM(net_fees_usd) over the whole result equals SUM(amount_usd) over the filtered lines whose engagement resolves to a client with a non-NULL first_engagement_date. It does not equal SUM(amount_usd) over the full filtered line set, which is larger by exactly the excluded NULL-cohort revenue: add that separately reported figure back and the two agree. Any remainder after that is a line whose engagement_id has no matching row in fct_engagement, which is a referential finding to name rather than absorb.
COUNT(DISTINCT client_id) at months_since_first = 0 within a cohort_month is less than or equal to the number of clients whose first_engagement_date falls in that month.
Zero rows have a negative months_since_first; if any appear, name the clients rather than filtering them out.
Re-running with period_end_date in place of period_start_date moves only lines whose billing period spans a month boundary.

Follow-up

How would you turn this table into a 12-month net revenue retention ratio, and what exactly becomes the denominator?
One account contributes 40 percent of a cohort's month-13 fees. How do you present the cohort so a reader sees that before they act on the ratio?
If subsidiaries roll up to parent_client_id, which first_engagement_date defines the cohort, and does the rollup change the cohort a subsidiary lands in?

How would you design a product metric to measure the success of a new onboarding flow?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.
Fix the population and the time window before naming any metric.

Follow-up

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

How do you balance short-term metric gains against long-term user experience?

Medium · product sense and metrics

Approach

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.
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?
Which segment would you cut first, and what would that rule out?

You notice a 10% metric drop in daily active users; 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.
State what result would change your recommendation, so the answer is falsifiable.
Fix the population and the time window before naming any metric.

Follow-up

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

How would you determine the sample size required for an A/B test with a specific conversion goal?

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.
Say whether units interfere with each other, and switch design if they do.

Follow-up

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

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

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.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

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

Design a bookings metric a partner incentive cannot game

Medium · gaming · bookings · guardrails · incentive design

Leadership wants to pay partners on bookings, defined today as SUM(contract_value_usd) from fct_engagement at sold_at. Using fct_engagement (engagement_id, client_id, prior_engagement_id, pricing_model, contract_value_usd, sold_at, status, close_reason) and fct_invoice_line (engagement_id, line_type, amount_usd, status), list three distinct ways a partner could raise the current metric without creating value for the firm, then propose a replacement primary metric plus two guardrails that close them. For each guardrail, name the lag in days or months before it can be read.

Approach

Enumerate the gaming paths concretely rather than abstractly. Discount to close: contract_value_usd rises while margin falls. Re-book the same scope as a change order, so a prior_engagement_id chain counts one body of work several times. Book an optimistic contract_value_usd on outcome_based or retainer work, where nothing pins the number until delivery. Book work with no delivery capacity behind it, which surfaces later as cancelled or closed_early.
Replace the primary with a value measure that only completes on delivery: chain-collapsed booked value, netted against the same chain's eventual credit_note and write_off lines, or booked value multiplied by a sold-margin estimate computed at signature.
Guardrail one, booking-to-realisation: SUM(amount_usd) on line_type IN ('fees','milestone') twelve months after sold_at, divided by contract_value_usd, reported per partner. This closes the discount path and the optimistic-value path. Lag: twelve months.
Guardrail two, early-termination rate: share of booked value on engagements reaching status IN ('cancelled','closed_early') within 90 days of start_date, plus a net-new versus expansion split built from prior_engagement_id. This closes the re-booking path and the no-capacity path. Lag: 90 days.
State the residual honestly. Every signal that detects gaming resolves after the bonus would be paid, so the workable design is a holdback, paying a share at signature and the balance against the trailing measures, not a cleverer single number.

Worked solution (30 min)

Build the extension chain with a recursive CTE over prior_engagement_id, collapse each chain to its root, and compute booked value per chain alongside the raw per-row sum.
For engagements sold at least twelve months ago, compute realised value as SUM(amount_usd) over line_type IN ('fees','milestone','credit_note','write_off') and take the ratio to contract_value_usd.
Break that ratio down by partner and by pricing_model; expect the widest spread on outcome_based and retainer rows.
Compute the 90-day early-termination share of booked value per partner.
Write the payout rule with the holdback split and each guardrail's lag stated in the rule itself.

Expected result: A primary metric of chain-collapsed booked value carrying a sold-margin condition, two guardrails (twelve-month booking-to-realisation per partner, and 90-day early-termination share), and an explicit holdback because both guardrails resolve after the signature date. Three named gaming paths, each mapped to the guardrail that closes it.

Verify it yourself

Chain-collapsed bookings are less than or equal to the row-level sum. If they are exactly equal, prior_engagement_id is unpopulated and the re-booking path is invisible in the data, which is itself the finding.
Booking-to-realisation has a visibly wider distribution on outcome_based and retainer engagements than on fixed_fee.
Every guardrail in the write-up carries a stated lag in days or months.

Follow-up

A partner argues the discount was necessary to win a strategic-tier account. What evidence would you accept for that, and is any of it auditable from these tables?
Your strongest guardrail lags twelve months. What do you pay on during the scheme's first year?

Firm margin rose while every pricing model's margin fell

Easy · mix shift · margin · decomposition

Firm-wide engagement gross margin rose from 31% to 34% quarter over quarter. Split by fct_engagement.pricing_model, margin fell inside time_and_materials, fixed_fee, retainer and outcome_based. Sources are fct_invoice_line (engagement_id, line_type, amount_usd, period_start_date, period_end_date) and fct_time_entry (engagement_id, hours, cost_rate_usd, status). Explain the aggregate move, quantify how much of the 3-point change is mix and how much is within-model, and state which number you put in front of practice leadership and why.

Approach

Write the aggregate as a weighted mean: M = sum over pricing models p of w_p * m_p, where m_p is margin within p and w_p is p's share of the fee base. The weights must be the fee base, because margin is a fees-weighted ratio; weighting by engagement count answers a different question.
Apply the exact three-term decomposition: dM = sum (w_p1 - w_p0) * m_p0 + sum w_p0 * (m_p1 - m_p0) + sum (dw_p)(dm_p). Verify the three components sum to the reported +3.0 points before interpreting any of them.
Identify which weights moved and in which direction; the aggregate can only rise this way if fee share shifted toward the structurally higher-margin models, typically fixed_fee delivered under budget or retainer.
Guard against composition inside each stratum: repeat the same decomposition within pricing_model by practice_area and account_tier, so a within-model decline is not itself an unexamined mix effect.
Confirm both sides of the ratio are on the same clock and the same scope: numerator includes line_type IN ('fees','milestone','credit_note') with credits signed negative; the cost side includes all approved delivery hours, billable and non-billable alike.
Report within-model margin as the headline delivery signal and mix as a separate, named line with its own owner, since mix is a sales decision and margin is a delivery one.

Follow-up

Next quarter the mix reverts. What happens to the aggregate, and what will you have already told leadership so this is not a surprise?
Which of the four pricing models should not be pooled with the others even for a mix-adjusted number, and why?
How would you present this if the mix shift were deliberate strategy rather than accident?

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.

Ownership when the result is inconvenient

Sometimes the honest read is that the initiative did not work, and the person who commissioned the analysis was hoping otherwise. Interviewers want to know whether you softened it. Prepare the case where you delivered an unwelcome result, how you presented the uncertainty without hiding behind it, and what the team did next.

Can you share an example of how you mentored a junior team member or contributed to a team’s learning culture?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Defending your own impact claim without randomisation or clean units

Hard · self-selection · clustered inference · impact measurement

At your review you plan to claim that the realisation dashboard you built recovered 1.4 million dollars. The evidence is that engagement-month realisation rose four points over two quarters among engagements whose leads used it. Adoption was voluntary. There are about sixty client accounts and the top five carry most fees. A new rate card shipped in the same quarter. Write the claim you can defend, the estimate you would actually produce, and what you say when asked for a causal number you cannot get.

Approach

Name the probe: whether you can separate the number you want from the number the data supports, under review pressure, without either inflating it or retreating to saying nothing can be known.
State both identification problems concretely. Voluntary adoption means adopting leads are plausibly the ones who already manage realisation, so the comparison is confounded at the person level. The rate card changes bill_rate_usd, which sits in the realisation denominator, so part of the four-point move is arithmetic rather than behavioural.
Neutralise what you can. Recompute realisation with bill rates snapshotted on work_date, or hold the denominator at the old rate card, so the rate-card change cannot move the metric by construction. Then rerun the comparison.
Get the inference right for the unit count. Cluster at client_id, not engagement, because engagements in one account share a partner, a rate card and a team. With sixty accounts and five carrying most fees, the effective cluster count is far below sixty, so report a wild cluster bootstrap interval rather than plain cluster-robust standard errors, which are biased downward in that regime.
Report both weightings and explain the divergence: an account-weighted estimate describes the typical account, a value-weighted one describes the revenue, and if they disagree a small number of accounts is carrying the result. Then give the decision-relevant sentence: the defensible range, whether its lower bound still clears the build cost, and what a proper staggered rollout would have bought.

Follow-up

The pre-period trends for adopters and non-adopters are not parallel. What do you report then?
You get to design the next rollout. What do you change so the same question is answerable, without randomising individual accounts?

Three requests, one week, and the one you defer

Medium · prioritisation · stakeholder management · scope reduction

Three requests arrive in one week. Finance needs days sales outstanding recomputed for a board meeting in four days. A partner wants a proposal win-rate model for a pursuit review in three weeks. Delivery wants a staffing forecast, with no date attached. You have one week of your own capacity and no analyst. Write the prioritisation you send back, the request you defer, and the message you send to the person whose request you defer.

Approach

Name the probe: whether you prioritise on decision dates and reversibility, or on who asked most forcefully.
Score each request on four things: what decision it changes, the date that decision is made, the cost of being late, and the cost of being wrong. A board figure has a hard date and a high cost of being wrong; a forecast with no date has neither.
Reduce scope rather than dropping work. The win-rate model is the largest piece and the easiest to get wrong, because features written after the decision, such as engagement_id and revised pricing, leak the label, and because withdrawn and no_decision proposals are not missing at random. A two-day descriptive win-rate cut by is_competitive and loss_reason answers most of what a pursuit review needs, with the model scoped separately.
Defer explicitly, with a date and a smaller substitute, rather than leaving a request to decay quietly. Silence is read as agreement and then as failure.
Put the trade-off in writing so it can be overturned by someone with more context than you have, and say what you would drop if the deferred request becomes urgent.

Follow-up

The partner escalates to the practice lead. What do you change, and what do you refuse to change?
What evidence would make you drop the finance work instead?