Interview guides · Allegheny County Department Of Human Services

Allegheny County Department Of Human Services Data Scientist Interview Questions & Guide 2026

Prepare for the data scientist interview at Allegheny County Department Of Human Services with original practice on experiment design, SQL, and metric reasoning, each exercise paired with a worked solution and the reasoning behind it.

The 60-second brief

The Allegheny County Department Of Human Services (DHS) is a national leader in leveraging data and technology to improve public sector outcomes. As a Data Scientist, you will operate at the intersection of public policy, social impact, and advanced analytics. You will work with a nationally recognized, integrated dataset to solve critical problems related to mental health, addiction, homelessness, and child protective services. Your work directly influences how the county allocates over $1 billion in resources, aiming to optimize services for more than 200,000 residents each year.

Most of the loop measures decision-making under uncertainty rather than recall. You are scored on whether you state your assumptions, commit to an estimate you can defend, and say explicitly what evidence would change it.

Focus areas

Segment margin by pricing model before comparing
Cluster inference at the account, not the engagement
Correct late-entered timesheets before trending recent weeks

Overview

This role is not just about building models; it is about creating sustainable, scalable software that drives real-world change. You will collaborate with a 40-person Analytics team, technology partners, and program leadership to design feedback loops, evaluate the efficacy of interventions, and augment human decision-making. The environment is highly intellectual and mission-driven, seeking individuals who prioritize impact and simplicity over unnecessary complexity. If you are intellectually curious and eager to apply rigorous empirical methods to improve the lives of the most vulnerable, this position offers a unique opportunity to shape the future of local government.

The Allegheny County Department Of Human Services interview process

Allegheny County Department Of Human Services candidates report 4 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

A screening call is a matching exercise run by someone who will not evaluate your statistics. They are checking that the work described on your resume is work you personally did, and that its scope matches the level the role is written for. Logistics get settled in the same half hour so nobody spends an interviewer's afternoon on a mismatch. The answer that fails is the one narrated in the plural. If every sentence is 'we built' and 'the team decided', there is nothing specific to write down about you. Name the piece that was yours, the decision you made inside it, and what changed after.

What to demonstrate

Whether the ownership implied by your resume survives one round of follow-up about who actually did which part
Whether your described scope (data size, stakeholders, what shipped) matches the seniority the role is written at
Whether timeline, location and compensation expectations make the rest of the loop worth scheduling

How to prepare

Rewrite your top three resume bullets in the first person singular, each with the decision you made and what moved afterwards, then say them out loud once so the 'we' does not return under pressure
Attach one number to each project: the baseline, the change, and the window it was measured over. Where impact was never measured, say that plainly rather than inventing a figure
Settle your compensation range before the call and give it as a range with a reason behind it, such as current total comp or a competing timeline, instead of deflecting the question twice

Technical Skills Assessment

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

Experience Interviews

An added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.

What to demonstrate

Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact

How to prepare

Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.

Project Discussion

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Where candidates lose points

Trending utilisation or revenue on work_date without accounting for timesheet backfill

Time entries are created days to weeks after the work happens, and the backfill tail often runs two to six weeks. A dashboard keyed on work_date therefore shows the most recent weeks as a decline that reverses on every refresh. The fix is either to hold the reporting window back past the observed backfill tail (measure the tail with the timesheet submission lag metric rather than guessing) or to report an as-of-entered_at snapshot so the series is internally consistent, and to state which one you used.

Modelling win rate on proposals with a recorded outcome, using fields written after the decision

Two failures compound here. First, stage IN ('withdrawn','no_decision') is not missing at random: those are disproportionately deals that were going to be lost, so training on won-plus-lost only inflates apparent win rate and distorts the coefficients. Second, fields like engagement_id, final scope and revised pricing are populated after the outcome is known, so including them leaks the label and produces a model with excellent backtest accuracy and no forward value. Restrict features to values knowable at submitted_at.

Reading experiment results before checking the arm split

