Interview guides · Balyasny Asset Management

Balyasny Asset Management Data Scientist Interview Questions & Guide 2026

Timed data scientist practice for Balyasny Asset Management: short SQL and probability sets run end to end, then scored against a rubric.

The 60-second brief

As a Data Scientist at Balyasny Asset Management, you play a vital role in bridging advanced quantitative research, data engineering, and product execution. You operate at the intersection of large-scale financial data analysis and high-performance trading infrastructure, turning complex datasets into actionable alpha signals and operational optimizations. Your work directly influences how research teams test hypotheses, evaluate market behavior, and deploy scalable data pipelines.

Product-sense cases reward reasoning from a mechanism to a testable prediction. Reciting every metric you can name reads as pattern matching; naming the single quantity that would move if your explanation were true reads as thinking.

Focus areas

Build point-in-time panels without lookahead
Attach uncertainty to every Sharpe claim
Measure shortfall against arrival, not VWAP

Overview

This position demands rigorous analytical thinking, exceptional coding discipline, and a deep appreciation for statistical validity. You will regularly collaborate with portfolio managers, quantitative researchers, and software engineers to design robust product metrics, build predictive models, and diagnose anomalies in production data feeds. Whether you are constructing predictive models for asset pricing, optimizing experimentation frameworks, or auditing complex financial datasets, your contributions directly impact the firm's competitive edge in global markets.

Expect a high-stakes, intellectually demanding environment where precision and speed are equally prized. The work requires you to defend your methodological choices under scrutiny while remaining adaptable to rapidly shifting business priorities. If you thrive on solving unstructured problems with rigorous quantitative methods, this role offers an unmatched platform for professional impact.

The Balyasny Asset Management interview process

Balyasny Asset Management candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Phone Screen

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What to demonstrate

Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
Whether you name what you have not done instead of stretching to cover every line of the posting
Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Technical Evaluation

Much of what gets scored here happens out loud while you type. Nobody can see your reasoning inside a half-written query, so five silent minutes read as being stuck even when they are not. State the plan in plain language first: which tables, what grain you are aggregating to, and the one filter that defines the population. Then write it. The narration doubles as insurance, because a wrong plan gets caught early and cheaply while a wrong query gets caught at the end with no time left to redo it. A timed statistics section, where one exists, is a separate test with its own clock.

What to demonstrate

Whether the query you write matches the plan you just described
What you do with a hint, meaning whether the correction gets absorbed or the first approach gets defended
Whether you can debug your own wrong output by reading the result set and naming which part of the query produced the anomaly

How to prepare

Solve three problems while screen-sharing into a recording, then watch it back and mark every stretch longer than thirty seconds where you said nothing
Practise compressing the plan into one sentence before typing, then check afterwards whether the finished query actually matched it
Time yourself on statistics questions that carry a business reading, such as what a confidence interval does and does not claim, rather than re-reading notes without a clock

Stakeholder Meetings

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.

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.

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.

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.

SQL that silently fans out on a one-to-many join

State the grain of each table and the grain you want in the result before writing the join. Pre-aggregate the many side to the join key, or use EXISTS or a window function, and verify with a row count against COUNT(DISTINCT id) rather than trusting that the numbers look plausible.

Questions & practice

Explain the mathematical intuition behind hypothesis testing and how you minimize Type I and Type II errors in high-noise environments.

Medium · statistics and probability

Approach

Quantify uncertainty explicitly rather than reporting a point estimate alone.
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?
How would you explain this result to someone who does not know statistics?

How do you define and track key performance indicators for model decay and signal degradation over time?

Medium · machine learning and modelling

Approach

Set a baseline first, so any model has something honest to beat.
Pick an evaluation metric that matches the cost of each error type, not a default.
Say how the offline result would be validated online before it is trusted.

Follow-up

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

Sessionise a corrected fill stream into trading bursts

Hard · sessionisation · execution · vectorization

