Interview guides · Augment Professional Services

Augment Professional Services Data Scientist Interview Questions & Guide 2026

Data scientist prep for Augment Professional Services candidates: drill A/B design, power and minimum detectable effect on original exercises.

The 60-second brief

As a Data Scientist at Augment Professional Services, you are not just a technical contributor; you are a strategic advisor and a builder of high-impact AI and machine learning solutions. This role sits at the intersection of advanced analytics, enterprise consulting, and scalable engineering. You will be tasked with transforming complex client data into actionable insights, automated pipelines, and predictive models that drive measurable business value.

Nearly every loop contains a round whose deliverable is a recommendation to someone non-technical. Practise stating a conclusion, the confidence attached to it, and the cost of being wrong in each direction, because that triple is the artifact being graded.

Focus areas

Separate bookings, recognised revenue and collected cash
Segment margin by pricing model before comparing
Cluster inference at the account, not the engagement

Overview

Because Augment Professional Services partners with diverse enterprises to solve their most critical challenges, your impact will span across multiple domains and industries. You will frequently engage with executive stakeholders to define problem spaces, architect end-to-end data strategies, and lead technical teams in delivering robust solutions. Whether you are optimizing supply chain logistics, building predictive maintenance models, or designing natural language processing applications, your work directly influences the operational success of the company's clients.

At the Principal level, which is a key focus for the company's Houston-based teams, the expectations are even higher. You will be expected to operate with significant autonomy, mentoring junior data scientists, and establishing best practices for MLOps and model governance. The environment is fast-paced, intellectually demanding, and highly collaborative, offering you the opportunity to shape the future of AI adoption across major enterprise organizations.

The Augment Professional Services interview process

Augment Professional Services candidates report 7 rounds · ≈ 4-6 weeks. The stages below are what candidates describe, not a published process.

Recruiter Screen

Whoever runs this call is usually not a practitioner. They take notes, and a hiring manager skims those notes later, so the real question is whether your work survives being written down by someone outside the field. Test every project sentence against that: could a non-specialist repeat it correctly without knowing what a propensity score is? Carry a plain-language version of each project and one reason you want this particular role that you could not copy onto another application. Vagueness at this stage reads as inexperience, even when the underlying work was genuinely deep.

What to demonstrate

Whether a non-specialist can restate your projects accurately, since their paraphrase is what reaches the hiring manager
Whether your reason for wanting the role points at the work itself rather than the company's reputation
Whether your language signals the level being screened for: what you decided yourself versus what you were handed

How to prepare

Write a two-sentence, jargon-free version of each major project: the question nobody could answer, and the decision your work changed. Read it to someone outside data and have them repeat it back
Point your 'why this role' answer at something concrete in the job description or the product surface you would be working on, and keep it to two sentences
Have two questions ready about measurement: which metric the team is held to, and who acts on an analysis once it lands

Technical Phone 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

Onsite/Virtual Loop

A day of back-to-back interviews samples your floor, not your ceiling. Four hours in, the habits that carry a good answer are the first to go: restating the question before solving it, asking what the data would have to look like, checking a number before quoting it. What the day decides is whether the tired version of you is still someone to leave alone with an ambiguous problem. The round that sinks a candidate is usually not the hardest one. It is the one immediately after the round that went badly.

What to demonstrate

Whether the late rounds get the same clarifying questions as the first one, or whether you start answering immediately to save effort
Whether a weak answer stays in the room it happened in, instead of following you into the next conversation as apology or distraction
Whether the quality of your questions holds up, since fatigue removes curiosity about the problem before it removes knowledge of the method

How to prepare

Rehearse the length, not just the content: book four mock interviews of different types in one afternoon with short gaps, because the one you need to observe is the fourth
Put the two or three questions you ask at the start of any problem on a card in front of you, so that under fatigue it is a habit you run rather than a decision you make
Decide in advance what the gap between rooms is for: water, one line of notes on anything you promised to follow up, and an explicit close on the round that just ended so it does not travel
Prepare a different closing question for each interviewer, so the end of a long day does not produce the same one four times

