Interview guides · Barbaricum

Barbaricum Data Scientist Interview Questions & Guide 2026

Diagnose a metric move for Barbaricum data scientist prep: split by segment, rule out a logging change, separate mix shift from behaviour.

The 60-second brief

As a Data Scientist/App Developer at Barbaricum, you are stepping into a hybrid, high-impact role that sits at the intersection of advanced analytics and practical software engineering. Barbaricum operates heavily within the defense, intelligence, and national security sectors. In this role, your work directly supports critical government missions, transforming raw, complex datasets into actionable tools and user-friendly applications for stakeholders who rely on precise data to make high-stakes decisions.

How much statistics you need depends on the flavour of the seat. Experiment-facing work wants you deep enough to notice that repeated looks at accumulating data inflate the false positive rate of a fixed-sample test; modelling-facing work wants estimation and honest uncertainty intervals.

Focus areas

Analyse at the account grain, cluster errors
Measure churn only on renewal-eligible accounts
Separate contracted seats from actively used seats

Overview

Your impact extends far beyond training machine learning models in a vacuum. Because this position blends data science with application development, you will be responsible for the end-to-end lifecycle of data products. You will build the models, design the architecture, and develop the front-end interfaces or APIs that allow non-technical users to interact with your findings seamlessly. Based in Omaha, NE—a major hub for defense and strategic command operations—your work will directly interface with key mission partners.

This role is ideal for technical problem-solvers who thrive on autonomy and enjoy seeing their models deployed into the real world. You can expect to navigate complex, often sensitive data environments, requiring a balance of rigorous analytical thinking, robust software engineering practices, and a deep appreciation for operational security and user experience.

The Barbaricum interview process

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

Recruiter Phone Screen

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

What to demonstrate

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

How to prepare

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

Technical Evaluation

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

What to demonstrate

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

How to prepare

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

Practical 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

Final Panel Interview

A loop is not scored one interview at a time. The people you meet compare notes afterwards, usually in a meeting you are not in, and the outcome turns on what each of them can say about you when asked. That rewards something other than survival: every room needs one specific thing worth repeating, and none of them can contradict another. The common way to lose is to tell the same project four times with different numbers in it, or to be uniformly fine in a way that leaves nobody with anything to argue for.

What to demonstrate

Whether your account of a project survives being told twice, with the same scale, the same metric definition and the same numbers each time
Whether each interviewer leaves with one concrete claim they could make on your behalf later, rather than an absence of complaints
Whether a question you already answered in an earlier room gets the same answer at the same depth, without visible impatience

How to prepare

Write a one-page fact sheet for your two or three main projects that fixes the numbers you will quote: rows of data, the metric as a single sentence, the effect you measured and how long the work took. Say them aloud from the sheet until they come out identical every time
For each kind of room you expect, decide the one sentence you want that interviewer repeating in a debrief, then check during the mock that you said it outright instead of implying it
Rehearse answering the same project question twice in one sitting, the second time as though you had not just answered it, because the thing that needs fixing is the flatness that creeps into a repeated story

Where candidates lose points

Reporting a mean over accounts when account revenue is heavy-tailed

When a small number of accounts hold most of the revenue, the sample mean is dominated by whichever of them happens to be in the sample, and the sample variance keeps growing as more data arrives instead of stabilising. In that regime the usual central-limit-based confidence interval understates uncertainty, and a single renewal or a single large account's batch job can flip the sign of a measured effect. The fixes are to pre-register a winsorisation or capping rule before looking at the outcome, to report account counts crossing a threshold alongside the revenue figure, or to define the estimand on a bounded transform. Choosing the cap after seeing the result is a separate and worse problem, because the cap then encodes the answer.

Reading consumption metrics before the metering lag window has closed

Usage pipelines land late and correct themselves, which is exactly what is_restated and restated_at record. A dashboard queried on day T sees a partially populated tail for the last several days, so the most recent points always slope downward and always look like a regression. Analysts then explain the artefact, and sometimes ship a change to fix it. Establish the empirical settling time by measuring how much a given usage_date's total moves between first_written_at and its final value, exclude that many trailing days from every reportable figure, and never compare a fresh period against a settled one.

