Interview guides · Intercontinental Exchange

Intercontinental Exchange Data Scientist Interview Questions & Guide 2026

Case practice for Intercontinental Exchange data scientist candidates: scope a vague prompt into a measurable question before you analyze it.

The 60-second brief

As a Data Scientist at Intercontinental Exchange (ICE), you sit at the intersection of global financial markets and advanced computational science. You are responsible for transforming massive, high-velocity datasets into actionable intelligence that powers the world’s leading network of exchanges, clearing houses, and mortgage technology platforms. Your work directly influences how transparently and efficiently global markets operate, from pricing complex derivatives to optimizing the technological infrastructure that supports trillions of dollars in transactions.

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

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

Overview

This role is not merely about building models; it is about solving high-stakes problems with real-world financial consequences. You will collaborate with cross-functional teams of quantitative researchers, financial engineers, and software developers to build robust, scalable solutions. Whether you are analyzing bond markets, developing predictive models for equity performance, or refining clearing algorithms, your contributions are central to Intercontinental Exchange’s mission of providing market participants with the data and technology they need to navigate modern finance.

The role often carries titles such as Quantitative Analyst or Quantitative Researcher, reflecting the heavy emphasis on mathematical rigor and financial domain expertise required at ICE.

The Intercontinental Exchange interview process

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

Initial Screening

Whoever runs this call is usually not a practitioner. They take notes, and a hiring manager skims those notes later, so the real question is whether your work survives being written down by someone outside the field. Test every project sentence against that: could a non-specialist repeat it correctly without knowing what a propensity score is? Carry a plain-language version of each project and one reason you want this particular role that you could not copy onto another application. Vagueness at this stage reads as inexperience, even when the underlying work was genuinely deep.

What to demonstrate

Whether a non-specialist can restate your projects accurately, since their paraphrase is what reaches the hiring manager
Whether your reason for wanting the role points at the work itself rather than the company's reputation
Whether your language signals the level being screened for: what you decided yourself versus what you were handed

How to prepare

Write a two-sentence, jargon-free version of each major project: the question nobody could answer, and the decision your work changed. Read it to someone outside data and have them repeat it back
Point your 'why this role' answer at something concrete in the job description or the product surface you would be working on, and keep it to two sentences
Have two questions ready about measurement: which metric the team is held to, and who acts on an analysis once it lands

Technical Deep-Dive

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

Team Interaction

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.

Final Onsite Assessment

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

Filtering on as_of_date rather than knowledge_ts, so restated fundamentals, revised index constituents and retroactively applied split and dividend adjustments enter the backtest before they were knowable.

Vendors overwrite history in place. A quarterly figure filed 45 days after period end is stored against period end, an index addition announced five business days before it takes effect is stored against the effective date, and a split applied tonight rewrites every prior close in the adjusted series. Each of those gives the strategy information it could not have had, and the resulting lift is concentrated in the highest-turnover, highest-apparent-alpha names. The signal_score table separates the two timestamps precisely so this filter can be written correctly.

Reporting the best backtest out of many trials as if it were a single pre-registered test.

The maximum of N noisy Sharpe estimates grows roughly like the standard error times sqrt(2 ln N) even when every underlying strategy has zero edge, so with a few hundred variants an in-sample Sharpe near 1 is the expected result of pure noise. Worse, the search is rarely counted honestly: parameter sweeps, universe changes, date-range choices and feature variants all count as trials. Quote the number of configurations tried, deflate the Sharpe for it, and keep a genuinely untouched holdout period. Note also that the asymptotic standard error of a Sharpe estimate is approximately sqrt((1 + SR^2/2)/T) for i.i.d. normal returns, which for three years of daily data is roughly 0.33, so two strategies differing by 0.3 in Sharpe are not distinguishable.

Analysing at a different unit than the one randomised

Say out loud what was randomised (user, device, account, cluster) and make the analysis unit match, or account for the clustering with cluster-robust standard errors, the delta method, or aggregation up to the randomised unit. Randomising users and then running a test over sessions understates variance and inflates the false-positive rate.

Crediting a treatment for regression to the mean

Selecting a group because it is extreme (lowest-engagement users, accounts having their worst month, the bottom decile of a score) moves that group's expected next-period value back toward the average even under no treatment, by exactly as much as the selecting measure is imperfectly correlated with its own later value. Compare against units that met the same selection rule and went untreated, or use two pre-periods so the bounce-back is visible before the intervention starts. A pre-post number on a group chosen for being extreme measures the selection rule, not the treatment.

Questions & practice

Solve this logic puzzle: [Common brainteasers focusing on probability and combinatorics].

