Interview guides · Intuit Management Consultancy

Intuit Management Consultancy Data Scientist Interview Questions & Guide 2026

Case practice for Intuit Management Consultancy data scientist candidates: scope a vague prompt into a measurable question before you analyze it.

The 60-second brief

As a Data Scientist at Intuit Management Consultancy, you sit at the intersection of advanced analytics, statistical rigor, and core business strategy. Your primary mission is to transform complex financial and consumer datasets into actionable insights that power high-impact products like lending platforms and demand planning systems. You will work closely with cross-functional partners across product, engineering, and finance to drive key financial health metrics and optimize user experiences at scale.

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

Focus areas

Cluster inference at the account, not the engagement
Reconstruct pipeline stage history from mutable rows
Compute utilisation against a defended availability denominator

Overview

Your impact directly influences how millions of customers manage their financial lives, making high-stakes decisions backed by rigorous quantitative evidence. Whether you are designing sophisticated experimentation frameworks, building predictive models for financial risk, or diagnosing sudden shifts in core product metrics, your work shapes the strategic roadmap of the business. The role demands an exceptional balance of technical depth and product intuition, requiring you to communicate complex statistical concepts clearly to non-technical stakeholders.

You will enter a fast-paced, data-driven environment where hypotheses are constantly tested, validated, and deployed into production. While the scope of projects can range from algorithmic credit risk assessment to full-service demand forecasting, the expectation for technical excellence remains consistently high. Expect to be challenged on your ability to scale solutions, defend your methodological choices, and tie every model back to tangible business value.

The Intuit Management Consultancy interview process

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

Recruiter Conversation

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 Screening

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

Technical and Business Case Evaluations

A case round is decided by whether you leave the interviewer with a recommendation, not by how much analysis you narrate on the way there. The prompt is open on purpose, so the first job is to convert it into a decision someone could act on: ask what would be done differently depending on the answer. From there name the quantity that would settle it, state the assumptions you need, and commit. Candidates who cover more ground than anyone expected and still end on "it depends" score below candidates who scoped narrowly and said what they would do.

What to demonstrate

Whether the version of the question you choose to answer is genuinely narrower than the prompt and still worth answering
Whether the recommendation arrives as an action with a number attached, rather than as a summary of what you looked at
Whether assumptions are stated at the moment you rely on them, instead of collected into a disclaimer at the end
Whether you notice when a branch you are exploring would not change the decision either way

How to prepare

Take six open prompts and write only the scoping move for each: the one-sentence question you would actually answer and the decision it feeds. Give yourself three minutes per prompt and stop there.
Put a five-minute warning into every practice case and force a closing statement that names the action, the result that would justify it, and the result that would reverse it.
Record one case and count how long you talked before naming a measurable quantity. Past roughly five minutes, what you are calling scoping is narration.

Virtual Onsite Loop

Where a loop ends with a senior leader, that conversation is rarely another skills test. The technical signal already exists by then, so the questions tend to open up: what you would look at first, where a metric you have heard about could mislead, what you would push back on. The decision being made is scope, which in practice means level and how much you would be trusted to own unsupervised. Treating it as a formality is the usual mistake. An open question late in the day is still being scored, and a vague answer reads as someone who has not run anything themselves.

What to demonstrate

Whether your view of the business has anything specific behind it, given that you are working only from what is public and are expected to say so
Whether the scope of work you describe owning matches the scope of the role, instead of sitting a level below it
Whether you can disagree with something concrete and stay useful about it, rather than agreeing with everything said in the room
Whether your questions are ones only this person could answer, as opposed to ones the recruiter already covered

How to prepare

Build one view you could defend for two minutes using only public information: what the funnel probably looks like, which metric likely drives decisions, and where that metric could mislead. Being wrong for a stated reason survives this round; having no view does not
Write down the largest piece of work you have owned from question to decision, who else touched it, and what you decided alone, then check that it reads at the level you are interviewing for
Prepare one thing you would want changed if you joined and phrase it as a question rather than a verdict, so it opens a conversation instead of closing one