Naming a model class before naming the deployment constraints

Set out the latency budget, the label delay, the retraining cadence, the interpretability requirement and the number of labelled examples, then pick the model that fits them. A boosted-tree answer to a problem where each decision must be explained to the affected user is a well-executed answer to the wrong question.

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

How would you explain a complex machine learning model (like XGBoost) to a military commander with no technical background?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Pick an evaluation metric that matches the cost of each error type, not a default.
Say how the offline result would be validated online before it is trusted.

Follow-up

Where could label leakage enter this setup?
What would you monitor after launch to know the model is still valid?

Walk me through the steps you take to evaluate the success of a classification model.

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
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?

What is cross-validation, and why is it important in the model training process?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Check what information would not exist at prediction time, and exclude it.
Set a baseline first, so any model has something honest to beat.

Follow-up

Where could label leakage enter this setup?
What would you monitor after launch to know the model is still valid?

Join daily usage to the contract version live that day

Medium · as-of-join · pandas · numpy · contracts

fct_usage_daily has account_id, usage_date and net_amount_cents. fct_subscription_period has account_id, subscription_period_id, plan_tier, term_start_date, term_end_date, arr_cents and is_current, with one row per contract term version and every amendment inserting a new row. Attach to each usage row the subscription_period_id whose term brackets usage_date (term_start_date <= usage_date <= term_end_date). pd.merge_asof and an interval-condition merge are unavailable; use sorting and numpy.searchsorted. Then report monthly net revenue by plan_tier. Terms for one account do not overlap, and usage may fall outside every term.

Approach

Say out loud why the cheap version is wrong: joining on is_current stamps today's plan tier onto last year's usage, so every account that upgraded has its history reclassified and revenue-by-tier becomes a function of when the query ran.
Sort each account's terms by term_start_date and use np.searchsorted(term_start, usage_date, side='right') - 1 to get the last term that started on or before the usage date. Do it per account group, or globally after encoding (account_id, date) into one monotone key.
searchsorted only enforces the left edge. Validate the right edge afterwards — usage_date <= the candidate's term_end_date — and set the match to NA where it fails. That NA is usage in a gap between contracts and must stay visible instead of being folded into the expired term.
Assert non-overlap before trusting the lookup, and write the assertion so it is capable of passing. prev_end = terms.groupby('account_id').term_end_date.shift() is NaT on each account's first row, and NaT < Timestamp evaluates to False rather than NA, so a comparison followed by .fillna(True) has nothing left to fill and the assertion fires on every account's first term whatever the data looks like. Guard the null yourself: assert (prev_end.isna() | (prev_end < terms.term_start_date)).all(). The failure mode of getting this wrong is not a false alarm you notice once — it is an assertion someone deletes because it never passes, after which overlapping terms make searchsorted return one of them with no trace in the output.
Aggregate after the join, grouping by (usage_date month, plan_tier) with dropna=False so the unmatched bucket appears as its own row and the total still ties to the ungrouped sum of net_amount_cents.

Worked solution (35 min)

terms = terms.sort_values(['account_id','term_start_date']); prev_end = terms.groupby('account_id').term_end_date.shift(); assert (prev_end.isna() | (prev_end < terms.term_start_date)).all()
Per account group: idx = np.searchsorted(g.term_start_date.values, u.usage_date.values, side='right') - 1; rows with idx < 0 are unmatched.
Gather subscription_period_id, plan_tier and term_end_date by positional index, then null the match wherever usage_date > the gathered term_end_date.
monthly = joined.assign(month=joined.usage_date.dt.to_period('M')).groupby(['month','plan_tier'], dropna=False).net_amount_cents.sum()

Expected result: Exactly one output row per input usage row, a subscription_period_id that is NA precisely for usage outside every term, and a monthly table whose values sum to the ungrouped total of net_amount_cents once the NA-tier bucket is included.

Verify it yourself

