Steelcase Data Scientist Interview Questions & Guide 2026

The 60-second brief

At Steelcase, a Data Scientist plays a pivotal role in bridging the physical and digital worlds. As a global leader in office furniture, interior architecture, and space solutions, Steelcase relies on data-driven insights to design workplace environments that foster collaboration, productivity, and employee well-being. This position sits at the intersection of IoT sensor data, workspace analytics, supply chain logistics, and business intelligence, making it an incredibly diverse and high-impact role.

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

Focus areas

• 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

You will be responsible for transforming complex datasets into actionable strategies. Whether you are analyzing spatial utilization data to help clients optimize their real estate footprints, forecasting demand to streamline global manufacturing, or building predictive models to enhance product development, your work directly influences the future of work. The scale and complexity of the data you handle will require a blend of robust statistical modeling, modern machine learning techniques, and creative problem-solving.

This is not a purely backend engineering role; it is a highly collaborative position that demands strategic influence. You will partner with product managers, workspace designers, and business leaders to turn raw data into compelling narratives. For those who thrive on seeing their digital solutions manifest in physical, real-world environments, this role offers a unique and deeply satisfying career path.

The Steelcase interview process

Steelcase 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

Cognitive 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

Behavioral Panel Interview

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.

Technical Interview

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

Where candidates lose points

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.

Sizing safety stock as z times sigma_D times the square root of lead time

That form assumes lead time is deterministic. When lead time itself varies, the standard deviation of demand over lead time is sqrt(L_bar * sigma_D^2 + D_bar^2 * sigma_L^2), and the second term dominates whenever supply is unreliable, so the familiar formula can understate the requirement severalfold for a long, variable inbound lane. Two further preconditions are routinely forgotten: demand is assumed independent across periods, which promotions and order batching break, and z maps to cycle service level (the probability of no stockout in a replenishment cycle), not to fill rate, which additionally depends on order quantity through the unit normal loss function. Quoting a z-derived number as a fill rate overstates achieved service, and the gap widens as order quantity shrinks.

Dropping rows with missing values without naming the mechanism

Say whether the values are missing at random, missing by a known process, or missing in a way that depends on the outcome, and handle them accordingly. Deleting incomplete rows silently redefines the population whenever missingness correlates with what you are measuring.

Over-explaining the method and under-explaining the implication

Lead with the answer and what you would do about it, then give the approach when asked. Roughly one sentence of method per three of implication is the right ratio for a stakeholder-facing answer; the interviewer already knows what a regression is.

Questions & practice

Explain the mathematical difference between L1 (Lasso) and L2 (Ridge) regularization, and when you would use each.

Medium · statistics and probability

Approach

1. Write down the assumption the method needs before you use the method.
2. Sanity-check the answer against a simple bound or a simulated case.
3. Quantify uncertainty explicitly rather than reporting a point estimate alone.

Follow-up

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

Walk me through the bias-variance tradeoff and how you diagnose overfitting in a random forest model.

Medium · machine learning and modelling

Approach

1. Pick an evaluation metric that matches the cost of each error type, not a default.
2. Frame the prediction: the label, the moment of prediction, and the action it triggers.
3. 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?

How do you handle highly imbalanced datasets when training a predictive classification model?

Medium · machine learning and modelling

Approach

1. Say how the offline result would be validated online before it is trusted.
2. Pick an evaluation metric that matches the cost of each error type, not a default.
3. 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?

Recover unconstrained demand from stockout-censored history

Hard · censoring · maximum likelihood · em algorithm

A legacy extract has only inventory_date, sku_id, location_id, shipped_qty and stockout_flag; demand_qty was not populated before a cutover date. On a stockout day, shipped_qty is a lower bound on that day's demand. Model daily demand per sku-location as Poisson and fit its mean by maximum likelihood, treating stockout days as right-censored observations. Return the fitted mean per cell alongside the naive mean of shipped_qty and the censored share. Then validate against the post-cutover window where demand_qty exists.

Approach