One trading day of execution_fill: fill_id, order_id, instrument_id, exec_ts (microsecond UTC venue time), received_ts, fill_qty, fill_px, venue_mic, liquidity_flag, is_correction, corrects_fill_id. About 9.4M rows, delivered in received_ts order, with roughly 0.3% corrections including chains and zero-quantity busts. Produce one row per (order_id, burst), where a burst is a maximal run of surviving fills whose consecutive exec_ts gap is at most 90 seconds, carrying start_ts, end_ts, n_fills, qty, quantity-weighted price and the added-liquidity share. No per-order Python loop.

Approach

Resolve corrections with set arithmetic rather than iteration: any fill_id appearing in corrects_fill_id has been superseded, so drop those rows, then drop rows with fill_qty = 0 to remove busts, including a bust that supersedes a real fill. Chains need no special case, because every intermediate row is itself somebody's target.
Sort by (order_id, exec_ts, fill_id). The file arrives in received_ts order and venue timestamps are not monotone in arrival, so sorting is load-bearing rather than tidy; tie-break on fill_id because exec_ts collides at microsecond resolution on active names.
gap = df.groupby('order_id')['exec_ts'].diff(); new_burst = gap.isna() | (gap > Timedelta('90s')); burst_seq = new_burst.groupby(df.order_id).cumsum(). Two passes over an already-sorted frame, no Python-level iteration.
Aggregate in one groupby([order_id, burst_seq]): min and max of exec_ts, size, sum of fill_qty, sum of fill_qty*fill_px, and sum of fill_qty where liquidity_flag == 'added'. Derive the weighted price after aggregation as notional over quantity, never as a mean of fill_px.
Then the concentration flag: join each burst's quantity against the order's surviving total and the order's working span (last exec_ts minus first), and mark bursts holding over 40% of quantity in under 5% of the span. Those are the auction prints and the blocks, and they are the orders whose shortfall is driven by one decision rather than by the algo.

Worked solution (40 min)

superseded = set(f.corrects_fill_id.dropna().astype('int64')); f = f[~f.fill_id.isin(superseded)]; f = f[f.fill_qty > 0].
f = f.sort_values(['order_id','exec_ts','fill_id'], kind='mergesort').
Build gap, new_burst and burst_seq as above, then assert burst_seq is 1 on each order's first surviving fill (gap.isna() makes new_burst True there, so the grouped cumsum is 1-based) and increases by exactly 1 at every boundary, with no gaps in the sequence.
g = f.groupby(['order_id','burst_seq'], sort=False); build the aggregate frame, then wavg_px = notional_sum/qty_sum.
Reconcile against parent_order.filled_qty and hand-check the three orders with the most bursts.

Expected result: One row per (order_id, burst_seq). Within an order the burst quantities sum to the surviving filled quantity, bursts are contiguous and non-overlapping in time, and consecutive bursts are separated by strictly more than 90 seconds. Most orders yield one to three bursts, with a heavy tail of several dozen belonging to all-day participation algos.

Verify it yourself

groupby('order_id').qty.sum() must equal parent_order.filled_qty for every order with status = 'filled'; a shortfall is an unresolved correction and an excess is a missed supersession.
start_ts of burst k+1 minus end_ts of burst k must always exceed 90 seconds by construction; any pair that does not means the sort is wrong.
Each burst's quantity-weighted price must lie between the minimum and maximum fill_px inside it.
Surviving fill count plus dropped count must equal the input row count.

Follow-up

Two bursts on one order are separated by 91 seconds. What does a 90-second threshold do to the distribution of bursts per order, and how would you pick the threshold from the data instead of by hand?
How would you sessionise across orders instead, over all fills in one instrument in one account, and what breaks when two strategies trade the same name in opposite directions?
One venue reports exec_ts in local time rather than UTC. What would that look like in the burst output, and which check catches it?

Write a SQL query using window functions to calculate running totals and moving averages of trade executions over a sliding 30-day window.

Medium · sql and data manipulation

Approach

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

How would you identify duplicate records in a high-frequency financial transaction table with millions of rows using SQL?

