Interview guides · BNSF Railway

BNSF Railway Data Scientist Interview Questions & Guide 2026

BNSF Railway Data Scientist interview guide: reported rounds, practice questions with worked solutions and a 7-day prep plan.

The 60-second brief

As one of North America's largest freight railroad networks, BNSF Railway operates a vast infrastructure spanning 32,500 route miles across 28 states and three Canadian provinces. A Data Scientist at BNSF Railway sits at the intersection of heavy industrial operations and modern analytical architecture. The primary objective is to transform massive streams of operational, sensor, spatial, and business data into optimized decisions that directly influence network safety, fuel efficiency, velocity, and asset utilization.

Most of the loop measures decision-making under uncertainty rather than recall. You are scored on whether you state your assumptions, commit to an estimate you can defend, and say explicitly what evidence would change it.

Focus areas

Trace an on-time miss to one node
Price a service-level change in working capital
Separate true demand from censored, stocked-out sales

Overview

Unlike pure-play consumer software companies, BNSF Railway operates in a capital-intensive environment governed by rigorous safety regulations and physical constraints. As a Data Scientist, your models directly inform complex operational challenges. You might develop predictive algorithms to forecast network bottlenecks, optimize locomotive routing to minimize fuel consumption, build computer vision models to detect track defects in real time, or design supply chain cost-minimization solutions for intermodal package routing.

The impact of this role is measurable and immediate. A small percentage improvement in fuel efficiency or network velocity translates to tens of millions of dollars in operational savings and significant reductions in carbon emissions. You will work closely with software engineers, operational managers, and business stakeholders, acting as a technical leader who bridges high-level statistical modeling with practical operational implementation.

The BNSF Railway interview process

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

Coding 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

Remote Screening Interview

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

What to demonstrate

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

How to prepare

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

Panel Interviews

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

Behavioral Assessments

Rounds of this kind usually include one question about work that did not go well, and it is the part that carries the most information. Anyone can narrate a shipped win. What the interviewer learns from a project that stalled is how you behave without a result to hide behind: whether you noticed the problem yourself, how long it took, and who you told. Answers that route the failure onto a data pipeline or a reorganisation close the topic without answering it, and the follow-up comes back to your own part.

What to demonstrate

Whether you found the error yourself or someone else found it, and how long it sat before anyone knew
What you changed afterwards, stated as a check you now run rather than a lesson you now believe
Whether the mistake you choose has real cost attached, such as a quarter of misdirected roadmap or a metric that was reported upward, instead of one that flatters you

How to prepare

Choose a failure you caught yourself and be ready to say what tipped you off. A story where someone else caught it is still usable, but you will be asked why you missed it.
Write down the check you added afterwards and where it lives now, so the correction is a concrete artefact rather than a resolution.
Rehearse saying the cost out loud. Candidates shrink the number by instinct once the interviewer is in the room.

Where candidates lose points

Treating shipped units as demand

Shipments are censored at available inventory: on a stockout day the recorded quantity is a supply ceiling, not customer intent, and orders that were never placed because the item showed as unavailable leave no row at all. A forecast fitted on that history learns the constraint, under-forecasts the fast movers that stock out most often, and produces the replenishment that causes the next stockout, so the error compounds in one direction rather than averaging out. The fix is to model demand_qty rather than shipped_qty, flag stockout days with stockout_flag and treat them as censored (fit with a censored likelihood, or estimate unconstrained demand from uncensored periods and comparable locations), and to report how much of the history was censored alongside any accuracy number.

Averaging rates across SKU-locations instead of re-summing

Fill rate, turns, OEE and on-time rate are all ratios whose denominators differ by orders of magnitude between cells, so an unweighted mean gives a slow-moving C item at a small node the same vote as a high-volume A item at a national node. The blended figure then moves whenever the portfolio mix moves, and it can improve in every cell while the company-level ratio worsens, or the reverse, which is Simpson's paradox with a warehouse attached. Always sum numerator and denominator to the reporting level and divide once, and when a rate must be compared across nodes, standardise on a fixed SKU mix before reading anything into the difference.

Crediting a treatment for regression to the mean