1. Write the likelihood before any code. Uncensored days contribute the Poisson pmf at the observed count; stockout days contribute the survival probability P(X >= y), because all you learned is that demand was at least what you managed to ship.
2. Solve it as an EM fixed point rather than reaching for a general optimiser: the censored MLE equals the complete-data mean with each censored observation replaced by its conditional expectation, so iterate lambda_next = (sum of uncensored y + sum of E[X | X >= y, lambda]) / n until it stops moving.
3. Use the closed form for the conditional expectation rather than truncating an infinite sum: for a Poisson, sum over k >= y of k*p(k) equals lambda times P(X >= y-1), so E[X | X >= y] = lambda * P(X >= y-1) / P(X >= y) for y >= 1. That removes the tail-truncation error entirely.
4. Refuse to fit cells with too little signal. A sku-location with three non-zero days or with every day censored has no identifiable mean; return the naive figure with a flag rather than a confident number.
5. Validate on the post-cutover window where demand_qty is populated: fit the estimator on shipped_qty and stockout_flag alone, and compare both the censored estimate and the naive mean against the true mean of demand_qty.

Worked solution (45 min)

1. Split each cell's days into censored (stockout_flag true) and uncensored, and record the censored share.
2. Initialise lambda at the mean of shipped_qty over all days, which is a strict lower bound on the answer.
3. Iterate the EM update using scipy.stats.poisson.sf for the survival terms, stopping when the change in lambda falls below 1e-8 or after 500 iterations.
4. Assemble a per-cell frame with lambda_hat, naive_mean, censored_share, n_days and a converged flag; mark thin cells rather than fitting them.
5. On the post-cutover window, compute the true mean of demand_qty per cell and report the absolute error of lambda_hat against the absolute error of naive_mean.

Expected result: Per-cell lambda_hat that is greater than or equal to the naive mean of shipped_qty in every cell, with equality exactly when the cell has no censored days. The uplift grows with censored share. On the validation window, lambda_hat has lower mean absolute error against the true demand_qty mean than the naive figure does.

Verify it yourself

• Assert lambda_hat >= mean(shipped_qty) for every cell; the EM fixed point guarantees this because E[X | X >= y] >= y, so any violation is an implementation bug.
• Re-solve one cell by directly minimising the negative log-likelihood with a one-dimensional optimiser and confirm it agrees with the EM answer to at least six decimal places.
• Simulate a cell with known lambda, censor it artificially at a known threshold, and confirm the estimator recovers lambda within Monte Carlo error while the naive mean does not.
• Confirm cells with zero censored days return lambda_hat exactly equal to the sample mean, since the likelihood reduces to the ordinary Poisson case.

Follow-up

• The fitted mean is 18 percent above the naive mean for A-class items and 2 percent above for C-class. Why would you expect that ordering?
• The residual variance is three times the fitted mean. What does that break and what distribution would you move to?
• A stockout is often caused by a demand spike that day. What does that do to your estimate and can you correct it?

How would you optimize a slow-performing SQL query that is scanning millions of rows of IoT event data?

Medium · sql and data manipulation

Approach

1. Compute rates by summing numerator and denominator separately, never by averaging rates.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

• How does the query change if the join becomes one-to-many?
• How would you verify this result without re-running the same query?

Write a SQL query using window functions to find the peak utilization hours for a specific office zone.

Medium · sql and data manipulation

Approach

1. Compute rates by summing numerator and denominator separately, never by averaging rates.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. State the window function and its partition and ordering out loud before writing it.

Follow-up

• How would you verify this result without re-running the same query?
• How does the query change if the join becomes one-to-many?

Describe how you would handle duplicate records and missing timestamps in a raw telemetry dataset.

Medium · sql and data manipulation

Approach

1. Compute rates by summing numerator and denominator separately, never by averaging rates.
2. Say which table is the grain you start from, and join outward from it.
3. State the window function and its partition and ordering out loud before writing 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?

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.

Worked solution (45 min)

1. CTE pipeline: select sku_id, location_id, inventory_date, on_order_qty + in_transit_qty AS pipe, and LAG(pipe) over the partition; drop rows where the lag is NULL and count them.
2. CTE placements: placed_qty = GREATEST(pipe - lag_pipe, 0), aggregated to sku-location-week.
3. CTE received: weekly demand_qty per node for the lowest echelon, and for higher echelons the weekly sum of children's placed_qty joined on parent_location_id.
4. Join the two weekly series per node, require a minimum week count, and compute stddev_samp and avg on each side.
5. Emit echelon, node, weeks_observed, cv_orders, cv_demand, bullwhip = (cv_orders / cv_demand) ^ 2, and the dropped first-date row count.

Expected result: One row per node with its echelon, weeks_observed, cv_orders, cv_demand and bullwhip as the squared CV ratio, plus an echelon-level rollup and the count of partition-start rows excluded for having no lag value.

Verify it yourself

