Interview guides · Colgate-Palmolive

Colgate-Palmolive Data Scientist Interview Questions & Guide 2026

Timed data scientist practice for Colgate-Palmolive: short SQL and probability sets run end to end, then scored against a rubric.

The 60-second brief

At Colgate-Palmolive, a Data Scientist is more than just a technical specialist; you are a strategic partner in a digital transformation that touches billions of consumers worldwide. The data science team works across consumer packaged goods (CPG) and technology, using data to optimize everything from supply chain logistics and manufacturing efficiency to personalized marketing and product innovation. Whether you are improving the demand forecasting for Colgate Total or analyzing consumer sentiment for Hill's Pet Nutrition, your work directly impacts the daily lives of people in over 200 countries.

Seniority shifts the scope more than the words in the title do. Earlier-career loops mostly check that you execute a well-posed analysis correctly; senior loops check that you can decide which question is worth answering and defend what you chose not to do.

Focus areas

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

Overview

The role is critical because Colgate-Palmolive relies on data-driven insights to maintain its market leadership in a rapidly evolving digital landscape. As a Data Scientist, you will tackle complex, large-scale problems that require a blend of statistical rigor and business acumen. You will find yourself working in a collaborative environment where your ability to translate high-level business challenges into actionable machine learning models is highly valued. Colgate-Palmolive's data is large in scale, which suits candidates interested in deep learning, predictive analytics, and optimization.

You can expect to work on high-impact projects that define the future of the company. This might involve building recommendation engines for Colgate-Palmolive's e-commerce platforms, developing computer vision models for quality control in its manufacturing plants, or using natural language processing to understand global market trends. At Colgate-Palmolive, data work is tied to the company's stated aim of a healthier, more sustainable future.

The Colgate-Palmolive interview process

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

Screening Phase

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What to demonstrate

Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

Automated Assessments

A handful of shapes account for most of what gets asked in this format: a ranking or deduplication inside groups, a running or rolling total, a period-over-period comparison, and a cohort tracked forward over time. Recognising the shape quickly is most of the speed here; deriving it from scratch while a clock runs is where the time goes. Know that a window function keeps every row while a GROUP BY collapses them, and know which one the question needs. If the exercise is in Python instead of SQL, the same shapes arrive as groupby with transform, shift and merge, and the same grain mistakes are available.

What to demonstrate

Whether you reach the right construct without a detour, such as ROW_NUMBER over a partition to deduplicate instead of a self-join against a MAX subquery
Whether you know what your window frame actually is, since adding ORDER BY inside OVER changes the default frame and silently changes a running total
Whether the thing runs. A near-miss that throws an error scores below a plainer query that returns the right rows.

How to prepare

Write each of the four shapes once from memory against a small schema and keep the working version somewhere you will reread it: dedupe with ROW_NUMBER, a running total, a month-over-month change with LAG, and a retention table
Compute one running total twice on data with tied timestamps, once on the default frame and once with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and look at where the two disagree
If Python is on the table, rebuild the dedupe and the running total with groupby and cumsum, then assert the two implementations return identical rows

Live Technical Deep Dives

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

Group Discussions

An added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.

What to demonstrate

Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact

How to prepare

Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.

Where candidates lose points

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.

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.

Optimising accuracy on a heavily imbalanced target

State the base rate first, then choose the metric from the relative cost of a false positive against a false negative: precision and recall at the operating threshold, PR-AUC, or expected cost. At a 1 percent positive rate, predicting the majority class for everyone scores 99 percent accuracy and is worthless.

Defining the cohort on a post-treatment condition

Ask how rows entered the table. Filtering on something that treatment itself influences, such as users who finished onboarding or accounts still active at ninety days, breaks comparability between arms; define the population at an entry point that precedes exposure and keep everyone in it.

Questions & practice

"We want to optimize our toothbrush inventory in Southeast Asia. What data would you need, and what model would you build?"

Medium · machine learning and modelling

Approach

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

Follow-up

What would you monitor after launch to know the model is still valid?
How would you choose the decision threshold, and who owns that choice?

What is the "Curse of Dimensionality," and how does it affect your models?

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.
Check what information would not exist at prediction time, and exclude it.

Follow-up

