Interview guides · Cox Automotive

Cox Automotive Data Scientist Interview Questions & Guide 2026

Data scientist prep for Cox Automotive: practice defining a supply chain and logistics metric, picking its guardrails and defending the tradeoff.

The 60-second brief

At Cox Automotive, data is the engine that drives the entire automotive ecosystem. As a Data Scientist, you will work at the intersection of technology, data engineering, and business strategy, directly influencing brands like Autotrader, Kelley Blue Book, and Manheim. Your work will power predictive pricing models, optimize vehicle valuation algorithms, and streamline logistics for the world's largest wholesale vehicle marketplace.

Allocate prep to your weakest link rather than your favourite topic. Of the three things that usually gate the outcome (SQL that is correct under messy joins, sound reasoning about experiments, and structured framing of an open-ended problem), candidates tend to over-invest in modelling theory and under-invest in framing.

Focus areas

Separate true demand from censored, stocked-out sales
Join daily snapshots to shipment events safely
Size safety stock under variable lead times

Overview

This role is highly collaborative and carries immense strategic influence. You will not just build models in isolation; you will solve complex, real-world problems such as supply chain optimization, inventory management, and personalized consumer experiences. By transforming massive datasets into actionable intelligence, you will help dealers, manufacturers, and consumers make smarter decisions.

Joining the data science team means operating at a scale that few companies can match. You will have access to rich, proprietary automotive datasets and the cloud infrastructure necessary to build sophisticated machine learning pipelines. It is a highly challenging yet rewarding environment where your analytical insights translate directly into measurable business growth.

The Cox Automotive interview process

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

HR Screening Call

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What to demonstrate

Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
Whether you name what you have not done instead of stretching to cover every line of the posting
Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Technical Screening

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

What to demonstrate

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

How to prepare

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

Practical Analytics Task

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

Final Panel Presentation

A day of back-to-back interviews samples your floor, not your ceiling. Four hours in, the habits that carry a good answer are the first to go: restating the question before solving it, asking what the data would have to look like, checking a number before quoting it. What the day decides is whether the tired version of you is still someone to leave alone with an ambiguous problem. The round that sinks a candidate is usually not the hardest one. It is the one immediately after the round that went badly.

What to demonstrate

Whether the late rounds get the same clarifying questions as the first one, or whether you start answering immediately to save effort
Whether a weak answer stays in the room it happened in, instead of following you into the next conversation as apology or distraction
Whether the quality of your questions holds up, since fatigue removes curiosity about the problem before it removes knowledge of the method

How to prepare

Rehearse the length, not just the content: book four mock interviews of different types in one afternoon with short gaps, because the one you need to observe is the fourth
Put the two or three questions you ask at the start of any problem on a card in front of you, so that under fatigue it is a habit you run rather than a decision you make
Decide in advance what the gap between rooms is for: water, one line of notes on anything you promised to follow up, and an explicit close on the round that just ended so it does not travel
Prepare a different closing question for each interviewer, so the end of a long day does not produce the same one four times

Where candidates lose points

Computing average inventory from period-end snapshots

Shipments cluster before period close, so the month-end on-hand position is systematically the lowest point of the month; turns computed against it are biased high and days of supply biased low, frequently by ten to twenty percent, and the bias grows precisely when close-period push is strongest. The same shape of error appears when a daily snapshot is joined to shipment events on date equality: a SKU with several legs on one day fans the snapshot out and multiplies the valued inventory. Average across every daily snapshot in the window for the denominator, and join snapshots to events with an explicit as-of condition and a row-count check at each grain before aggregating.

Scoring intermittent demand with MAPE

MAPE is undefined whenever the actual is zero, which is most days for a C-class item at a forward-stocking location, and it is asymmetric even where it is defined: under-forecasting is bounded at 100 percent error while over-forecasting is unbounded. Optimising it therefore drives systematic under-forecasting on exactly the items whose stockouts are most expensive, and the bias is invisible in the headline because the zero-actual rows were dropped before averaging. Use WMAPE (sum of absolute errors over sum of actuals), a scaled error such as RMSSE, or a pinball loss at the service quantile the policy targets, and always state the aggregation level and forecast lag, because the same series scores very differently at daily SKU-store level and weekly SKU-region level.