• Total placed_qty over a long window is close to the total of receipts implied by falls in in_transit_qty. A large shortfall means same-day place-and-receive is common and the derivation is understating placements.
• Nodes at echelon 1 or 2 with no children under parent_location_id are excluded explicitly rather than silently producing a NULL or zero demand denominator.
• Recomputing the CVs on daily instead of weekly buckets changes the ratio. An identical ratio means the weekly aggregation never took effect.
• placed_qty is never negative, and its maximum is bounded by the largest single-day pipeline increase in the raw data.

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?

Which evaluation metrics would you prioritize for a business-critical demand forecasting model, and why?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. 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?

Solve spatial reasoning puzzles that require rotating or manipulating 3D objects mentally.

Medium

Approach

1. Clarify what is being asked and what a complete answer would contain.
2. Work from the decision backwards to the evidence you would need.
3. State your assumptions explicitly before working the problem.

Follow-up

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

Define success for a later order cut-off at one node

Easy · metric tree · guardrails · timezone boundaries

A regional distribution centre wants to move its outbound order cut-off from 14:00 to 17:00 local time. You have fct_order_line (ordered_at_utc, requested_ship_date, promised_ship_date, actual_ship_at_utc, actual_delivery_at_utc, original_promised_delivery_date, line_status), fct_shipment_leg (tendered_at_utc, picked_up_at_utc, promised_delivery_at_utc, delivered_at_utc, expedite_premium_cents, accessorial_cost_cents, exception_code) and dim_location.timezone. Define the primary success metric, two guardrails, and the readout window. State any cost this schema cannot see.

Approach

1. Pin the grain to the decision: the change is a local clock change, so bucket lines by the node-local order date derived from ordered_at_utc and dim_location.timezone, because raw UTC dates move every evening order across the boundary the experiment is about.
2. Make perfect order rate against original_promised_delivery_date the primary, and keep same-local-day ship rate as the operational read underneath it, since a ship scan is internal effort and a delivery is what the account experiences.
3. Set guardrail one on landed cost per delivered unit for outbound legs from this node, which is where a missed carrier cut-off shows up as expedite_premium_cents and accessorial_cost_cents rather than as a service miss.
4. Set guardrail two on outbound on-time against promised_delivery_at_utc, split into dock dwell (tendered_at_utc to picked_up_at_utc) and transit, because a later cut-off compresses the runway before it touches transit time.
5. Declare the exclusion: warehouse labour and overtime have no table in this schema, so name the cost qualitatively and name the system that would settle it, rather than implying the numbers priced it.
6. Fix the window before the data: weekly by local date, a 14-day publication lag so late deliveries and damage claims land in their own period, and a two-week burn-in excluded from both arms.

Worked solution (20 min)

1. Derive local_order_date by converting ordered_at_utc with the timezone on ship_from_location_id, then flag each line against the old and the new cut-off time.
2. Define same-local-day ship rate as lines with actual_ship_at_utc falling on local_order_date over lines flagged before cut-off, re-summing numerator and denominator for any weekly roll-up.
3. Define perfect order rate at the order grain: every line shipped_full, actual_delivery_at_utc at or before original_promised_delivery_date end of local day, and no joined leg carrying exception_code in ('damage','address_issue') or leg_status in ('refused','lost').
4. Define the cost guardrail as (linehaul + fuel surcharge + accessorial + expedite premium) over shipped_units on delivered outbound legs from this node, monthly by lane.
5. Write the readout page: primary, two guardrails, four pre-period weeks, post-period starting at the burn-in end, and the rollback rule stated before anyone looks.

Expected result: A one-page definition where the primary is perfect order rate against the original promise at the order grain, weekly by local promise date with a 14-day lag; guardrails are landed cost per delivered unit on outbound legs and on-time-to-promise split into dwell and transit; warehouse labour is named as a declared exclusion; and the rollback rule is a stated rise in landed cost per unit against a flat or falling perfect order rate.

Verify it yourself

• Lines with a local order time within one hour either side of the cut-off form a non-trivial population in both periods; near zero means the timezone conversion did not apply.
• Summing numerator and denominator across weeks reproduces the period rate exactly, whereas averaging weekly rates does not; if the two agree, the volumes are suspiciously uniform and worth checking.
• Every denominator line resolves to a ship_from_location_id present in dim_location with a non-null timezone.

Follow-up

• Same-day ship rate improves but P90 order cycle time is flat. What happened?
• How would you detect the node simply promising later dates instead of shipping faster?
• What single reading would make you roll this back inside a week?

