Tesco Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Tesco, you are positioned at the heart of one of the world’s most complex retail and logistics ecosystems. Your work directly influences how millions of customers interact with the brand, whether through personalized product recommendations, supply chain optimization, or forecasting demand for Tesco Mobile and grocery operations. You are not just building models; you are solving large-scale, high-impact business problems that keep a global retailer moving efficiently.

A large share of questions open as "how would you measure X", where the real work is choosing the metric, fixing its denominator, and defining the population it applies to. Any computation comes last and is frequently not required at all.

Focus areas

• Diagnose conversion drops by step and device
• Cohort revenue by first delivery, not signup
• Compute margin after discounts, returns and shipping

Overview

This role requires a blend of rigorous technical capability and strong product intuition. You will collaborate with cross-functional teams—including engineers, product managers, and commercial stakeholders—to translate ambiguous business challenges into actionable data products. Given the scale of Tesco's data, you will find yourself working on problems that demand both precision and scalability, making this an ideal environment for those who thrive when balancing statistical depth with real-world application.

Focus your preparation on how your technical solutions directly tie back to business KPIs and customer value, as Tesco interviewers prioritize impact over purely theoretical model performance.

The Tesco interview process

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

Recruiter Screen

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

• Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
• Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
• The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

• Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
• Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
• Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical Assessments

Before anything else, this round is a reading test. You are given a small schema and a question phrased in business language, and most of the difficulty sits in the gap between them. Who counts as an active user, does a refunded order still count as an order, is that date column an event time or a load time. Weak answers start typing immediately and compute something precise about the wrong population. Strong ones pin the definition in one sentence, name the column that encodes it, then write the query. On a timed assessment with nobody to tell, write the definition in a comment anyway.

What to demonstrate

• Whether an ambiguous term becomes a specific column and filter before any computation happens
• Whether you read the schema for keys and cardinality rather than only for column names
• Whether the result answers the question at the grain it was asked at, per user or per session or per day

How to prepare

• Take three metrics you already use and write down the exact filter and exact grain behind each, then practise stating one of them in a single sentence out loud
• On a schema you have never seen, spend the first minute writing what one row of each table means and which key it is unique on, then predict which joins can duplicate rows
• Rehearse a version where the definition changes halfway through, and edit the query you have instead of starting over

Behavioral Assessments

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

What to demonstrate

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

How to prepare

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

Take-Home Task

Before any modelling, the dataset is itself the first test. Take-home data usually carries something broken: rows duplicated at an unexpected grain, a join that silently drops part of the population, timestamps stored in more than one timezone, or missingness correlated with the outcome. An hour spent profiling row counts, key uniqueness and date ranges is not overhead, because it decides whether every number after it is real. What separates submissions is whether you report the defects you found and adapt the analysis to them, rather than modelling over them quietly and hoping the aggregate absorbs it.

What to demonstrate

• Whether you established the grain of each table and checked row counts after every join, and said so in the writeup
• Whether data defects you found are surfaced with their effect on the conclusion, instead of being dropped without comment
• Whether filters and exclusions are reproducible from the submitted code, with the size of the excluded population quantified

How to prepare

• Write a short profiling script you can point at any unfamiliar table: row count, distinct key count, null rate per column, and the min and max of every date field, then run it before anything else
• Write the funnel or the join chain as one query and check the row count at each grain, so a silent fan-out shows up as a number rather than as a wrong answer later
• On a past dataset, list every exclusion you applied and how many rows each one removed, then draft the single sentence about it you would put in a report

Final Behavioral Rounds

Behavioural answers from data candidates get audited in a way that answers from other roles do not. When you say a model lifted retention, the next question is the denominator, the window, and how you knew the lift was not seasonal. So attach the measurement to each claim while you tell it: what the metric was before, over what period, and against what comparison. Numbers with no baseline read as rounded-up memory, and one unsupported figure tends to make the rest of the story sound rehearsed.

What to demonstrate

• Whether each impact number arrives with a baseline, a window and a comparison, or as a bare percentage
• Whether you can name the method that attributed the effect to your work (an experiment, a staged rollout, a seasonal control) or concede the link was correlational
• Whether the magnitudes stay internally consistent when the interviewer multiplies them against the scale you described earlier

How to prepare

