Interview guides · Integral Federal

Integral Federal Data Scientist Interview Questions & Guide 2026

Take-home style practice for data scientist candidates: a messy dataset, a fixed time budget, and an analysis you write up and defend.

The 60-second brief

As a Data Scientist at Integral Federal, you will play a pivotal role in harnessing data to drive informed decision-making and enhance strategic outcomes. This position is vital not only for the organization but also for the clients and end-users who rely on our analytics to optimize their operations. Your work will impact a variety of projects, from developing predictive models to creating insightful visualizations, ultimately ensuring that our solutions meet the complex demands of our clients.

Ask early whether the loop includes an asynchronous take-home or a timed live case, because the two are graded on different things. A take-home is read as an artifact: the question you decided to answer, what you did about missing or malformed records, and a conclusion stated plainly enough for someone to act on. A reviewer who cannot rerun your notebook discounts the result whatever score is printed in it. Hold to the stated time box and write down what you would have done with more of it, since the follow-up round is usually a live defence of the same work.

Focus areas

Design outputs that survive public disclosure
Model censored case durations without survivorship bias
Separate obligations, outlays and ceilings correctly

Overview

The role is particularly interesting because it combines advanced analytical techniques with real-world applications, enabling you to tackle challenging problems across a range of sectors. You will collaborate with various teams, including engineering and product management, to deliver data-driven insights that guide the design and improvement of our products. Expect to engage with large datasets, utilize cutting-edge tools, and contribute to innovative solutions that can shape the future of our services.

The Integral Federal interview process

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

Preliminary Screening

Rounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.

What to demonstrate

Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options

How to prepare

Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.

In-Depth Technical Interviews

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

Whether your row counts survive each join, and whether you notice on your own when they do not
Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Behavioral Interviews

Behavioural answers from data candidates get audited in a way that answers from other roles do not. When you say a model lifted retention, the next question is the denominator, the window, and how you knew the lift was not seasonal. So attach the measurement to each claim while you tell it: what the metric was before, over what period, and against what comparison. Numbers with no baseline read as rounded-up memory, and one unsupported figure tends to make the rest of the story sound rehearsed.

What to demonstrate

Whether each impact number arrives with a baseline, a window and a comparison, or as a bare percentage
Whether you can name the method that attributed the effect to your work (an experiment, a staged rollout, a seasonal control) or concede the link was correlational
Whether the magnitudes stay internally consistent when the interviewer multiplies them against the scale you described earlier

How to prepare

For each story, write the impact line as metric, value before, value after, window, and how attribution was established. Any line missing two of those five is a follow-up you will answer badly.
Re-derive one headline number from the source table rather than the deck that reported it. Resume numbers drift upward across retellings.
Decide in advance which figures you cannot share, and prepare the ratio or relative change you can give instead, so a confidentiality limit does not read as evasion.

Final Interviews

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

Suppressing one small cell in a published table and leaving the row and column totals in place

A single suppressed cell is recoverable by subtracting the published cells from the published margin, so primary suppression without complementary suppression protects nothing. Two separately published tabulations of the same underlying data compose as well, meaning two individually safe releases can jointly identify a cell. Either apply complementary suppression across the whole table and check it against the margins, or use a formal mechanism with an accounted budget, remembering that Laplace noise for a count query is scaled to sensitivity over epsilon, that sequential releases add their epsilons, and that noisy counts are not automatically non-negative or additively consistent across aggregation levels until post-processed.

Dividing an administrative count by a survey population estimate and reporting the rate as if it were exact

At block-group and tract scale the published margin of error is often a large fraction of the estimate, so the variance of the resulting rate is dominated by the denominator rather than the numerator, and a leaderboard of areas ranked by point estimate is largely a leaderboard of the smallest and noisiest areas. Propagate the denominator error into the rate, or aggregate up until the relative standard error is acceptable, and state the threshold used. The same join also breaks silently across boundary vintages: a geo_code can refer to a different physical area after a redraw, so joining current-vintage geography onto historical events reassigns records and manufactures a trend break.

Writing SQL without stating NULL and tie-breaking behaviour

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

Interpreting a change before checking data quality and logging

