Interview guides · Agile Defense

Agile Defense Data Scientist Interview Questions & Guide 2026

SQL practice for Agile Defense data scientist candidates: window functions, funnel and retention queries, joins that quietly drop rows.

The 60-second brief

As a Data Scientist at Agile Defense, you step into a pivotal role dedicated to transforming complex data streams into actionable intelligence for defense and national security missions. Your day-to-day contributions directly impact high-stakes operations, helping stakeholders make rapid, data-informed decisions in fast-moving environments. You will bridge the gap between advanced analytical modeling and practical, mission-critical deployment, ensuring that data infrastructure delivers tangible value.

In modelling rounds the live question is usually why this model class for this problem, and how you would know six weeks after launch that it is still working. Deriving gradients by hand is rarely what is being probed.

Focus areas

Audit disparate impact before any model ships
Estimate eligible populations outside administrative data
Design outputs that survive public disclosure

Overview

This role requires a unique blend of robust technical execution and sharp product sense. You will design experimentation frameworks, build scalable data pipelines, and diagnose unexpected metric shifts across critical operational systems. Whether you are analyzing complex defense datasets or optimizing product metrics for internal platforms, your work influences strategic planning and tactical execution alike.

Operating within Agile Defense means collaborating closely with cross-functional teams of engineers, product managers, and defense domain experts. You will encounter high ambiguity, requiring you to formulate your own hypotheses, validate them rigorously using statistical methods, and communicate your findings clearly to non-technical leaders. If you thrive on solving multifaceted challenges where precision matters, this position offers an exceptional platform for professional impact.

The Agile Defense interview process

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

Recruiter Screen

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical Assessments

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

Panel 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

Computing coverage or take-up with an administrative denominator, for example approved applications divided by all applications

That ratio measures throughput among people who already found the system, not delivery to the people entitled to the service. It gets better when outreach is cut, because the marginal applicant is the one most likely to be denied or to abandon, and it gets worse when a new access channel brings in harder cases. The correct denominator is a modelled eligible population from survey microdata run through the eligibility rules, and the gap between it and the applicant count is usually the finding.

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.

Sizing estimates built on unnamed, unrevisable assumptions

Write each assumption as a named number you can change, then show the arithmetic so the interviewer can challenge one input instead of the whole answer. Finish by saying which assumption the result is most sensitive to, which matters more than the point estimate.

Crediting a treatment for regression to the mean

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

Questions & practice

When would you choose a Bayesian approach over frequentist hypothesis testing?

Medium · statistics and probability

Approach

Translate the result into the decision it informs, in one plain sentence.
Quantify uncertainty explicitly rather than reporting a point estimate alone.
Say what the estimate is of, and over what population it generalises.

Follow-up

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

How do you select the appropriate evaluation metric for a classification model with heavily imbalanced classes?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Set a baseline first, so any model has something honest to beat.
Say how the offline result would be validated online before it is trusted.

Follow-up

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

Reconcile obligations, ceilings and outlays across snapshot dates

Easy · snapshots · aggregation · data quality

obligations holds one row per funding action: obligation_action_id, award_id, action_type, action_at, fiscal_year, obligated_delta_cents (signed), ceiling_amount_cents, outlay_to_date_cents, program_code, snapshot_date and is_current_snapshot. The table is a stack of as-of snapshots, and outlay_to_date_cents is an award-level cumulative figure repeated on every action row inside a snapshot. Produce a per-award frame with net obligated value, ceiling in force and outlay; a program by fiscal-year obligation total; and an exception report for awards where net obligated exceeds the ceiling or outlay exceeds net obligated.

Approach

Filter to is_current_snapshot and assert that exactly one snapshot_date survives. If several do, the flag is maintained per award rather than per snapshot and every total below counts some awards twice.
Net obligated per award is the sum of signed obligated_delta_cents. Deobligations and terminations are negative and must be summed in; filtering to action_type = 'new_award' reports gross intent, not money committed.
Ceiling is the value carried on the latest action by action_at, because a modification can raise it. Summing ceiling_amount_cents across actions is a category error: it is a maximum the award may reach, not cash.
Outlay is award-level and repeated, so reduce it with first after asserting nunique() == 1 per award. Summing it multiplies the figure by the action count, and the result still looks plausible, which is what makes it dangerous.
Build fiscal totals by grouping deltas on the action's fiscal year, and cross-check the stored fiscal_year against the value derived from action_at. Disagreements are an assignment bug worth listing, not rounding.
Exception frame: net > ceiling, outlay > net, earliest action not of type new_award, and net < 0. Report counts and cents rather than percentages of a denominator you have not defended.