Medium · sql and data manipulation

Approach

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.
State the window function and its partition and ordering out loud before writing it.

Follow-up

How would you verify this result without re-running the same query?
How does the query change if the join becomes one-to-many?

Order fill funnel by execution algorithm, in counts and notional

Easy · funnel · conditional-aggregation · cohorting

From parent_order (order_id, algo_name, order_qty, filled_qty, status, arrival_ts, arrival_mid_px), build a monthly funnel per algo_name over orders whose arrival_ts falls in the month: orders released, orders with any fill (filled_qty > 0), orders fully filled (status = 'filled'), and the two step conversion rates. Produce the same funnel in notional, using order_qty * arrival_mid_px. Orders with status = 'rejected' never reached the market — state how you treat them and implement that choice.

Approach

Use conditional aggregation in a single pass — COUNT(*) FILTER (WHERE filled_qty > 0) and friends — rather than three subqueries joined together, so every numerator and denominator provably comes from the same row set.
Make the denominator decision explicit: rejects are an order-management outcome, not an execution outcome, so exclude them from both numerator and denominator and report their count in a separate column. An exclusion you cannot see in the output is an exclusion nobody can audit.
Cast before dividing. With integer counts, filled / released floors to 0 in Postgres; filled::numeric / NULLIF(released, 0) gives the rate and also handles an algo with zero orders in the month.
Compute the notional funnel with the same FILTER clauses over SUM(order_qty * arrival_mid_px). Divergence between the count funnel and the notional funnel is the finding: 60 percent of orders completing but 20 percent of notional means the large orders are the ones stalling.
Group by date_trunc('month', arrival_ts) and algo_name, and left-join a distinct algo list if months with zero activity must still appear as rows.

Worked solution (20 min)

Write one aggregate query over the month with FILTER clauses for released, any-fill and fully-filled, plus a rejected count.
Wrap it and compute the two conversion rates with explicit numeric casts and NULLIF guards.
Duplicate the three FILTER clauses over SUM(order_qty * arrival_mid_px) to get the notional funnel.
Reconcile: released + rejected must equal the raw count of orders with arrival_ts in the month.
Scan for algos where the count rate and notional rate differ by more than 10 points and note them for follow-up.

Expected result: One row per (month, algo_name) with released, any-fill and fully-filled counts, two rates in [0, 1], the matching notional funnel, and a visible rejected count.

Verify it yourself

Any-fill count is greater than or equal to fully-filled count for every row.
Both rate columns fall in [0, 1] and are NULL rather than an error where released is zero.
Released plus rejected, summed across algos, equals the raw month order count.
An order with filled_qty > 0 and status = 'canceled' appears in any-fill but not in fully-filled.

Follow-up

filled_qty sits on the parent but the fills live in execution_fill. How would you find parents whose filled_qty disagrees with the sum of their non-correction fills, and which side would you trust?
The fully-filled rate rises after participation_rate_target is lowered. Is that an improvement, and what second metric settles it?

Walk me through how you would evaluate the success of a newly introduced quantitative research tool for portfolio managers.

Medium · product sense and metrics

Approach

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

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?

How do you manage competing priorities when multiple portfolio managers request custom data pipelines simultaneously?

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.
State what result would change your recommendation, so the answer is falsifiable.

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?

Design a framework to measure user engagement and signal generation frequency across different trading desks.

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.
State what result would change your recommendation, so the answer is falsifiable.

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 do you balance quantitative metric optimization with qualitative feedback from senior portfolio 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.
State what result would change your recommendation, so the answer is falsifiable.

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?

Walk me through how you would handle network effects and interference between treatment and control groups in an experimentation setup.

Medium · a/b testing and experimentation

Approach

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

Follow-up

What would you do if you could not randomise at all?
How would you handle interference between treated and control units?

How do you determine statistical significance and handle multiple testing corrections when evaluating dozens of simultaneous feature experiments?

Medium · a/b testing and experimentation