Selecting a group because it is extreme (lowest-engagement users, accounts having their worst month, the bottom decile of a score) moves that group's expected next-period value back toward the average even under no treatment, by exactly as much as the selecting measure is imperfectly correlated with its own later value. Compare against units that met the same selection rule and went untreated, or use two pre-periods so the bounce-back is visible before the intervention starts. A pre-post number on a group chosen for being extreme measures the selection rule, not the treatment.

Reading an observational correlation as a causal effect

Name the confounder you are most worried about and the design that would remove it: an experiment, a difference-in-differences with a checked pre-period trend, an instrument, or a regression discontinuity. When none is available, state which direction the bias likely runs and bound the claim accordingly.

Questions & practice

Explain the mathematical trade-offs between dynamic regression models and traditional ARIMA structures when forecasting regional train arrival delay times.

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Frame the prediction: the label, the moment of prediction, and the action it triggers.
Check what information would not exist at prediction time, and exclude it.

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?

Audit a daily inventory snapshot for silent corruption

Easy · data quality · invariants · completeness

You receive fct_inventory_daily with inventory_date, sku_id, location_id, on_hand_qty, allocated_qty, blocked_qty, available_qty, in_transit_qty, demand_qty, shipped_qty, stockout_flag and standard_cost_cents. The table is supposed to carry one row per active sku-location per date, including days with no movement. Write a check suite that returns one row per named check with the number of sku-location-days affected, the share of the scope, and the on-hand value at standard cost sitting behind the failures. Do not repair anything; the deliverable is the evidence.

Approach

Start with the arithmetic identity the table declares: available_qty must equal on_hand_qty minus allocated_qty minus blocked_qty. Report exact row counts rather than a boolean, because a handful of violations is a feed bug and a uniform offset is a definition change upstream.
Check sign constraints separately from the identity. Negative on_hand_qty usually means receipts posted out of order, while negative available_qty is legitimate at some sites when allocation is allowed to over-commit, so flag it and ask rather than assuming corruption.
Check calendar completeness per sku-location by comparing the observed row count against the number of dates between that pair's first and last appearance. Count the missing dates without reindexing the frame, because filling them with zeros converts a feed gap into a plausible run of zero-demand days.
Check stockout_flag in one direction only: available_qty of 0 at the cut-off with stockout_flag false is a contradiction, while flag true with positive available_qty is not, since the flag records an intraday touch of zero that a late receipt can recover.
Weight every failure by on_hand_qty times standard_cost_cents so the summary orders checks by money at risk rather than by row count, and state the scope denominator on each row.

Worked solution (20 min)

Define the scope explicitly as the distinct sku-location pairs and the date range under audit, and store the scope row count for use as every check's denominator.
Evaluate the identity, sign and stockout-flag checks as boolean masks over the frame and record the count and affected value for each.
For completeness, group by sku_id and location_id, take min and max inventory_date and the row count, and compare the count to the number of calendar days spanned.
Assemble one output row per check with check_name, failing_rows, share_of_scope and failing_on_hand_value_cents, sorted by value descending.
Spot-print five failing rows per check so the output is actionable rather than a set of counts.

Expected result: A small DataFrame with one row per check. Shares are between 0 and 1 against a stated scope denominator, and the value column is computed as on_hand_qty times standard_cost_cents on failing rows only.

Verify it yourself

Inject a known defect into a copy (flip one available_qty by +1) and confirm the identity check count rises by exactly one.
Confirm the completeness check reports zero missing dates for a sku-location pair you verify by hand against a printed date list.
Confirm the sum of on-hand value over all rows exceeds the value reported on any single check, and that no check's share exceeds 1.0.
Confirm no check silently drops nulls: count nulls per column first, since a comparison against null returns false and hides the row.

Follow-up

Someone proposes asserting shipped_qty is at most demand_qty on the same row. Why does that fire on thousands of healthy rows?
Half the missing dates fall on Sundays at one set of nodes. What is the most likely explanation and does it change the severity?
Which of these checks would you run as a blocking gate before a nightly planning job, and which as a monitored report?

Measure demand amplification hop by hop up the network