Worked solution (25 min)

Filter on is_current_snapshot, then assert obligations['snapshot_date'].nunique() == 1 and stop if it fails.
Group by award_id with a different reducer per column: sum for obligated_delta_cents, the value at max action_at for ceiling_amount_cents, and first for outlay_to_date_cents guarded by an nunique check.
Group by program_code and fiscal_year over the signed deltas for the fiscal totals, and recompute fiscal_year from action_at to list mismatched rows.
Build the exception frame by stacking the four rule violations with a rule column, so one award can appear more than once.
Return the three frames with cents intact; convert to dollars only at presentation.

Expected result: A per-award frame (award_id, net_obligated_cents, ceiling_in_force_cents, outlay_cents, outlay_ratio), a program by fiscal-year total built only from signed deltas, and an exception frame with one row per violated rule. Awards carrying terminations show net below the sum of their positive actions, and a low outlay_ratio on a recent award is expected rather than an exception.

Verify it yourself

The sum of per-award net equals the sum of obligated_delta_cents over the filtered rows, to the cent.
Every award has exactly one distinct outlay_to_date_cents and one distinct snapshot_date after filtering.
Recomputing fiscal_year from action_at reproduces the stored column for every row, or the mismatched rows are listed rather than silently overwritten.
No award in the per-award frame has ceiling_in_force_cents below the ceiling on its earliest action unless a modification lowered it, which should be visible as a matching action row.

Follow-up

Outlay sits at 40 percent of obligations for a program this fiscal year. Name three innocent explanations before you write the word underspending.
Leadership asks how much money was committed this year for an award that spans three years with option exercises. What number do you give and what do you refuse to give?
The snapshot flag turns out to be per award. What breaks in your aggregation and how do you rebuild it from snapshot_date alone?

How do you handle missing or malformed data points when writing complex JOIN statements in SQL?

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.
Check whether any join is one-to-many before aggregating, or the sums inflate.

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?

How would you identify duplicate records in a massive database table without losing transactional history?

Medium · sql and data manipulation

Approach

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.
Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

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

Point-in-time join across slowly changing dimension and boundary vintage

Hard · scd type 2 · point-in-time join · fan-out

fact_application has application_id, constituent_id and submitted_at. dim_constituent is type 2 with constituent_id, residence_geo_id, effective_from and effective_to (NULL marks the current row). dim_geography has geo_id as the primary key, unique to one geography and one boundary vintage, plus geo_code, vintage_year, effective_from, effective_to and deprivation_quartile; the same geo_code refers to different physical areas in different vintages. Count applications submitted between 2021 and 2025 by submission year and by the deprivation quartile of the residence as it stood on submitted_at. Exactly one output row per application must feed the count.

Approach

Join dim_constituent with a range predicate, submitted_at >= effective_from AND (effective_to IS NULL OR submitted_at < effective_to), using a half-open interval so an application submitted exactly at a version boundary matches one version and not two.
Join dim_geography on residence_geo_id, not on geo_code: geo_id is already unique to the geography-and-vintage pair, so it carries the vintage for free, while geo_code multiplies each application by the number of vintages that code has existed in and reassigns records across a redraw.
Assert the grain rather than assuming it, by counting rows before and after each join; a type 2 dimension joined on the natural key alone produces one row per version of the person and the inflation is largest for constituents who moved, which is exactly the population a quartile breakdown is about.
If the dimension has overlapping ranges from a bad load, deduplicate with ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY effective_from DESC) and keep rn = 1, but record how many applications needed it instead of hiding the repair.
Report the count of applications that failed to match any version as its own line, because a point-in-time join that silently drops pre-history applications understates the earliest years and produces a fake upward trend.

Worked solution (40 min)

Record COUNT(*) of fact_application in the date range as the target grain before writing any join.
Join to dim_constituent with the half-open range predicate and re-count; investigate any increase before proceeding.
Join to dim_geography on residence_geo_id and re-count again, confirming the number is unchanged.
LEFT JOIN rather than INNER JOIN at both steps first, so unmatched applications are visible and countable instead of silently gone.
Group by EXTRACT(YEAR FROM submitted_at) and deprivation_quartile, with a separate bucket for unmatched rows.
Re-run the whole thing with a naive current-row join and compare the quartile distribution to size the error.