Spend the first pass on row volume by day, null rates, duplicate keys, and whether the step change lands on a release or tracking-migration date. A discontinuity that coincides with a deploy is an instrumentation hypothesis before it is a behavioural one.

Questions & practice

Describe a time you implemented a machine learning model. What were the results?

Medium · machine learning and modelling

Approach

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

Follow-up

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

What metrics would you use to evaluate the performance of a model?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Check what information would not exist at prediction time, and exclude it.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

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

Laplace release of nested counts with consistent post-processing

Hard · differential privacy · laplace mechanism · post-processing

counts holds exact denial counts by (district_geo_id, tract_geo_id, denial_reason_code), where tracts nest inside districts and each constituent contributes to exactly one cell. Release the tract by reason cells and the district totals under a total privacy budget epsilon, using a Laplace mechanism you write yourself. Then post-process each district so its tract cells are non-negative and sum exactly to the released district total. Finally show empirically how per-query error moves when one epsilon is split across k sequential queries.

Approach

Fix the adjacency and the sensitivity before touching data. The full tract by reason histogram has L1 sensitivity 1 under add-or-remove adjacency and 2 under replace-one, because a replaced record leaves one cell and enters another. Say which you are using; it doubles the noise scale b = sensitivity / epsilon_cells.
Split the budget honestly. District totals are a coarsening of the same records rather than a disjoint query, so releasing them alongside the cells composes sequentially and epsilon_cells + epsilon_totals = epsilon. Disjointness buys you something only across records, for example separate districts released by separate mechanisms.
Add noise with a seeded generator: rng.laplace(0, b, size). Laplace(b) has variance 2b squared, so per-cell standard deviation is sqrt(2) * b. Compare that number against the typical cell count before deciding the release is publishable at all.
Post-process per district: clip the noisy district total at 0, then project the noisy tract vector onto {x >= 0, sum x = T} by minimising squared distance. The solution is x_i = max(y_i - tau, 0) with tau found by sorting y descending and scanning for the value that makes the sum hit T. If integers are required, round with largest remainders so the total survives the rounding.
Run the sweep: for k in 1 to 8, split one epsilon into k sequential queries each with b = k * sensitivity / epsilon, and plot RMSE against k. Per-query standard deviation grows linearly in k, which is the whole argument for releasing fewer and coarser queries.
State why the reconciliation is free: any function of a differentially private output is differentially private with the same parameters, provided it touches no raw data again.

Worked solution (45 min)

Write laplace_release(counts, sensitivity, epsilon, rng) returning noisy values, and use it once for the tract by reason cells and once for the district totals.
Implement project_to_simplex(y, T) with the sorting-and-threshold search, and assert its two constraints on the way out.
Apply the projection per district, then largest-remainder rounding if integers are required, and assemble the released frame.
Sweep k from 1 to 8 with b = k * sensitivity / epsilon, recording RMSE against the true counts over several seeds per k.
Report per-cell noise standard deviation, the RMSE curve, and the smallest aggregation level at which noise standard deviation falls under 5 percent of the cell count.

Expected result: A released frame whose tract cells are non-negative and sum exactly to the released district total, an empirical noise standard deviation matching sqrt(2) * sensitivity / epsilon within Monte Carlo error, and an RMSE curve rising linearly in k when one epsilon is split across k sequential queries.

Verify it yourself

Empirical standard deviation of the added noise over 10,000 draws is within Monte Carlo error of sqrt(2) * b.
After projection, min(x) >= 0 and sum(x) equals the released total exactly; assert both rather than eyeballing them.
Projecting onto the exact (non-noisy) total can never increase error, since the true vector lies in that convex set; with a noisy total the improvement holds only while the total's noise is small relative to the cells', which the sweep should confirm rather than assume.
Raising epsilon by a factor of 10 shrinks the noise standard deviation by the same factor and the release converges on the exact counts.

Follow-up

You could skip epsilon_totals and derive district totals by summing the noisy tract cells. Compare the variance of that against a directly measured total and say when each wins.
A reason code appears in only two tracts, with a true count of 3 in each. What do you publish, and does the noise alone make it safe?
The same table is released monthly for a year. What is the honest statement about cumulative budget, and what would you change in the design?

Total time each case spent awaiting evidence

