Interview guides · Koch Minerals & Trading

Koch Minerals & Trading Data Scientist Interview Questions & Guide 2026

Modeling drills for a Koch Minerals & Trading data scientist interview: baselines, regression interpretation, class imbalance, model defense.

The 60-second brief

At Koch Minerals & Trading, a Data Scientist does not work in a vacuum. You are placed at the intersection of global commodity markets, industrial operations, and advanced mathematical modeling. The models and insights you generate directly impact trading strategies, risk management, supply chain logistics, and asset optimization across a massive global footprint. Whether you are forecasting market trends, optimizing logistics, or analyzing industrial data, your work has immediate financial and operational consequences.

If the team owns experimentation, expect depth past a two-sample test: minimum detectable effect and its roughly inverse-square-root dependence on sample size (holding power, significance level and variance fixed), variance reduction from pre-period covariates, interference between units, and when a sequential design is the right call.

Focus areas

Model impact and borrow before claiming capacity
Build point-in-time panels without lookahead
Attach uncertainty to every Sharpe claim

Overview

This role is unique because of the sheer variety of data and business problems you will encounter. Koch Minerals & Trading operates as a core part of the broader Koch Industries ecosystem, meaning you may collaborate with or build solutions for key subsidiaries such as EFT Analytics (focused on industrial analytics) and INVISTA (focused on chemical intermediates and polymers). To succeed, you must be comfortable translating highly complex, unstructured market and industrial data into clear, actionable trading signals and operational efficiencies.

This is a highly visible position where success is measured by the real-world value your models generate. If you are passionate about applying cutting-edge quantitative methods to physical and financial trading markets, this role offers an unparalleled platform to see your algorithms drive multi-million dollar business decisions.

The Koch Minerals & Trading interview process

Koch Minerals & Trading candidates report 4 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

HR Screening Call

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

Technical Evaluation

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

Intensive Technical Round

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

Onsite or Panel Interview

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

What to demonstrate

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

How to prepare

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

Where candidates lose points

Judging execution quality against interval VWAP and treating a favourable number as proof of good trading.

Interval VWAP is a benchmark the trader partly determines: trading in line with volume tracks VWAP almost by construction, and stretching an order over a longer interval makes the benchmark easier while exposing the position to price drift that the benchmark never charges. Arrival price is the benchmark aligned with the decision, because it charges both the spread and the drift between decision and completion, including the unfilled remainder. Reporting both, and reporting the opportunity cost of unfilled quantity, is what separates a real TCA from a flattering one.

Modelling transaction cost as a constant number of basis points, independent of order size and volatility.

Temporary market impact scales approximately with volatility times the square root of participation, that is, of order quantity divided by average daily volume, so cost per share rises as size rises rather than staying flat. A constant-bps assumption is roughly right for the small orders used to calibrate it and badly wrong for the size the strategy would actually run, which is how a book that backtests well at modest notional loses money at ten times the size. It also makes capacity unmeasurable, because capacity is exactly the notional at which marginal impact equals marginal alpha.

Ending an analysis without a recommendation or next step

Close with what you would do and what would change your mind, stated as a condition you can check later. If the evidence is genuinely inconclusive, recommend the specific next measurement and say what it costs in time or exposure.

Writing SQL without stating NULL and tie-breaking behaviour

Before calling a query finished, say what it does with NULLs, ties and empty groups. NOT IN against a subquery containing a single NULL returns no rows at all, and RANK, DENSE_RANK and ROW_NUMBER differ precisely on ties, so name which one the question requires.

Questions & practice

If we want to optimize the supply chain logistics for a physical commodity like minerals or oil, what data sources would you look for, and how would you structure the predictive model?

Medium · machine learning and modelling

Approach

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

Follow-up

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

Walk me through the most complex machine learning project on your resume. What was the business impact, and how did you measure it?

Medium · machine learning and modelling

Approach

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

Follow-up

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

Suppose a trading desk wants a model to identify short-term arbitrage opportunities in energy markets. What features would you engineer, and how would you validate the model's risk?

Medium · machine learning and modelling

Approach

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

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

Annualized information ratio with an honest standard error

Easy · performance · inference · pandas