Hard · bullwhip · hierarchy traversal · variance

Using dim_location (location_id, parent_location_id, echelon, location_type) and fct_shipment_leg (leg_id, shipment_id, origin_location_id, destination_location_id, direction, tendered_at_utc, shipped_units), quantify how order variability grows upstream. fct_shipment_leg carries no sku_id and no link back to order lines, so shipped_units is the node's whole mixed-SKU flow and the measurement is node-level by construction. For each node compute weekly units flowing out (what it served downstream) and weekly units flowing in (what it ordered up), then the ratio of their variances on detrended, deseasonalised residuals. Walk parent_location_id from a store to echelon 0 and report the ratio at each hop, naming the hop where amplification is introduced. dim_location has no path column; build the chain yourself.

Approach

Date orders by tendered_at_utc, not delivered_at_utc. Tender is the closest observable proxy for the moment the ordering decision was made; dating by delivery shifts the series by transit time and smears the variance you are trying to measure.
Build both series on the same calendar weeks and in the same units. Variance scales with the aggregation window, so a ratio formed from daily inbound against weekly outbound measures the calendar, not the ordering rule. The units are mixed-SKU counts because the leg table carries no sku_id, so a node whose product mix drifts toward smaller or larger pack sizes moves both series for reasons unrelated to its ordering rule; print that caveat next to the number rather than implying a per-SKU result.
Detrend and deseasonalise before taking variances, or compute the ratio on residuals from a simple weekly seasonal baseline. A growing node otherwise scores as amplifying, when all you have measured is its trend.
Walk the parent chain iteratively: start from the store rows, join dim_location to itself on parent_location_id, and repeat until parent is null, capping the loop at the known echelon depth and asserting the path length matches the echelon difference so a data cycle raises rather than hangs.
Read the output as a sequence, not a set of numbers. A pass-through node such as a cross-dock should sit near 1.0, and the hop where the ratio jumps is where a batching rule, a minimum order quantity or truckload rounding lives. That is the node to fix, not the node that is complaining.

Follow-up

The ratio at one hop is 4.2 and the node insists it orders exactly to forecast. What ordering rules would produce that number anyway?
What would it take to run this for a single SKU family rather than total units, given fct_shipment_leg carries neither a sku_id nor any link to order lines, and which hops would still be unmeasurable after you added that link?
What would you expect this measurement to look like during a promotion, and does that change your conclusion?

Given stock price or asset inventory data, compute aggregate summary statistics across variable time windows and structure the output into a specific nested dictionary format.

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
State the window function and its partition and ordering out loud before writing it.
Say which table is the grain you start from, and join outward from it.

Follow-up

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

Write a query using SQL window functions to rank train arrivals per hub and calculate the moving average of dwell times across a rolling 7-day window.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Say which table is the grain you start from, and join outward from it.
Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

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

As-of inventory position behind each short-shipped line

Medium · as-of join · window functions · lateral join

For every fct_order_line with line_status in ('shipped_short','backordered','cancelled_no_supply') and requested_ship_date in the last quarter, attach the inventory state that governed it: the fct_inventory_daily row for the same sku_id and ship_from_location_id with the greatest inventory_date strictly before requested_ship_date. Snapshots are taken at the location-local end of day. Return the line, ordered_qty, shipped_qty, and that snapshot's available_qty, blocked_qty, on_order_qty and in_transit_qty, plus the snapshot's age in days. Lines with no prior snapshot must appear with NULLs rather than disappearing from the result.

Approach

Recognise the join is an inequality, not an equality. The snapshot is an end-of-day state, so the position that governed allocation is the last close strictly before the requested ship date, and the same-date row reflects the outcome rather than the cause.
Implement it with LEFT JOIN LATERAL (SELECT ... WHERE sku_id and location_id match AND inventory_date < requested_ship_date ORDER BY inventory_date DESC LIMIT 1) ON TRUE, or equivalently ROW_NUMBER() OVER (PARTITION BY order_line_id ORDER BY inventory_date DESC) = 1 over a bounded candidate set.
Bound the lateral with a floor such as inventory_date >= requested_ship_date - 14 so the scan does not walk the whole history for each line, and let that floor produce NULLs rather than silently dropping the line.
Keep the join LEFT. Lines at a node with no prior snapshot are a data-quality finding worth counting, and an INNER join converts them into an invisible bias toward nodes with clean snapshot coverage.
Check the row count before and after the join: the output must have exactly one row per order_line_id, and any excess means the as-of match is returning more than one candidate.