Compare observed arm counts against the intended allocation ratio, not an assumed even split, and set the alarm far below the conventional 0.05: at 0.05 roughly one healthy experiment in twenty trips it, which is why sample-ratio checks usually run at p < 0.001 or stricter. The test's power scales with sample size, so it misses a real diversion on a small experiment and fires on an imbalance too small to move the estimate on a very large one. A flag means go find the assignment or logging fault before reading any outcome, not report a mismatch.

Generalising beyond the population the sample actually supports

State the frame the sample was drawn from and where it diverges from the population you want to talk about: time window, platform, geography, opt-in. If a group is excluded from the frame, either weight to known margins or narrow the claim rather than quietly extending it.

Questions & practice

How do you determine the appropriate sample size for an evaluation of a new social service intervention?

Medium · statistics and probability

Approach

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

Follow-up

How would you explain this result to someone who does not know statistics?
Which assumption here is most likely to be violated in practice?

Describe a project where you had to balance competing priorities between building a new model and maintaining existing infrastructure.

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Set a baseline first, so any model has something honest to beat.
Frame the prediction: the label, the moment of prediction, and the action it triggers.

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?

Collapse time entries into contiguous staffing spells

Medium · sessionisation · gap and island · vectorisation

From approved delivery time entries (consultant_id, engagement_id, work_date, hours, charge_code, status, with engagement_id not null), build staffing spells. Per consultant and engagement, collapse weeks containing any logged hours into contiguous runs, where three or more consecutive zero-hour weeks end a spell. Output one row per spell with consultant_id, engagement_id, start_week, end_week, active_weeks, gap_weeks and total_hours. Consultants sit on several engagements at once, so spells from different engagements may overlap in time and must not be merged. No Python loop over rows.

Approach