• For each story, write the impact line as metric, value before, value after, window, and how attribution was established. Any line missing two of those five is a follow-up you will answer badly.
• Re-derive one headline number from the source table rather than the deck that reported it. Resume numbers drift upward across retellings.
• Decide in advance which figures you cannot share, and prepare the ratio or relative change you can give instead, so a confidentiality limit does not read as evasion.

Where candidates lose points

Judging merchandising and recommendation changes on the surface they touch

Click-through or attributed revenue on a recommendation slot rises whenever the slot shows items the customer was going to buy anyway, so the surface metric measures capture rather than creation, and the units almost always come from a different slot, a search result or a later visit. The correct read is site-wide net revenue per session over a holdout, adjusted for returns, because surfacing more apparel or more discounted stock reliably moves both the return rate and the discount depth in the wrong direction while the click metric improves.

Fitting demand models on sales when sales are censored by availability

Units sold equal the minimum of demand and what was sellable, so every day a SKU was out of stock contributes a zero that looks identical to genuine indifference. A model trained on that history forecasts the stockout, the buy shrinks, availability falls further, and the error compounds each cycle, which is why a declining SKU forecast should always be checked against minutes_unavailable before it is believed. The fixes are to restrict the fit to in-stock periods, to model availability explicitly as an exposure term, or to use a censored likelihood; all three require the availability history to be retained at a finer grain than a daily end-of-day snapshot, which is exactly what teams tend to discard.

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.

Reaching for a model before the target metric exists

Before naming an algorithm, write down the label, the prediction time, and the action that changes when the score crosses a threshold. If you cannot say what decision the output drives, any modelling choice is guesswork dressed up as method.

Questions & practice

How would you evaluate the impact of a price-sensitivity model on overall basket size?

Medium · machine learning and modelling

Approach

1. Say how the offline result would be validated online before it is trusted.
2. Frame the prediction: the label, the moment of prediction, and the action it triggers.
3. Set a baseline first, so any model has something honest to beat.

Follow-up

• Where could label leakage enter this setup?
• How would you choose the decision threshold, and who owns that choice?

Simulate the buy quantity that maximises expected season profit

Medium · simulation · newsvendor · asymmetric loss · quantiles

You get demand_draws, 10000 samples of total season demand for one seasonal SKU, generated from the historical demand model. There is a single buy before the season, no replenishment, and leftovers clear at the end. Unit economics in cents: full price 6000, landed unit cost 2400, clearance recovery 1500 net of handling. Write a simulator that evaluates expected profit over a grid of buy quantities and returns the argmax. Then state the closed-form answer this must agree with, and quantify the profit lost by buying to the mean demand instead.

Approach

1. Write profit for a single demand draw d and quantity q as (price - salvage) * min(d, q) - (cost - salvage) * q, which is the algebraic rearrangement of pricemin(d,q) + salvage(q-d)+ - cost*q and avoids computing two branches.
2. Vectorise over the grid: np.minimum.outer(demand_draws, q_grid) gives a draws-by-grid matrix; take the column means to get expected profit per q in one pass rather than looping over draws.
3. Derive the closed form before looking at the simulation output. Underage cost is the lost contribution per unit of unmet demand, 6000 - 2400 = 3600; overage cost is the loss per unsold unit, 2400 - 1500 = 900; the critical ratio is 3600 / 4500 = 0.8, so the optimum is the 80th percentile of demand.
4. Compare argmax of the simulated curve with np.quantile(demand_draws, 0.8) and check they agree to within one grid step; a systematic gap means the profit function is miscoded, not that the theory is wrong.
5. State the preconditions that make the critical ratio valid: one selling season, salvage below cost below price, demand independent of the quantity ordered, and no goodwill cost for a stockout. Adding a lost-sale penalty raises the underage cost and pushes the quantile up.

Worked solution (30 min)

1. Set q_grid = np.arange(0, demand_draws.max() * 1.2, 5) so the grid spans past the plausible optimum on both sides.
2. Build sold = np.minimum.outer(demand_draws, q_grid) and profit = 4500 * sold - 900 * q_grid, then take profit.mean(axis=0).
3. Read off q_grid[expected_profit.argmax()] and compare with np.quantile(demand_draws, 0.8).
4. Evaluate expected profit at q = demand_draws.mean() and report the shortfall against the optimum in cents and as a percentage.
5. Plot or tabulate the curve near the optimum to confirm it is concave and flat-topped, which is why being slightly over is cheaper here than being slightly under.