What would you monitor after launch to know the model is still valid?
How would you choose the decision threshold, and who owns that choice?

Describe the architecture of a Random Forest and how it differs from Gradient Boosting.

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.
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?

Weighted forecast error at the lag ordering actually consumes

Easy · wmape · forecast bias · aggregation grain · pandas

You are given fct_inventory_daily as a DataFrame with inventory_date, sku_id, location_id, demand_qty and forecast_qty_lag7. Replenishment is decided weekly per SKU per node, so score the forecast at that grain over a trailing 8 weeks. WMAPE is the sum of absolute weekly errors over the sum of weekly demand; weighted mean percentage error is the same ratio with the error left signed. forecast_qty_lag7 is null before a series existed. Return one row per location_id carrying both statistics, the shared denominator, and the count of rows excluded. No forecasting library.

Approach

Decide the null rule before writing any aggregation: a sku-location-week where any constituent day has a null forecast must be dropped whole. Signed error here is forecast minus demand, so summing 7 days of demand against the 5 days of forecast that survived leaves the numerator short by two days of forecast and drives the ratio negative, which reads as systematic under-forecasting the model never committed.
Aggregate demand_qty and forecast_qty_lag7 to sku x location x week first, then difference. Differencing daily and summing the absolute values afterwards answers a different question, since intraday timing error cancels inside a week and the ordering decision never sees it.
Build week buckets from inventory_date with a fixed anchor (a Monday-start ISO week or a rolling 7-day offset), and use the same buckets for both series so no partial week sits at either end of the 8-week window.
Compute the two statistics by summing numerator and denominator to the location level and dividing once. Never average sku-level ratios: a C-class item with 3 units of weekly demand would otherwise carry the same weight as an A-class item with 3,000.
Return the denominator and the excluded row count as columns, not as a printed aside, so a reader can tell a genuinely accurate node from one with almost no scored history.

Worked solution (20 min)

Filter to the trailing 8 complete weeks by inventory_date, then drop any sku-location-week containing a null forecast_qty_lag7 and record how many rows that removed.
Group by sku_id, location_id, week and sum demand_qty and forecast_qty_lag7 into weekly totals.
Add abs_err = (forecast - demand).abs() and signed_err = (forecast - demand) on the weekly frame.
Group by location_id and sum abs_err, signed_err and demand_qty; divide the first two by the third to get wmape and wmpe.
Attach the denominator and the excluded count, and sort by denominator descending so the nodes that matter read first.

Expected result: One row per location_id with wmape, wmpe, demand_denominator and excluded_rows. wmape is non-negative; wmpe carries a sign, positive meaning the node is over-forecast under the forecast-minus-demand convention and negative meaning under-forecast. A node with wmpe near zero and wmape near 0.3 is unbiased but inaccurate.

Verify it yourself

Recompute WMAPE at daily grain over the identical row set: it must be greater than or equal to the weekly figure, because the absolute value of a sum of daily errors never exceeds the sum of their absolute values.
Recompute the signed bias at daily grain: it must equal the weekly figure exactly, since signed errors and the denominator both add across days. A mismatch means the two grains are not covering the same rows.
Confirm abs(wmpe) is less than or equal to wmape at every location; a violation means the two statistics are being computed over different row sets, which is the signature of a partially-dropped week.
Set forecast_qty_lag7 equal to demand_qty on a copy and confirm both statistics return exactly 0.0.

Follow-up

The same series is scored at lag 28 and WMAPE roughly doubles. Is that a model problem or an expected property of the horizon?
A node shows WMAPE of 0.35 and bias of 0.01. What can you and can you not conclude about its inventory position?
How would you report accuracy for a SKU whose weekly demand is zero in 40 of the 52 weeks?

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.

Worked solution (45 min)

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.
CTE placements: placed_qty = GREATEST(pipe - lag_pipe, 0), aggregated to sku-location-week.
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.
Join the two weekly series per node, require a minimum week count, and compute stddev_samp and avg on each side.
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?

Dead stock cells excluding SKUs used as substitutes

Medium · anti-join · null semantics · dead stock