You are given active, one row per (account_id, business_date) with active_return: the daily arithmetic portfolio-minus-benchmark return, net of all costs, as a decimal. Three accounts, roughly 750 business days each. Rows are absent on exchange holidays and on days before an account's funded_date. Per account, compute the annualized information ratio (mean times 252, divided by standard deviation times sqrt(252)) and an approximate standard error for that ratio, then report a 95% interval. Do not reindex onto a calendar. State what T you used and why.

Approach

Drop null active_return per account rather than filling it. T must be the number of days the account actually traded: holidays and pre-funding days are not evidence, and treating them as such changes both the point estimate and the interval.
Compute the daily mean m and the daily standard deviation s with ddof=1. The annualized IR is (m/s)*sqrt(252), because the 252 in the numerator and the sqrt(252) in the denominator collapse to a single sqrt(252) factor. Say that out loud instead of writing two separate annualizations that can drift apart.
Take the standard error at the frequency the statistic is estimated at: SE(SR_daily) is approximately sqrt((1 + SR_daily^2/2)/T) for i.i.d. normal returns, then scale it by sqrt(252), the same factor as the point estimate. At daily frequency SR_daily^2/2 is of order 1e-3, so the SE is effectively sqrt(252/T) and depends only on elapsed years.
Report IR plus or minus 1.96*SE per account, and check the lag-1 autocorrelation of active_return. The i.i.d. assumption behind that SE is the same assumption that justifies the sqrt(252) scaling, so if the series is autocorrelated both numbers need widening and you should say by how much.

Worked solution (20 min)

groupby('account_id'), dropna on active_return, and record n per account before anything else.
Per account compute m = mean, s = std(ddof=1), ir = m/s*sqrt(252).
sr_d = m/s; se_ann = sqrt((1 + sr_d**2/2)/n)sqrt(252); interval = ir +/- 1.96se_ann.
Compute lag-1 autocorrelation of active_return per account and print it in the same table as the interval.

Expected result: For an account with an annualized IR of 1.0 over 756 trading days the standard error is about 0.58 (sqrt(252/756) = 0.577; the SR^2/2 term moves it by under 0.1%), so the 95% interval runs roughly from -0.13 to 2.13 and the IR is not distinguishable from zero. Three years of daily data cannot establish an IR of 1.

Verify it yourself

sqrt(252/T) must reproduce your standard error to three decimals at daily frequency; a materially different number means you annualized the SE by 252 rather than sqrt(252).
Computing IR as (m252)/(ssqrt(252)) must equal (m/s)*sqrt(252) exactly.
Doubling the sample length should shrink the interval by sqrt(2), not by 2.

Follow-up

The shortest account has 14 months of history. How much of the spread between the best and worst account IR is explainable by sampling noise alone?
One account carries mark_source = 'vendor_eval' on 30% of days. What does that do to the denominator, and to the independence assumption behind the standard error?
How many years of daily data would you need to distinguish an IR of 0.8 from an IR of 1.1 at 95% confidence?

Ramp completion cohort by funding month under amended mandates

Hard · cohort-analysis · scd2 · window-functions · retention

From account_mandate (account_id, funded_date, target_gross_exposure_pct, status, effective_from, effective_to) and position_daily (business_date, account_id, strategy_id, weight_pct_nav, market_value_base), compute ramp completion per funding month: the share of accounts funded in month M whose daily gross exposure first reached 90 percent of target_gross_exposure_pct on or before the 20th trading day after funded_date, plus the median trading day to completion. target_gross_exposure_pct can be amended mid-ramp, so use the value in force on each business_date.

Approach

Compute daily gross as SUM(ABS(weight_pct_nav)) grouped by (account_id, business_date), summing across strategy_id. Absolute values are the point: a market-neutral book nets to roughly zero and would otherwise read as an account that never deployed.
Cross-check that gross against SUM(ABS(market_value_base)) divided by the account's NAV. If the two disagree by more than rounding, the weights are stale or the NAV source is wrong, and every downstream rate inherits that error.
Range-join the mandate on business_date >= effective_from AND business_date < COALESCE(effective_to, DATE '9999-12-31'). Joining on account_id alone fans out one position-day per mandate version, which inflates gross and corrupts the session index at the same time.
Index sessions per account with DENSE_RANK() OVER (PARTITION BY account_id ORDER BY business_date) over dates strictly after funded_date, then take MIN(day_idx) FILTER (WHERE gross_pct >= 0.9 * target_gross_exposure_pct). The first crossing is the definition; an account that touched target once and fell back has not completed, and MAX or ANY would score it as if it had.
Aggregate to the funding month: completion rate is completers within 20 sessions over all accounts funded that month, and the median is PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY day_idx) among completers only. Flag months whose 20-session window has not closed as provisional rather than dropping the incomplete accounts, which would bias the rate upward.