Medium · window functions · event streams · censoring

fact_case_event is append-only with case_event_id, application_id, event_type, event_at, prior_status and new_status. A case enters the pending_evidence state on an event where new_status = 'pending_evidence' and leaves it at that application's next event in event_at order; it may enter more than once, and it may still be sitting there at the extract timestamp. Return per application_id the number of pending_evidence stints and the total hours spent in that status, counting any open stint up to the extract timestamp. Order events within an application by event_at with case_event_id as the tiebreaker.

Approach

Compute LEAD(event_at) OVER (PARTITION BY application_id ORDER BY event_at, case_event_id) over the full event stream in a CTE, before filtering to pending_evidence rows: if you filter first, LEAD returns the next entry into pending_evidence rather than the exit from it, and every stint is measured from one entry to the following entry.
Include case_event_id in the ORDER BY because staff backdating puts several events on the same event_at, and a window with a non-deterministic order silently reorders a transition pair.
In the outer query, keep only rows where new_status = 'pending_evidence' and set the stint end to COALESCE(next_event_at, extract_ts), which is what makes open stints count instead of vanishing.
Aggregate with COUNT(*) for stints and SUM(EXTRACT(EPOCH FROM (stint_end - event_at)) / 3600.0) for hours, grouping by application_id.
Flag applications whose final stint is open separately, because those durations are right-censored lower bounds and must not be pooled into a mean as if they were complete.

Follow-up

The average of this total is used to claim evidence handling improved. Why is that average biased downward while the backlog is growing, and what would you report instead?
Two events on one application share an event_at and describe opposite transitions. How do you decide which came first?
How would you extend this to give the time in every status rather than just pending_evidence, in one pass?

Submission completion rate by intake channel, thirty-day window

Easy · funnel · cohorts · conditional aggregation

fact_application has application_id, channel (online, phone, mail, in_person, kiosk, partner_org), created_at, submitted_at (NULL if the draft was never submitted) and status. Report, per weekly created_at cohort and per channel, the number of applications started, the number submitted within 30 days of created_at, and the completion rate. Include only cohort weeks that closed at least 30 days before the extract date. Rows left in status 'draft' or 'abandoned' stay in the denominator. Return cohort_week, channel, started, submitted_30d, completion_rate.

Approach

Bucket on created_at, not submitted_at: the cohort is defined by when the work entered the system, so a row that never submits must still land in a cohort week.
Express the numerator as a conditional aggregate, COUNT() FILTER (WHERE submitted_at IS NOT NULL AND submitted_at < created_at + INTERVAL '30 days'), so the denominator stays COUNT() over the same grouped set and cannot drift from it.
Cut incomplete cohorts with a WHERE on the cohort week rather than on individual rows, and anchor the maturity test on the week's last possible created_at, not its first. A week runs [week_start, week_start + 7 days), so the latest application in it needs 30 more days and the week qualifies only when week_start + INTERVAL '37 days' <= extract_date. Testing week_start + 30 days admits a week whose Sunday arrivals have had 23 days of exposure, and that week reports a depressed rate that reads as a regression.
Group by channel from the start rather than pooling then splitting, because channel mix changes week to week and a pooled rate moves with the mix.
Return the raw numerator and denominator next to the rate so a reader can see which channels carry enough volume for the rate to mean anything.

Worked solution (20 min)

Write the cohort key as date_trunc('week', created_at) and confirm the row count of the base table equals the sum of the started column, proving nothing was dropped by the grouping.
Add the 30-day conditional count and verify submitted_30d never exceeds started for any group.
Filter to cohort weeks where date_trunc('week', created_at) + INTERVAL '37 days' <= extract_date: 7 days to carry the week out to its last created_at, plus the 30-day submission window. Under date_trunc a week starts Monday, so this is the Monday whose following Sunday's arrivals have all had their full 30 days.
Compute completion_rate as submitted_30d::numeric / NULLIF(started, 0) to avoid a divide-by-zero on a channel with no starts in a week.
Scan the output for channels sitting at exactly 1.000 and note them as a measurement artefact rather than a result.