Deep-Dive Technical Interview

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

Coding and Data Manipulation

A handful of shapes account for most of what gets asked in this format: a ranking or deduplication inside groups, a running or rolling total, a period-over-period comparison, and a cohort tracked forward over time. Recognising the shape quickly is most of the speed here; deriving it from scratch while a clock runs is where the time goes. Know that a window function keeps every row while a GROUP BY collapses them, and know which one the question needs. If the exercise is in Python instead of SQL, the same shapes arrive as groupby with transform, shift and merge, and the same grain mistakes are available.

What to demonstrate

Whether you reach the right construct without a detour, such as ROW_NUMBER over a partition to deduplicate instead of a self-join against a MAX subquery
Whether you know what your window frame actually is, since adding ORDER BY inside OVER changes the default frame and silently changes a running total
Whether the thing runs. A near-miss that throws an error scores below a plainer query that returns the right rows.

How to prepare

Write each of the four shapes once from memory against a small schema and keep the working version somewhere you will reread it: dedupe with ROW_NUMBER, a running total, a month-over-month change with LAG, and a retention table
Compute one running total twice on data with tied timestamps, once on the default frame and once with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and look at where the two disagree
If Python is on the table, rebuild the dedupe and the running total with groupby and cumsum, then assert the two implementations return identical rows

Behavioral Interview

Most of the weight in this round sits on the disagreement questions. Data work routinely produces an answer someone senior did not want, and the interviewer is trying to learn what you do in that hour. Both failure modes are common: folding as soon as a director pushes back, and treating the pushback as ignorance to be corrected with a better chart. A strong answer usually contains a specific thing the other person knew that you did not, and describes how you found out whether it changed the conclusion.

What to demonstrate

Whether you can state the other side's argument accurately before you explain why you disagreed
What you treated as evidence during the disagreement, such as a rerun under their assumption or a holdout check, rather than persuasion technique
Whether you distinguish being overruled from being wrong, and can give an example of each

How to prepare

Write out one disagreement where you turned out to be wrong, and say what in the data misled you. Candidates prepare the story where they were right, and the follow-up asks for the other one.
For your main disagreement story, be ready to say what result would have made you drop your position. If no such result exists, you were not arguing from the data.
Practise stating the opposing position out loud in one sentence the stakeholder would accept, then continue the story.

Where candidates lose points

Treating accounts as independent observations

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

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 a dozen metrics with no multiplicity control

Nominate one primary metric before launch and treat the rest as guardrails or exploratory, with Bonferroni or Benjamini-Hochberg applied when you intend to make claims from them. Twenty independent tests at 0.05 under the null produce at least one false positive about 64 percent of the time.

Ending an analysis without a recommendation or next step

Close with what you would do and what would change your mind, stated as a condition you can check later. If the evidence is genuinely inconclusive, recommend the specific next measurement and say what it costs in time or exposure.

Questions & practice

Implement a function to perform stratified sampling on a highly imbalanced dataset.

Medium · statistics and probability

Approach

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

Follow-up

Which assumption here is most likely to be violated in practice?
What sample size would you need to detect an effect half this size?

Walk me through the mathematical formulation of a Gradient Boosting Machine. How does it differ from AdaBoost?

Medium · statistics and probability

Approach

Sanity-check the answer against a simple bound or a simulated case.
Write down the assumption the method needs before you use the method.
Translate the result into the decision it informs, in one plain sentence.

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 the vanishing gradient problem in deep neural networks and discuss methods to mitigate it.

Medium · machine learning and modelling

Approach

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.
Check what information would not exist at prediction time, and exclude it.

Follow-up

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

Attach effective-dated cost rates without merge_asof

Medium · type-2 scd · interval join · searchsorted

time_entries (consultant_id, work_date, hours) has roughly two million rows. dim_consultant is a type-2 dimension (consultant_sk, consultant_id, standard_cost_rate_usd, effective_from, effective_to nullable, is_current) with a handful of rows per consultant. Attach the cost rate in force on each entry's work_date. You may not use pandas.merge_asof and you may not materialise the consultant_id cartesian product. Treat intervals as half-open, meaning [effective_from, effective_to), with a NULL effective_to open-ended. Return unmatched entries separately with a reason rather than dropping them.

