Interview guides · Amaris Consulting

Amaris Consulting Data Scientist Interview Questions & Guide 2026

Data scientist prep for Amaris Consulting: practice defining a consulting and client services metric, picking its guardrails and defending the tradeoff.

The 60-second brief

As a Data Scientist at Amaris Consulting, you play a pivotal role in bridging complex mathematical modeling and high-stakes business strategy for global clients. Operating within an independent technology consulting framework, you will drive initiatives ranging from advanced pricing optimization and automated revenue management to master-data intelligence and adaptive rule-discovery pipelines. Your work directly impacts how client organizations process millions of records, automate operational decisions, and scale their digital products with confidence.

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

Focus areas

Correct late-entered timesheets before trending recent weeks
Cluster inference at the account, not the engagement
Compute utilisation against a defended availability denominator

Overview

This position demands a unique blend of technical mastery and client-facing acumen. You will not only architect, build, and optimize large-scale machine learning systems using Python, Spark, and major cloud infrastructure, but you will also translate ambiguous business requirements into robust data science use cases. Whether you are deploying predictive pricing models for retail environments or reconstructing legacy business rules from enterprise databases, your success depends on your ability to build reliable proofs of concept and guide them through industrialization.

The environment at Amaris Consulting is dynamic, international, and deeply collaborative. You will work alongside multidisciplinary teams of data engineers, product stakeholders, and SAP functional leads across more than 60 countries. Expect a fast-paced setting where you are given the autonomy to drive innovation while maintaining rigorous engineering standards. Success in this role requires intellectual curiosity, resilience when facing legacy data constraints, and a clear talent for communicating complex analytical insights to non-technical audiences.

The Amaris Consulting interview process

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

Initial Outreach

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What to demonstrate

Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

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

Technical Case Study

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

What to demonstrate

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

How to prepare

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

Final Presentation

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

Where candidates lose points

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

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

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.

Building features from data that postdates the prediction time

Check every feature against the timestamp at which the model would actually score, and drop anything computed from a window that includes or follows the label event. For a forecasting use case, split train and test by time rather than at random, and split by entity when the same entity recurs.

Defining the cohort on a post-treatment condition

Ask how rows entered the table. Filtering on something that treatment itself influences, such as users who finished onboarding or accounts still active at ninety days, breaks comparability between arms; define the population at an entry point that precedes exposure and keep everyone in it.

Questions & practice

What is the difference between parametric and non-parametric hypothesis testing, and when would you apply each in a production environment?

Medium · statistics and probability

Approach

Translate the result into the decision it informs, in one plain sentence.
Write down the assumption the method needs before you use the method.
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?
Which assumption here is most likely to be violated in practice?

Share an experience where you mentored a junior engineer or helped upskill a teammate on large-scale feature engineering.

Medium · machine learning and modelling

Approach

Say how the offline result would be validated online before it is trusted.
Pick an evaluation metric that matches the cost of each error type, not a default.
Frame the prediction: the label, the moment of prediction, and the action it triggers.

Follow-up

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

Cluster bootstrap a margin change across few, unequal accounts

Hard · cluster bootstrap · few clusters · mix shift

engagements has engagement_id, client_id, pricing_model, fees_usd, cost_usd and period in {pre, post} around a rate-card change. There are about 35 client_ids across roughly 400 engagements, and the top five clients carry a large share of fees. Estimate the pre-to-post change in value-weighted gross margin within pricing_model, and give a 95% interval by resampling whole client_ids with replacement, B = 2000. Write the bootstrap yourself; no resampling library. Report the account-weighted estimate alongside and explain any divergence.

Approach

