Interview guides · Duke Energy

Duke Energy Data Scientist Interview Questions & Guide 2026

Practice explaining analysis before a Duke Energy data scientist interview: the recommendation, the uncertainty, the follow-ups.

The 60-second brief

As a Data Scientist at Duke Energy, you are at the forefront of modernizing one of the largest electric power holding companies in the United States. Your work directly impacts how energy is generated, distributed, and consumed by millions of customers. You will leverage massive datasets—ranging from smart meter telemetry and grid sensor data to weather forecasts and customer usage patterns—to build predictive models that drive strategic business decisions.

Coding rounds for this role are usually data-manipulation shaped rather than data-structure shaped: group-bys, joins, time windows, ranking within a partition. Confirm the format before spending a week on graph traversal.

Focus areas

Join daily snapshots to shipment events safely
Separate true demand from censored, stocked-out sales
Evaluate forecasts at the lag ordering actually uses

Overview

The impact of this position is both critical and highly visible. You will contribute to products and problem spaces such as predictive maintenance for power generation assets, grid load forecasting, anomaly detection for outage prevention, and optimization algorithms that support the transition to renewable energy sources. Your insights help ensure grid reliability, reduce operational costs, and improve the overall customer experience while advancing the company's sustainability goals.

Expect a role that balances complex technical execution with significant stakeholder management. You will navigate a highly regulated, legacy-rich industry that is actively transforming its digital footprint. This requires not only advanced machine learning capabilities but also the ability to translate highly technical concepts into actionable business strategies for engineering teams, operations managers, and executive leadership.

The Duke Energy interview process

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

Phone Screen

A screening call is a matching exercise run by someone who will not evaluate your statistics. They are checking that the work described on your resume is work you personally did, and that its scope matches the level the role is written for. Logistics get settled in the same half hour so nobody spends an interviewer's afternoon on a mismatch. The answer that fails is the one narrated in the plural. If every sentence is 'we built' and 'the team decided', there is nothing specific to write down about you. Name the piece that was yours, the decision you made inside it, and what changed after.

What to demonstrate

Whether the ownership implied by your resume survives one round of follow-up about who actually did which part
Whether your described scope (data size, stakeholders, what shipped) matches the seniority the role is written at
Whether timeline, location and compensation expectations make the rest of the loop worth scheduling

How to prepare

Rewrite your top three resume bullets in the first person singular, each with the decision you made and what moved afterwards, then say them out loud once so the 'we' does not return under pressure
Attach one number to each project: the baseline, the change, and the window it was measured over. Where impact was never measured, say that plainly rather than inventing a figure
Settle your compensation range before the call and give it as a range with a reason behind it, such as current total comp or a competing timeline, instead of deflecting the question twice

Online Technical 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

Virtual Interviews

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.

Panel Interview

A loop is not scored one interview at a time. The people you meet compare notes afterwards, usually in a meeting you are not in, and the outcome turns on what each of them can say about you when asked. That rewards something other than survival: every room needs one specific thing worth repeating, and none of them can contradict another. The common way to lose is to tell the same project four times with different numbers in it, or to be uniformly fine in a way that leaves nobody with anything to argue for.

What to demonstrate

Whether your account of a project survives being told twice, with the same scale, the same metric definition and the same numbers each time
Whether each interviewer leaves with one concrete claim they could make on your behalf later, rather than an absence of complaints
Whether a question you already answered in an earlier room gets the same answer at the same depth, without visible impatience

How to prepare

Write a one-page fact sheet for your two or three main projects that fixes the numbers you will quote: rows of data, the metric as a single sentence, the effect you measured and how long the work took. Say them aloud from the sheet until they come out identical every time
For each kind of room you expect, decide the one sentence you want that interviewer repeating in a debrief, then check during the mock that you said it outright instead of implying it
Rehearse answering the same project question twice in one sitting, the second time as though you had not just answered it, because the thing that needs fixing is the flatness that creeps into a repeated story

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.

Treating shipped units as demand

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

Never asking what decision the analysis will inform