len(joined) == len(usage); the join must not fan out.
joined.net_amount_cents.sum() == usage.net_amount_cents.sum().
For every matched row, assert term_start_date <= usage_date <= term_end_date directly rather than trusting searchsorted.
Run the non-overlap assertion on a frame where every account has exactly one term: prev_end is NaT throughout, so the assertion must pass. An assertion that fires there is testing the shift, not the data.
Spot-check one account that upgraded mid-term: its usage before the amendment must still carry the old plan_tier.

Follow-up

An amendment takes effect on the 17th of a month. How do you report that month's revenue by tier?
What changes if terms can overlap because of a co-term amendment?
How would you verify this against a SQL implementation using a BETWEEN condition?

Sessionise interactive API traffic with a thirty-minute inactivity gap

Medium · window functions · sessionisation · gaps and islands

fct_api_request carries account_id, user_id (null for service accounts), request_at, traffic_class and http_status. Using only traffic_class = 'interactive' rows with a non-null user_id, group each seat's requests into sessions on a 30-minute inactivity threshold: a request more than 30 minutes after the previous request from the same (account_id, user_id) opens a new session. For one ISO week return, per account, the session count, the median session duration in minutes and the median requests per session. A single-request session has a duration of zero.

Approach

Filter first: traffic_class = 'interactive' and user_id IS NOT NULL. Machine traffic has no sessions in any useful sense, and leaving CI or batch rows in produces sessions that are really cron schedules.
Get the previous timestamp with LAG(request_at) OVER (PARTITION BY account_id, user_id ORDER BY request_at). Partitioning by user_id alone stitches one person's work across two different accounts into one fabricated session, because a human holds memberships in several accounts.
Flag a boundary where the lag is NULL or request_at - lag > interval '30 minutes'. Decide and state whether exactly 30 minutes continues the session; strictly greater is the conventional choice and needs to be written down either way.
Assign session ids with SUM(boundary::int) OVER (PARTITION BY account_id, user_id ORDER BY request_at ROWS UNBOUNDED PRECEDING), the standard running-count construction for islands.
Roll up to sessions with min(request_at), max(request_at) and count(*), then to accounts with percentile_cont(0.5) WITHIN GROUP (ORDER BY ...). Use medians, not means: session length is strongly right-skewed and one long-running client dominates the average.

Follow-up

Sessions belonging to colleagues in one account are correlated. What does that do to a t-test on session length across an experiment arm?
A long-poll or streaming endpoint keeps a connection open for hours. How do you stop it reading as one twelve-hour session?

Net revenue retention on a cohort frozen twelve months back

Hard · retention · as-of join · cohort arithmetic

fct_subscription_period carries subscription_period_id, account_id, arr_cents, plan_code, term_start_date, term_end_date, booked_at, amendment_type, superseded_by_id (the subscription_period_id of the version that replaced this one, null on the live version of a lineage) and is_current. Compute net revenue retention for month M: the summed arr_cents at M for the set of accounts holding arr_cents > 0 at M-12, divided by that same set's arr_cents at M-12. An account can hold more than one live subscription, a churned account contributes zero rather than dropping out, and nothing signed after M-12 may enter either side. Return the ratio plus the expansion, contraction and churn components in cents.

Approach

Write one reusable as-of ARR snapshot parameterised by a date: rows whose term brackets the date AND whose booked_at is at or before the date, then only the version of each lineage that is still live at that date, then sum arr_cents per account. The booked_at guard matters because an amendment signed in advance otherwise co-exists with the term it replaces and double counts the account.
Collapse the lineage on superseded_by_id, not on any attribute of the contract. Keep a row when superseded_by_id IS NULL, or when the successor it points at was booked after the date. Ranking with ROW_NUMBER() OVER (PARTITION BY account_id, plan_code ...) instead is wrong in both directions: an amendment that moves the account from one plan_code to another puts the old and new versions in different partitions, so both are rank 1, both bracket the date, and the account's arr_cents is counted twice; and two genuinely concurrent subscriptions that happen to share a plan_code land in one partition, so one of them is deleted.
Resolve the successor with a LEFT JOIN back to fct_subscription_period on subscription_period_id, and treat a missing successor as not superseded. An inner join would silently delete an account's ARR on a dangling pointer, which is a data-quality bug in the source, not a retention movement.
Never use is_current for the M-12 side. is_current describes today; using it at the historical snapshot backdates the present contract onto last year's cohort and makes retention look like 100 percent by construction.
Freeze the cohort from the M-12 snapshot where arr_cents > 0, then LEFT JOIN the M snapshot onto it and COALESCE the missing side to zero. An inner join deletes exactly the churned accounts, which is the single largest way this number gets overstated.
Return a ratio of sums, not a mean of per-account ratios. The two are different estimands: contraction is floored at zero while expansion is unbounded, so the mean of ratios is both biased relative to the aggregate and far noisier on a skewed revenue base.
Decompose per account on the delta: positive delta is expansion, negative delta with a non-zero M value is contraction, a zero M value is churn. The three components must reconcile to numerator minus denominator.
Prove no leakage: any account whose first contract began after M-12 must be absent from both sides, and the cohort row count must be identical in the numerator and denominator.