Expected result: A tidy grid of five years by four quartiles plus an unmatched bucket, where the row counts sum exactly to the count of applications submitted in the range. The naive current-row variant returns a larger total than the application count, and the excess concentrates in constituents with multiple address versions.

Verify it yourself

SUM of all output counts, including the unmatched bucket, equals COUNT(*) of fact_application for the date range.
COUNT() after the constituent join equals COUNT() before it.
No application_id appears more than once in the intermediate joined set, verified with a HAVING COUNT(*) > 1 probe.
Each matched application's geo row has effective_from <= submitted_at, checked as an assertion rather than eyeballed.

Follow-up

A boundary redraw happened in 2023. What exactly goes wrong in a year-over-year quartile comparison if you attach current-vintage geography to every year, and which direction does it bias?
How would you present a 2021-to-2025 trend when the quartile definitions themselves were re-derived within each vintage?
Some applications match no constituent version because the identity was resolved after submission. Include them, exclude them, or report separately?

How do you design a composite index metric that combines multiple disparate performance indicators?

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.
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?

How do you prioritize your workload when multiple cross-functional teams request urgent data analyses simultaneously?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.
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?

How would you design a core engagement metric for a new defense intelligence dashboard?

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

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?

Your primary active-user metric dropped by 15% overnight. Walk through your systematic approach to diagnosing the root cause.

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.
Decompose the metric into the rates that drive it, and say which one you would check first.

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?

Explain how you determine the required sample size and minimum detectable effect before launching an experiment.

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
Name the randomisation unit first; it decides the variance and what the test can detect.
State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

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

When is it appropriate to stop an A/B test early, and what are the statistical risks of doing so?

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
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.

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 balance feature velocity with maintaining rigorous data quality in a fast-paced environment?

Medium

Approach

Work from the decision backwards to the evidence you would need.
State your assumptions explicitly before working the problem.
Clarify what is being asked and what a complete answer would contain.

Follow-up

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

Minimum detectable effect for a mailed take-up nudge

Easy · power · minimum detectable effect · randomisation unit

A modelled eligible list holds 48,000 households that have never applied to one program. You may randomise a mailed notice 1:1 at the household level, and dim_constituent.household_id is the assignment unit. The outcome is at least one fact_application row with submitted_at inside 90 days of the mail date. Baseline submission in this list is 6.2 percent. Staff want to detect a 3 percent relative lift. Give the MDE at 80 percent power and 5 percent two-sided alpha, and say whether the 3 percent target is reachable.

Approach

Fix the unit before any arithmetic: assignment is at household_id and the outcome is a household-level binary, so the sample is 48,000 households, not the larger constituent count those households expand to.
Use the equal-arm two-proportion normal approximation, n per arm = (z_0.975 + z_0.80)^2 * [p0(1-p0) + p1(1-p1)] / delta^2, with z_0.975 + z_0.80 = 1.960 + 0.842 = 2.802.
Solve it backwards for the MDE at the sample you actually have, n = 24,000 per arm: delta = 2.802 * sqrt(2 * 0.062 * 0.938 / 24,000).
Convert the absolute MDE into a relative lift against the 6.2 percent baseline so it is directly comparable with the target staff stated.
Invert the same formula at delta = 0.03 * 0.062 to report the sample the 3 percent target actually requires, which is the number that settles the argument.
Separate statistical detectability from the decision threshold: the smallest lift that pays for the mailing should have been set before the power calculation, and it belongs in the same memo.

Worked solution (20 min)

Confirm n = 24,000 per arm and p0 = 0.062, so p0(1-p0) = 0.058156 and the numerator (2.802)^2 * 2 * 0.058156 = 7.849 * 0.116312 = 0.9129.
MDE = sqrt(0.9129 / 24,000) = sqrt(3.804e-5) = 0.00617.
Express it as a relative lift: 0.00617 / 0.062 = 0.0995.
Invert for the stated target: delta = 0.03 * 0.062 = 0.00186, so n = 0.9129 / 0.00186^2 = 263,900 per arm.

Expected result: The MDE is about 0.62 percentage points absolute, roughly a 10 percent relative lift. Detecting a 3 percent relative lift needs about 264,000 households per arm, more than ten times the entire eligible list, so this trial cannot answer that question and must be repowered on a larger effect or dropped.

Verify it yourself