Worked solution (30 min)

Filter fct_order_line to the three statuses and the quarter, and record the row count as the target.
Attach LEFT JOIN LATERAL over fct_inventory_daily with the strict inequality, the fourteen-day floor, ORDER BY inventory_date DESC and LIMIT 1.
Project the four quantity columns plus snapshot_age_days = requested_ship_date - inventory_date.
Compare the result row count with the target; investigate any difference before aggregating anything.
Count and group the NULL-snapshot rows by ship_from_location_id to see whether the gap is a node or a SKU launch artefact.

Expected result: Exactly one row per qualifying order_line_id, with snapshot columns drawn from the greatest inventory_date strictly less than requested_ship_date, snapshot_age_days at least 1, and NULL snapshot columns where no prior row exists inside the floor.

Verify it yourself

COUNT() of the result equals COUNT() of the filtered order-line set. Any excess is fan-out from a non-unique as-of match.
MAX(inventory_date) per line is strictly less than that line's requested_ship_date, with no equality cases.
The count of NULL-snapshot rows is small and attributable to specific nodes or newly launched SKUs rather than spread evenly.

Follow-up

Split the output by short_reason_code. Which codes should show available_qty at or near zero, and what does it mean when 'no_stock' lines show healthy availability?
blocked_qty is large on many of these lines. Is that a planning problem, and who owns it?
How would you extend this to the position at the moment of allocation rather than the previous close, and what would you need that the daily snapshot does not give you?

How do you prioritize competing requests from field operations, safety teams, and executive management?

Medium · product sense and metrics

Approach

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

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?

Design a metric framework to measure the operational health and efficiency of automated switching yards.

Medium · product sense and metrics

Approach

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.
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?
What would you do if the primary metric and the guardrail moved in opposite directions?

Write a Python script using NumPy and Pandas to calculate the mutual information metric between operational telemetry features without using external scikit-learn helper modules.

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.
Fix the population and the time window before naming any metric.

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 detect and mitigate multi-collinearity when working with tightly coupled locomotive sensor metrics?

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

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?

How would you design an A/B test to validate a new fuel-efficient throttle routing policy across active train crews without cross-contamination?

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Name the guardrails that would stop a launch even on a positive primary result.
Say whether units interfere with each other, and switch design if they do.

Follow-up

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

What are the major experimentation pitfalls when running network-level tests where control and treatment rail corridors share physical infrastructure?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Name the guardrails that would stop a launch even on a positive primary result.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

A planner scorecard that aggregation cannot flatter

Hard · wmape · gameable metrics · forecast bias

Demand planners will be scored on forecast accuracy. fct_inventory_daily carries demand_qty, forecast_qty_lag7, forecast_qty_lag28 and stockout_flag at sku by location by date, with NULL forecasts before a series existed. Specify the scorecard: error metric, grain, lag, exclusion policy and guardrail. Then show the three ways a planner raises the headline without forecasting better, and state which single number you would prefer to accuracy if you could publish only one.

Approach

Fix the grain and lag to the decision. The ordering policy consumes a forecast at one horizon for one sku-location-week cell, so the score is computed there; a number quoted at any other grain is not the number the policy used and cannot be compared to it.
Choose WMAPE, sum of absolute errors over sum of actuals, or a pinball loss at the service quantile the policy targets. Rule out MAPE explicitly: it is undefined at zero actuals, which is most days for a C item at a forward-stocking location, and it is asymmetric where it is defined, so optimising it drives systematic under-forecasting.
Show why aggregation flatters, and show it as arithmetic rather than opinion. The absolute value of a sum is at most the sum of absolute values, so errors cancelling across SKUs, locations or weeks make aggregated WMAPE weakly lower than the disaggregated figure on the same denominator, with no forecasting improvement involved.
Name the other two routes: scoring at a shorter lag than ordering actually uses, and exclusion drift, where NULL-forecast rows, zero-demand cells or stockout days are dropped without publishing the excluded count, quietly removing the hardest cells from the sample.
Set the guardrail on signed bias, weighted mean percentage error over the same window and cells, reported per ABC/XYZ cell. A series can be inaccurate but unbiased, or accurate on average and persistently short, and only the second wrecks inventory in one direction.
Answer the last question directly: prefer the outcome that accuracy is a means to, achieved fill rate at a given inventory at cost per cell, because that is what the business buys, and accuracy that does not move it is not worth paying for.