Where candidates lose points

Computing days-to-pay or proposal cycle time over completed records only

At any snapshot date, invoices that have already been paid are disproportionately the fast ones, and proposals that already have a decision are disproportionately the quick ones. Averaging over the completed set alone biases both numbers downward, and the bias grows exactly when the business is deteriorating, because the slow cases are the ones still open. Unpaid and undecided records are right-censored; use Kaplan-Meier or a restricted mean up to a fixed horizon, and never fill paid_at with a placeholder.

Treating accounts as independent observations

Revenue is concentrated: a small number of client_ids typically carries a large share of fees, and engagements within one account share a partner, a rate card and a delivery team. Ordinary standard errors computed over engagements therefore understate uncertainty badly. Cluster at client_id, and with fewer than roughly 40 clusters use a wild cluster bootstrap or a CR2 correction, because cluster-robust standard errors are downward-biased in that regime and will manufacture significance that a replication will not reproduce.

Over-explaining the method and under-explaining the implication

Lead with the answer and what you would do about it, then give the approach when asked. Roughly one sentence of method per three of implication is the right ratio for a stakeholder-facing answer; the interviewer already knows what a regression is.

Extrapolating a first-week lift inflated by novelty effects

Plot the treatment effect by days since first exposure instead of quoting one pooled average. A lift that decays toward zero across the test window is behaviour that will not persist, and annualising it produces a forecast that misses by an order of magnitude.

Questions & practice

Walk through a probability scenario involving conditional probability and false positive rates in fraud detection.

Medium · statistics and probability

Approach

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

Follow-up

How would you explain this result to someone who does not know statistics?
What sample size would you need to detect an effect half this size?

How would you detect and handle outliers in a skewed financial dataset before fitting a regression model?

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.
Set a baseline first, so any model has something honest to beat.

Follow-up

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

Measure the timesheet backfill curve and pick a reporting cutoff

Easy · late-arriving data · data quality · cohort curves

time_entries has work_date (date), entered_at (timezone-aware UTC timestamp), hours and status. Given a snapshot_date, restrict to work_date in [snapshot_date - 180 days, snapshot_date - 60 days] so every cohort is fully observed. For k = 0..45, compute F(k): the share of a work_date cohort's final hours that already existed as of work_date + k days, pooled across cohorts. Return the 46-point curve and the smallest k with F(k) >= 0.99. Some rows are entered before the work date; those lags are real, not errors.

Approach

Compute lag = (entered_at converted to the reporting timezone and taken as a date) - work_date in whole days, then clip negative lags to 0 instead of dropping them; leave and planned time are routinely entered ahead of the work date and dropping them deflates the early curve.
Take cohort totals as groupby(work_date).hours.sum() over the restricted window. These are final only because the window stops 60 days short of the snapshot, which is why the restriction is in the prompt.
Build the numerator by summing hours per (work_date, lag), sorting by lag, taking a per-cohort cumsum, then reindexing each cohort onto the full 0..45 lag grid and forward-filling, so a cohort with no entries at a given lag holds its previous level rather than disappearing.
Pool as sum(numerators) / sum(denominators) at each k, not as the mean of per-cohort shares. Holiday weeks are tiny cohorts and would otherwise carry the same weight as a full week.
Read k* off the pooled curve and report F(45) with it: if F(45) is below about 0.995 the tail runs past the grid and k* is a lower bound, not the answer.

Worked solution (25 min)

Restrict rows to the [snapshot - 180d, snapshot - 60d] window and compute lag_days = (entered_at.dt.tz_convert(tz).dt.normalize().dt.date - work_date).dt.days, then lag_days = lag_days.clip(lower=0).
cohort_total = df.groupby('work_date').hours.sum(); by_lag = df.groupby(['work_date','lag_days']).hours.sum().
Reindex by_lag onto MultiIndex.from_product([cohorts, range(0,46)]), fill 0, cumsum within work_date to get hours_by_k.
F = hours_by_k.groupby(level='lag_days').sum() / cohort_total[cohorts_in_grid].sum(); assert F is non-decreasing.
k_star = int(F[F >= 0.99].index.min()) if any, else report 'not reached within 45 days' along with F(45).