Expected result: The simulated argmax lands within one grid step of the empirical 80th percentile of demand_draws. Buying to the mean of demand_draws is worse (strictly, unless the mean happens to coincide with that 80th percentile), and the shortfall equals the integral of the marginal profit 4500*P(D>q) - 900 taken from the mean up to the 80th percentile; that integral is non-negative whichever of the two is larger, because the marginal is decreasing in q and crosses zero exactly at the optimum. The mean is not the median and neither is the target: nothing in the prompt makes this demand distribution symmetric, and the seasonal demand these draws come from is normally right-skewed, so mean > median while both sit well below the 0.8 quantile. Report np.mean, np.median and np.quantile(demand_draws, 0.8) side by side rather than letting any one of them stand in for another.

Verify it yourself

• argmax matches np.quantile(demand_draws, 0.8) within one grid step
• Setting salvage equal to cost makes overage free, so the critical ratio is 1 and the expected-profit curve rises to max(demand_draws) and is exactly flat above it; argmax then returns the first grid point at or above max(demand_draws), not the top of the grid
• Setting salvage to zero gives a critical ratio of 3600/6000 = 0.6 and a visibly lower optimum
• Expected profit at the simulated optimum is at least expected profit at np.mean(demand_draws), and strictly greater whenever the mean differs from the 0.8 quantile. Print mean, median and the 0.8 quantile of the fixture and confirm all three differ, so a run that silently substituted the median for the mean, or either for the quantile, cannot pass

Follow-up

• A stockout sends some customers to a substitute SKU you also own. How does that change the underage cost, and in which direction does the optimal quantity move?
• The demand draws come from a model fitted on sales history that contains stockouts. What is wrong with the draws, and which direction does the error push the buy?
• How would you extend this to two buys, an initial commitment and a mid-season reorder with a lead time?

Implement demand-weighted in-stock rate from a written definition

Easy · metric definition · weighting · pandas · aggregation

inventory has one row per snapshot_date, sku_id, node_id with columns node_type, minutes_unavailable (0 to 1440), was_listed (bool), forecast_units (nullable float) and gross_units_sold. Implement the weekly demand-weighted in-stock rate per node: numerator sums forecast_units * (1440 - minutes_unavailable) / 1440 over rows with was_listed True, denominator sums forecast_units over the same rows. Weeks start Monday. Also return the unweighted rate, the share of eligible rows dropped for a null forecast, and the count of SKUs behind each week.

Approach

1. Write the eligibility filter once as a boolean mask (was_listed and forecast_units notna) and apply it to numerator and denominator together, since a row eligible for one and not the other silently changes the metric's meaning.
2. Clip minutes_unavailable to [0, 1440] and check how many rows needed clipping, because an out-of-range value from a clock or restart bug produces an availability factor above 1 or below 0 and a rate that leaves [0, 1].
3. Derive the week key with snapshot_date - to_timedelta(dayofweek, 'D') rather than dt.isocalendar().week, which returns a year-agnostic integer and collides across years at the turn of January.
4. Aggregate numerator and denominator separately by (node_id, week) and divide at the end. Never take the mean of daily rates: a week rolls up by re-summing both sides, and a low-demand day would otherwise carry the same weight as a peak day.
5. Compute the unweighted rate as the simple mean of the availability factor over the same rows and report both, so the long-tail distortion is visible rather than assumed.

Follow-up

• forecast_units is the number the buy was placed against, so it is wrong exactly when the forecast was wrong. What else could weight this, and what does each alternative bias?
• A SKU with was_listed False all week disappears from both sides. Is that the right behaviour, and how would you separate 'not stocked here' from 'stocked out here'?
• The rate is 97 percent but the merchandising team says the top sellers are empty. What cut of this data would you show to settle it?

Write a query using SQL window functions to calculate a rolling average of sales over the last 7 days.

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 does the query change if the join becomes one-to-many?
• What breaks if events arrive late or out of order?

Explain how you would optimize a complex query that is timing out on a large transaction table.

Medium · sql and data manipulation

Approach

1. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
2. Check whether any join is one-to-many before aggregating, or the sums inflate.
3. State the window function and its partition and ordering out loud before writing it.

Follow-up

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

Net revenue per order without fanning out multiple return lines

Medium · join fan-out · pre-aggregation · revenue definition