Doubling n must shrink the MDE by a factor of sqrt(2) = 1.41, not by 2.
The unpooled variance p0(1-p0) + p1(1-p1) at p1 = 0.0682 is 0.058156 + 0.063549 = 0.121705 against the pooled 2 * 0.058156 = 0.116312, so the MDE grows by sqrt(1.0464) = 1.023, about 2.3 percent: 0.00617 becomes 0.00631, or 9.95 percent relative becomes 10.2 percent. The pooled shortcut is the optimistic end of the range rather than an identity, and 2.3 percent is small enough that the conclusion does not turn on it.
Round-trip the MDE through a two-sample proportion calculator: at alpha 0.05 two-sided and power 0.80, p1 = 0.062 and p2 = 0.0682 must return roughly the sample you already have, about 24,850 per arm on unpooled variance or 23,750 on the pooled shortcut, because p2 - p1 = 0.0062 is the MDE itself.
The 264,000 per arm figure belongs to the 3 percent target, not to the MDE: it comes from p2 = 0.062 * 1.03 = 0.0639, which a calculator returns as about 268,000 per arm unpooled against the 263,900 step 4 gets with the pooled shortcut. A calculator that returns 264,000 from p2 = 0.0682 means delta was typed as the target when the sample was typed as the one on hand.

Follow-up

If the outcome were measured per constituent rather than per household, how would the required sample change and what quantity would you need to estimate first?
The eligible list is itself modelled and carries a margin of error. Does that affect the power calculation, the interpretation of the coverage metric, or both?
A third of the list has no deliverable address on file. What estimand can you still report, and what have you lost?

Reopen rate falls when the closure code list is revised

Easy · definition change · denominator integrity · enum crosswalk

The 30-day reopen rate on service requests fell from 9.1 percent to 5.4 percent in the quarter a revision to the controlled code lists shipped. Using fact_service_request (service_request_id, request_type_code, priority, reported_at, closed_at, closure_code, reopen_of_request_id, assigned_unit_id), decide whether field quality changed. Deliver the metric rebuilt on a single definition across the break, plus a short statement of what the published number is now actually measuring.

Approach

Pin the definition first. The metric counts, among requests closed in the window with closure_code = 'resolved', those with a later row whose reopen_of_request_id points at them within 30 days. Both sides depend on an enum that just changed, so build a crosswalk from new codes back to the pre-change codes and recompute the whole series on the old definition before comparing anything.
Inspect the denominator's composition, not just its size. Tabulate the share of closures by closure_code by month across the break. If closures moved out of 'resolved' into 'closed_on_age', 'referred_external' or 'duplicate', the denominator lost precisely the cases most likely to be reported again, which lowers the rate with no change in the field.
Hold request_type_code fixed. A revised list retires and splits codes, so a pooled rate compares a different mix of work before and after. Compute within request type on the crosswalked codes, then aggregate with base-period weights.
Test the numerator's plumbing separately. Look for near-duplicate reports at the same geo_id and request type within 30 days that carry a NULL reopen_of_request_id. A rise in unlinked duplicates after the release means the self-reference stopped being written, which collapses the numerator independently of anything operational.
Publish a sanity series that cannot be moved by recoding: repeat reports per 1,000 closures counting every closure code, alongside the pinned-definition metric.

Follow-up

The crosswalk is many-to-many because one retired code split into three. How do you allocate history across the split without inventing precision?
If reopen linkage is genuinely broken, what is your interim metric and how would you state its bias direction in the published note?

Suggested practice checklist

Instead of guessing where the week should go, day one measures it under a fixed rubric and allocates the remaining hours in proportion to the gaps. The method is deliberately rigid: the allocation is written down before any studying starts and is not renegotiated when a topic turns out to be unpleasant.

Day 1 — Diagnostic, scored before you study anything

Sit a 100-minute timed diagnostic in four blocks: 30 minutes of SQL across three prompts, 25 minutes of short-answer statistics, 25 minutes on one modelling or case prompt, and 20 minutes delivering one behavioural story aloud.
Score each block from 0 to 3 on a fixed rubric where 3 is correct and fluent, 2 is correct but slow or prompted, 1 is partially correct, and 0 is stuck, grading the output rather than how the attempt felt.
Allocate the hours for days two to five roughly in proportion to 3 minus the score in each block, write the allocation down, and commit to not revising it midweek.

Deliverable: A scored rubric and a fixed hour allocation for the rest of the week.

Day 2 — Largest gap: find the boundary rather than the subject