Aggregate to (consultant_id, engagement_id, week_start) with summed hours and keep only weeks with positive hours. The absent weeks are the signal, so materialising zeros here would destroy the thing you are detecting.
Convert week_start into an integer week index, ((week_start - epoch_monday).dt.days // 7), so gap detection is integer subtraction rather than calendar arithmetic that breaks over month and year boundaries.
Sort by [consultant_id, engagement_id, week_index], diff the week index inside each pair, and mark a spell start where the diff is null (first row of the pair) or greater than 3. A diff of 1 is adjacent weeks and a diff of 3 is two empty weeks, which the tolerance permits.
Take spell_id = the cumulative sum of that boolean over the whole frame so ids are globally unique, then a single groupby on [consultant_id, engagement_id, spell_id] yields min and max week, active week count and summed hours; gap_weeks = (end - start + 1) - active_weeks.
Do not deduplicate overlapping spells across engagements. A consultant on two engagements in the same week is the normal case, and that overlap is the fact any capacity or context-switching question needs.

Worked solution (30 min)

Filter to status == 'approved', charge_code == 'client_delivery' and engagement_id.notna(); derive week_start by subtracting the weekday offset from work_date.
wk = df.groupby(['consultant_id','engagement_id','week_start'], as_index=False).hours.sum(); wk = wk[wk.hours > 0]; wk['wi'] = (wk.week_start - pd.Timestamp('1970-01-05')).dt.days // 7.
wk = wk.sort_values(['consultant_id','engagement_id','wi']); d = wk.groupby(['consultant_id','engagement_id']).wi.diff(); wk['new_spell'] = d.isna() | (d > 3); wk['spell_id'] = wk.new_spell.cumsum().
spells = wk.groupby(['consultant_id','engagement_id','spell_id']).agg(start_week=('week_start','min'), end_week=('week_start','max'), active_weeks=('wi','size'), total_hours=('hours','sum'), span=('wi', lambda s: s.max() - s.min() + 1)).reset_index(); gap_weeks = span - active_weeks.
Run the conservation assertion (spell hours sum to input hours) and the toy case below before returning.

Expected result: One row per spell, where a single pair with active week indices [1, 2, 5, 9] produces two spells: weeks 1 to 5 with active_weeks 3 and gap_weeks 2, and week 9 alone with active_weeks 1 and gap_weeks 0.

Verify it yourself

Summed total_hours across spells equals the summed hours of the filtered input, exactly.
Within a pair, spells are disjoint, ordered, and separated by at least three empty weeks.
No spell contains an internal gap longer than two weeks, verified by re-expanding one spell against the week-level frame.
Two consultants with identical week patterns on the same engagement produce two separate spells, not one.

Follow-up

Re-run with a one-week and a four-week tolerance. What happens to the spell count, and which tolerance would you defend to a staffing lead?
Using these spells, how would you measure how many engagements a consultant is split across in a given week, and why is that not just a count of rows?

Write a query using SQL window functions to calculate the running total of service interactions per client over a rolling 6-month period.

Medium · sql and data manipulation

Approach

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

Follow-up

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

Given a complex schema of human services data, how would you join tables to identify cohorts of residents receiving multiple types of care?

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
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 does the query change if the join becomes one-to-many?
What breaks if events arrive late or out of order?

Rebuild proposal stage timeline from a mutable event stream

Hard · event streams · window functions · censoring · funnel

fct_proposal holds proposal_id, client_id, expected_value_usd, stage, is_competitive, created_at and decided_at, and its stage column is overwritten in place. fct_proposal_stage_event holds stage_event_id, proposal_id, from_stage, to_stage and occurred_at. For proposals created in the trailing year, rebuild the ordered stage timeline with days spent in each stage, flag durations still open at the snapshot as censored, and build a funnel of distinct proposals and expected value ever reaching qualifying, scoping, submitted and decided. Reconcile each timeline's terminal stage against fct_proposal.stage and report disagreements.

Approach

Order events per proposal and derive each stage's exit with LEAD(occurred_at) OVER (PARTITION BY proposal_id ORDER BY occurred_at, stage_event_id). The tiebreak on the event key matters because two transitions can share a second. LEAD returns NULL on the last event of every proposal, decided or not, so a NULL lead marks the end of the stream and nothing more — it is not by itself a censoring indicator.
Close that final interval by state rather than by the NULL. Where decided_at IS NOT NULL, close it at decided_at with is_censored = FALSE; where decided_at IS NULL, close it at snapshot_date with is_censored = TRUE. Only the second group is censored, and marking it lets a consumer choose Kaplan-Meier or a restricted mean. A median computed over completed transitions alone is biased downward, because at any snapshot the still-open records are disproportionately the slow ones, and the bias grows exactly when the pipeline is slowing. A decided_at earlier than the last event's occurred_at yields a negative final interval: report those proposals, do not clamp them.
Handle re-entry. A proposal can go scoping to submitted and back to scoping, so per-stage entries outnumber proposals. Build the funnel on COUNT(DISTINCT proposal_id) for 'ever reached stage X', and keep the per-entry rows separate for duration work.
Build the funnel from the reconstructed timeline, never from fct_proposal.stage. That column records only where a proposal came to rest, so every proposal that passed through scoping and moved on is invisible in it and the funnel's middle collapses.
Reconcile the last to_stage per proposal against fct_proposal.stage. Disagreements mean events are missing or a row was edited outside the event path, and the size of that set is the ceiling on how far anything else here can be trusted.
If asked for win rate by value, exclude stage IN ('withdrawn','no_decision') from both numerator and denominator and filter is_competitive = TRUE. Those outcomes are not losses, they are not missing at random, and sole-sourced follow-on work inflates the rate.

Worked solution (45 min)

Count events per proposal and check for proposals with zero events; those exist only in the mutable table and must be reported, not dropped.
Add the LEAD-based exit timestamp with the event-key tiebreak, then close each final interval with COALESCE(lead_occurred_at, decided_at, snapshot_date) and set is_censored only where decided_at IS NULL, so days_in_stage is defined on every row.
Aggregate to the funnel with COUNT(DISTINCT proposal_id) and SUM of expected_value_usd per 'ever reached' stage.
Compute the median days in stage over completed transitions only, and report the censored count beside it so the reader sees what the median excludes.
Take the last to_stage per proposal with a window function and diff it against fct_proposal.stage, returning the mismatched proposal_ids.

Expected result: Per (proposal_id, stage entry) rows carrying entered_at, exited_at (the next event's occurred_at; decided_at on a decided proposal's final interval; NULL while still open), days_in_stage and is_censored; a funnel table of distinct proposals and summed expected value ever reaching each stage; a median per stage over completed transitions with its censored count; and a reconciliation list of proposals whose terminal event disagrees with the current stage column.

Verify it yourself

For a proposal with a decision, the sum of days_in_stage across its entries equals decided_at minus its first event date, to within a day.
Distinct proposals at the funnel's first stage equals the cohort size, and each later stage is less than or equal to the one before it.
The is_censored rows are exactly the final-event rows of proposals with decided_at IS NULL, and their count equals the number of cohort proposals with no decided_at. Counting NULL leads instead returns one row per proposal — the entire cohort — since LEAD is NULL on every proposal's last event, so the two counts agree only in the degenerate case where nothing has been decided.
Proposals with more than one entry into the same stage are visible as re-entries, and the funnel does not double count them.

Follow-up

Estimate median proposal cycle time with the censored proposals included. What does the estimator need from your output?
Two events for one proposal share occurred_at to the second. How does your ordering resolve it, and how would you detect that it happened?
Which fields on a won proposal are populated only after the decision, and why does that rule them out of a win-rate model?

Explain the trade-offs between different methods for handling missing data in a longitudinal study.

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

Follow-up

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

How do you balance the need for high-quality, reusable software with the urgency of delivering insights for time-sensitive public policy decisions?

Medium · product sense and metrics

Approach

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

If we notice a sudden drop in a key performance metric for our child protective services dashboard, how would you go about diagnosing the cause?

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

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 would you design a metric to measure the success of a new intervention for homeless housing?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
State what result would change your recommendation, so the answer is falsifiable.
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?
What would you do if the primary metric and the guardrail moved in opposite directions?

What are the most common experimentation pitfalls you have encountered when running pilot programs in a field setting?

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?

How would you design an experiment to test the efficacy of a new mental health outreach program?

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

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

Diagnose a sample ratio mismatch before reading the result

Easy · sample ratio mismatch · randomisation integrity · chi-square test

A staffing-recommendation tool was randomised one to one across 840 billable-role consultants, with assignment drawn from a hash of consultant_id, so 420 sit in each arm and the assigned arm is recoverable for every consultant. The analysis table, built from exposure logs, holds 300 treatment and 420 control consultants: 720 rows against 840 assignments. Treatment shows a 1.9-point utilisation lift at p = 0.01, and the sponsor wants it in the deck tomorrow. Run the right test on the allocation, quantify how far the split sits from 1:1, decide whether the lift can be reported, and name the two most likely mechanisms given that the table came from exposure logs rather than the assignment table.

Approach

Test the split before touching the outcome. Chi-square goodness of fit on the 720 retained rows against an expected 360/360 gives chi-square = 2 * (60^2 / 360) = 20.0 on 1 degree of freedom, p around 7.8e-6. The equivalent binomial z is 60 / sqrt(720 * 0.25) = 4.47, and z^2 = 20.0 reproduces the chi-square.
Then run the sharper test that a recoverable assignment makes available: per-arm retention against the known 420. Control retains 420/420 = 100%, treatment retains 300/420 = 71.4%. That 28.6-point gap says more than the split test does, because it names which arm lost rows and how many, rather than only that the ratio is off.
Use a much stricter alpha for the allocation test than for the outcome, commonly 0.0005. The split test protects no multiplicity budget and should fire only on real breakage; 7.8e-6 clears even that bar by a factor of about sixty.
Treat the outcome as unreportable rather than as interesting-with-a-caveat. Under a sample ratio mismatch the analysis population is no longer the randomised population, so the comparison is observational and the lift carries no protection from confounding.
Trace the mechanism from the table's provenance, starting from the fact that the deficit is one-sided. A symmetric filter, such as an inner join to dim_consultant on is_current = TRUE, drops rows from both arms at similar rates: it shrinks n without moving the ratio, so it cannot be the cause here and should be ruled out rather than listed. Two one-sided candidates fit. First, the exposure log records a treatment consultant only once they open the tool, so non-openers vanish from treatment and the arm is silently conditioned on engagement, which is a post-assignment filter. Second, the arms are populated by different loggers: treatment exposure is written by the tool's own client-side beacon while control exposure is written server-side by the staffing screen, so the arms differ in logging coverage rather than in behaviour.
Rebuild on intention to treat from the assignment table, so all 840 assigned consultants sit in their assigned arm whether or not they opened the tool, then re-run the split test before re-running the outcome.

Worked solution (15 min)

Reconcile row counts first: the assignment table puts 420 in each arm, the analysis table holds 300 and 420, so 120 rows are missing and every one of them is on the treatment side.
Expected under 1:1 on the 720 retained rows: 360 per arm, observed deviation 60 in each direction.
chi-square = (300 - 360)^2 / 360 + (420 - 360)^2 / 360 = 10 + 10 = 20.0 on 1 degree of freedom.
Convert: z = sqrt(20.0) = 4.47, two-sided p = 2 * (1 - Phi(4.47)) = 7.8e-6.
Observed treatment share 300/720 = 0.4167; 95% interval 0.4167 +/- 1.96 * sqrt(0.4167 * 0.5833 / 720) = 0.4167 +/- 0.0360, so [0.381, 0.453], excluding 0.5.
Retention by arm against assignment: control 420/420 = 1.000, treatment 300/420 = 0.714. The loss is entirely one-sided, which is the signature of a treatment-side filter rather than of noise.

Expected result: chi-square = 20.0 on 1 degree of freedom, p around 7.8e-6, treatment share 0.4167 with a 95% interval of roughly [0.381, 0.453] that excludes 0.5, and a one-sided retention gap of 100% control against 71.4% treatment. The allocation is broken and the utilisation result is not reportable until the analysis table is rebuilt from assignment rather than exposure.

Verify it yourself

The chi-square and the binomial z must agree: z^2 = 4.47^2 = 20.0 = chi-square.
Arm counts must be reconciled against the 840 randomised consultants, not only against each other. They sum to 720, so 120 rows were dropped, and the drop sitting entirely on one arm is what makes it a filter.
A candidate mechanism must be capable of producing a one-sided deficit. Any filter applying equally to both arms shrinks n while leaving the ratio near 1:1, so it fails this test and is not the explanation.
Re-running the split test on the assignment table rather than the exposure table should come back non-significant if the hash itself is sound.

Follow-up

Suppose the analysis table had held all 840 consultants, split 402 to 438. Run the same test and tell me whether you would report the result.
If non-openers are themselves a treatment effect, because the tool is bad enough that people ignore it, what does intention to treat estimate, and what would you need to estimate the effect on compliers?
How would you monitor for sample ratio mismatch continuously without creating a peeking problem on the outcome?

Days sales outstanding improved while collections got worse

Medium · right censoring · survival analysis · cash

A cash dashboard reports mean days-to-pay as AVG(paid_at - issued_at) over fct_invoice_line where status = 'paid'. It improved from 47 to 39 days last quarter. The finance lead insists collections are worse, and invoice volume rose about 30% after a large batch issued early in the quarter. Using invoice_line_id, engagement_id, issued_at, due_date, paid_at, status and amount_usd, explain the contradiction and deliver the estimate you would report instead, with its assumptions.

Approach

Name the bias precisely: at any snapshot, the set of paid invoices over-represents fast payers, because slow ones have not finished yet. Unpaid, partially paid and disputed lines are right-censored observations, not absent ones, and averaging over completions alone is biased downward.
Explain why the bias grew: a large young cohort adds many invoices whose slow half cannot yet appear in the paid set, so a volume increase alone pushes the naive mean down even with unchanged payment behaviour.
Build the censored dataset: event time = paid_at - issued_at for paid lines; censoring time = snapshot_date - issued_at for status IN ('issued','partially_paid','disputed'). Confirm no placeholder dates were substituted for NULL paid_at.
Estimate with Kaplan-Meier by issue-month cohort and report both the median and the restricted mean to a fixed horizon such as 90 days, so cohorts of different maturity are compared over the same span. State the independent-censoring assumption: censoring here is administrative, driven by issue date rather than by payment behaviour, which is what makes it defensible.
Cross-check with a metric that needs no model: share of each cohort's invoiced amount collected by day 30, 60 and 90. If the recent cohort's curve sits below prior cohorts at the same age, collections really did deteriorate.
Report amount-weighted as well as count-weighted, since one large disputed invoice moves cash without moving the count.

Follow-up

Disputed invoices may never pay at all. Does that break the censoring assumption, and how would you handle it?
How would you turn this into a weekly operational report that cannot be gamed by issuing more invoices?
Proposal cycle time has the same structure. What is the equivalent fix there?

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.

Being wrong in public

A number you shipped turned out to be wrong, and someone had already acted on it. That is one of the most useful stories a data person can carry. What is being scored is how fast you noticed, who you told first, and what you changed in the process so the same class of error could not repeat quietly.

Tell me about a time you identified a flaw in a previous analysis or model—how did you address it?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Scoping a one-line question about practice profitability in twenty minutes

Medium · scoping · metric definition · exclusions

A practice leader asks, in one line, whether the data_and_ai engagements are profitable. You have fct_engagement, fct_time_entry and fct_invoice_line, and twenty minutes before they travel. Some engagements are retainer or outcome_based, so contracted_hours is NULL; some invoices are unpaid; pursuit hours sit on charge_code = 'client_pursuit' with engagement_id NULL. Produce the scoping conversation: the questions you ask, the metric you commit to in writing, the exclusions you will have to make, and what you deliver by when.

Approach

Name the probe: whether you convert an ambiguous one-liner into a decision with a metric attached, or start querying and return a number nobody can act on.
Ask what changes based on the answer. Repricing the rate card, changing the staffing pyramid, and deciding whether to keep selling fixed_fee are three different questions, and they need different cuts of the same data. Pick the one the leader actually has authority over.
Commit the metric in writing on the spot: fees plus milestone plus credit_note lines from fct_invoice_line as the revenue side, the sum of hours times cost_rate_usd over all approved entries, billable and non-billable alike, as the cost side, inception to date, reported separately by pricing_model.
List what you will not answer and why: overrun on retainer and outcome_based work, because contracted_hours is NULL there and needs an explicit predicate rather than a silent NULL drop; cash collected, because unpaid invoices are censored; and pursuit cost, which has no engagement_id and must either be allocated on a stated rule or reported as a separate line.
Commit two deliverables with dates rather than one vague one: the stratified margin table within two days, the mix decomposition within a week. Name the single assumption that, if wrong, flips the conclusion.

Follow-up

They insist on one number for a partner meeting. Which single number do you give, and what sentence goes with it?
If you had to allocate client_pursuit hours to engagements, what rule would you use and how would you show the answer is not sensitive to it?

Three requests, one week, and the one you defer

Medium · prioritisation · stakeholder management · scope reduction

Three requests arrive in one week. Finance needs days sales outstanding recomputed for a board meeting in four days. A partner wants a proposal win-rate model for a pursuit review in three weeks. Delivery wants a staffing forecast, with no date attached. You have one week of your own capacity and no analyst. Write the prioritisation you send back, the request you defer, and the message you send to the person whose request you defer.

Approach

Name the probe: whether you prioritise on decision dates and reversibility, or on who asked most forcefully.
Score each request on four things: what decision it changes, the date that decision is made, the cost of being late, and the cost of being wrong. A board figure has a hard date and a high cost of being wrong; a forecast with no date has neither.
Reduce scope rather than dropping work. The win-rate model is the largest piece and the easiest to get wrong, because features written after the decision, such as engagement_id and revised pricing, leak the label, and because withdrawn and no_decision proposals are not missing at random. A two-day descriptive win-rate cut by is_competitive and loss_reason answers most of what a pursuit review needs, with the model scoped separately.
Defer explicitly, with a date and a smaller substitute, rather than leaving a request to decay quietly. Silence is read as agreement and then as failure.
Put the trade-off in writing so it can be overturned by someone with more context than you have, and say what you would drop if the deferred request becomes urgent.

Follow-up

The partner escalates to the practice lead. What do you change, and what do you refuse to change?
What evidence would make you drop the finance work instead?