Approach

Fix the semantics before writing code: half-open intervals are what make the join single-valued on the day a rate changes. Compare work_date at start-of-day in the reporting timezone against effective_from in the same zone, and write that comparison down as a stated assumption.
Sort dim_consultant by (consultant_id, effective_from) and assert non-overlap inside each consultant, since searchsorted only returns the right answer if the intervals are ordered and disjoint.
Build the composite sort key so it cannot overflow: dense-code the consultant ids through one shared sorted category mapping, express time as whole days since a floor date earlier than every date in both frames, and take SCALE as a power of two above the largest day offset, giving key = code * SCALE + offset. Assert code.max() * SCALE + off.max() < np.iinfo(np.int64).max before using it. Multiplying a raw consultant_id by a scale larger than a nanosecond timestamp (about 1.75e18 for present-day dates) leaves an id budget of only int64_max / SCALE, roughly five consultants, before the product passes 9.22e18; beyond that numpy wraps silently, and it does not even wrap consistently negative (with SCALE = 2e18 an id of 12 lands back on a large positive key), so the order breaks without a sign flip to catch it.
With a key that fits, np.searchsorted(dim_key, entry_key, side='right') - 1 is the index of the last interval starting at or before work_date, in O((n + k) log k) and with no cartesian product. SCALE only has to exceed the span of the offsets, never an absolute epoch value, so if a rate can change intraday, switch the offset unit to seconds (a 2^31 scale covers 68 years) and re-run the same assertion.
Validate the candidate rather than trust it: the match holds only if work_date < effective_to or effective_to is NULL. Everything else is an exception, either work logged before the first dimension row or a genuine gap in the SCD.
Return matched rows carrying consultant_sk and the rate, plus an exceptions frame with counts by reason, so a gap in the dimension surfaces as a number rather than as quietly missing cost.

Worked solution (30 min)

dim = dim.sort_values(['consultant_id','effective_from']); assert within each consultant that effective_from is strictly increasing and effective_to.fillna(max) >= the next effective_from.
cats = np.union1d(time_entries.consultant_id.unique(), dim.consultant_id.unique()), which is sorted, so code = pd.Categorical(ids, categories=cats).codes.astype('int64') preserves consultant_id order and the sort above still holds; an id absent from the dimension codes to -1 instead of matching something else.
floor = min(work_date.min(), effective_from.min()).normalize(); off = (ts.dt.normalize() - floor).dt.days.to_numpy() in both frames; SCALE = 1 << 20 (1,048,576 days, about 2871 years); assert off.min() >= 0, off.max() < SCALE and code.max() * SCALE + off.max() < np.iinfo(np.int64).max; key = code * SCALE + off.
idx = np.searchsorted(dim_key, entry_key, side='right') - 1; entries whose code is -1 are exceptions with reason 'consultant_not_in_dimension'; rows with idx < 0, or whose matched dimension code differs from the entry's code, are exceptions with reason 'no_interval_before_work_date'.
Check the upper bound: keep rows where effective_to is NaT or work_date < effective_to; the rest are exceptions with reason 'gap_in_scd'.
Brute-force 200 random entries with a straight boolean filter over that consultant's rows and diff the rates against the vectorised answer.

Expected result: Matched rows equal to the input count minus exceptions, each carrying exactly one consultant_sk and standard_cost_rate_usd, plus an exceptions frame grouped by reason. No input row is duplicated and none is silently dropped.

Verify it yourself

len(matched) + len(exceptions) == len(time_entries), which is the property an inner join would break.
The 200-row brute-force sample agrees on every rate.
An entry whose work_date equals an effective_from takes the new rate, not the old one, exactly once.
Per-consultant total cost changes by (new_rate - old_rate) * hours after the change date, and by nothing before it.
np.diff(dim_key) is strictly positive, asserted rather than assumed: this is the only cheap guard against a key that has wrapped, since an overflowed product can land back on a positive value and a non-negativity check alone would pass it. Renumbering the consultant_ids (say to 4000001..4000050) must not change a single attached rate, which is the fixture a raw-id-times-nanoseconds key fails once ids run past int64_max / SCALE.