Expected result: A monotone non-decreasing 46-point series F(0)..F(45) bounded by 1.0, plus an integer k* (or an explicit 'not reached by day 45') reported together with the value of F(45).

Verify it yourself

F is non-decreasing in k and F(45) <= 1.0, with equality only if no row in the window has a lag above 45 days.
The pooled curve lies between the minimum and maximum per-cohort curve at every k, since it is a weighted average of them.
One cohort recomputed by hand from raw rows matches the vectorised result.
Removing the clip and dropping negative lags lowers F(0); the difference equals the hours entered ahead of their work date.

Follow-up

The dashboard refreshes daily. Would you hold the window back past k*, or publish an as-of-entered_at series instead, and what does each choice cost the reader?
One practice area has a tail twice as long as the rest. Does that change the firm-wide cutoff, or does it change what you publish per practice area?

Write a SQL query using window functions to calculate the running 30-day moving average of transaction volumes per user.

Medium · sql and data manipulation

Approach

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

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

How would you approach a case problem where you must clean, restructure, and aggregate raw transactional data entirely in SQL before building a downstream model?

Medium · sql and data manipulation

Approach

Check whether any join is one-to-many before aggregating, or the sums inflate.
Compute rates by summing numerator and denominator separately, never by averaging rates.
Say which table is the grain you start from, and join outward from it.

Follow-up

What breaks if events arrive late or out of order?
How does the query change if the join becomes one-to-many?

Given a sparse user activity table, how would you write a query to identify consecutive days of active usage?

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
Check whether any join is one-to-many before aggregating, or the sums inflate.
Say which table is the grain you start from, and join outward from it.

Follow-up

What breaks if events arrive late or out of order?
How does the query change if the join becomes one-to-many?

Find signed engagements with no approved billable hour

Medium · anti-join · null semantics · not exists

fct_engagement holds engagement_id, start_date and status. fct_time_entry holds time_entry_id, consultant_id, engagement_id, charge_code, work_date, hours, is_billable and status, and engagement_id is NULL on bench, leave, training and internal rows. Return engagements with start_date in the trailing quarter and status IN ('active','closed_delivered','closed_early') that have no approved billable time entry with work_date within 45 days of start_date, giving engagement_id, start_date and days elapsed. Then explain why NOT IN over a subquery selecting fct_time_entry.engagement_id is the wrong instrument here.

Approach

Express the exclusion as NOT EXISTS with a correlated predicate covering all four conditions at once: te.engagement_id = e.engagement_id AND te.is_billable AND te.status = 'approved' AND te.work_date < e.start_date + INTERVAL '45 days'. NOT IN can only carry a single column, so a multi-condition anti-join forces a contorted subquery even before the NULL problem.
State the NULL semantics precisely: NOT IN (SELECT engagement_id FROM fct_time_entry) evaluates to NULL rather than TRUE for every candidate as soon as one NULL is present in the list, because x <> NULL is unknown. The WHERE clause keeps only TRUE, so the query returns zero rows and reads as a clean bill of health. This table guarantees NULLs, since bench and leave rows carry no engagement.
Note the equivalent LEFT JOIN ... WHERE te.engagement_id IS NULL form. It is correct, but it materialises a join only to discard most of it; NOT EXISTS states the intent and most planners execute both as the same anti-join.
Guard the window against truncation: an engagement that started 10 days ago cannot yet have failed a 45-day test, so require start_date <= current_date - 45 or report the too-early set separately rather than counting them as failures.
Decide on the late-timesheet risk explicitly. A missing entry may mean unstaffed work or an unsubmitted timesheet, so either hold the cutoff back past the measured submission lag or label the recent tail as provisional.