Medium · statistics and probability

Approach

Write down the assumption the method needs before you use the method.
Translate the result into the decision it informs, in one plain sentence.
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 would you optimize a memory-intensive algorithm for processing large-scale financial logs?

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.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

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

Walk me through the implementation of a linear regression model from scratch.

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Say how the offline result would be validated online before it is trusted.
Check what information would not exist at prediction time, and exclude it.

Follow-up

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

Build a point-in-time as-of join without merge_asof

Medium · pandas · point-in-time · vectorization

Two frames. sig: instrument_id (int64), knowledge_ts (tz-aware UTC, irregular, several rows per instrument per day), zscore_xs, about 7.5M rows across 5,000 instruments. bars: instrument_id, bar_close_ts (tz-aware UTC, one row per instrument per trading day), fwd_ret_5d, about 7.5M rows. For each bar row attach the most recent zscore_xs whose knowledge_ts is at or before that instrument's bar_close_ts, or NaN when the newest such score is more than 10 calendar days old. You may not use pd.merge_asof.

Approach

Concatenate both frames with a source flag, sort by (instrument_id, ts, is_bar) so signal rows precede bar rows at an identical timestamp, then groupby('instrument_id')[[...]].ffill() and keep the bar rows. One sort and one forward fill, with no per-group Python.
Carry the signal's knowledge_ts through the forward fill as its own column. It is the only way to apply the 10-day staleness cap afterwards, and it is the audit trail showing which score each bar actually used.
The alternative is two searchsorteds. Factorize instrument_id to dense codes, compress both timestamp arrays to a shared integer rank, form key = code*(n_unique_ts+1) + rank so lexicographic order becomes numeric order, then np.searchsorted(sig_key, bar_key, side='right') - 1. Validate that the found row belongs to the same instrument, or the last row of one instrument is silently attached to the first bar of the next.
Normalize timezones before sorting. A tz-naive column compared against a tz-aware one raises in current pandas and compared wrongly in older ones; convert both with dt.tz_convert('UTC') and assert the dtype rather than trusting the loader.
Apply the cap as (bar_close_ts - carried_knowledge_ts) > Timedelta('10D') and set those to NaN. Report the NaN share by month: a rising share is usually a vendor outage or a delisted name still present in the bar file.

Worked solution (30 min)

Rename sig.knowledge_ts and bars.bar_close_ts to a common 'ts', add is_bar as 0/1, keep the original signal timestamp in its own column, concat with ignore_index=True.
combined = combined.sort_values(['instrument_id','ts','is_bar'], kind='mergesort') so equal keys preserve input order.
combined[['z_ff','kts_ff']] = combined.groupby('instrument_id', sort=False)[['zscore_xs','sig_ts']].ffill()
out = combined[combined.is_bar == 1]; set z_ff to NaN where out.ts - out.kts_ff exceeds Timedelta('10D').
Spot-check 200 randomly chosen bar rows against a brute-force lookup over the raw sig frame.

Expected result: Exactly one output row per input bar row, no duplication, with z_ff populated wherever a score exists within 10 calendar days of that bar. len(out) equals len(bars).

Verify it yourself

len(out) == len(bars), and out[['instrument_id','ts']] matches bars row for row once both are sorted; a changed row count means the concat introduced or dropped keys.
For 200 random bar rows, sig[(sig.instrument_id == i) & (sig.knowledge_ts <= t)].iloc[-1] must equal z_ff.
kts_ff <= ts must hold everywhere; a row where it does not is a sort-order bug, not a data bug.
Raising the cap to 10 years must make the NaN count equal the number of bars with no prior score at all.

Follow-up

The signal file contains rows with is_backfilled = TRUE, written after as_of_date by a rerun. Does your join still produce a point-in-time panel, and what would you change?
How would you extend this to a forward-looking join, the first score strictly after the bar, and where in a research pipeline is that the correct thing to want?
What changes when bar_close_ts and knowledge_ts can be exactly equal because the signal is computed off that same close?

Given a dataset, write a SQL query to identify specific trends in trade volume over a rolling 30-day window.

Medium · sql and data manipulation

Approach

Check whether any join is one-to-many before aggregating, or the sums inflate.
State the window function and its partition and ordering out loud before writing it.
Say which table is the grain you start from, and join outward from 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?

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.

How do you prioritize your work when facing multiple competing deadlines from different business units?

Medium · product sense and metrics

Approach

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.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

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

How confident are you in your ability to write production-level code in Python, R, or C++?

Medium

Approach

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

Follow-up

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