Follow-up

Rates were backdated a quarter after the fact. How do you keep last quarter's published cost from moving when someone reruns the report?
How does the implementation change if effective_to is inclusive rather than exclusive, and what breaks on the boundary day if you get that wrong?

Write a SQL query to calculate the 30-day rolling retention rate of users based on their login history.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
State the window function and its partition and ordering out loud before writing it.
Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

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

Write a Python function from scratch to calculate the K-Nearest Neighbors for a given dataset without using scikit-learn.

Medium · sql and data manipulation

Approach

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

Follow-up

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

Given a table of user transactions, write a SQL query to find the top 3 users by transaction volume for each month in the past year.

Medium · sql and data manipulation

Approach

Check whether any join is one-to-many before aggregating, or the sums inflate.
Compute rates by summing numerator and denominator separately, never by averaging rates.
State the window function and its partition and ordering out loud before writing it.

Follow-up

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

Deduplicate to the latest issued invoice line per engagement

Easy · window functions · dedup · determinism

fct_invoice_line holds invoice_line_id, invoice_id, engagement_id, line_type, period_end_date, amount_usd, issued_at and status; draft lines have issued_at NULL. Return exactly one row per engagement_id, its most recently issued line, with invoice_line_id, line_type, amount_usd and issued_at. Two lines on the same engagement can share an issued_at value to the second, so the result must be identical on every run against unchanged data. Write it with a window function, then say in one sentence when DISTINCT ON would be the better choice.

Approach

Filter to issued lines (issued_at IS NOT NULL, status <> 'draft') before ranking. Ranking first and filtering afterwards can leave an engagement whose top-ranked row was a draft with no row at all in the output.
Rank with ROW_NUMBER() OVER (PARTITION BY engagement_id ORDER BY issued_at DESC, invoice_line_id DESC). The surrogate key as the final ORDER BY term is what makes ties resolve the same way on every execution; without it the planner is free to return either tied row.
Wrap the ranked query in a CTE or subquery and filter rn = 1, since a window function cannot appear in the WHERE clause of the query that computes it.
Name why RANK() is wrong here: on tied issued_at it assigns 1 to both rows, so the result carries two rows for one engagement and any downstream SUM double counts.
Compare against DISTINCT ON (engagement_id) ... ORDER BY engagement_id, issued_at DESC, invoice_line_id DESC: same answer, one sort, usually cheaper, but the window form generalises the moment you also want rn <= 3 or a second ranking in the same pass.

Worked solution (15 min)

Count distinct engagement_id over the filtered (issued, non-draft) line set. This is your target row count.
Write the ROW_NUMBER CTE with both ORDER BY terms and select rn = 1.
Assert the contract with GROUP BY engagement_id HAVING COUNT(*) > 1 over the result; it must return nothing.
Deliberately construct or find an engagement with two lines sharing issued_at and confirm exactly one survives.
Rewrite with DISTINCT ON and diff the two result sets row by row.

Expected result: One row per engagement that has at least one non-draft issued line. Row count equals COUNT(DISTINCT engagement_id) over that filtered set. Engagements holding only draft lines are absent, which is correct here; listing them is an anti-join, not a dedup.

Verify it yourself

Result row count equals COUNT(DISTINCT engagement_id) on the filtered input.
GROUP BY engagement_id HAVING COUNT(*) > 1 over the result returns zero rows.
Two consecutive runs produce byte-identical output when ordered by engagement_id.
The DISTINCT ON rewrite returns the same invoice_line_id for every engagement.

Follow-up

Now return the latest line per engagement per line_type. What changes in the PARTITION BY, and what does that do to the row count?
Return the latest and second-latest line together. Which of the two constructs survives that requirement?
issued_at is TIMESTAMPTZ and finance reports on a single local calendar. Where can 'latest' flip between the two conventions?