Worked solution (45 min)

Aggregate position_daily to (account_id, business_date) gross and record the pre-join row count.
Range-join the SCD2 mandate and assert the post-join row count is unchanged.
Add the session index restricted to dates after funded_date, and compute the first-crossing day per account.
Bucket accounts by date_trunc('month', funded_date) and compute rate plus PERCENTILE_CONT median among completers.
Mark months where funded_date + 20 sessions exceeds the latest business_date in the panel as provisional.

Expected result: One row per funding month with account count, completion rate in [0, 1], median trading day to completion among completers, and a provisional flag on months whose window has not closed.

Verify it yourself

The count of (account_id, business_date) pairs is identical before and after the mandate join.
Every counted completion day index lies between 1 and 20.
The denominator equals a direct COUNT of accounts with funded_date in that month.
An account whose target was amended mid-ramp appears exactly once per business_date in the joined set.

Follow-up

An account funded on the 28th has two sessions of history when this runs. What do you publish for that month, and how does the number change when it is restated?
Completion rate falls while every account still reaches target eventually. Which constraint would you test first — borrow availability, the restricted list, or a participation cap — and what query settles it?
Two accounts funded the same day, same strategy, diverge by 8 sessions. Name three legitimate causes and the column that would evidence each.

List fills that were never corrected, and survive the NULL

Medium · anti-join · null-semantics · data-quality

execution_fill records a cancel/correct as a new row with is_correction = TRUE and corrects_fill_id referencing the original; an ordinary fill has is_correction = FALSE and corrects_fill_id IS NULL. Return every original fill for a given settle_date that was never corrected, with fill_id, order_id, fill_qty, fill_px and commission_amt. A colleague's version filters WHERE fill_id NOT IN (SELECT corrects_fill_id FROM execution_fill) and returns zero rows on a day with thousands of fills. Explain why, then write the correct query.

Approach

Name the mechanism precisely. x NOT IN (...) expands to x <> v1 AND x <> v2 AND .... Against a NULL element the comparison is UNKNOWN, so the conjunction is either FALSE (when x matches some value) or UNKNOWN, never TRUE. Every probe row is filtered out. It is not an error, which is why it survives review.
Rewrite as NOT EXISTS (SELECT 1 FROM execution_fill c WHERE c.is_correction AND c.corrects_fill_id = f.fill_id). This uses plain equality, is immune to NULLs in the inner column, and is the form the planner turns into a hash anti-join.
If NOT IN has to stay for some reason, add WHERE corrects_fill_id IS NOT NULL to the subquery. It is correct, and it is fragile: the next nullable column someone probes reintroduces the identical bug with no new symptom.
Scope the correction lookup by time, not by the same settle_date. A correction commonly arrives a day or more after the fill it amends, so restricting the inner query to the report date reports already-corrected fills as clean.
Close with arithmetic: originals on the date, minus distinct originals referenced by any correction row, must equal the returned count. If it does not, a correction references a fill_id that does not exist, which is a feed integrity issue worth raising.

Follow-up

Would LEFT JOIN ... WHERE c.fill_id IS NULL give the same answer here? What changes if a single fill is corrected twice?
A correction lands three days after the TCA report went out. How do you make the report reproducible as-of its publication date rather than silently restating it?

How would you design an anomaly detection system to flag unusual trading behavior or operational disruptions in our shipping routes?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.
Fix the population and the time window before naming any metric.

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?

How did you handle missing data, anomalies, or poor data quality in your previous professional projects?

Medium

Approach

Say what you would check first and why it is the highest-information step.
Work from the decision backwards to the evidence you would need.
Clarify what is being asked and what a complete answer would contain.

Follow-up

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