Open with who makes the decision, what the options are, and by when. The answer determines the precision you need, the segments worth cutting, and whether an observational read suffices or an experiment is required.

Generalising beyond the population the sample actually supports

State the frame the sample was drawn from and where it diverges from the population you want to talk about: time window, platform, geography, opt-in. If a group is excluded from the frame, either weight to known margins or narrow the claim rather than quietly extending it.

Questions & practice

What is the difference between L1 and L2 regularization, and when would you use each?

Medium · statistics and probability

Approach

Say what the estimate is of, and over what population it generalises.
Quantify uncertainty explicitly rather than reporting a point estimate alone.
Sanity-check the answer against a simple bound or a simulated case.

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?

How do you check for stationarity in a time-series dataset, and what do you do if the data is non-stationary?

Medium · statistics and probability

Approach

Sanity-check the answer against a simple bound or a simulated case.
Say what the estimate is of, and over what population it generalises.
Translate the result into the decision it informs, in one plain sentence.

Follow-up

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

Explain the bias-variance tradeoff and how it impacts model selection.

Medium · machine learning and modelling

Approach

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

Follow-up

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

Cluster bootstrap for landed cost per delivered unit

Medium · bootstrap · clustering · ratio estimator · mix shift

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

Approach

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

Worked solution (35 min)

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

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

Verify it yourself

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

Follow-up

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

Write a SQL query to find the top 5% of energy consumers in a given zip code over the last month.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Compute rates by summing numerator and denominator separately, never by averaging rates.
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?
How would you verify this result without re-running the same query?

Write a Python function to calculate a rolling 7-day average for a time-series dataset.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Check whether any join is one-to-many before aggregating, or the sums inflate.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

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?

What is your approach to version controlling your data science projects?

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Compute rates by summing numerator and denominator separately, never by averaging rates.
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?

Stockout streaks of three days or more per cell

Medium · window functions · gaps and islands · stockout analysis

fct_inventory_daily carries one row per sku_id x location_id x inventory_date for every active pair, including days with nothing on hand, plus stockout_flag marking days where available_qty reached zero. Return every run of consecutive stockout days lasting three days or more: sku, location, start date, end date, run length, and demand_qty summed across the run. Return alongside it, per sku-location, the count of such runs in the window. Verify that the daily row series is contiguous for each pair before relying on date arithmetic, and say what you do about pairs that fail.

Approach

Filter to stockout_flag = TRUE, then assign ROW_NUMBER() OVER (PARTITION BY sku_id, location_id ORDER BY inventory_date).
Form the island key as inventory_date - rn * INTERVAL '1 day'. Two rows share a key only when their dates are exactly consecutive, so the key cannot weld separate runs together; the exposure runs the other way. A day with no snapshot row inside a true stockout breaks that run into fragments, and any fragment under three days is deleted by the threshold, so the run leaves the output rather than appearing with a wrong length.
Prove contiguity before trusting the key: per sku-location compare COUNT(DISTINCT inventory_date) against (MAX(inventory_date) - MIN(inventory_date) + 1), and separately confirm no pair carries two rows on one date, since a duplicate date advances rn without advancing the calendar and shifts every key after it. Pairs that fail get a generated date spine: LEFT JOIN generate_series over the window so each day is labelled stockout, in stock, or no snapshot, then apply a stated rule for the no-snapshot days instead of letting the key make that call silently. The conservative rule is to break the run at the hole and mark both fragments gap_adjacent.
Group by sku, location and island key for MIN and MAX date, COUNT(*) as length and SUM(demand_qty) as the demand exposed to the stockout, keeping runs of length >= 3.
Aggregate once more per sku-location for the run count, and mark runs touching either window edge as censored, since their true length is unknown from this window alone.

Worked solution (30 min)