Worked solution (45 min)

Write arr_asof(d) as a CTE or lateral: from fct_subscription_period s take rows with term_start_date <= d AND term_end_date >= d AND booked_at <= d, LEFT JOIN fct_subscription_period succ ON succ.subscription_period_id = s.superseded_by_id, keep the row when s.superseded_by_id IS NULL OR succ.subscription_period_id IS NULL OR succ.booked_at > d, then sum arr_cents per account_id across every surviving version.
Sanity-check the lineage rule on one amended account before going further: at a date after the amendment, the account must contribute exactly one version per lineage even when the amendment changed plan_code, term dates or both.
Materialise base = arr_asof(M-12) filtered to arr_cents > 0, and curr = arr_asof(M).
LEFT JOIN curr onto base on account_id and COALESCE(curr.arr_cents, 0) AS arr_now.
Compute nrr = sum(arr_now)::numeric / NULLIF(sum(base.arr_cents), 0), and the three components with SUM(...) FILTER on the sign of arr_now - base.arr_cents and on arr_now = 0.
Reconcile: assert sum(arr_now) - sum(base.arr_cents) = expansion - contraction - churn, and assert the cohort account count is identical on both sides.

Expected result: A single row with nrr as a ratio, plus expansion_cents, contraction_cents and churn_cents as positive magnitudes satisfying numerator minus denominator equals expansion minus contraction minus churn, over a cohort whose membership is fixed at M-12.

Verify it yourself

At each snapshot date, the surviving row count per account equals the number of distinct live subscription lineages on that account, not the number of contract versions bracketing the date.
For an account whose amendment changed plan_code, the snapshot returns one version and its snapshot ARR equals the amended arr_cents; swapping in a ROW_NUMBER partition on (account_id, plan_code) returns two versions for that account and inflates the side it sits on, which is the fastest way to see the bug.
The three components reconcile to the numerator-denominator delta to the cent.
The distinct account count is the same in the numerator and the denominator.
No account with a first term_start_date after M-12 appears anywhere in the output.
Re-running after new accounts sign leaves the ratio unchanged, which is the defining property of a frozen cohort.

Follow-up

Net revenue retention can rise while the business shrinks. Show one mechanism and name the guardrail that catches it.
How do you handle an account that co-terms two subscriptions into one mid-window, so the subscription count changes but the money does not?
Finance computes this from invoiced amounts and gets a different number. Which is right for which question?

How do you prioritize your tasks when you are responsible for both data science research and application development?

Medium · product sense and metrics

Approach

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.
Restate the decision this analysis has to support, and who acts on the answer.

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 do you manage dependencies and environment configurations in your Python projects?

Medium

Approach

Say what you would check first and why it is the highest-information step.
Clarify what is being asked and what a complete answer would contain.
Work from the decision backwards to the evidence you would need.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

How do you ensure the applications you build are secure against common vulnerabilities?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
State your assumptions explicitly before working the problem.
Say what you would check first and why it is the highest-information step.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Define success for widening the free usage allowance

Easy · north star · guardrails · gameable metrics · unit economics