Worked solution (40 min)

Build the scored panel at sku by location by week, summing demand_qty and taking the forecast at the lag the ordering policy uses, excluding NULL-forecast cells and counting every exclusion.
Compute WMAPE and signed WMPE per planner per ABC/XYZ cell by re-summing numerator and denominator, never by averaging cell ratios.
Recompute the same WMAPE at region and at month to demonstrate the mechanical improvement, and record the difference as the aggregation premium.
Recompute with and without stockout days and with and without zero-demand cells, recording each shift as the exclusion premium.
Publish WMAPE, WMPE, the excluded-row count and the paired inventory-and-service outcome, with grain and lag printed on the page rather than held in a footnote.

Expected result: A scorecard pinned to sku by location by week at the ordering lag, using WMAPE with signed bias beside it and per-ABC/XYZ reporting so the tail cannot be abandoned; three gaming routes each sized on the real data as an aggregation premium, a lag premium and an exclusion premium; and a stated preference for achieved fill rate at a given inventory at cost as the single number, with forecast accuracy kept as a diagnostic rather than as the target.

Verify it yourself

Aggregated WMAPE is less than or equal to disaggregated WMAPE on the same rows in every roll-up tested; a case where it is higher means the two were computed over different denominators.
On one panel, the absolute value of WMPE is at most WMAPE; a violation means the two used different exclusions.
Excluded rows plus scored rows equal the full panel; if they do not, rows are being dropped somewhere other than the stated policy.

Follow-up

Two planners have identical WMAPE and opposite bias. Which one is doing more damage, and to what?
The scorecard is per planner but forecasts are hierarchical and reconciled. Whose number is the reconciled one, and who gets charged for the reconciliation loss?

OEE fell six points on a constraint work centre

Medium · oee decomposition · mix shift · manufacturing

OEE on a constraint work centre fell from 0.72 to 0.66 over one month and plant leadership has requested maintenance spend. From fct_production_run (planned_production_seconds, run_seconds, unplanned_downtime_seconds, changeover_seconds, ideal_cycle_seconds_per_unit, planned_qty, total_qty, good_qty, scrap_qty, rework_qty, downtime_reason_code, sku_id, run_status) and dim_sku, decompose the six points into availability, performance and quality, then decide whether the performance term is a machine problem or a SKU mix problem. Deliverable: the decomposition and one recommendation.

Approach

Rebuild OEE by re-summing: SUM(good_qty * ideal_cycle_seconds_per_unit) over SUM(planned_production_seconds) for each month. Averaging per-run OEE gives a five-minute run the same weight as an eight-hour run and produces a figure that belongs to no work centre.
Compute the three factors on the identical row set: availability = SUM(run_seconds) / SUM(planned_production_seconds); performance = SUM(total_qty * ideal_cycle_seconds_per_unit) / SUM(run_seconds); quality = SUM(good_qty) / SUM(total_qty). Verify their product equals the re-summed OEE; if it does not, the three calculations are running over different rows.
Attribute the move on the log scale, where the factors are exactly additive: ln(OEE1/OEE0) = ln(A1/A0) + ln(P1/P0) + ln(Q1/Q0). This removes any argument about how to split the cross-terms and gives each factor a defensible share of the six points.
If performance carries the move, separate a slower machine from a harder product mix before recommending anything. ideal_cycle_seconds_per_unit is SKU-specific, so recompute month two's performance holding month one's SKU volume mix fixed. Performance that is flat under a fixed mix means the line runs as fast as ever on slower SKUs.
Interrogate the denominator itself. planned_production_seconds excludes unstaffed hours, so adding or removing a shift changes OEE with nothing physical having changed, and whether changeover_seconds sits inside or outside planned time decides whether a changeover appears as an availability loss at all.