Choose a randomisation unit when treated orders move the price

Medium · interference · cluster randomisation · switchback

You are testing a participation-rate cap that slows large parent orders. The desk works about 3.2 parent orders per instrument per day, and two orders in the same instrument on the same day consume the same displayed liquidity. Three designs are proposed: randomise per parent order, randomise per instrument-day cluster, or switch the whole desk between arms in thirty-minute buckets. Choose one, justify it from the interference structure rather than from convenience, and quantify what your choice costs in required sample size relative to per-order randomisation.

Approach

State the interference concretely. Under per-order randomisation a treated order that trades more slowly leaves displayed liquidity behind for a control order in the same name, so the control arm's shortfall improves because of the treatment. The measured contrast is biased toward zero, and because the bias scales with the treated fraction rather than with noise, running the test longer does not remove it.
Keep per-order randomisation only for changes with no market footprint, such as logging, post-trade reporting or an internal parameter that cannot alter the order's interaction with the book. Anything that changes when or how much you trade fails the no-interference assumption.
Price the cluster design. With intra-cluster correlation rho of shortfall within an instrument-day and mean cluster size m, the design effect is 1 + (m - 1) * rho, and the order count from a per-order power calculation must be multiplied by it. Inference has to use cluster-robust or cluster-bootstrap standard errors keyed on instrument_id by business_date, not on order_id.
Price the switchback. It removes cross-order interference outright because the whole desk is one arm at any instant, but the effective sample size collapses to the number of buckets rather than the number of orders, and buckets are serially correlated through inventory carried across boundaries and through the intraday volatility profile. It needs assignment balanced within each day so time-of-day is not confounded with arm, a burn-in that discards orders straddling a boundary, and inference at the bucket level.
Recommend cluster randomisation here, because the interference is contemporaneous and confined within a name. Reserve the switchback for a change that consumes a genuinely shared pool, such as internal crossing inventory, a locate pool or a broker's balance sheet, where isolating by instrument isolates nothing.

Worked solution (30 min)

Estimate rho from history by fitting a one-way random-effects model of per-order shortfall grouped on instrument_id by business_date, or by the equivalent ANOVA variance decomposition.
Compute the design effect 1 + (3.2 - 1) * 0.25 and multiply the per-order sample size from the fixed-horizon power calculation.
Count the switchback's units: thirteen half-hour buckets over a regular session times sixty trading days gives 780 buckets, 390 per arm, and compute the bucket-level standard deviation of shortfall.
Express the switchback's minimum detectable effect in bucket standard deviations as 2.8016 * sqrt(2 / 390), then state that serial correlation between buckets inflates it further.
Compare required calendar time across the two viable designs at the same detectable effect, and write the standard-error estimator into the analysis plan before the test starts.

Expected result: At rho = 0.25 and m = 3.2 the design effect is 1.55, so 8,550 orders per arm becomes about 13,250 per arm, a 55 percent increase, and standard errors rise by sqrt(1.55), about 25 percent. The switchback over 780 buckets resolves roughly 0.20 of the bucket-level standard deviation before any serial-correlation penalty, which is usually the binding constraint. Cluster randomisation on instrument-day is the recommendation.

Verify it yourself

The design effect must be at least 1, and equals 1 only when rho is 0. A negative rho estimate means the variance decomposition is absorbing a covariate you should be stratifying on instead.
Cluster-robust standard errors must exceed the naive ones by roughly sqrt(design effect). If they do not, the clustering key is wrong or the clusters are singletons.
Reliability of cluster-robust inference is driven by the number of clusters, not the number of orders. Below roughly forty clusters per arm, switch to a wild cluster bootstrap and say so.

Follow-up

How would you estimate rho before running the test rather than discovering it afterwards?
What spillover survives the cluster design: same sector, same risk factor, the same broker's book on the same day?
If the desk insists on per-order randomisation, can you bound the interference bias, and which direction does it push the decision?

TCA traded notional fell while book turnover held

Medium · pipeline failure · timezone · segment cuts · reconciliation

The daily TCA report's traded notional dropped about 12 percent starting on a Tuesday, while turnover computed from position_daily is unchanged. No orders were rejected and parent_order.status shows the usual filled share. You have parent_order (order_id, decision_ts, arrival_ts, terminal_ts, order_qty, filled_qty, broker_code, algo_name) and execution_fill (fill_id, order_id, exec_ts TIMESTAMPTZ(6), received_ts, venue_mic, fill_qty, fill_px, is_correction). Find the missing notional and name the mechanism. Give the ordered checklist, including the cut you would make first and the cut you would deliberately not make first.