Accepting a metric definition without asking about the denominator

Pin down the denominator, the eligibility filter and the time window before computing anything: conversion rate per session, per user, per eligible user and per new user are four different numbers with different behaviour. Restate the definition in one sentence and get agreement before you analyse.

Sizing estimates built on unnamed, unrevisable assumptions

Write each assumption as a named number you can change, then show the arithmetic so the interviewer can challenge one input instead of the whole answer. Finish by saying which assumption the result is most sensitive to, which matters more than the point estimate.

Questions & practice

Can you explain the concept of eigenvalues and eigenvectors, and how they are utilized in dimensionality reduction techniques like PCA?

Medium · statistics and probability

Approach

Say what the estimate is of, and over what population it generalises.
Write down the assumption the method needs before you use the method.
Sanity-check the answer against a simple bound or a simulated case.

Follow-up

Which assumption here is most likely to be violated in practice?
What sample size would you need to detect an effect half this size?

How do you approach optimization problems, and what experience do you have with operations research techniques?

Medium · statistics and probability

Approach

Say what the estimate is of, and over what population it generalises.
Write down the assumption the method needs before you use the method.
Sanity-check the answer against a simple bound or a simulated case.

Follow-up

How would you explain this result to someone who does not know statistics?
Which assumption here is most likely to be violated in practice?

Given a dataset of vehicle listings, how would you approach building a model to predict the optimal time-to-market for a dealer?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Set a baseline first, so any model has something honest to beat.
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?
Where could label leakage enter this setup?

Describe a situation where a model you deployed did not perform as expected in production. How did you diagnose and address the issue?

Medium · machine learning and modelling

Approach

Say how the offline result would be validated online before it is trusted.
Pick an evaluation metric that matches the cost of each error type, not a default.
Set a baseline first, so any model has something honest to beat.

Follow-up

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

Cluster bootstrap for landed cost per delivered unit

Medium · bootstrap · clustering · ratio estimator · mix shift

You have delivered outbound legs from fct_shipment_leg: leg_id, shipment_id, leg_seq, carrier_id, origin_location_id, destination_location_id, shipped_units, cube_m3, linehaul_cost_cents, fuel_surcharge_cents, accessorial_cost_cents, expedite_premium_cents. In this extract the linehaul for a multi-stop load is booked entirely on leg_seq 1, so allocate it across the load's legs by cube first. Then estimate the difference in landed cost per delivered unit between two carriers on lanes both serve, with a 95 percent interval. Write the resampling yourself; no bootstrap library.

Approach

Allocate the linehaul before anything else, in proportion to each leg's cube over the load's total cube, and keep the other three cost columns where they already sit. State the choice: allocating by weight instead reorders lanes whenever freight is bulky rather than dense.
Restrict to the set of lanes both carriers actually serve in the window. Comparing over all lanes measures which carrier was assigned the cheap lanes, not which carrier is cheaper.
Resample shipments, not legs. Legs of one load share a single allocated linehaul and a single dispatch decision, so treating them as independent draws understates the variance of the estimate.
Recompute the statistic as a ratio of sums on every resample, total allocated cost over total shipped units. Taking the mean of leg-level cost-per-unit values instead gives a different estimand in which a one-unit leg counts as much as a full truckload.
Report both the raw difference and a lane-standardised difference where lane weights are fixed at the pooled volume share, and say which one you would put in front of a decision maker.

Worked solution (35 min)

Compute per-shipment total cube, allocate the leg_seq 1 linehaul across legs by cube share, and form leg_total_cost from the four cost columns.
Build the lane key from origin_location_id and destination_location_id, and keep only lanes with delivered volume from both carriers.
Compute the point estimate per carrier as sum(leg_total_cost) / sum(shipped_units), and take the difference.
Draw 2,000 bootstrap replicates by sampling shipment_ids with replacement within each carrier, rebuilding both sums from the sampled legs and recomputing the ratio difference.
Take the 2.5th and 97.5th percentiles of the replicate differences, then repeat the whole procedure with resampling stratified inside lane to produce the mix-standardised interval.