fct_order_line (order_line_id, order_id, quantity, unit_paid_price_cents, line_status, placed_at_utc, delivered_at_utc, currency_code, fx_rate_to_usd) joins one-to-many to fct_return_line (return_line_id, rma_id, order_line_id, quantity_returned, refund_amount_cents, refunded_at_utc); one order line can generate several return lines when a customer returns part of a quantity twice. For orders placed in a given month, return order_id, gross_usd over delivered lines, refund_usd, and net_usd. Attribute every refund to the parent order's placed month, not the refund month. Convert with the parent line's fx_rate_to_usd.

Approach

1. Count rows at each grain before joining: lines in the month, return lines against those lines, and the number of order lines with more than one return line. That last count is the size of the fan-out you are about to create.
2. Pre-aggregate returns in a CTE keyed on order_line_id: SUM(quantity_returned), SUM(refund_amount_cents). This collapses the many side to one row per key, so the subsequent join is one-to-one.
3. LEFT JOIN the aggregate onto the order lines and COALESCE the refund to 0, so lines with no return survive; an inner join here deletes the majority of revenue.
4. Apply fx_rate_to_usd at the line, before any SUM, because each line carries its own rate and summing local cents first then applying one rate mixes currencies.
5. Filter gross to line_status = 'delivered', but keep refunds attached to their delivered parent rather than filtering on refunded_at_utc, which is what produces placed-month attribution.
6. Roll up to order_id last, and keep the line-grain CTE available so any suspicious order can be decomposed.

Worked solution (25 min)

1. Compute total gross USD from fct_order_line alone for the month and write it down as the reference figure.
2. Write the naive join version, sum gross, and confirm it exceeds the reference.
3. Build the returns CTE grouped by order_line_id and LEFT JOIN it, then re-sum gross and confirm it now equals the reference.
4. Aggregate to order_id with gross_usd, refund_usd, net_usd.
5. List the top ten orders by refund_usd / NULLIF(gross_usd, 0) as a face-validity pass.

Expected result: The pre-aggregated query's total gross equals the gross computed from fct_order_line with no join at all. The naive version is strictly larger, and the difference is concentrated entirely in order lines with two or more return lines.

Verify it yourself

• SELECT order_line_id FROM fct_return_line GROUP BY 1 HAVING COUNT(*) > 1 identifies exactly the lines whose gross was inflated in the naive version.
• Total quantity_returned per order line never exceeds that line's quantity; violations are a data bug, not a modelling choice.
• Row count out equals the count of distinct order_id with at least one delivered line.
• net_usd equals gross_usd minus refund_usd on every row.

Follow-up

• Refunds arrive weeks after the sale. How long do you hold this month's number before publishing it, and what do you key that lag off?
• How does this query change if you need net revenue per customer rather than per order, given household_id is nullable?
• A refund exceeds the line's paid amount. Name two legitimate reasons and one bug.

How would you design a metric to measure the success of a new personalized recommendation feature?

Medium · product sense and metrics

Approach

1. State what result would change your recommendation, so the answer is falsifiable.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. Fix the population and the time window before naming any metric.

Follow-up

• How would you detect that the metric is being gamed rather than genuinely improving?
• What would you do if the primary metric and the guardrail moved in opposite directions?

How do you determine if a result is statistically significant when dealing with high-volume, noisy retail data?

Medium · a/b testing and experimentation

Approach

1. State the primary metric and the minimum effect worth shipping, then size the test.
2. Decide the analysis before seeing data, including how long it runs and when you look.
3. Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

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

If your control and treatment groups show a difference, how do you verify that it is not due to a selection bias?

Medium · a/b testing and experimentation

Approach

1. State the primary metric and the minimum effect worth shipping, then size the test.
2. Say whether units interfere with each other, and switch design if they do.
3. Decide the analysis before seeing data, including how long it runs and when you look.

Follow-up

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

What are the most common experimentation pitfalls you have encountered, and how do you mitigate them?

Medium · a/b testing and experimentation

Approach

1. Say whether units interfere with each other, and switch design if they do.
2. Decide the analysis before seeing data, including how long it runs and when you look.
3. State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

• What would you do if you could not randomise at all?
• What would you conclude if the result is positive but the test is underpowered?

If you noticed a sudden drop in the "add-to-cart" rate on the app, how would you go about diagnosing the root cause?