Diagnose a sample ratio mismatch in an order-level rollout

Easy · srm · randomisation · diagnostics

A 50/50 order-level randomisation of a candidate routing algorithm has run twelve trading days. parent_order holds 48,112 rows carrying the legacy algo_name and 46,020 carrying the candidate. The candidate arm's implementation shortfall looks 2.1 bps better. State whether that 2.1 bps is usable, show the arithmetic that decides it, and name the two mechanisms in this schema most likely to produce the imbalance: one in how the arm label is written, one in what happens to orders the candidate refuses.

Approach

Test the split before reading the metric. With N = 94,132 and expected 47,066 per arm, the goodness-of-fit statistic is 2 * (O - E)^2 / E on one degree of freedom. A p-value this small is not a bad coin, it is a broken assignment path.
Establish what algo_name actually records. If it is the algorithm that executed rather than the one assigned at arrival_ts, then a mid-flight fallback overwrites the label and there is no randomised comparison in the table at all. The fix is a separate column persisted at randomisation time and an intent-to-treat analysis keyed on it.
Look for post-assignment rejection and re-release. If the candidate declines orders it cannot handle, for instance side = 'sell_short' on names whose borrow_status is 'hard_to_borrow', or order_type = 'close_auction', and those orders are re-sent under a new order_id, the hardest orders accumulate in control and the candidate wins for a reason unrelated to routing.
Localise the deficit by cross-tabulating arm against side, order_type, asset_class, adv_20d quintile and borrow_status. A deficit spread evenly across every cell points upstream to logging; a deficit concentrated in one cell names the gate that is rejecting orders.
Report the comparison as uninterpretable and restart after the assignment record is fixed. Do not reweight the arms back to balance: the missing orders are missing non-randomly, and reweighting assumes exactly the ignorability that the mismatch disproves.

Worked solution (20 min)

Total N = 94,132, expected 47,066 per arm, observed deviation 1,046.
Compute the statistic: 2 * 1046^2 / 47066 = 46.5 on one degree of freedom, and convert to a p-value.
Express the split as percentages so the size of the problem is legible without a test statistic.
Cross-tabulate arm against side, order_type, borrow_status and adv_20d quintile, and find which cells carry the 1,046.
Compare the count of status = 'rejected' and the distribution of cancel_reason between arms; a candidate arm with fewer rejections than control is the fingerprint of re-release.

Expected result: Chi-square 46.5 on one degree of freedom, p of order 1e-11, a 51.11 / 48.89 split. The verdict is 'unusable', not 'usable with a caveat'. If the missing orders concentrate in sell_short rows on hard-to-borrow names, the mechanism is post-assignment rejection and re-release, and the 2.1 bps is the candidate arm being handed an easier book.

Verify it yourself

Under a fair split the standard deviation of one arm's count is sqrt(N/4) = 153.4, so a 1,046 deviation is 6.8 standard deviations; 6.8 squared must reproduce the chi-square of 46.5.
The two arm counts must reconcile to the number of parent_order rows created in the window. If they do not, orders are being duplicated or relabelled rather than merely misassigned.
A ratio near 1:1 inside every pre-registered slice means the cause sits upstream of assignment; a ratio far from 1:1 in one slice identifies the gate.

Follow-up

How large an imbalance would you still act on at this sample size, and what is the power of the ratio test itself?
If the assignment log is unrecoverable, is a matched within-instrument comparison an acceptable substitute, and what does it now assume?
Which slices would you have pre-registered, and how do you keep the slicing from becoming its own multiple-comparisons problem?

Every algo improved yet blended shortfall got worse

Medium · simpsons paradox · tca · mix shift · attribution

Notional-weighted implementation shortfall by algo_name improved month over month for all five algos, the best by 3.1 bps and the worst by 0.4 bps, while the blended desk number rose from 21.0 to 25.2 bps. Tables: parent_order (order_id, order_qty, filled_qty, arrival_mid_px, avg_fill_px, adv_20d, algo_name, broker_code, order_type) and execution_fill. Produce the arithmetic that reconciles the per-algo improvements to the blended deterioration, name what actually changed, and state what you would and would not recommend to the head of trading.

Approach