CTE completeness: per sku_id, location_id compute COUNT(DISTINCT inventory_date) and the date span, and flag pairs where they disagree or where COUNT(*) exceeds COUNT(DISTINCT inventory_date).
CTE flagged: SELECT the stockout rows with ROW_NUMBER partitioned by sku_id, location_id ordered by inventory_date.
CTE islands: add grp = inventory_date - rn * INTERVAL '1 day'.
GROUP BY sku_id, location_id, grp producing MIN, MAX, COUNT() and SUM(demand_qty); filter COUNT() >= 3.
Second aggregate over that result for runs per sku-location, plus a censored flag where start equals the window start or end equals the window end.

Expected result: One row per qualifying run with start_date, end_date, length and summed demand_qty, every length at least 3, plus a per-cell run count whose sum across cells equals the number of run rows returned. Length, end_date - start_date + 1 and the island's row count agree by construction of the key, so their agreement is not evidence that the runs are real.

Verify it yourself

Every gap between two consecutive returned runs for the same sku-location contains at least one day carrying a snapshot row with stockout_flag = FALSE. A gap made only of days with no snapshot row is a hole rather than a recovery, and those two runs are likely one run the key split.
The completeness CTE flags zero pairs. Where it flags any, that pair's run lengths and run count are lower bounds and must be labelled as such, not published beside clean pairs as if comparable.
Total stockout days across all returned runs is less than or equal to the total count of stockout_flag rows in the window.

Follow-up

The longest runs cluster on X-class A items. What does that combination suggest to look at before touching the safety stock policy?
How would you estimate lost demand across a run, given that demand_qty records the orders that were placed but not the orders nobody bothered to place?
The snapshot is taken at each location's local end of day and the pairs span several timezones. What breaks, and what stays true?

How do you prioritize your work when given multiple urgent projects from different managers?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Name one primary metric, then the guardrail that stops it being gamed.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
Which segment would you cut first, and what would that rule out?

Explain how you would optimize a pandas script that is running out of memory.

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
Decompose the metric into the rates that drive it, and say which one you would check first.
Name one primary metric, then the guardrail that stops it being gamed.

Follow-up

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

How would you design an A/B test to evaluate a new customer energy-saving program?

Medium · a/b testing and experimentation

Approach

Name the guardrails that would stop a launch even on a positive primary result.
Decide the analysis before seeing data, including how long it runs and when you look.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

A carrier scorecard the carrier cannot move on its own

Medium · gameable metrics · mix shift · immutable promise

Design the quarterly scorecard that decides carrier award volume. You have fct_shipment_leg (carrier_id, mode, service_level, origin_location_id, destination_location_id, tendered_at_utc, picked_up_at_utc, promised_delivery_at_utc, delivered_at_utc, planned_transit_hours, actual_transit_hours, stop_count, shipped_units, linehaul_cost_cents, fuel_surcharge_cents, accessorial_cost_cents, expedite_premium_cents, leg_status, exception_code). promised_delivery_at_utc is mutable and exception_code is carrier-supplied. Specify the primary metric, the guardrails, the mix control, and what must change in the data model before the first scorecard is published.

Approach

Name the data defect first. A mutable promise column lets the scored party move its own target, so require a tender-time snapshot of the promise, either an immutable column or a promise event log, and label any scorecard built before that exists as advisory.
Split the clock by who controls it: shipper dwell from tendered_at_utc to picked_up_at_utc, carrier transit from picked_up_at_utc to delivered_at_utc. Score transit against planned_transit_hours and report dwell separately, so a late tender is neither laundered into a carrier miss nor available as a carrier excuse.
Treat exception_code as a claim, not a fact. Publish on-time both with and without carrier-coded exceptions; excluding weather then has to be argued in the open, and a widening gap between the two series is itself the signal.
Control the mix. On-time and cost per unit both move with lane, mode, stop_count and service_level, so compare carriers only within lane by mode by service level cells and roll up on a volume weighting frozen from a prior period.
Pair service with delivered cost per unit including accessorials and expedite premium, because on-time bought by upgrading to guaranteed service or by paying expedite is a price increase rather than a carrier improvement.
Publish the award rule before the quarter opens, including the minimum leg count per cell below which a carrier is unranked, so a carrier with nine lucky legs cannot outrank one with nine hundred.