Approach

Name the randomisation unit first; it decides the variance and what the test can detect.
Say whether units interfere with each other, and switch design if they do.
Decide the analysis before seeing data, including how long it runs and when you look.

Follow-up

What would you do if you could not randomise at all?
How would you handle interference between treated and control units?

Walk me through your preferred approach for feature selection and dimensionality reduction when dealing with hundreds of correlated alternative data features.

Medium

Approach

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.
Work from the decision backwards to the evidence you would need.

Follow-up

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

Cut short-side financing cost without cutting the short alpha

Medium · borrow · financing · tradeoffs · guardrails

Management wants short-side financing cost down. From position_daily you have financing_cost_base_day, borrow_rate_bps, borrow_status ('general_collateral', 'hard_to_borrow', 'unavailable', 'n_a'), quantity and market_value_base per instrument per business_date; from signal_score you have decile_rank per as_of_date. Design the metric set for this project: a primary, a guardrail, and the decision rule when they disagree. The complication is that the most negative decile is disproportionately hard to borrow, so the cheapest achievable borrow book is the one that has stopped taking the trade the strategy exists to take.

Approach

Refuse total financing_cost_base_day as the primary: it is minimised perfectly by closing the short book. Denominate it instead as short-notional-weighted borrow_rate_bps, so the metric measures the price paid per unit of short exposure rather than the quantity of exposure taken.
Benchmark that rate against a broad market borrow rate. A firm-wide easing of lending supply lowers the weighted rate without anyone doing anything, and a project that books that as a win will also book the reverse as a failure.
Set the guardrail on the quantity the primary can be improved by destroying: the short book's notional-weighted mean decile_rank, joined on instrument_id and business_date equal to as_of_date. If the rate falls while that mean drifts up toward the universe midpoint, the saving came from abandoning the trade.
Convert the conflict into one comparison rather than two metrics fighting. Hold a short only where expected annualized alpha exceeds borrow_rate_bps plus impact amortised over the expected holding period. That rule keeps expensive bottom-decile names whose alpha covers the borrow and drops mid-decile names whose alpha does not, which is the opposite of what a raw cost target produces.
Separate in reporting the wins that do not touch the book: rate shopping across lenders for the same instrument on the same day, early warning from borrow_status transitions into hard_to_borrow, and term borrow for names held long enough to justify it. Without that split the project will claim credit for alpha it destroyed.

Worked solution (30 min)

From position_daily where quantity is negative, compute daily short-notional-weighted borrow_rate_bps, split by borrow_status, and aggregate monthly.
Difference that series against a broad market borrow-rate proxy so supply-side moves are not scored as project outcomes.
Join to signal_score on instrument_id and business_date equal to as_of_date, and compute the short book's notional-weighted mean decile_rank by month.
Estimate expected alpha per decile from the trailing IC and the realized cross-sectional return spread, then compare decile 1 alpha net of its borrow cost against decile 3 alpha net of its borrow cost.
Write the net-of-borrow inclusion rule and replay the last 12 months to list exactly which positions it would have removed.

Expected result: A two-line card plus a rule: primary is short-notional-weighted borrow_rate_bps against the market benchmark; guardrail is mean short-book decile_rank with a stated band such as at or below 2.5; rule is hold the short only when expected annualized alpha exceeds borrow_rate_bps plus amortised impact. The replay should show the rule keeping most expensive bottom-decile names and cutting mid-decile expensive ones.

Verify it yourself

Short-notional-weighted and unweighted mean borrow rates must differ; identical values mean the weighting is not applied or one name dominates the book.
Monthly financing_cost_base_day must reconcile to weighted rate times average short notional times days over the day-count basis. Confirm whether the desk accrues on 360 or 365 before quoting the reconciliation as passing.
Mean short-book decile_rank should already sit well below the universe midpoint. If it sits near the midpoint, the short book is not signal-driven and the guardrail is measuring nothing.

Follow-up