Follow-up

Availability fell and downtime_reason_code is mostly 'material_shortage'. Is that a maintenance problem, and who owns it?
rework_qty is 400 against total_qty of 10,000. Are reworked units counted once or twice in total_qty, and how does each answer change the quality factor?
What guardrail stops a plant from hitting an OEE target by scheduling long runs of its fastest SKU?

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.

The project you argued against

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

How do you handle low sample sizes and high variance when running experimental interventions on specialized maintenance equipment?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Quantify your own impact on a safety stock change honestly

Hard · impact measurement · confounding · difference in differences

In March you re-sized safety stock at twelve regional DCs using the variable lead time formula. Unit fill rate at those nodes rose from 94.1 to 96.5 percent by June. In the same window a supplier that had been running 71 percent inbound on-time recovered to 93 percent, and Q2 volume is seasonally about 8 percent below Q1. Your manager asks for one sentence on impact for a performance review. Write the claim you are willing to defend under challenge, and explain how you bounded the portion attributable to your change.

Approach

Refuse the naive claim first: the 2.4 point gross movement contains at least three generating processes, and attributing all of it is the sort of claim that collapses the moment someone plots the supplier's on-time series next to it.
Build a comparison group from nodes that did not get the change, check that their pre-period fill rate moved in parallel with the treated nodes for at least two quarters before March, and estimate the difference in differences with variance clustered at the node, since twelve treated clusters is the effective sample size regardless of how many node-weeks there are.
Strip the confounders you can measure directly rather than trusting the design alone: restrict to sku-location cells sourced from other suppliers to remove the recovery, and hold the sku mix fixed across periods so the seasonal volume drop does not enter through mix.
Sanity-check the mechanism, not just the coefficient: if your change worked, the improvement should concentrate in cells whose reorder point rose and in short_reason_code = 'no_stock' lines, and should be absent where the policy did not move. If the gain is spread evenly, something else caused it.
State the result as an interval with the confounders named, and state the cost side too: extra units held at the applicable cost of capital, so the claim is a net one rather than a service headline.
Write the sentence so that it survives someone else re-running it, which usually means a smaller number than the gross movement and a named method.

Follow-up

The comparison nodes are not parallel in the pre-period. What do you do next?
Your bounded estimate is roughly half the gross movement. How do you present that to a manager who already told his boss the larger number?

Walk through an inventory analysis that turned out wrong

Medium · error postmortem · snapshot bias · inventory turns

Six months ago you published inventory turns by node using the month-end on_hand_qty snapshot from fct_inventory_daily as the denominator, and two DCs cut cover on the strength of it. A finance review later showed month-end is systematically the lowest on-hand point of the month, because shipments cluster before close, so your turns were overstated and days of supply understated. Tell the story: what you built, how the error surfaced, what it cost, what you corrected, and the control that now prevents this class of mistake rather than this specific instance.

Approach

Give the facts in order and own the decision, not just the code: you chose the month-end snapshot because it was one row per sku-location and fast, and you did not check whether the sampling point was representative.
Quantify the error rather than describing it: recompute the same window against the average of every daily snapshot and state the gap in turns and in days of supply, which in a network with close-period push typically runs ten to twenty percent.
Separate the consequence from the mistake honestly: say what the two DCs did, whether service actually degraded, and if it did not, say so instead of inflating the damage to sound accountable.
Describe the correction and the notification: who was told, how quickly, and whether the restated number changed the recommendation.
Close on the generalised control: a denominator convention written into the metric definition, plus a row-count assertion at each join grain, because the same shape of error appears when a daily snapshot is joined to shipment events on date equality and one shipment with several legs fans the snapshot out.

Follow-up

How did you decide whom to tell first, and how did you phrase it?
What made you trust the month-end snapshot in the first place, and what would have caught it in review?