Break the weakest area into five named sub-skills (for query work: grain control, window frames, date arithmetic, set logic with NULLs, and reading a query plan) and rate each one, so the rest of the week targets a sub-skill instead of a subject.
Solve three problems chosen to sit just above where the rating drops off, and for each write the first move you failed to make.
Re-solve one of them from memory four hours later, on paper, with nothing open.

Deliverable: A five-item sub-skill map with the two blocking sub-skills circled.

Day 3 — Largest gap: drill the blocking sub-skill

Do eight short repetitions of the same shape rather than eight different problems, so what you practise is the pattern and not the puzzle.
Write the rule you now hold in one sentence, then test it against a case built to break it: a ranking function over a column with ties, or a two-sample test on observations that are obviously dependent.
Have someone else read your one-sentence rule and find the precondition you left out.

Deliverable: One rule statement with its preconditions attached and one counterexample that would have caught the incomplete version.

Day 4 — Second gap, plus maintenance on your strongest area

Run the same sub-skill map and boundary protocol on the second-largest gap, compressed into half the day.
Spend 25 timed minutes on your strongest area to stop it decaying, choosing the hardest problem you can still finish rather than an easy warm-up.
Compare how the two areas fail: whether you lose time on recall, on setup, or on arithmetic, because the fix differs for each.

Deliverable: A second sub-skill map plus a one-line diagnosis of how each area fails you.

Day 5 — The gap that is not a skill

Record yourself answering one technical and one behavioural prompt, then count two things in the playback: how many seconds before your first clarifying question, and how many sentences you started without knowing where they ended.
Rewrite your three most-used stock phrases into shorter versions, and practise saying "I do not know, here is how I would find out" without softening it into a guess.
Deliver one answer again with a hard 90-second limit to force structure before detail.

Deliverable: Two recordings with a counted improvement in time-to-first-question.

Day 6 — Retest under day-one conditions

Sit the same 100-minute diagnostic structure with new prompts of comparable difficulty and score it on the identical rubric.
Compare block by block, and for any block that did not move, change the method rather than adding hours: a block stuck at 1 usually means the practice was too varied, not too short.
Write which single block you would still lose the offer on.

Deliverable: A second scored rubric placed next to the first, with one named remaining risk.

Day 7 — Full loop under interview conditions

Run a 60-minute mock covering the two blocks that moved least, with an interviewer instructed to interrupt and change direction.
Write your recovery script for the moment you go blank: restate the question, state your assumption, name the first thing you would check.
Reduce the week to the rule statements you wrote, each with its preconditions attached, then say every one of them out loud without reading it and cut any you cannot state in a single sentence, since a rule you have to reconstruct mid-answer will not survive being interrupted.

Deliverable: A one-page card holding the recovery script and only the rules you could state from memory.

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.

How do you handle network effects or interference between treatment and control groups?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Allocate one analyst-week across three competing requests

Medium · prioritisation · capacity · expectation setting

Three requests land on Monday and you have one analyst-week. A statutory report of decision counts by program_code is due in nine days. An operations team wants a backlog projection to size next quarter's staffing. An equity lead wants the first-response equity ratio by deprivation quartile for a briefing in six weeks. Produce the allocation, the message you send to whichever teams you defer, and name the one deliverable you will refuse to produce in the time available.

Approach

Sort by who owns the date. The statutory deadline is external and non-negotiable; the other two have owners who can trade scope or timing, which makes them negotiable even though both feel urgent.
Cost each request in hours including the slow parts rather than the query: the statutory extract needs a validation pass and review time, the backlog projection needs arrival rates and a real capacity ceiling from dim_unit, and the equity ratio needs request-type stratification and a boundary-vintage-correct join to dim_geography.
Allocate with review time inside the estimate, not after it. Finish the statutory report early enough that a second person can check it, because a late correction on a statutory return costs more than every other item on the list combined.
Scope the backlog projection down to something honest: a capacity-bounded projection that states the stability condition, since a queue only clears when the arrival rate is below the throughput ceiling, and Little's Law relates work in progress to arrival rate times time in system only in steady state, which a seasonal arrival pattern violates.
Turn the deferral into a commitment: a date, a named smaller interim artefact, and the specific input you need from them in the meantime, so "deferred" is falsifiable rather than a soft no.
Refuse the one thing that cannot be done well: a single-date backlog clearance forecast with no capacity assumption, and say why in one sentence rather than negotiating it down.

Follow-up

The operations director escalates to your manager. What do you send, and what do you not say?
On day six the statutory extract fails a validation check. What drops, and who finds out first?

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?