The weighted borrow rate falls 40 basis points and mean short-book decile_rank is unchanged. Name two explanations other than good work by this project.
How do you price a name whose borrow_status is 'unavailable' rather than merely expensive, given it never appears in the cost series at all?
A recall forces a buy-in on a bottom-decile short. Where does that cost land in your metric set, and should it?

Mean daily IC halved with no model change

Easy · ic · data quality · metric decomposition

The trailing-252-day mean rank IC for one signal_id fell from 0.031 to 0.016 over roughly six months. No model_version changed and no research code shipped. You have signal_score (signal_id, model_version, instrument_id, as_of_date, knowledge_ts, zscore_xs, universe_id, coverage_flag, is_backfilled, computed_at) joined to a daily forward-return panel keyed on instrument_id and date. Decide whether the forecast decayed or the input panel changed. Deliverable: an ordered checklist and the one query that settles it. You may not retrain the model.

Approach

Stop looking at the trailing mean and recompute IC for each as_of_date. A 252-day moving average turns a single-day step into a decline that takes 252 days to complete, so the shape you were shown is equally consistent with a sudden break and with gradual decay. The first difference of the daily series locates a break to within a day.
Count distinct instruments per as_of_date and per universe_id. A universe that silently shrinks changes the population the rank correlation runs over, which makes the new number a different estimator rather than the same one measured worse.
Recompute the daily series restricted to coverage_flag = 'computed' and overlay it on the all-rows series. If the restricted series is flat across the break, the drop lives entirely in imputed or stale scores and the forecast is intact.
Look at computed_at and the is_backfilled share around the break date. A live-job outage and a rerun that rewrote the same dates with different values produce the same IC drop and need different fixes.
Only if all three cuts come back clean should decay be entertained, and then it has to be measured as decay: IC at horizons 1 through 21 and a half-life, not one number compared to another number.

Follow-up

How many trading days of daily IC would you need to distinguish a true IC of 0.031 from 0.016 at conventional power, and what does that say about the six-month window you just used?
If stale rows carry forward a score from one fixed prior date, what happens to their measured IC as the gap widens, and what does that do to the pooled number?

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.

Tell me about a time you had to defend a complex methodological or statistical decision to a skeptical stakeholder.

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

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

Answering whether a six-week-old signal is working yet

Easy · uncertainty · executive communication · statistical power

A new signal has been live six weeks: 30 trading days of realized cross-sectional IC against a 5-day forward return, mean 0.030, standard deviation across days 0.12. An executive with no statistics background asks in a Monday meeting whether it is working and wants a yes or a no. You have the daily IC series and nothing else. Give an answer in three sentences plus one number the executive can hold onto, and say when the question becomes answerable.

Approach

What is probed: whether you can be honest about statistical power without hiding behind the word significant and without giving a yes that gets quoted back at you in three months.
Compute the interval before you speak. The standard error of the mean daily IC is 0.12 divided by the square root of 30, which is 0.022, so a mean of 0.030 sits about 1.4 standard errors from zero. That is the optimistic bound and it is already not a yes.
Adjust for overlap and say that you did. A 5-day forward return sampled every day shares four of five days with its neighbour, so the honest standard error uses a Newey-West estimator with at least 4 lags and lands materially above 0.022. Presenting the naive figure without that caveat is the same error as the signal's own author would make.
Convert power into a date rather than a verdict. Detecting a true mean IC of 0.03 at two standard errors needs roughly (2 x 0.12 / 0.03)^2 = 64 independent days, and with the overlap inflation of a 5-day horizon that is on the order of 300 trading days, so the question becomes answerable around fifteen months in, not six weeks.
Give one number and one decision, because wait is useless on its own. Offer a tripwire that makes waiting active: a pre-committed stop if the trailing 60-day mean IC turns negative, and a named review date.

Follow-up

Another desk called their signal working after four weeks. What do you say when the executive raises that?
What single observation before the review date would make you stop the signal early?
The six-week mean is minus 0.03 instead. Does your answer change in substance or only in sign?

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?