Find dead stock: sku_id x location_id cells whose latest fct_inventory_daily row has on_hand_qty > 0 and which have no fct_order_line with that sku_id and ship_from_location_id in the last 90 days. Also exclude any SKU that filled another line as a substitute in that window; fct_order_line.substituted_sku_id is NULL on every line that was not substituted. Value each cell at on_hand_qty * standard_cost_cents taken from the snapshot, not from dim_sku. Return the cell, days since its last demand line, and value, ordered by value. Treat line_status = 'cancelled_no_supply' as demand.

Approach

Take the latest snapshot per sku-location with ROW_NUMBER() OVER (PARTITION BY sku_id, location_id ORDER BY inventory_date DESC) = 1, or an equivalent MAX(inventory_date) join, and keep rows with on_hand_qty > 0.
Anti-join demand with NOT EXISTS against fct_order_line on both sku_id and ship_from_location_id over the 90-day window, counting every line status including cancelled_no_supply, because unmet demand is still demand and a cell with it is not dead.
Write the substitute exclusion as NOT EXISTS, or force substituted_sku_id IS NOT NULL inside the subquery. NOT IN over a column that contains NULLs evaluates to NULL for every candidate that is not an explicit match, so the predicate is never true and the result set is empty.
Value from the snapshot's standard_cost_cents rather than dim_sku, since the dimension carries only the cost in force today and is revised at period close without back-dating.
Get days since last demand from a LEFT JOIN LATERAL MAX(ordered_at_utc), and report cells that never had a line in a separate bucket instead of as a very large number.

Follow-up

The query returns zero rows. Name the two likeliest causes and say which you would test first, with the one-line test.
How do you separate genuinely dead stock from a seasonal item out of season and from a lifecycle_status = 'pre_launch' SKU?
The same SKU is dead at one node and short at another. Write the query that finds that pattern and say what it implies about the policy.

How do you prioritize your tasks when you have multiple high-priority deadlines?

Medium · product sense and metrics

Approach

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

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?

"Our e-commerce sales dropped by 5% last month. How would you investigate this using data?"

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

Follow-up

What would you do if the primary metric and the guardrail moved in opposite directions?
Which segment would you cut first, and what would that rule out?

An OEE target that makes the plant run the wrong SKUs

Medium · oee decomposition · guardrails · mix shift

A constraint work centre is put on an OEE target. Using 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, run_status) and fct_inventory_daily (on_hand_qty, demand_qty), define OEE precisely, decide whether planned changeover sits inside or outside the denominator, and name the guardrail that stops the target being met by running long batches of whatever is easiest. Defend the conflict rather than hiding it.

Approach

Write the metric exactly: SUM(good_qty * ideal_cycle_seconds_per_unit) over SUM(planned_production_seconds), re-summed for every roll-up, and show that it factors into availability times performance times quality so a movement is attributable before anyone argues about it.
Settle the changeover question in writing, because it decides gameability. Leaving planned changeover inside the denominator makes OEE fall whenever variety rises, which is the behaviour the target would then buy; excluding it protects mix but hides real capacity loss. Exclude it and carry changeover_seconds as its own reported series.
Use ideal_cycle_seconds_per_unit per SKU and work centre rather than a line average, since a line-average nameplate rate turns performance into a function of which SKU happened to run.
Set the guardrail on demand-weighted schedule adherence, good_qty against planned_qty at SKU level weighted by what the network is short of, paired with finished-goods days of supply for the SKUs that were skipped.
State the conflict plainly: a plant maximising OEE lengthens runs and drops small-volume SKUs, which raises the score and lowers service, so the two metrics are published together and the target is set on the pair, not on OEE alone.
Scope the metric to constraint work centres, because OEE on a non-constraint rewards making inventory nobody is waiting for, and scope is part of the definition rather than a footnote.

Worked solution (25 min)

Compute the factors per work centre per shift: availability = run_seconds / planned_production_seconds, performance = (total_qty * ideal_cycle_seconds_per_unit) / run_seconds, quality = good_qty / total_qty.
Verify the identity on every row: the product equals good_qty * ideal_cycle_seconds_per_unit / planned_production_seconds.
Build the mix control as SKU share of run_seconds per period, then split a period-over-period OEE move into within-SKU rate change and mix change.
Build the guardrail from demand-weighted schedule adherence plus days of supply from fct_inventory_daily for the SKUs on the plan.
Write the target as a pair with an explicit adherence floor, and state the decision rule for the case where OEE rises while adherence falls.