A proposal triples the free-tier included allowance on the requests_thousands SKU. Finance expects the count of weekly active organisations to rise. You have fct_usage_daily (billable_quantity, included_quantity_applied, overage_quantity, net_amount_cents, cogs_cents, is_restated), dim_account (account_status, is_internal, converted_to_paid_at) and fct_api_request (traffic_class, http_status, environment). Define the primary metric this change should be judged on, two guardrails, and the numeric decision rule you would agree before launch. State explicitly how the obvious metric can be satisfied without the product getting better for anyone.

Approach

Name the mechanism before naming any metric. A larger allowance moves quantity out of overage_quantity into included_quantity_applied, so net_amount_cents falls while cogs_cents is unchanged, and accounts that previously sat under the production volume floor now cross it without paying anything. A count of active accounts therefore moves for reasons that have nothing to do with the product.
Set the primary metric at the account grain: weekly active organisations with at least one successful request (http_status < 400) in a workspace where environment = 'production' and traffic_class in ('interactive','batch'), over accounts with is_internal = false. Report accounts that existed before launch separately from the post-launch signup cohort, so the number is not quietly an acquisition metric.
Attach the two guardrails that close the gaming routes. First, gross margin per account, (sum(net_amount_cents) - sum(cogs_cents)) / sum(net_amount_cents) computed per account per month and reported as a distribution, specifically the count and revenue share of margin-negative accounts. Second, the free and trial share of weekly active organisations.
Add the conversion link the change is supposed to buy: free-to-paid conversion within 90 days for the affected cohort, read from converted_to_paid_at. Without it, the primary metric rewards permanent free use indefinitely.
Write the decision rule with numbers before launch: ship if weekly active organisations rise by at least the pre-agreed threshold, 90-day free-to-paid conversion does not fall, and the count of margin-negative accounts stays under a stated cap. Note that the margin guardrail can only be read after the metering settling window has closed, since fct_usage_daily rows restate in place.

Worked solution (20 min)

Compute per-account monthly gross margin from fct_usage_daily as (sum(net_amount_cents) - sum(cogs_cents)) / sum(net_amount_cents), grouped by account_id and calendar month, restricted to usage_date values older than the measured metering settling window.
Simulate the allowance change on the trailing settled month: for each account, move min(overage_quantity, allowance_delta) from overage_quantity into included_quantity_applied and recompute net_amount_cents at that account's existing list and discount rates, leaving cogs_cents untouched.
Recount weekly active organisations under the simulated state with the production volume floor applied, and split the delta into accounts that were already active and accounts newly crossing the floor.
Report the before and after margin distribution, the count of accounts crossing into negative margin, and the revenue share those accounts hold.

Expected result: A two-part readout: the simulated change in weekly active organisations split by whether the account was already active, and the change in the count and revenue share of margin-negative accounts. The answer names the primary metric (weekly active organisations on qualifying production workloads), two guardrails (the per-account margin distribution and the free/trial share of actives), a pre-agreed numeric rule, and states plainly that the primary metric alone can be satisfied entirely by accounts that will never pay.

Verify it yourself

Simulated net_amount_cents can only fall or stay flat for every account; any account whose simulated revenue rises means the allowance was applied in the wrong direction.
cogs_cents must be byte-identical before and after, because an allowance changes what is charged, not what is served.
The sum of per-account margins must reconcile to the aggregate margin computed directly from fct_usage_daily totals over the same accounts and months.

Follow-up

Weekly active organisations jump 14% in the first week after launch. How much of that can you attribute to the change with no control group, and what would you need to do better?
An account settles at usage permanently just below the new allowance and never pays. Does your primary metric count it as a success, and should it?

Blended gross margin fell while every segment improved

Medium · mix shift · simpsons paradox · as-of join

Blended gross margin across paying accounts fell from 71 percent to 66 percent over two quarters, yet margin improved inside every plan_tier and every deployment_model. You have fct_usage_daily (account_id, sku_code, usage_date, net_amount_cents, cogs_cents), fct_subscription_period (account_id, plan_tier, term_start_date, term_end_date) and dim_account (account_id, deployment_model, is_internal, effective_from, effective_to). Produce an exact decomposition of the five-point move into mix, rate and interaction terms, and name the segment shift that carries it.

Approach