Medium

Approach

1. Say what you would check first and why it is the highest-information step.
2. Clarify what is being asked and what a complete answer would contain.
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?

Choose the randomisation unit for a signed-out surface

Medium · randomisation unit · clustering · contamination

You are testing a size-guide module that is visible to signed-out visitors. Exposure can be keyed on fct_session.session_id, fct_session.anonymous_id, dim_customer.customer_id or dim_customer.household_id. Note that fct_session.customer_id is NULL until sign-in, fct_order_line rows from channel 'store_pos' and 'subscription_autoship' carry session_id NULL, and household_id is non-null for 28 percent of accounts with mean household size 1.4. The primary metric is net revenue per exposed visitor over 30 days. Choose the unit, state what each rejected option breaks, and quantify the variance cost of your choice.

Approach

1. Rule out session_id first: a 30-day outcome spans many sessions, so a visitor would be re-randomised on every visit, diluting the contrast toward zero and breaking the independence the variance estimate assumes.
2. Rule out customer_id for exposure: it is NULL until sign-in, so a customer-keyed assignment cannot be resolved at the moment the signed-out visitor sees the module. It remains the right unit for a signed-in-only surface.
3. Pick anonymous_id as the assignment key, then confront its two leaks. The same person on desktop and on ios_app holds two anonymous_ids and can land in both arms, which attenuates the effect; and orders arriving through store_pos or subscription_autoship carry session_id NULL, so their revenue cannot be attributed to any arm.
4. Handle the unattributable revenue explicitly: measure the share of net revenue that cannot be keyed back, confirm it is similar in both arms, and report the estimate as conditional on web-attributable revenue rather than quietly dropping it.
5. Handle households by assigning every anonymous_id sharing a household_id to the same arm where the link is known, and clustering standard errors on household_id. Price the cost with the design effect 1 + (m-1) * ICC so the sample increase is a number, not a worry.

Worked solution (30 min)

1. Assign on anonymous_id, held for the full 30-day window in a persistent bucket rather than recomputed per session.
2. Force household consistency: where dim_customer.household_id is non-null, take the arm of the first assigned member for all members.
3. Compute the design effect with mean cluster size 1.4 and an assumed intra-household correlation of 0.3: 1 + (1.4 - 1) * 0.3 = 1.12.
4. Inflate the per-arm sample by 12 percent and cluster the standard errors on household_id where present, on anonymous_id otherwise.
5. Report the attribution gap: the percentage of net revenue from store_pos and subscription_autoship lines with session_id NULL, split by arm, as a pre-declared balance check.

Expected result: Assign on anonymous_id with household-consistent buckets and household-clustered standard errors; a design effect of 1.12 means 12 percent more exposed visitors for the same power. Revenue on lines with session_id NULL is excluded and reported separately with its arm split.

Verify it yourself

• Sanity-check the ICC empirically on pre-period data: regress one household member's 30-day revenue on another's and confirm the correlation is near the assumed 0.3.
• Confirm the arm assignment is stable by counting anonymous_ids that appear in both arms across the window; that count should be zero.
• Confirm the unattributable revenue share differs by less than a percentage point between arms, otherwise the exclusion is itself a source of bias.

Follow-up

• Cross-device contamination attenuates the measured effect. If 18 percent of exposed visitors see both arms, what does that do to the estimate, and does it make the test conservative or anti-conservative?
• How would you decide whether to reconcile anonymous_id to customer_id retrospectively after sign-in, given that the reconciliation itself is only possible for visitors who converted?

Every device improved but the aggregate rate fell

Medium · mix shift · simpsons paradox · segmentation

Week over week, visit-to-order conversion rose in all four device_type values, yet the all-up rate fell 18 basis points. fct_session gives session_id, order_id, device_type, entry_channel, campaign_id, customer_id (NULL when signed out), tracking_consent and is_bot_flagged. Deliverable: decompose the aggregate change into a within-segment component and a mix component that reconcile to the observed delta, name the traffic shift that produced the mix term, and state which single number belongs in the weekly review.

Approach