Describe a project where you had to balance technical perfection with a tight client deadline. What trade-offs did you make?

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you optimize a Pandas script that is currently running out of memory when processing a 5GB CSV file?

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.
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?
What would you do if the primary metric and the guardrail moved in opposite directions?

Walk me through how you would architect a batch-scoring pipeline that needs to process 10 terabytes of data nightly.

Medium

Approach

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

Follow-up

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

Choose a randomisation unit when consultants are a shared pool

Medium · interference · randomisation unit · switchback · sutva

A staffing optimiser proposes consultant allocations to engagements. The proposal on the table is to randomise at the engagement: half of new engagements get optimiser-suggested staffing, half go through the current manual process. The claimed benefit is lower time to first billable hour, measured from fct_engagement.start_date to the earliest fct_time_entry work_date with is_billable = TRUE. Every engagement in a practice area draws from the same capacity-constrained consultant pool. Explain what the engagement-level estimate would actually measure, propose a design that measures what leadership wants, and state the price your design pays.

Approach

Name the violation. SUTVA requires one engagement's outcome to be unaffected by another's assignment, and a fixed consultant pool makes that false by construction: a consultant the optimiser frees is staffed onto a control engagement, so treatment improves and control degrades from the same act.
Say what the biased estimate is. The engagement-level contrast measures displacement, treatment against a control that treatment has actively worsened, so it overstates the firm-level effect. In the limiting case where the optimiser only reorders a fixed queue of consultant-weeks, the firm-level effect is zero while the engagement-level contrast is large and significant.
Move the randomisation unit up to the level that contains the contention, the staffing pool, which here is practice_area crossed with home_region. Units then share no consultants and the interference is internalised. The price is that five practice areas by four regions yields at most 20 clusters, which is small-cluster inference territory.
Or run a switchback inside each pool: randomise each weekly staffing cycle to optimiser or manual so every pool is its own control, turning 20 clusters into 20 pools times many cycles. The price is carryover, since allocations made in a treated week constrain availability in the following control week, so you discard a burn-in window at each switch and you cannot measure any effect whose horizon exceeds the switch interval.
Analyse at the level of randomisation: pool-week means, pool and calendar-week fixed effects, standard errors clustered at pool, and a wild cluster bootstrap rather than the plain cluster-robust sandwich at 20 clusters.

Worked solution (30 min)

Write potential outcomes against the full assignment vector, Y_i(z) rather than Y_i(z_i), and show the dependence on z_j for any j sharing the pool.
Work the fixed-pool case: if the optimiser reorders a conserved queue of consultant-weeks, the sum of days-to-staff across engagements is unchanged, so the pool-level effect is zero while treated engagements are faster by exactly the amount control engagements are slower.
Enumerate candidate units, engagement, account, partner, pool, pool-week, and for each state whether it contains the contention and how many independent units it yields.
Select a pool-week switchback with the cycle length set to the staffing-meeting cadence, a one-week burn-in discarded at every switch, and a target of at least 26 usable pool-weeks per pool.
Pre-specify the analysis: pool-week mean days to first billable hour on a treatment indicator with pool and calendar-week fixed effects, clustered at pool, wild bootstrap with Webb six-point weights.

Expected result: A design memo that rejects engagement-level randomisation on interference grounds, adopts a pool-week switchback over practice_area by home_region pools with a stated burn-in and calendar-week fixed effects, and states plainly that the design cannot detect effects whose horizon exceeds the switch interval.

Verify it yourself

On simulated reordering-only data, where the true pool-level effect is zero, the proposed estimator must return an effect near zero; a large estimate means the unit is still wrong.
Treated and control pool-weeks should have similar counts of engagement starts and similar practice-area mix, since randomisation runs over time rather than over units.
The independent-unit count behind the standard error must be the number of pools, not the number of pool-weeks, unless within-pool serial correlation has been modelled explicitly.

Follow-up