Write the decomposition rather than arguing about it. With w_i the share of month notional in algo i and x_i its shortfall, the blended change is the sum over i of w1_i times (x1_i minus x0_i), plus the sum over i of x0_i times (w1_i minus w0_i). The first term is the within-algo effect, the second is mix, and the two sum to the blended change exactly, with no residual term to argue about.
Evaluate both terms. A negative within-effect and a larger positive mix effect is the whole answer arithmetically, and the real question becomes why the weights moved.
Do not stop at the weights. Re-run the same decomposition inside fixed participation buckets (order_qty divided by adv_20d) and inside volatility buckets, to test whether algo_name is standing in for order difficulty. If every algo still improves within every bucket, the per-algo improvements are real and the desk simply traded a harder month.
Separate the two stories a single mix shift can carry: the desk chose to route more notional to an expensive algo, or the incoming order flow changed (larger orders, more urgency, a newly onboarded strategy) and pushed itself into the algo built for it. Only the first is a routing decision.
Recommend on the within-bucket numbers, and say plainly that routing flow to the algo with the lowest average would move the easy orders rather than the cost.

Follow-up

The mix shift is driven by orders above 10 percent of adv_20d. Is the right response to change routing, to change how the strategy releases those orders to the desk, or neither?
How would you present this so a reader who only ever sees the blended series does not conclude the desk got worse?
What would make you suspect the per-algo improvements are themselves a selection artefact?

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.

Rigour against a deadline

Nearly every data role forces a trade between the analysis you want and the one that fits the decision window. Prepare a case where you deliberately shipped something less rigorous, named the weakness to the person relying on it, and said what would change your answer. The naming is the part interviewers listen for.

Describe a time you had to explain a highly complex model to a non-technical stakeholder or business trader. How did you structure the conversation?

Medium · behavioural and stakeholder questions

Approach

Name the disagreement or constraint, and how you resolved it with evidence.
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.

Follow-up

What would you do differently if you ran that project again?
How did you know the outcome was caused by your change?

How do you handle seasonality and trend decomposition in high-frequency time series data?

Medium · behavioural and stakeholder questions

Approach

State the situation in two sentences and spend the rest on your reasoning.
Quantify the outcome, including what you would not claim credit for.
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?

Disagreeing with a product manager over an account leaderboard

Medium · stakeholder disagreement · attribution · dispersion

A product manager wants to ship a client portal widget ranking every separately managed account against its peers in the same strategy composite, by trailing 12-month net return. You believe the ranking will mostly order accounts by mandate mechanics rather than by anything a client can act on. You have position_daily, account_mandate (SCD2) and benchmark returns for 140 accounts in the composite. Build the case and bring a counter-proposal you would ship. The product manager has a launch date and a client asking for exactly this.

Approach

What is probed: whether you can lose the feature and keep the working relationship, meaning your disagreement arrives with a shippable alternative rather than as a veto.
Measure the dispersion before arguing about it. Compute the cross-sectional standard deviation of trailing 12-month net return across the 140 accounts. If it is small, the product manager is right and you are not, and you want to know that before the meeting rather than during it.
Decompose the dispersion into causes you can name from the tables: time spent ramping between funded_date and the date gross exposure reached 90 percent of target_gross_exposure_pct, average cash weight over the period, restricted names via position_daily.is_restricted, single-name cap differences across account_mandate SCD2 versions, and fee schedule including whether a performance fee crystallised above the high-water mark. Report the share each explains and the residual.
Convert the finding into the client's decision, because that is what moves a product manager. If most dispersion is mandate mechanics, the leaderboard tells a client to change managers when the honest action is to relax a constraint or fund fully. A wrong action is an argument; a noisy statistic is a preference.
Bring the alternative that keeps the launch date: the same widget, showing the account's return against its own benchmark and its own constraint set, with a named driver line such as your restricted list cost 34 bps, instead of a rank. It answers what the client actually asked and it survives a phone call.
Pre-commit to being wrong. If the residual dominates the decomposition, the leaderboard is measuring something real, and saying so in the same memo is what makes the rest of it credible next time.

Follow-up

Dispersion is 180 bps and mandate mechanics explain 40 percent of it. What do you ship?
The client asked for a rank by name. Do they get one, and what do you put next to it?
How do you keep this from becoming a standing veto on anything this product manager proposes?