Worked solution (20 min)

Count engagements matching the status and start_date predicates; this is the denominator.
Write the NOT EXISTS version and record its row count.
Write the NOT IN version on the same data and observe zero rows, then confirm with SELECT COUNT(*) FROM fct_time_entry WHERE engagement_id IS NULL that NULLs are present.
Cross-check by counting engagements that DO have a qualifying entry and verifying the two counts sum to the denominator.
Open one engagement from the result and look at its raw time entries to see whether the cause is no staffing or unapproved timesheets.

Expected result: A short list of engagement_id, start_date and days_since_start for engagements that started at least 45 days ago and carry no approved billable hour in their first 45 days. The NOT IN variant returns zero rows on the same data.

Verify it yourself

Count of flagged engagements plus count of engagements with at least one qualifying entry equals the count of engagements matching the status and date filters.
Every returned engagement has start_date at or before current_date - 45.
Spot-checking one flagged engagement in fct_time_entry shows either no rows or only unapproved or non-billable rows.
Replacing NOT EXISTS with LEFT JOIN ... IS NULL returns an identical set.

Follow-up

Turn this into a time-to-first-billable-hour distribution. What do you do with engagements that never recorded one?
Should client_pursuit hours count as the engagement being staffed, and how does that choice change the list?
How would you prove the empty result from the NOT IN version is a bug rather than good news?

How would you design the primary success and guardrail metrics for a new automated lending feature?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.
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?
Which segment would you cut first, and what would that rule out?

How would you define engagement metrics for a financial management tool used intermittently by consumers?

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
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

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

What framework would you use to evaluate the long-term customer lifetime value impact of a redesigned onboarding flow?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.
Fix the population and the time window before naming any metric.

Follow-up

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

Explain the mathematical intuition behind statistical significance and how you prevent false positives when running multiple concurrent tests.

Medium · a/b testing and experimentation

Approach

Name the guardrails that would stop a launch even on a positive primary result.
Say whether units interfere with each other, and switch design if they do.
State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

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

You notice an unexpected drop in conversion rate two days into a major product experiment; walk through your step-by-step diagnosis.

Medium · a/b testing and experimentation

Approach

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

Follow-up

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

Build the driver tree beneath gross margin per engagement-week

Medium · metric tree · decomposition · margin · pricing models

Gross margin per delivered engagement-week is the north-star. Using fct_invoice_line (engagement_id, line_type, amount_usd, period_start_date, period_end_date) and fct_time_entry (engagement_id, work_date, hours, is_billable, status, bill_rate_usd, cost_rate_usd), decompose it into drivers a delivery lead can act on, such that the drivers recombine to the parent metric exactly rather than approximately. Show the algebra. Then say which driver a staffing decision moves, which a pricing decision moves, and which a scope-control decision moves.

Approach

Start from the identity margin = 1 - cost / fees, then introduce standard value SV = SUM(hours x bill_rate_usd) over approved billable entries. That gives cost / fees = (cost / SV) / (fees / SV), and fees / SV is realisation rate.
Split cost / SV into two readable ratios. With H_total as all approved hours on the engagement-week and H_bill as approved billable hours, c as the hours-weighted mean cost rate over H_total and b as the billable-hours-weighted mean bill rate, cost / SV = (H_total / H_bill) x (c / b). So margin = 1 - (H_total / H_bill) x (c / b) / realisation, exact by construction.
Verify exactness numerically before presenting it. Recompute margin from the three drivers on a real engagement-week and diff it against the direct (fees - cost) / fees. A tree that only holds approximately cannot attribute a share of a change to any driver.
Map each driver to a decision. Staffing mix moves c / b, because seniority moves cost and bill rates together but not at the same ratio. Rate card and discount policy move realisation and b. Scope control and rework move H_total / H_bill, since every non-billable delivery hour lands in the numerator only.
State the scope limit: the tree is interpretable only within a pricing_model. On fixed_fee, weekly fees come from milestone recognition and do not respond to hours at all, so realisation absorbs the entire hour overrun and c / b carries no information about whether the work is on budget.