Expected result: A definition sheet: OEE = SUM(good_qty * ideal_cycle_seconds_per_unit) / SUM(planned_production_seconds) on constraint work centres only, with planned changeover excluded from the denominator and reported as its own series; availability, performance and quality published beside the product; rework handling written down explicitly; and a paired target under which a rise in OEE accompanied by a fall in demand-weighted schedule adherence is not recorded as a win.

Verify it yourself

Availability times performance times quality reproduces the headline on every row to rounding; a mismatch means one factor is using a different denominator.
good_qty plus scrap_qty reconciles to total_qty on completed runs once the rework convention is stated; rows that do not reconcile are a data defect rather than a performance result.
Holding SKU mix fixed at the prior period and recomputing shows how much of the OEE movement was mix rather than rate; if the two versions agree, the mix genuinely did not move and that should be said out loud.

Follow-up

Availability rose and performance fell in the same week. Give two explanations and the query that separates them.
Where does rework_qty enter your definition, and what does that choice reward?

Landed cost per unit rose with every lane flat

Easy · mix shift · simpsons paradox · cost per unit

Landed cost per delivered unit rose from 4.12 dollars to 4.49 dollars month over month, yet cost per unit is flat or lower in every individual lane and no contract rate changed. From fct_shipment_leg (linehaul_cost_cents, fuel_surcharge_cents, accessorial_cost_cents, expedite_premium_cents, shipped_units, mode, service_level, origin_location_id, destination_location_id, leg_status, direction), quantify how much of the 37-cent move is mix and how much is rate, then state what you would do about it. Multi-stop loads are allocated across legs by weight.

Approach

Reproduce the headline by summing the four cost columns and shipped_units over legs with leg_status = 'delivered' and direction IN ('outbound','interfacility'), dividing once. Confirm both facts hold on the same row set; if the lane figures came from an unweighted average of lane rates, the paradox is an artefact of the averaging and the work stops here.
Write cost per unit as a weighted mean of lane costs, C = SUM(w_i * c_i), where w_i is the lane's share of shipped_units. Compute within = SUM(w0 * (c1 - c0)), mix = SUM(c0 * (w1 - w0)) and the interaction term, and verify they sum to 37 cents.
If mix dominates, find which weights moved. Cut w by mode, by service_level and by distance band: parcel and ltl cost multiples of ftl per unit for the same goods, and 'expedite' carries expedite_premium_cents, so a migration of units across those categories raises the blend with no rate moving anywhere.
Separate a rate change from a surcharge change by decomposing the numerator as well. Fuel surcharge and accessorials move independently of contract linehaul, so report all four cost components per unit side by side for both months before accepting that rates held.
State the action in the units the decomposition produced. A mix problem is solved by load consolidation, order cut-off changes or minimum order quantities. A carrier negotiation only touches the within-lane term, which by hypothesis is the small one.

Follow-up

Units migrated from truckload to parcel. Is that a transport failure, an order-profile change, or an inventory-positioning failure, and how would you tell them apart?
You allocated multi-stop cost by weight. Which lanes change rank if you allocate by cube instead, and for what kind of freight?
How would you build a mix-adjusted cost index that a monthly review can read without redoing this decomposition every time?

Suggested practice checklist

Roughly 90 minutes a night on weekdays with one longer weekend block. The plan deliberately cuts scope rather than compressing everything, on the assumption that finishing one thing a night beats half-starting four.

Day 1 — Fix the scope and set a baseline

Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list for everything else and keep it visible all week.
Take one 20-minute SQL prompt and one 10-minute metric question cold, and write the single sentence that says what blocked each attempt, since that sentence is what decides which two topics get the most evenings.
Set the week's one rule: one problem finished to completion every night, including the night you only have 40 minutes.

Deliverable: A one-page scope with an explicit not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One query pattern, written three times

Choose the single pattern most likely to appear (a cohort retention grid, or a funnel counted by user) and write it three times from a blank file rather than editing the previous attempt.
On the third attempt, write the grain of every CTE as a comment before writing its body.
Stop at 90 minutes even if the third version is imperfect, and write the one thing you would fix with another hour.