Expected result: A point difference in cents per delivered unit with a 95 percent percentile interval from 2,000 replicates, reported twice: raw, and standardised to a fixed lane mix. The two differ whenever the carriers' lane mixes differ, and can differ in sign.

Verify it yourself

Run the same bootstrap resampling legs independently instead of shipments: that interval should be narrower, and the gap is the intra-load correlation you would have thrown away.
Confirm the mean of the bootstrap replicates is close to the point estimate; a large gap indicates a bias worth investigating before quoting the interval.
Confirm total allocated linehaul per shipment equals the original leg_seq 1 linehaul to within rounding, and that no leg received a negative allocation.
Confirm the interval narrows roughly with the square root of the number of shipments by rerunning on a random half of the loads.

Follow-up

The interval crosses zero. What would you need in volume or in window length to resolve a difference of 2 cents per unit?
One carrier's accessorials are rising while its linehaul is flat. What is that signature telling you about execution versus rates?
How does your interval change if one shipment accounts for 15 percent of the units?

Perfect order rate against the original promise in local time

Hard · timezone boundaries · order-grain rollup · semi-join

Compute weekly perfect order rate at order grain. An order qualifies only if every fct_order_line has line_status = 'shipped_full', every line's actual_delivery_at_utc falls on or before original_promised_delivery_date at the ship-from node's local end of day (dim_location.timezone holds IANA names), and no fct_shipment_leg reached through shipment_id carries exception_code in ('damage','address_issue') or leg_status in ('refused','lost'). The denominator is orders whose original_promised_delivery_date lands in the week. Undelivered lines fail. Publish with a fourteen-day lag and declare invoice accuracy as out of scope.

Approach

Convert the event into local time rather than comparing against a UTC-anchored deadline: (actual_delivery_at_utc AT TIME ZONE 'UTC' AT TIME ZONE loc.timezone)::date <= original_promised_delivery_date. Casting the promise date to a timestamp puts the cut-off at UTC midnight starting the promise day, which is up to a full day early and wrong in both directions depending on the node's offset.
Evaluate the three components as booleans at line grain, then roll to the order with BOOL_AND, coercing a NULL actual_delivery_at_utc to FALSE explicitly so an undelivered line fails rather than propagating NULL and dropping the order out of the comparison.
Test the shipment exceptions with EXISTS against fct_shipment_leg, never with a join. One shipment has many legs and one order has many lines, so a join multiplies the order into the denominator and changes the metric.
Aggregate to order grain first, then count qualifying orders over orders promised in the week. The rate is orders over orders; a line-level average is a different and easier metric.
Apply the publication lag by restricting to weeks whose promise dates are at least fourteen days old, and emit the three component pass rates beside the headline so any movement is attributable to completeness, timeliness or exceptions.

Worked solution (40 min)

CTE line_flags: join fct_order_line to dim_location on ship_from_location_id, producing is_full, is_on_time using the AT TIME ZONE conversion with COALESCE to FALSE, and has_exception from an EXISTS over fct_shipment_leg.
CTE order_flags: GROUP BY order_id with BOOL_AND(is_full), BOOL_AND(is_on_time), BOOL_AND(NOT has_exception) and MIN(original_promised_delivery_date) for bucketing.
Aggregate per DATE_TRUNC('week', promise date): COUNT() as denominator, COUNT() FILTER (WHERE all three) as perfect_orders, and the three component counts.
Restrict output to weeks ending at least fourteen days ago.
Re-run the on-time expression once with a naive UTC comparison and record the delta as evidence the conversion is live.

Expected result: One row per week with orders_in_denominator, perfect_orders, rate, and three component pass rates, where the rate is less than or equal to every component rate and weeks inside the fourteen-day lag are absent from the output.

Verify it yourself