1. Write the aggregate as p = sum over segments of w_i * p_i, where w_i is the segment share of eligible sessions and p_i is the segment rate. Every claim that follows has to come out of that identity rather than out of intuition.
2. Compute the three terms explicitly: within = sum w_i_old * (p_i_new - p_i_old), mix = sum (w_i_new - w_i_old) * p_i_old, interaction = the remainder. Confirm they sum to the total delta before interpreting anything; if they do not, the eligibility filter differs between the two weeks.
3. Locate the mix term. Rank segments by (w_i_new - w_i_old) * (p_i_old - p_old) to see which share move did the damage, then push one level deeper: entry_channel by device, campaign_id, signed-in versus anonymous, country.
4. Decide whether the mix shift is the business result or an artefact. A paid social campaign buying low-intent mobile traffic is a real and intentional mix shift; a bot-rule change or an app tracking loss that removes high-converting sessions is not.
5. Publish the standardised rate (new segment rates at last week's mix) next to the raw rate, and say plainly that the raw rate fell because the traffic changed, not because the site got worse.

Follow-up

• Which mix do you standardise on: last week, a rolling 13-week base, or plan mix? What breaks when the base is stale?
• If one device segment is small enough that its rate is noisy, how does that noise propagate into the standardised number?
• Marketing wants credit for the within-segment gain. What do you need before you agree the campaign caused it?

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.

Ambiguous requests are the test

Most of the questions in this section reduce to one thing: can you be handed a vague request and come back with something useful? Prepare an example where the ask was underspecified, you chose an interpretation, and you said out loud which interpretation you chose. Describing how you narrowed the question matters more than the technique you eventually used.

How do you handle missing or inconsistent data when joining large datasets from different retail silos?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Disagree with a product manager about a recommendation slot

Medium · surface metrics · holdout analysis · disagreement

A product manager wants to ship a recommendation module that raised slot click-through by thirty-one percent and slot-attributed revenue by nineteen percent over a two-week test. You have the holdout and can compute site-wide net revenue per session. Over the same window the treatment arm shows a higher net unit return rate and higher promotional depth on purchased lines, and site-wide revenue per session is flat within noise. The product manager has a launch date. Present your position in a fifteen-minute meeting and state what you recommend.

Approach

1. Concede the surface result rather than disputing it. Click-through did rise. The open question is whether the units are new or moved from a search result, another slot or a later visit.
2. State the measurement claim in one sentence: slot-attributed revenue counts capture, and only the holdout prices the counterfactual of what those customers would have bought anyway.
3. Give the site-wide read with an interval, and say whether the test was powered to detect the effect size being claimed. Flat within noise is not evidence of no effect, and treating it as such is the fastest way to lose the argument to someone who notices.
4. Bring the two mechanisms that explain the gap and point at where they are visible: the module surfaces more apparel and more discounted stock, which moves quantity_returned in fct_return_line and line_discount_cents in fct_order_line.
5. Recommend something shippable this week rather than a block: launch behind a holdout that stays on, with site-wide net revenue per session after returns as the read at return maturity, and the decision rule agreed now.

Follow-up

• The product manager says returns take ninety days and the launch cannot wait. What do you ship on in the meantime?
• What result would make you say the module is working?

Scope a one-line request for our best customers

Easy · scoping · stakeholder questions · definitions

A merchandising director messages you: can you pull our best customers. There is no other context. You have dim_customer, fct_order_line and fct_return_line. Best could mean highest net spend, highest contribution margin, most frequent, most recent, lowest return rate or highest expected future value, and the resulting lists differ enormously. You get one clarifying exchange before the director is in meetings for the rest of the day. Write what you send back, and describe how you proceed if no reply comes.

Approach

1. Ask about the decision, not the definition. What are you going to do with the list narrows six candidate metrics faster than asking which of six metrics they meant.
2. Put a stated default in the same message so a non-reply is still progress: trailing 365 days, customer grain, net of returns, and say so in one clause.
3. Make the ambiguity concrete with a measured number rather than a menu. One line reporting the overlap between the top decile by net revenue and the top decile by contribution margin forces the choice without a meeting.
4. Name the filters that depend on the use rather than on the metric. A mailing list needs email_consent = TRUE and excludes account_status in ('closed','fraud_blocked'); a buy-planning read wants neither filter.
5. If nothing comes back, ship the default with the definition, window and return treatment written at the top of the output, and attach the alternative cut so the conversation continues on an artefact.

Follow-up

• The director replies that it is for a loyalty upgrade offer. What changes in the query?
• How do you stop this becoming six different best-customer lists across the company?