Write blended margin as a revenue-weighted average of segment margins, m = sum over i of w_i * m_i, where w_i is the segment's share of net_amount_cents. Margin is a ratio of sums, so only revenue weights reproduce the blended figure.
Apply the exact three-term decomposition: delta_m = sum(delta_w_i * m_i0) + sum(w_i0 * delta_m_i) + sum(delta_w_i * delta_m_i), read as mix, rate and interaction. It is exact by construction, so the three terms must sum to the observed change with no residual.
Build segments from as-of joins: take the fct_subscription_period version whose term brackets each usage_date and the dim_account version whose effective_from and effective_to bracket it, rather than joining on is_current and backdating today's attributes over last year's usage.
Run the decomposition on more than one segmentation, at minimum plan_tier, deployment_model and sku_code, since the mix that actually moved may not be the one anybody already suspected.
Once the driving segment is identified, determine whether the shift came from acquisition landing new accounts in a lower-margin segment or from existing accounts migrating, because those have different owners and different remedies.

Follow-up

The interaction term is sometimes large. What does it mean operationally, and when would you prefer a log-mean decomposition that distributes it across the other two terms?
Revenue-weighted average margin is one construction. What changes if leadership wants margin weighted by account count, and which question does each version answer?

Suggested practice checklist

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

Day 1 — Build a fixture you can check answers against

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

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

Day 2 — Joins, filters and NULL semantics

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

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

Day 3 — Window functions and frames

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

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

Day 4 — The four analytical query patterns

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

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

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

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

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

Day 6 — One day for everything that is not SQL

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

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

Day 7 — Full loop rehearsal

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

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

Rigour against a deadline

Nearly every data role forces a trade between the analysis you want and the one that fits the decision window. Prepare a case where you deliberately shipped something less rigorous, named the weakness to the person relying on it, and said what would change your answer. The naming is the part interviewers listen for.

Describe a situation where you had to push back on a stakeholder's request because it was technically unfeasible. How did you handle it?

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.
Pick a story where you drove the decision, not one where you observed it.

Follow-up

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

How do you handle missing or corrupted data in a dataset before training a model?

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.
Quantify the outcome, including what you would not claim credit for.

Follow-up

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

State the measured impact of your own work honestly

Hard · impact · causal inference · self-assessment

You are asked for the business impact of a renewal-risk worklist you shipped nine months ago. Customer success used it, and renewals in the covered segment came in 6 points above the prior year. Coverage was assigned by the team itself: they worked the top of your list and also the accounts they were already worried about. Produce the impact claim you are willing to defend, the number you refuse to claim, and the design you would have asked for at the start.

Approach

The interviewer is probing whether you can separate what you shipped from what you caused, and whether you would have built the measurement in rather than reconstructing it afterwards. Both halves are being scored.
Name the confound precisely. Coverage assignment is doubly selected: the largest accounts get an owner because they are valuable, and distressed accounts get one because they are at risk. The naive covered versus uncovered comparison mixes a strong positive selection with a strong negative one and can come out with either sign depending on which rule dominated. Matching on account size does not fix it, because the risk signal that triggered coverage is the same signal that predicts the outcome.
Split the claims by what each needs to be true. Ranking quality is defensible from precision at k on out-of-time renewals. Adoption is defensible from timestamps showing what share of listed accounts were contacted. The outcome claim is not defensible without a design, and saying so is the point of the exercise.
Look for identification before giving up on it. A capacity cut-off, a territory boundary, or a period in which the list existed but was unstaffed can assign coverage for reasons unrelated to account health, and any of those supports a bounded estimate.
State the design you would ask for now and its price: a randomly withheld slice of the list, held for two renewal quarters, with the expected cost in renewals stated openly. That cost is what it takes to be able to answer this question at all.
Give a bounded number rather than none. Six points with an explicit statement of how much of it you can attribute is more useful than either claiming the whole figure or declining to quantify anything.

Follow-up

Your manager wants the 6 points in a promotion packet. What wording do you accept, and what do you strike?
What would have had to be true for the naive covered versus uncovered comparison to be valid?
If the holdout costs the team real renewals, how do you justify asking for it, and to whom?