Expected result: One row per (cohort_week, channel) pair with started > 0, completion_rate in [0, 1]. mail and in_person return 1.000 in every week because those channels have no draft state, so created_at and submitted_at are written in the same transaction. online and kiosk return rates strictly below 1 and are the only channels where the metric carries information.

Verify it yourself

SUM(started) across all output rows equals COUNT(*) of fact_application restricted to the same created_at range and the included cohort weeks.
No row has submitted_30d > started.
Relaxing the anchor from 37 days to 30 days admits exactly one further cohort week, and that week's online and kiosk rates sit visibly below their neighbours; dropping the filter entirely extends that depressed tail. Both confirm the maturity cut is doing work and that 30 days is the wrong anchor.
A count of rows with status IN ('draft','abandoned') is non-zero and is exactly the shortfall between started and applications ever submitted.

Follow-up

mail and in_person come back at exactly 1.000 every week. Is that a bug, and how would you present the metric so a reader does not read it as those channels performing best?
How would the query change if you wanted completion within 30 days measured in business days against the published service standard instead of calendar days?
A channel's completion rate rises 8 points the week a new form launches. What would you check before reporting it as an improvement?

How would you approach a project with ambiguous requirements?

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

Follow-up

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

Discuss how you would estimate the potential impact of a new feature based on historical data.

Medium · product sense and metrics

Approach

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

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 do you prioritize tasks when managing multiple projects?

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.

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?

You are given a dataset with various features. How would you determine the most important variables?

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

Follow-up

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

Explain the difference between supervised and unsupervised learning.

Medium

Approach

Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.
Work from the decision backwards to the evidence you would need.

Follow-up

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

Switchback design for a shared-capacity dispatch rule

Medium · switchback · interference · carryover

A new rule routes service requests to crews by predicted travel time rather than by district. Crew hours are fixed, so treating some requests and not others moves capacity between arms and a request-level split violates the no-interference assumption. Using fact_service_request with assigned_unit_id, reported_at and first_response_at, design a switchback across 12 districts over 28 days. State the switching unit, the burn-in handling, the estimand and the variance treatment. The outcome is the 90th percentile hours to first response.

Approach

Name the interference channel explicitly: crews are a shared and exhaustible resource, so under a request-level split the control arm's waiting time depends on how many treated requests are absorbing crew hours, and the difference in means describes neither the fully treated nor the fully untreated world.
Switch the whole district on the clock: randomise the rule at the district by time-block level so that at any instant every request in a district faces one regime, which makes the estimand the global treatment effect rather than a contaminated local one.
Set the block length against carryover, not convenience: blocks must be long relative to how long a job stays in progress, so with a multi-hour service standard a 6-hour block with the first 60 minutes discarded as burn-in is defensible and a 30-minute block is not.
Analyse at the level you randomised: compute the p90 per district-block, regress it on the assignment indicator with district and block-of-day fixed effects, and cluster by district-day so serial correlation inside a day is not counted as independent information.
Test the carryover assumption rather than asserting it: add the previous block's assignment as a regressor and confirm its coefficient is near zero, then re-estimate with a longer burn-in and report whether the point estimate moved.

Worked solution (35 min)

Count the design units: 12 districts * 4 blocks a day * 28 days = 1,344 district-blocks, about 672 per arm, which is the real sample size.
Define the block outcome as the p90 of (first_response_at - reported_at) in hours over requests reported after the burn-in window, and drop any block where more than 10 percent of requests are still open at extract, because its p90 is not identified from observed data.
Fit p90 ~ treat + district + block_of_day + day, with standard errors clustered by district-day.
Re-fit with lagged assignment included and with the burn-in extended to 120 minutes, and report that pair as the stated robustness check.

Expected result: A design with 1,344 district-block units, a stated global-treatment-effect estimand, burn-in exclusion tied to job duration, and inference clustered at district-day. The effect is reported as a difference in block-level p90 hours with an interval reflecting about 672 units per arm, not the request count.

Verify it yourself

Pre-period block-level p90 should balance across arms within noise; a systematic gap means the block schedule is correlated with demand.
The lagged-assignment coefficient should be indistinguishable from zero once the burn-in is long enough.
Running the identical model on the pre-period with a placebo schedule must return a null.

Follow-up