Worked solution (30 min)

Test mutability empirically: count legs whose promise sits implausibly far from tendered_at_utc plus planned_transit_hours, and legs delivered inside planned transit yet still flagged late; both patterns indicate promises edited after tender.
Build the leg fact with dwell_hours, transit_hours, an on_time flag against the tender-time promise, and an exception flag.
Aggregate to carrier by lane by mode by service level, carrying leg counts, on-time with and without exceptions, and delivered cost per unit.
Roll up to a carrier score using volume weights frozen from a prior period, so this quarter's own mix cannot reorder the ranking.
Produce the ranked table with the minimum-leg threshold applied and the excluded cells listed rather than dropped.

Expected result: A specification where the primary is on-time delivery at leg level against the tender-time promise, computed within lane by mode by service level and rolled up on frozen volume weights; guardrails are delivered cost per unit including accessorials and expedite premium, plus the dwell-versus-transit split; a minimum leg count per cell is stated; and a data-model change request covers an immutable tender-time promise and a non-carrier source for exception codes. Until that column exists the scorecard is published as advisory.

Verify it yourself

Re-running the roll-up with the current quarter's own volume weights changes the ranking only through mix; a large gap proves the mix control is load-bearing and must stay.
On-time with and without exception exclusions should track each other over time; a carrier whose two series diverge across a quarter is coding its way up.
Dwell hours plus transit hours equal tender-to-delivery elapsed time on every leg; any leg where they do not has a timestamp ordering defect and belongs in quarantine, not in the score.

Follow-up

A carrier's on-time is flat but its accessorial charge per leg has doubled. What do you check first?
How do you score a lane where one carrier holds all the volume and no comparator exists?

Perfect order rate falls only in the newest two weeks

Medium · right censoring · reporting lag · metric maturation

A weekly perfect order chart holds at 94 percent for eleven weeks, then reads 91 percent and 84 percent in the two most recent weeks. The metric requires every line shipped_full, actual_delivery_at_utc on or before original_promised_delivery_date, and no joined shipment leg carrying exception_code IN ('damage','address_issue') or leg_status IN ('refused','lost'). It is published on a 14-day lag. Using fct_order_line and fct_shipment_leg, decide whether service regressed and specify what the chart should show for incomplete weeks.

Approach

Start from the structure of the metric: the denominator is fixed at promise date while the numerator requires an event that may not have happened yet, so any week younger than the full delivery-plus-claims window is mechanically depressed. Establish that before hunting for a cause.
Quantify the incompleteness instead of assuming it. Per promise week, report the share of lines with actual_delivery_at_utc IS NULL and the share of shipments whose legs are still leg_status IN ('tendered','in_transit').
Build the maturation curve from history. For each of the eleven settled weeks, recompute the metric as it would have looked at 3, 7 and 14 days after the promise date, so a recent week can be compared against a healthy week at the same age rather than against a settled one.
Only after age-matching, test whether any residual is real by cutting it by ship_from_location_id, mode and exception_code, and asking whether the loss concentrates where a genuine failure would concentrate.
Fix the chart rather than only the analysis: suppress or grey weeks younger than the publication lag, or publish an age-matched estimate with an interval, and write the rule down so the next reader is not caught by the same shape.

Follow-up

Damage claims can arrive up to thirty days later. Does that argue for a longer lag or for a different numerator?
How would you detect a genuine regression inside the lag window without waiting two weeks for it to settle?
P90 order cycle time shows the same maturation shape but biased the other way. Why, and which metric is more dangerous to read early?

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.

Getting an analysis acted on

Work that nobody used is a common and unflattering pattern in data careers, and interviewers probe for it. Have a story about an analysis that changed a decision, and be specific about how you got it in front of the person who could act. Also have one about work that went nowhere, with your reading of why.

Describe a situation where you had to work with a difficult stakeholder or team member.

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
Close with what you would do differently, concretely.
Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

What would you do differently if you ran that project again?
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?

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

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