perfect_orders is less than or equal to the count passing any single component, for every week.
Switching the deadline to a naive UTC comparison shifts the on-time component by roughly the share of orders at nodes offset from UTC. No change at all means the conversion is not being applied.
The denominator equals COUNT(DISTINCT order_id) over orders promised in the week and does not change when the legs EXISTS clause is removed.
No order_id appears in more than one week.

Follow-up

All three component rates are flat but perfect order fell two points. How is that arithmetically possible, and what does it tell you about where the failures now land?
A node sits at UTC+13 and many orders are promised on the last day of the month. What does that do to your weekly and monthly buckets?
How do you handle an order whose lines ship from two nodes in different timezones, and which node's clock owns the promise?

Bullwhip ratio by echelon from daily pipeline deltas

Hard · window functions · bullwhip · variance ratio

fct_inventory_daily gives on_order_qty (ordered, not yet shipped) and in_transit_qty (shipped, not yet received) per sku x location x date; dim_location gives echelon and parent_location_id. There is no replenishment-order table, so derive placements: a new order raises on_order, a shipment moves quantity from on_order to in_transit, and a receipt lowers in_transit, so placed_qty on a date is the positive part of the day-over-day change in (on_order_qty + in_transit_qty). Compute weekly placements per node and the bullwhip ratio against the demand that node received, reported per echelon. State what the derivation misses.

Approach

Per sku-location, take LAG(on_order_qty + in_transit_qty) OVER (PARTITION BY sku_id, location_id ORDER BY inventory_date) and set placed_qty = GREATEST(pipeline - lag_pipeline, 0). The first date in each partition has no lag and must be dropped, not read as a placement equal to the whole standing pipeline.
Name the blind spots before anyone else does: an order placed and received inside one day nets to zero, a cancellation is indistinguishable from a receipt, and minimum order quantities plus lot-size multiples make placements lumpy by construction rather than by anyone's behaviour.
Define received demand by echelon. At the lowest echelon it is demand_qty in the same table. At a parent node it is the sum of its children's placed_qty joined through parent_location_id, not the parent's own demand_qty column, which is close to empty for a pure replenishment node.
Aggregate both series to weekly buckets before computing dispersion, because day-of-week ordering patterns otherwise dominate the variance, and require a minimum number of weeks before quoting any ratio.
Compute CV = stddev_samp / NULLIF(avg, 0) on each weekly series and report the ratio of squared CVs, stating that convention explicitly: a raw variance ratio is not level-free and is not comparable across nodes of different size.
Read the output by echelon and locate where the ratio first crosses one going upstream. That is the node whose ordering rule amplifies, and it is usually not the node where the schedule instability is being felt.

Follow-up

A node shows a ratio near 3. Name three ordering rules that produce that and say how you would tell them apart using only these tables.
Why might a plant's schedule instability be untouchable from inside the plant, and what would you change instead?
What would you want logged so that placements no longer have to be inferred from pipeline deltas, and what is the smallest such change?

How do you prioritize your work when managing multiple data science initiatives with competing deadlines?

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.
Name one primary metric, then the guardrail that stops it being gamed.

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?

Walk me through a recent data science project you completed. What was your specific approach, and what were the results?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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?

Explain the difference between L1 and L2 regularization. In what scenarios would you prioritize one over the other?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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?

A fill rate that improves by cancelling what it misses

Easy · gameable metrics · denominator integrity · audit series

Unit fill rate is defined as SUM(shipped_qty) over lines shipped on or before requested_ship_date, divided by SUM(ordered_qty) over lines with requested_ship_date in the week, excluding lines with line_status = 'cancelled_by_customer'. A node is now paid on it. Using fct_order_line (line_status, short_reason_code, ordered_qty, shipped_qty, cancelled_qty, substituted_sku_id, ordered_at_utc, requested_ship_date, account_id, sku_id, ship_from_location_id), list the routes by which this number rises without service improving, and specify the audit series that detects each.

Approach