Write the statistic as a pure function of a dataframe first. Within each pricing_model, margin = (sum(fees) - sum(cost)) / sum(fees) per period; the headline is the fee-weighted average of the within-model differences using fixed pre-period weights, so a shift in the mix of work sold cannot masquerade as a change in delivery.
Resample clusters, not rows: draw 35 client_ids with replacement and concatenate all engagements of each drawn client. A client drawn twice contributes its rows twice under distinct pseudo-ids, which is what preserves within-account correlation instead of averaging it away.
Recompute the statistic on each replicate and take a percentile interval from the 2.5th and 97.5th quantiles. With concentrated fees the replicate distribution is skewed, so a symmetric point plus or minus 1.96 times a standard error is wrong in the tail that matters.
Compute the account-weighted version (mean across clients of each client's margin change) next to the value-weighted one, and attribute the divergence to concentration rather than to noise; if they disagree in sign, that fact is the finding.
Name the regime honestly: at roughly 35 clusters both cluster-robust standard errors and the pairs cluster bootstrap under-cover, so state the fix you would run next, which is a wild cluster bootstrap with Rademacher weights or CR2 with t(G-1) critical values.

Worked solution (40 min)

stat(df): pivot fees and cost by (pricing_model, period), compute per-model margins, difference post minus pre, and weight the differences by each model's pre-period fee share; return one scalar.
Pre-split the frame into a dict of client_id to its rows, so each replicate is a concat of 35 preselected blocks rather than a boolean filter over 400 rows.
rng = np.random.default_rng(11); for b in range(2000): draw client ids with replacement, concat their blocks, call stat, store; assert the replicate's nunique cluster draw count is 35 every time.
ci = np.quantile(reps, [0.025, 0.975]); compute the account-weighted estimate as the unweighted mean of per-client margin changes over clients present in both periods.
Re-run a row-level bootstrap on the same data for contrast and record both interval widths, then leave-one-client-out to see whether one account drives the point estimate.

Expected result: A point estimate stated in margin points, a percentile interval from 2000 cluster replicates that is wider than the row-level bootstrap interval on the same data, an account-weighted estimate reported beside it, and an explicit note that at 35 clusters the interval still under-covers.

Verify it yourself

Every replicate draws exactly 35 clusters; replicate row counts vary while cluster counts do not.
The row-level bootstrap interval is narrower than the cluster one; quantify the ratio rather than assert it.
The point estimate lies inside the percentile interval, and the interval is asymmetric around it when fees are concentrated.
The within-model differences plus the mix term reconcile to the naive pooled change, which shows the decomposition is complete.

Follow-up

Implement the wild cluster bootstrap and show at what cluster count its p-value separates from the naive one.
One client is 30% of fees. Show the interval with and without that account and say which one you would present, and to whom.
What pre-period check would make you willing to call this a causal effect of the rate card rather than a correlation?

Given a transaction log, write a query to flag users who made three consecutive purchases within a single calendar week.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Check whether any join is one-to-many before aggregating, or the sums inflate.
Say which table is the grain you start from, and join outward from it.

Follow-up

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

Write a query using SQL window functions to calculate the running 30-day average revenue per user across multiple geographical regions.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Check whether any join is one-to-many before aggregating, or the sums inflate.
Say which table is the grain you start from, and join outward from it.

Follow-up

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

Engagement margin without fan-out across two fact tables

Medium · fan-out · join grain · mix shift

fct_engagement holds engagement_id, pricing_model and contract_value_usd. fct_invoice_line holds engagement_id, line_type, amount_usd and status. fct_time_entry holds engagement_id, hours, cost_rate_usd, is_billable and status. For engagements closed last year, return fees (line_type IN ('fees','milestone','credit_note') and status <> 'draft'), delivery cost (SUM(hours * cost_rate_usd) over approved entries, billable and non-billable alike) and gross margin, grouped by pricing_model. A single SELECT joining the engagement to both fact tables and then aggregating produces wrong numbers: say what the error is, then write the correct query.

Approach

Do the arithmetic out loud. An engagement with 40 qualifying invoice lines and 900 approved time entries yields 36,000 rows on the double join: SUM(amount_usd) comes back 900 times too large and SUM(hours * cost_rate_usd) 40 times too large. The two inflation factors are different, so the ratio moves too — per engagement the double join replaces the cost-to-fee ratio with (cost / fees) * (line count / entry count), so a true 30 percent margin reads as 1 - 0.7 * (40 / 900), about 97 percent.
Know which shape of this bug actually survives review. The ratio is preserved only where an engagement carries the same number of qualifying invoice lines as approved time entries, and then only as a row-count-weighted margin, 1 - SUM(cost_i * k_i) / SUM(fees_i * k_i), which still differs from the true pooled margin unless k_i is constant across engagements. So you get either a total absurd enough to tempt someone into a scaling patch, or, on a population where the two counts happen to track each other, a believable-looking ratio sitting on fees and cost that are each wrong by orders of magnitude.
Aggregate each fact table to engagement grain in its own CTE, then LEFT JOIN both onto fct_engagement. One row per engagement then holds by construction rather than by inspection.
Include non-billable delivery hours in cost. Rework, unbilled travel and pursuit time on a live account are real fully-loaded cost, and excluding them flatters fixed-fee work specifically, which is the mix you most need to see clearly.
Treat zero and NULL as different: an engagement with no invoice lines has undefined margin, not zero. Divide by NULLIF(fees, 0) and keep those engagements in a labelled bucket instead of letting an inner join hide them.
Group by pricing_model before reporting any firm-wide figure, and publish the fee mix next to it. Fixed-fee margin falls as hours rise, uncapped time-and-materials margin does not, so a shift in what was sold moves the pooled number with no change in delivery at all.

Worked solution (30 min)

Compute standalone control totals: total fees over the filtered invoice-line set and total cost over the approved time-entry set, both for the closed-engagement population.
Build fees_by_engagement and cost_by_engagement as separate CTEs at engagement grain.
LEFT JOIN both onto fct_engagement, compute margin with NULLIF on the denominator, and group by pricing_model.
Run the naive double join on a single engagement and confirm its fee total equals the true total times that engagement's time-entry count, and its cost total the true cost times its invoice-line count.
Report the fee mix by pricing_model alongside the margin column.

Expected result: One row per pricing_model with total fees, total cost, gross margin as (fees - cost) / fees, and the share of fees that model represents. Engagements with fees of zero or NULL are reported as a separate count rather than folded into the ratio.

Verify it yourself

Total fees across the grouped output equals the standalone control total for fees; the same holds for cost.
Joining the two CTEs and counting rows returns exactly the number of qualifying engagements.
The naive double join on one engagement reproduces both predicted multipliers, confirming the diagnosis rather than assuming it.
Margin computed per pricing_model and then fee-weighted back together equals the pooled margin over the same population.

Follow-up

Decompose a period-over-period margin move into a within-pricing-model component and a between-model mix component, and show the two sum to the total.
Where does not_to_exceed_usd change the time-and-materials picture, and how would you find engagements that crossed it?
Roll this up to the account through parent_client_id. What must the recursive CTE guard against?

How would you design a metric to measure the success of an automated dynamic pricing feature in an e-commerce platform?

Medium · product sense and metrics

Approach

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

Imagine we notice a sudden shift in user purchasing behavior after a UI update. How would you determine if this change affects our core business metrics?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Fix the population and the time window before naming any metric.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

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

A key client wants to launch a new recommendation feed. What metrics would you track to ensure it improves user engagement without cannibalizing core conversion rates?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.

Follow-up

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

A client reports a sudden 15% drop in conversion rates week-over-week. Walk me through your step-by-step framework for metric drop diagnosis.

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

Follow-up

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

Explain how you would account for multiple testing corrections when monitoring dozens of secondary metrics during a live 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.
Decide the analysis before seeing data, including how long it runs and when you look.

Follow-up

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

What common experimentation pitfalls have you encountered, and how do you mitigate sample ratio mismatch (SRM)?

Medium · a/b testing and experimentation

Approach

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

Follow-up

What would you conclude if the result is positive but the test is underpowered?
How would you handle interference between treated and control units?

How do you determine the required sample size and minimum detectable effect for a low-traffic enterprise software feature?

Medium

Approach

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

Follow-up

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

Handle a subgroup scan after a null primary result

Medium · multiple comparisons · false discovery rate · subgroup analysis · interaction test

The pre-registered primary outcome of an account-randomised pilot came back null. The sponsor asks for the breakdown by practice area and region: five practice areas by four regions, twenty cells, from 120 accounts. One cell shows p = 0.04 and the sponsor wants to roll out there. State the family-wise error rate of the unadjusted scan, apply both a family-wise and a false-discovery-rate correction to the reported p, name the test that actually asks whether the effect differs by segment, and give the per-cell detectable effect implied by the account count.

Approach

Quantify the scan. Twenty disjoint cells give near-independent tests, so the family-wise error rate at a nominal 0.05 each is 1 - 0.95^20 = 0.64. Under a true global null, one nominally significant cell is the expected outcome rather than a signal.
Apply Holm when any single cell will be acted on. The smallest p must clear 0.05/20 = 0.0025 for the first rejection, so p = 0.04 misses by a factor of sixteen. Holm dominates plain Bonferroni at the same family-wise level and costs nothing to use, so prefer it.
Apply Benjamini-Hochberg at q = 0.05 if the goal is to generate candidates for a follow-up rather than to act. With 0.04 as the smallest p, the rank-1 threshold is (1/20) * 0.05 = 0.0025, so nothing survives there either. The two procedures agreeing is itself worth stating.
Replace the scan with the test that answers the question asked. Fit one model with treatment, segment main effects and treatment-by-segment interactions, then run a single joint Wald or F test on the 19 interaction terms. That asks whether the effect differs, rather than asking twenty separate times whether it is non-zero somewhere.
Close with power arithmetic, which usually settles the argument faster than the correction does. 120 accounts spread over 20 cells is about six accounts per cell, so the per-cell MDE is roughly sqrt(20) = 4.5 times the overall MDE. A design that could not detect the pooled effect cannot detect a segment-specific one, so the winning cell is far more likely noise than discovery.

Worked solution (20 min)

Unadjusted family-wise error: 1 - 0.95^20 = 0.642.
Holm and Bonferroni threshold for the smallest p: 0.05 / 20 = 0.0025, against the observed 0.04.
Benjamini-Hochberg: sort all twenty p-values, find the largest k with p_(k) <= (k/20) * 0.05; with 0.04 smallest, the rank-1 threshold is 0.0025 and k = 0.
Fit treatment, segment main effects and treatment-by-segment interactions in one model, clustered at client_id, and run the joint test on the 19 interaction terms.
Compute the per-cell MDE as the overall MDE times sqrt(20) and set it beside the effect the winning cell is claiming.

Expected result: Unadjusted family-wise error of 0.64; the p = 0.04 cell clears neither Holm at 0.0025 nor Benjamini-Hochberg at q = 0.05; the defensible statement is that there is no evidence of effect heterogeneity, backed by a joint interaction test on 19 degrees of freedom and a per-cell MDE about 4.5 times the overall one.

Verify it yourself

Benjamini-Hochberg must be at least as permissive as Holm on the same p-vector; if it rejects fewer, the implementation is wrong.
Repeating the twenty-cell scan under a simulated global null should produce at least one p below 0.05 in roughly 64% of replications.
The joint interaction test must carry 19 degrees of freedom, one fewer than the number of cells.

Follow-up

How would you pre-register a subgroup analysis so that a finding inside it could actually be acted on?
The twenty cells differ in size and in treated share. What breaks in the independence assumption behind 1 - 0.95^20?
If the joint interaction test is significant but no individual cell survives correction, what do you report?

One region's delivery cost fell eighteen percent for two weeks

Medium · pipeline failure · data completeness · freshness checks

Cost per delivered engagement-week fell about 18% in one region for two consecutive weeks while fees were flat, so reported margin jumped. Source is fct_time_entry (consultant_id, engagement_id, work_date, entered_at, hours, cost_rate_usd, charge_code, status), loaded nightly from a regional timesheet system, joined to dim_consultant for home_region. Determine whether delivery genuinely got cheaper or rows are missing, name the evidence that distinguishes the two, and specify the automated check you would add.

Approach

Check row counts and coverage before any ratio: entries per workday and distinct consultant_id with at least one entry per workday, for the affected region against its own trailing four weeks and against other regions in the same weeks.
Use the shape of the loss to separate causes. A load failure removes whole days or whole consultants, so distinct-consultant coverage collapses while hours per reporting consultant stays normal. A real change lowers hours per consultant with coverage intact.
Split the cost identity: cost = hours x cost_rate_usd. Compute mean cost_rate_usd by level. If rates went NULL, zero or defaulted to a single value, the fault is in the dimension join, not the source extract.
Look for the loader's fingerprint: max(entered_at) per region per day, gaps aligned to a calendar boundary, and whether the missing dates fall on a particular day of week, which points at a scheduler that skipped a run rather than at delivery behaviour.
Confirm by waiting one cycle: a failed load backfills on re-run and the anomaly disappears from the restated weeks; a genuine change persists. Do not publish until that test has run.
Automate the check that would have caught it: per-region, per-workday assertions on row count and on distinct billable consultants covered, with thresholds derived from trailing medians rather than fixed constants, plus a freshness assertion on max(entered_at).

Follow-up

The same load also feeds utilisation. Why did utilisation not show an equally obvious break?
How do you choose the alert threshold so it survives holidays and genuine low-activity weeks?
If the rows never arrive, what do you do with the two weeks already reported to leadership?

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.

What the behavioural round is actually scoring

Interviewers here are not checking whether you can describe a project. They want the decision you made, why you made it under the information you had, and what changed afterwards that someone else could measure. A story that ends at 'I built a model' has no ending. Say what the model caused, or what you stopped doing because of it.

Tell me about a time when you had to explain a complex machine learning model or pricing recommendation to a non-technical client who was skeptical of the results.

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
Close with what you would do differently, concretely.
State the situation in two sentences and spend the rest on your reasoning.

Follow-up

How did you know the outcome was caused by your change?
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?

Your utilisation dashboard caused a staffing decision on incomplete data

Medium · late-arriving data · postmortem · reporting windows

Six weeks ago you shipped a weekly billable-utilisation dashboard keyed on fct_time_entry.work_date. It showed a six-point drop across the three most recent weeks. A practice lead pulled two consultants off an engagement in response. The drop reversed on the next refresh, because time entries are created days to weeks after the work happens and entered_at trails work_date. Describe what you do now: the diagnosis, the change to the artifact, and the conversation with the person who acted on your number.

Approach

Name the probe: whether you own a reporting-design error rather than reclassifying it as someone else's timesheet compliance problem, and whether you fix the class of bug instead of the single week.
Quantify before explaining. Measure the backfill curve directly from entered_at: for work_date D, the share of final hours that existed as of D plus k, for k from 1 to 45. That gives an observed tail length instead of a guessed cutoff.
Change the artifact so the incomplete region cannot be read as a trend. Either end the trended series at snapshot minus the measured tail, or publish an as-of-entered_at series that is internally consistent, and label which one is on screen.
Tell the person who acted, first and directly, with the corrected series and the specific decision to revisit. A correction that arrives after they notice costs more than the original error.
Add a standing completeness tile: timesheet submission lag, the share of entries where entered_at date minus work_date exceeds seven days, so the dashboard shows its own reliability rather than depending on you remembering.

Follow-up

Leadership still wants to see the current week. What do you show, and how do you label it?
One practice runs a three-day lag and another twenty days. Do you set one firm-wide cutoff or one per practice, and what does that cost in comparability?