Worked solution (30 min)

Pick one active time_and_materials engagement and one week. Compute weekly fees from fct_invoice_line where the billing period covers the week and line_type IN ('fees','milestone').
Compute cost = SUM(hours x cost_rate_usd) over all approved entries in that week, billable and non-billable alike, and SV = SUM(hours x bill_rate_usd) over approved billable entries only.
Compute the three drivers and recombine: 1 - (H_total / H_bill) x (c / b) / realisation.
Diff the recombined value against (fees - cost) / fees.
Repeat on a fixed_fee engagement and record which driver absorbs an hour of overrun.

Expected result: margin = 1 - (H_total / H_bill) x (c / b) / realisation, where realisation = fees / SV. Recombining the three drivers reproduces the directly computed margin to floating-point tolerance on every engagement-week tested. Staffing moves c / b, pricing moves realisation and b, scope control moves H_total / H_bill.

Verify it yourself

Recombined and direct margin agree to within rounding, including on a week with zero billable hours, which must be reported as undefined rather than silently zero.
On uncapped time_and_materials an extra delivery hour raises both cost and SV; on fixed_fee it raises cost only, and the whole effect lands in realisation.
H_total / H_bill is at least 1.0 by construction. A value below 1.0 means non-billable hours were dropped from the cost side.

Follow-up

H_total / H_bill moved from 1.08 to 1.40 on one engagement. Name three causes and the column that separates them.
How does this tree behave on a retainer, where contracted_hours is NULL and fees are flat regardless of hours worked?

Two-year-old engagement margins moved after a dimension join change

Hard · type-2 scd · restatement · effective dating

After a warehouse release, gross margin on engagements closed two years ago changed by up to six points, upward on some and downward on others. The cost model now joins dim_consultant on consultant_id AND is_current = TRUE to read standard_cost_rate_usd, instead of using the cost_rate_usd already snapshotted on each fct_time_entry row. Available columns include consultant_sk, consultant_id, level, standard_cost_rate_usd, effective_from, effective_to, is_current, termination_date. Explain the restatement, quantify who it favours, and specify the join you would ship.

Approach

State the mechanism in one line: joining a type-2 dimension on is_current pastes today's cost rate onto historical hours, so every consultant promoted or re-rated since restates their own history in the direction their rate moved. Because promotions differ per person, engagement margins move in both directions, which is exactly the observed pattern.
Quantify with three parallel cost computations per engagement: (a) the snapshotted fct_time_entry.cost_rate_usd, (b) an effective-dated join using work_date BETWEEN effective_from AND COALESCE(effective_to, 'infinity'), and (c) the shipped is_current join. Report the distribution of (c) minus (a) and correlate it with the share of hours delivered by staff who have since been promoted.
Check cardinality as a separate defect: an is_current join can drop consultants whose current row is missing or whose termination retired the row, and can fan out if consultant_id carries more than one current row. Compare hour totals before and after the join; they must be identical.
Decide the canonical source and justify it: the snapshotted rate on the time entry is what was booked and is authoritative for financial restatement. The effective-dated join is the reconciliation, and a systematic disagreement between (a) and (b) is its own defect worth raising rather than papering over.
Reissue the affected periods with an explicit changeover note, and pin the rule in a test that fails if is_current appears in any join from a fact table to a type-2 dimension on a historical grain.

Follow-up

Some entries have cost_rate_usd NULL or zero. Which source wins then, and what does that do to your reconciliation?
The same pattern exists for bill_rate_usd. What does it do to realisation, and in which direction?
How would you sweep every model in the warehouse for this class of bug without reading each one?

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.

The project you argued against