Deliverable: Three independent versions of the same query plus a note on what changed between them.

Day 3 — Only the statistics you will be asked to defend

Write, in under 200 words, how you would decide whether a difference between two groups is real: the test, its assumptions, and what you would switch to when an assumption fails.
Compute a 95 percent confidence interval for a difference in proportions by hand on realistic numbers, then write in one sentence what changes if the two samples are paired rather than independent.
Write your answer to "what does a p-value mean", check it against a definition, and delete the version that describes it as the probability the hypothesis is true.

Deliverable: A 200-word written answer and one hand-computed interval you can reproduce under pressure.

Day 4 — One case, and the assumptions holding it up

Answer one product case aloud in 20 minutes with a recording running, then listen back with a pen and mark every claim you asserted without saying what it rested on: an assumed user behaviour, an assumed data source, an assumed baseline rate, an assumed grain.
Pick the three assumptions the recommendation actually depends on, write how you would check each one against data, and say which one being wrong would flip the recommendation rather than merely weaken it.
Write the four-step structure you used onto a card small enough to hold in working memory when you are nervous.

Deliverable: One recording, three load-bearing assumptions each with a written check, and a four-step structure card.

Day 5 — Your own work, timed

Write a 90-second version and a four-minute version of your main project, and time both out loud rather than reading them.
Prepare answers to the two follow-ups that always come: what you would do differently, and how you knew it worked.
Put one number in the first sentence and be able to say exactly where that number came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in a longer weekend block

Run a 60-minute mock covering query work, a case and a behavioural question in a single sitting with no breaks, because sustained attention is the thing evenings have not trained.
Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

Write the 20-minute warm-up you will actually do on the morning of the interview: one query you can already write from a blank file, one metric you can define out loud, and nothing you have never seen before.
Re-read only your own notes from this week, and open no new material.
Write down the logistics: the tool you will be asked to work in, whether lookups are allowed, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the case structure, the project numbers, and the logistics.

Where your data came from

Data people depend on systems owned by other teams, and much of the job is negotiating for instrumentation, access, or a fix to a broken pipeline. Prepare an example of getting something changed upstream that you did not control. Describe what you asked for, what you traded, and how you worked while you waited.

Describe a situation where a project you were working on failed. What did you learn?

Medium · behavioural and stakeholder questions

Approach

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

Give an example of a time you went above and beyond for a stakeholder.

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

Follow-up

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

Defend a finding that the expedite program bought nothing

Medium · defending findings · counterfactual reasoning · landed cost

Over two quarters, expedite_premium_cents on outbound legs in fct_shipment_leg rose from 0.4 to 1.3 percent of landed cost, while perfect order rate moved from 91.2 to 91.5 percent, inside the week-to-week spread. The transport lead who sponsored the expedite program disputes the finding in a review in front of his director, arguing that your window contains a port disruption that would have made service worse without the spend. Present the finding, say what you concede on the spot, say what you hold, and name the evidence that would change your conclusion.

Approach

Restate his objection in its strongest form before answering it, because a counterfactual worsening is a legitimate argument and treating it as an excuse ends the conversation.
Separate what you measured from what you claimed: the data show no detectable service gain, not that expedite has no effect, and the difference is the entire argument.
Test his hypothesis with the data you already have rather than defending in the abstract: split legs by exception_code = 'customs_hold' and by lane, and compare expedited against non-expedited legs on the same lanes in the same weeks, since if expedite were holding the line the expedited lanes should show a service gap over comparable non-expedited ones.
Concede what is true: without a holdout you cannot rule out a protective effect, and the pre-period is contaminated by the disruption, so the honest statement is an upper bound on the gain rather than a zero.
Hold the part that survives: the spend is real, it is concentrated in a small set of lanes, and nobody set a decision rule for when a leg gets expedited, which is a controllable problem independent of the counterfactual.
Name the next measurement: a lane-level staggered switch-off with a stated burn-in, and say what it would cost and how long it would take.

Follow-up

He offers to run the switch-off only on his two best lanes. Why is that a problem, and what do you counter with?
His director asks you for a yes or no on cutting the budget today. What do you say?