Attack the denominator first: removing a line removes a miss, and the only exclusion the definition grants is customer cancellation, a status written by the same operation being scored.
Name re-entry: a short line closed and re-keyed as a fresh order arrives with a new requested_ship_date, so the miss leaves the scoring window entirely instead of being cancelled inside it.
Name intake suppression: a line never created because the item showed unavailable enters neither side of the ratio, which is the only route that leaves no trace in this table and therefore needs demand-side evidence to detect.
Name numerator inflation: substitution counted as a fill without saying so, partial shipments credited at ordered_qty, and shipping early against a requested date that was itself edited.
Attach one audit series to each route from the same table: weekly cancelled_by_customer share by node, share of accounts re-ordering the same sku within seven days of a cancellation, substitution share, and median hours from ordered_at_utc to terminal status.
Close with the structural fix: publish cancellation mix beside the rate, and pair it with perfect order rate, so that cancelling an order cannot improve both numbers at once.

Worked solution (20 min)

Compute the weekly fill rate per node by re-summing shipped_qty and ordered_qty, never by averaging line-level ratios.
Compute cancellation mix beside it: cancelled_qty split by line_status in ('cancelled_by_customer','cancelled_no_supply') as a share of ordered_qty, per node per week.
Compute the re-entry series: lines whose (account_id, sku_id, ship_from_location_id) reappears within seven days after a cancellation or a short ship.
Compute substitution share and median hours from ordered_at_utc to terminal status, both by node and week.
Quantify the exposure algebraically: moving x units of unmet demand out of the denominator takes a rate r to r divided by (1 minus x/D), then read that against normal week-over-week variation.

Expected result: Four named routes, each with its audit series, plus the arithmetic that sizes the exposure: excluding one percent of the denominator lifts a 95.0 percent fill rate to 95.96 percent, which exceeds a typical week-over-week swing, so any monitoring threshold has to sit well below a one-point move. The recommendation is to publish cancellation mix and perfect order rate alongside the headline rather than to tighten the definition alone.

Verify it yourself

The identity holds: denominator units equal shipped plus short plus backordered plus no-supply-cancelled units, with customer cancellations excluded and reported separately.
Recomputing after relabelling a share of cancelled_no_supply units as cancelled_by_customer reproduces the r divided by (1 minus x/D) formula to rounding.
A node improving genuinely shows a falling cancelled_no_supply share; a gamed one shows that share falling while cancelled_by_customer rises by a similar quantity in the same weeks.

Follow-up

Which of these routes still works if you score lines rather than units?
A node's cancelled_by_customer share doubled in one week. What confirms or clears it before you accuse anyone?

A seven percent demand rise that is a calendar artefact

Hard · seasonality · day-of-week · timezone

A planner reports regional demand up 7 percent for the most recent four weeks against the prior four and wants safety stock raised. fct_inventory_daily is cut at each node's local end of day using dim_location.timezone and working_calendar_id. The recent window contains one public holiday fewer and spans a daylight-saving transition. Using fct_inventory_daily (inventory_date, location_id, sku_id, demand_qty, stockout_flag), dim_location and dim_sku, test whether underlying demand moved at all. Deliverable: a calendar-adjusted growth figure and a statement of what you would raise, if anything.

Approach