How long a burn-in would you discard after each switch, and how would you estimate that length from data rather than assert it?
Time to first billable hour is right-censored for engagements not yet staffed at the analysis date. How does that interact with a switchback?
If the optimiser's benefit is pure reallocation within a fixed pool, what outcome would show a genuine firm-level gain?

Utilisation fell three straight weeks; test entry lag first

Easy · late-arriving data · utilisation · reporting cutoff

A weekly billable-utilisation dashboard keyed on fct_time_entry.work_date shows three consecutive declines, roughly 74% to 66%. Billable headcount, the leave calendar and the holiday calendar are unchanged. You have fct_time_entry (consultant_id, work_date, entered_at, approved_at, hours, is_billable, charge_code, status) and dim_consultant (consultant_id, fte_fraction, is_billable_role, hire_date, termination_date). Decide whether the decline is real or an artefact of when timesheets are entered. Deliver a corrected series, the reporting cutoff you would adopt, and the evidence for that cutoff.

Approach

Freeze the denominator before touching the numerator: available hours = scheduled workdays x 8 x fte_fraction, minus approved leave hours and region public holidays, prorated for hire_date and termination_date inside the week. Confirm it is flat across the three weeks, so any movement must be numerator.
Measure the backfill curve instead of guessing it: for each work_date D in a mature window, compute the share of that day's eventual hours whose entered_at <= D + k, for k = 1..45. Read off k*, the lag at which the curve reaches about 99%.
Restate at equal maturity: recompute every week counting only entries with (entered_at::date - work_date) <= k*, and suppress any week younger than k* days. Weeks are then comparable because each is observed at the same age.
Check the approval gate separately: the metric counts status = 'approved', so a backlog of 'submitted' or 'rejected' rows reproduces the same shape with no entry lag at all. Plot hours by status per week.
If the equal-maturity series is flat, report an artefact and publish the cutoff; if it still falls, segment by charge_code (client_delivery versus bench, training, internal_project) and by practice_area to locate the real movement.

Follow-up

The backfill tail itself lengthened this quarter. Is that a finding, and what would you do with it?
Leadership needs a number for the current week today. What do you give them, and how do you label it?
How would this analysis change if approval, not entry, were the slow step?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

The project you argued against

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

Tell me about a time you had to deliver a machine learning project that was failing or behind schedule. How did you turn it around?

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 did you decide not to do, and why?
What would you do differently if you ran that project again?

Explaining a censored days-to-pay number on a board slide

Easy · censoring · kaplan-meier · executive communication

A finance lead wants average days to get paid for the last four quarters, for a board slide. In fct_invoice_line, rows with status in ('issued','partially_paid','disputed') have paid_at NULL. The mean of (paid_at - issued_at) over paid invoices is 38 days. A Kaplan-Meier median, right-censoring the open invoices at snapshot_date minus issued_at, is 51 days. You get two sentences and one chart. Explain the number you put on the slide, the gap between the two figures, and what will make that number move next quarter.

Approach

Name the probe: whether you can give a non-technical decision-maker one number, the direction of the error in the alternative, and the reason, without teaching survival analysis.
Explain the mechanism in business language. Invoices that have been paid are disproportionately the ones that pay fast; the slow ones are still open and therefore missing from the 38-day average. The error is one-directional and it grows as collections get worse, which is exactly when the number matters.
Commit to one headline. Either the Kaplan-Meier median, or a restricted mean days-to-pay capped at a fixed horizon such as 90 days, which is easier for a finance audience to audit. Say which you used and why, and do not put both headline numbers on the slide.
Make the chart the survival curve or a simple share-paid-by-day-k curve rather than a bar of averages, because the audience question is really when cash arrives, not a single moment.
State the forward behaviour before you are asked: the figure for a recent quarter will rise or fall as open invoices resolve, so the slide carries the snapshot date and the share of invoices still open.

Follow-up

The exec asks why the number printed on last quarter's slide no longer reproduces. What is your answer, and what would have prevented the question?
How would you report this by account_tier without putting six survival curves on one slide?

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?