Approach

Reconcile at the order level first. parent_order.filled_qty is maintained by the order management system independently of the fill table, so orders where filled_qty exceeds SUM(fill_qty) over their non-correction fills enumerate exactly which fills the report lost. That turns a 12 percent aggregate into a concrete row set you can cut.
Cut those rows by exec_ts hour in UTC and by venue_mic before cutting by broker_code. The hour cut tests a time-boundary mechanism. The broker cut will also light up, because whichever broker routes the affected region's flow looks responsible, and that correlation is what sends people down the wrong path.
Read the report's date predicate. exec_ts is TIMESTAMPTZ(6) in UTC; if the job buckets fills by a local calendar date while bounding parent orders by a different zone's date, every fill that crosses midnight in the mismatched zone falls outside the join window and vanishes without raising an error.
Establish whether rows were dropped or misfiled. Sum the report over two days instead of one. If the two-day total reconciles, the rows landed in the adjacent bucket; if it does not, they are gone. These look identical in a daily total and demand different fixes.
Date the change against deploys and configuration rather than against market events, and re-run the prior week's data through the current job to confirm the code moved and the data did not.

Follow-up

The rows turn out to be misfiled rather than dropped. Does that change your severity assessment, and what does it do to the month-to-date figure?
Write the predicate you would use so the same report is correct for venues in every timezone, including one whose session spans the UTC date boundary.
received_ts and exec_ts differ by up to several seconds. Which belongs in the bucketing predicate and which belongs in latency monitoring?

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.

Stories where someone disagreed with you

Have two ready. In one, the data was on your side and you had to move someone who outranked you. In the other, the pushback was correct and you changed position. The second is the harder story and it lands better, because it shows you separate being right from being attached to an answer. Name the person's actual objection.

Describe a challenging project where you had to influence stakeholders using data-driven insights.

Medium · behavioural and stakeholder questions

Approach

State the situation in two sentences and spend the rest on your reasoning.
Pick a story where you drove the decision, not one where you observed it.
Quantify the outcome, including what you would not claim credit for.

Follow-up

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

How do you handle missing or noisy data in a high-frequency trading environment?

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.
Close with what you would do differently, concretely.

Follow-up

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

Ranking three quarters of requests into one quarter of capacity

Hard · prioritisation · expected value · stakeholder management

You are the only data scientist supporting three groups for one quarter. The execution desk wants the market-impact curve recalibrated; the last fit is fourteen months old and predates a volatility regime change. A portfolio manager wants a new signal researched. The client team wants a Brinson attribution that reconciles to reported active return, because it currently leaves an unexplained residual of roughly 40 bps a year. Each group believes theirs is first. Produce a ranked plan, the decision rule you used, and what you tell the two groups who do not go first.

Approach

What is probed: whether you can price work in the firm's units rather than in the requester's urgency, and whether the rule you used survives being stated out loud to the people it ranks last.
Convert each request into expected basis points of net active return per year with an explicit range, then divide by weeks of your time. The impact recalibration applies to every order: at 150 percent annualized one-way turnover the book trades roughly three times average gross per year counting both sides, so a 2 bps shortfall improvement is about 6 bps of gross annually. Small, high confidence, and applies whether or not any research succeeds.
Price the signal as an expected value rather than a hoped-for one. Most researched signals do not survive deflation for the number of configurations tried, since the maximum of many noisy Sharpe estimates grows roughly like the standard error times the square root of twice the natural log of the number of trials even with no true edge. A plausible 0.2 information-ratio contribution at a one-in-five survival rate is a large number heavily discounted, with a long right tail that is the reason to do it at all.
Price the attribution request by what it protects rather than by what it earns. A 40 bps unexplained residual is a number clients see, and attribution that does not reconcile is a credibility cost that surfaces later in the dollar redemption rate. Defensive work can rank first without generating a single basis point of alpha.
Rank, then sequence for parallelism. Put the short, high-confidence item first if it unblocks somebody else's work, and schedule the long-tailed research where a failure is cheap and discoverable early. State the rule before the numbers, so the groups who lose can argue with the inputs rather than with your loyalties.
Give each deferred group something real: a date, the specific input that would change the ranking, and the smallest useful piece you will do now, such as a one-day bisection of the 40 bps residual that tells the client team whether it is pricing, cash or trade timing.

Follow-up

The portfolio manager escalates to your manager's manager. What do you say, and what do you not say?
The quarter shortens by four weeks. Which item do you drop entirely rather than shrink?
How do you avoid becoming the person who always picks the execution desk's work because it is the easiest to quantify?