Count exposure before comparing totals, and be exact about what can differ. Any 28-day window contains exactly four of each weekday whatever date it is anchored to, so two consecutive four-week windows have identical weekday composition and a weekday-mix reweighting between them is identically zero. Assert it rather than assume it, then set it aside. The weekday term only becomes live when a window is not a whole multiple of seven days, which happens the moment someone compares calendar months or a truncated most-recent week, so the assertion is worth keeping in the query.
What does differ is working days, and that is where the arithmetic lives. Derive each window's non-working dates per node from working_calendar_id: on a five-day calendar a 28-day window holds 20 weekdays, so one public holiday fewer takes a node from 18 working days to 19 (5.6 percent more selling exposure) or from 19 to 20 (5.3 percent), before any demand moves. Compare demand per working day, not demand per calendar day.
Weight those days the way demand is actually distributed. Because order cut-offs are daily and local, days are not interchangeable: build a day-of-week index per node from 52 weeks of history with holiday dates excluded (mean demand_qty by weekday over the node's overall daily mean), and define exposure as the sum of that index across the window's working days. Demand per unit of index-weighted exposure is the adjusted figure; demand per plain working day is the cruder version and is wrong by the index spread whenever the holiday falls on an unusually heavy or light weekday.
Measure the holiday's shoulder separately, because the exposure adjustment cannot see it. Orders that would have been placed on the closed day arrive on the working day before or after, so a window with one holiday fewer also loses the displaced spike its neighbours carried. Compare demand_qty on the two working days either side of each holiday against those weekdays' index values and report the displaced units as their own line.
Bound the daylight-saving effect instead of arguing about it. The snapshot is cut at local end of day, so one inventory_date per timezone spans 23 or 25 hours: at most one extra or missing hour of order capture on one of 28 days, about 0.15 percent of the window even if that hour carried normal trading volume, which at an overnight transition it does not. Identify the transition date per timezone, compare its demand_qty against neighbouring same-weekday dates, and report growth with and without it so the term is shown to be small rather than presumed to be. Then test whatever residual survives against the same calendar weeks one year earlier under the identical exposure adjustment, and ask whether it is demand or the relaxation of censoring: an order never placed because the item displayed as unavailable leaves no row at all, so a window with fewer stockout days records higher demand_qty with underlying demand flat. Cut the residual by stockout_flag history per sku-location before calling it growth.

Follow-up

After adjustment the growth is 0.4 percent with a wide interval. What do you tell the planner, and what would change your answer?
Which of these adjustments would you bake into the standard report, and which stay manual because they need judgement?
If the residual is recovered censored demand rather than growth, does that argue for more safety stock or less?

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.

What the behavioural round is actually scoring

Interviewers here are not checking whether you can describe a project. They want the decision you made, why you made it under the information you had, and what changed afterwards that someone else could measure. A story that ends at 'I built a model' has no ending. Say what the model caused, or what you stopped doing because of it.

Tell me about a time you disagreed with a product manager or stakeholder regarding a model's requirements. How did you resolve it?

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
Pick a story where you drove the decision, not one where you observed it.
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?

Prioritise transport, plant and commercial in one week

Medium · prioritisation · stakeholder management · decision value

Three requests land in the same week. Transport wants landed cost per delivered unit split by lane and mode from fct_shipment_leg for a carrier bid that closes in nine days. A plant manager wants OEE on two work centres decomposed into availability, performance and quality from fct_production_run for a capital request due next month. Commercial wants a fill-rate root cause from fct_order_line for an account review on Thursday. You are the only data scientist and have about four working days. Give your order, the rule that produced it, and what you say to the two who wait.

Approach

Rank by the decision each request feeds and by whether your input can still change it, not by seniority or by who asked loudest: a bid closing in nine days is a live decision with a large irreversible spend attached, a capital request due next month has slack, and an account review has a fixed date but a smaller reversible decision.
Check reversibility and blast radius: carrier rates lock for a contract period across every lane, so an error or an absence there is expensive for a year, while the fill-rate story can be revised next week.
Look for the cheap partial that unblocks someone else: the fill-rate cut by node, by short_reason_code and by week is a few hours of work and covers most of what commercial needs on Thursday, so it does not have to wait behind the bid.
Sequence with explicit time boxes: the commercial cut first because it is short and date-locked, the lane and mode cost analysis next with the allocation basis for multi-stop loads stated up front, and the OEE decomposition scheduled into the following week with a date the plant manager can hold you to.
Tell the plant manager directly and early, with a date rather than a maybe, and say what you need from him in the meantime so the delay produces something.
Escalate the trade-off rather than absorbing it silently: your manager should know a capital request slipped a week, because that is a business choice and not yours alone to make.

Follow-up

The plant manager escalates and his director asks you to reorder. What do you do?
Which of the three would you push back on entirely, and what would you offer instead?

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?