Demand is not stationary and storms produce correlated bursts across districts. Which part of the design breaks first, and how do you repair it?
A stakeholder proposes randomising at the request level to get forty times more rows. What exactly does that estimate, and when would it be acceptable?

Online completion collapses the week autosave ships

Medium · instrumentation change · denominator inflation · cohort maturity

Submission completion rate for the online channel fell from 61 to 38 percent the week a redesigned form shipped with autosaved drafts; mail and in-person are flat. You have fact_application (application_id, channel, created_at, submitted_at, status) and fact_case_event (application_id, event_type, event_at). The product lead wants to roll the release back tomorrow. Decide whether the metric moved because behaviour changed or because the denominator did, and state the evidence that separates the two.

Approach

Look at the numerator in levels before touching the ratio. Count submitted applications per day for the online channel across the release. If the absolute count of submissions is unchanged while the rate fell, no constituent behaviour has changed and the denominator grew.
Establish what a row in the denominator now means. Autosave writes a draft row on first interaction, so applications that previously left no trace now appear with created_at set and never advance. Count created_at rows per day before and after: a step change with no matching change in downstream events is the signature.
Rebuild the metric on a denominator that is stable across the release. Restrict to drafts that reached a fixed progress marker, for instance those with at least one fact_case_event beyond 'created', and recompute both periods on that definition.
Keep channels separate. Mail, in_person and kiosk have no draft state, so their completion rate is structurally near 100 percent and pooling them hides the online move or dilutes it depending on mix.
Respect cohort maturity. The metric allows 30 days from created_at to submitted_at, so the release week is a partial cohort whose rate is mechanically low. Compare only cohorts that have fully matured, or report the day-7 completion share for both periods instead.

Follow-up

On the stable denominator the rate still fell 4 points. How do you decide whether that is the release or the week, and what would you instrument next?
What reporting change would you make so that a future instrumentation change cannot move this metric without being visible?

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.

Explaining a method to someone who will not follow the maths

Half of this section is about translation. Be ready to describe how you explained a result to someone who did not want the method, only the implication, and what you did when the simplified version started being repeated in a way that overstated it. Correcting your own simplification is a strong beat.

Describe a challenging project you worked on. How did you overcome the obstacles?

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.
State the situation in two sentences and spend the rest on your reasoning.

Follow-up

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

Present a hypothetical scenario where data leads to a business insight. How would you convey this to non-technical stakeholders?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Close with what you would do differently, concretely.
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?

Turn "how many people are we missing" into a scoped estimate

Easy · scoping · coverage · stakeholder communication

A programme director stops you and asks, "how many people are we missing?" You have fact_application, dim_constituent and dim_geography and nothing else. Before writing any SQL you owe her a written scope in thirty minutes: the question restated as something estimable, the denominator you will build and where it comes from, what the data cannot tell her, and a delivery date. Produce that scope note in under 300 words, plus the three clarifying questions whose answers would change the estimate.

Approach

Restate the request as a coverage estimand before negotiating anything else: distinct constituent_id with status = 'approved' and decision_at inside a named program year, over a modelled count of eligible constituents in the same geography and year. Say out loud that the denominator cannot come from fact_application, because every row there belongs to someone who already found the system.
Write the three questions whose answers move the number by more than the analysis will: which program_code and which rule_version_id define eligibility, which geo_level and vintage_year is the denominator drawn at, and whether "missing" means never created an application, created one but has submitted_at IS NULL, or was denied on a procedural denial_reason_code. Those are three different interventions and only the first is a pure outreach problem.
Name the denominator pipeline concretely: survey microdata plus a microsimulation of the eligibility rule in force at that rule_version_id, with the income and household definitions stated because reported_income_cents in the administrative table is self-reported and heaps on round values.
State the non-answers before they are asked for: the estimate is a count, not a list of people, and the gap cannot be attributed to a cause without a design.
Commit to a date and offer an interim artefact on day one, the funnel from created_at to submitted_at to decision_at by channel, so the director has something concrete while the denominator is built.

Follow-up

The director asks for the names of the people who are missing so outreach can call them. What do you say, and what could you legitimately produce instead?
Your modelled eligible population comes out 40 percent above the applicant count. What checks do you run before anyone sees that number?