Forecast accuracy collapsed at nodes missing snapshot rows

Medium · pipeline failure · wmape · data completeness

WMAPE at lag 7 for one region jumped from 31 percent to 58 percent over three weeks with no model change and no retraining. fct_inventory_daily is specified to carry one row per active sku-location-date even when on_hand_qty is zero, and a release changed how the end-of-day cut-off is derived from dim_location.timezone. Using fct_inventory_daily (inventory_date, sku_id, location_id, demand_qty, forecast_qty_lag7, stockout_flag) and dim_location, decide whether demand changed or the table did, and quantify the contaminated share before anyone retrains.

Approach

1. Run a completeness check before touching any accuracy number: expected rows per location per day from the active sku-location grid, against actual rows present. If the table is incomplete the accuracy question is not well posed, and this check costs one query.
2. Separate the two failure modes a cut-off change produces. Rows missing entirely shrink the denominator, and the sku-days that vanish are not a random sample. Rows present but with demand pushed onto the adjacent date create a one-day shift that inflates absolute error on both days while leaving the weekly total intact.
3. Test the shift hypothesis directly by comparing SUM(demand_qty) per ISO week per node before and after the release. If weekly totals match and only the daily split moved, the model is fine and the grain is broken.
4. Recompute WMAPE on clean node-days only and report both figures with the excluded row count and the share of demand those rows represent, since the metric definition already requires the excluded count to travel with the number.
5. Check whether the affected nodes share a timezone that crosses a daylight-saving boundary or sits far from the offset the pipeline assumed. That grouping is the discriminating evidence between a code defect and genuine demand movement.

Follow-up

• Weekly totals match and only the daily split moved. Does the ordering decision care? At what replenishment frequency does it stop mattering?
• How would you backfill the missing days, and which of them would you refuse to backfill?
• What assertion would you add to the snapshot job so this fails loudly instead of surfacing three weeks later as model drift?

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.

Your part, not the team's

Most data work is done by groups, so an interviewer has to work out which piece was yours. An answer that runs on 'we' for several minutes gets interrupted with a question about what you personally did, and by then the answer sounds defensive even when it is true. Mark your own contribution as you go, and name the parts that belonged to someone else instead of leaving them ambiguous. Keep a few specifics back as well, like the name of the metric or who actually objected, so a probe can be answered with something you had not already said.

Describe a time when you had to explain a complex machine learning concept to a business stakeholder who had no technical background.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Disagree with a planning manager about a safety stock cut

Medium · disagreeing with data · censoring · forecast evaluation

A planning product manager proposes cutting safety stock 30 percent on every C and Z class sku-location cell, citing a MAPE improvement from 41 to 28 percent over two quarters. Checking the query, you find MAPE is computed over fct_inventory_daily rows where the actual is greater than zero, and the actual used is shipped_qty rather than demand_qty; stockout_flag is true on 14 percent of the rows that were kept. You have fifteen minutes in his planning review. Make the disagreement, and propose what you would do instead of the flat cut.

Approach

1. Lead with the decision at risk, not the metric error: a 30 percent cut on intermittent items is the cheapest way to convert a measurement artefact into stockouts eight weeks later, by which time nobody will connect the two.
2. Name the two defects precisely. Dropping zero-actual rows is not a rounding choice, it removes most of the history for a C-class item at a forward-stocking location and it removes the rows where over-forecasting is penalised, so the surviving MAPE is biased toward whichever series stocked out. Using shipped_qty as the actual scores the forecast against a supply ceiling, so a series looks more accurate the more often it ran out.
3. Offer the replacement metric in the same breath: WMAPE, sum of absolute errors over sum of actuals against demand_qty, computed at the sku-location-week grain the ordering decision uses and at the lag it uses, reported next to signed bias so a persistently short series cannot hide inside an accuracy number.
4. Propose the smaller action that is defensible now: recompute on the corrected basis, rank cells by bias rather than accuracy, and cut cover only where the corrected series is unbiased or over-forecasting, in a staged rollout with a service guardrail.
5. Give him the win he actually wants: if the corrected numbers still support cuts on a subset, say so in advance, so the disagreement is about evidence rather than about territory.

Follow-up

• He says demand_qty is itself incomplete because customers stop ordering what shows out of stock. Is he right, and what do you do about it?
• Which items would you leave alone regardless of what the corrected metric says?

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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?