Saying no well is a senior skill and it is rarely rehearsed. Think of a time you told someone their analysis was not worth doing, or that the experiment could not answer their question at the sample size available. Explain what you offered instead. Refusal without an alternative reads as obstruction rather than judgement.

Share an experience where you had to explain a highly complex algorithmic decision to an executive stakeholder who was skeptical of the methodology.

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
Name the disagreement or constraint, and how you resolved it with evidence.
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 a request to redefine the utilisation denominator

Hard · denominator integrity · restatement · escalation

Before a quarterly review, a practice lead asks for two changes to reported billable utilisation: drop consultants allocated to engagements with status = 'paused' out of the denominator, and count approved travel hours booked to client_delivery as billable. Together the changes move reported utilisation from 71 to 79 percent. You must answer in writing today. Give your answer, the principle you apply to each change separately, and what you do if you are overruled.

Approach

Name the probe: whether you can hold a measurement line by adjudicating each proposal on its merits, rather than treating the request as bad faith or accepting it to avoid a fight.
Adjudicate the paused-engagement change against what the denominator means. Billable utilisation measures the share of available consultant hours that were sold. A consultant whose engagement is paused is still available and still costs salary, which is precisely the situation the metric exists to reveal. Removing them defines away the signal, so this one is refused on definitional grounds, not on suspicion.
Adjudicate the travel change as an empirical question, because it is one. If those hours are billed to the client, is_billable and bill_rate_usd should already reflect it; if they are charged and then written off or never invoiced, counting them raises utilisation while realisation falls. Check the invoice lines for the same engagement and period rather than arguing about principle.
Quantify each change separately, and the interaction. An eight-point move made of one point and seven points is a different conversation from two four-point moves, and the lead cannot make a good decision without that split.
If a change is accepted, restate history on the new definition and publish the old series alongside it with the date of the change. A definition applied forward only makes every period-over-period comparison meaningless. If overruled, comply with the restatement and the footnote, and escalate only the part that is a misstatement of fact rather than a defensible choice.

Follow-up

Which of the two changes would you accept, and what evidence flips your answer on the other one?
The lead wants the new definition applied only from this quarter forward. Why is that worse than applying it to history, and how do you explain that in one sentence?

Defending a fixed-fee overrun finding against the selling partner

Easy · metric definition · pricing model mix · stakeholder pushback

Over the trailing four quarters, fixed_fee engagements in one practice area show a median scope overrun ratio of 1.34, computed as approved client_delivery hours in fct_time_entry divided by fct_engagement.contracted_hours, and inception-to-date gross margin nine points below the time_and_materials book. The partner who sold most of that work says the denominator is wrong because change orders were signed, and each change order is a separate row linked by prior_engagement_id. You have ten minutes with the practice lead. Present and defend the finding.

Approach

Say out loud what is being probed: whether a specific methodological objection makes you recompute or makes you repeat yourself. Restate the metric as numerator, denominator and window before defending anything.
Test the objection empirically instead of debating it. Rebuild the denominator by walking the prior_engagement_id chain with a recursive CTE, summing contracted_hours across the original SOW and every extension, and summing approved delivery hours over the same chain.
Report both numbers and say what each one answers: the original-SOW denominator answers whether the team held the scope that was signed, the chained denominator answers whether the firm estimated the total work correctly. Neither is a trick; they are different questions.
Keep the margin claim separate from the overrun claim, and keep it stratified. Fixed_fee margin falls with hours worked while uncapped time_and_materials margin does not, so the nine-point gap is only meaningful within pricing_model, and a shift in the fixed_fee share of fees is reported alongside it.
Close with the decision and a falsifier: if the chained overrun is near 1.0, the problem is scope control on the original SOW, and the check is whether change orders repriced at the standard rate card or at the original blended rate.

Follow-up

The chained denominator narrows the overrun to 1.11. Does your recommendation change, and which number goes in the practice review?
How would you distinguish an estimation problem at sale time from a delivery problem during execution, using only these tables?