CGI Data Scientist Interview Questions & Guide 2026

The 60-second brief

A Data Scientist at CGI operates at the intersection of advanced analytics, software engineering, and business consulting. Unlike internal product-focused roles, data scientists at CGI work as strategic consultants, delivering tailored artificial intelligence and machine learning solutions to a diverse portfolio of clients across industries such as finance, government, healthcare, and manufacturing. You will be responsible for translating complex, sometimes ambiguous client business problems into structured data science initiatives that deliver measurable business value.

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

Focus areas

• Compute utilisation against a defended availability denominator
• Reconstruct pipeline stage history from mutable rows
• Separate bookings, recognised revenue and collected cash

Overview

The impact of this role is highly visible. Whether you are building predictive maintenance models for an industrial client, optimizing supply chains, or developing natural language processing pipelines for public sector services, your work directly influences operational efficiency and strategic decision-making. This environment demands not only deep technical expertise but also the agility to adapt to different client systems, data maturities, and business domains.

Working at CGI offers a unique scale of complexity and professional growth. You will collaborate with cross-functional teams of data engineers, business analysts, and project managers to take models from conceptualization and proof-of-concept all the way to production. This makes the position highly dynamic and intellectually stimulating, as you are constantly exposed to new technologies, architectures, and industry challenges.

The CGI interview process

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

Conversational Screening

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

What to demonstrate

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

How to prepare

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

Technical Evaluation

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

Managerial Evaluation

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

What to demonstrate

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

How to prepare

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

Final Management Interview

A loop is not scored one interview at a time. The people you meet compare notes afterwards, usually in a meeting you are not in, and the outcome turns on what each of them can say about you when asked. That rewards something other than survival: every room needs one specific thing worth repeating, and none of them can contradict another. The common way to lose is to tell the same project four times with different numbers in it, or to be uniformly fine in a way that leaves nobody with anything to argue for.

What to demonstrate

• Whether your account of a project survives being told twice, with the same scale, the same metric definition and the same numbers each time
• Whether each interviewer leaves with one concrete claim they could make on your behalf later, rather than an absence of complaints
• Whether a question you already answered in an earlier room gets the same answer at the same depth, without visible impatience

How to prepare

• Write a one-page fact sheet for your two or three main projects that fixes the numbers you will quote: rows of data, the metric as a single sentence, the effect you measured and how long the work took. Say them aloud from the sheet until they come out identical every time
• For each kind of room you expect, decide the one sentence you want that interviewer repeating in a debrief, then check during the mock that you said it outright instead of implying it
• Rehearse answering the same project question twice in one sitting, the second time as though you had not just answered it, because the thing that needs fixing is the flatness that creeps into a repeated story

Where candidates lose points

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.

Never asking what decision the analysis will inform

Open with who makes the decision, what the options are, and by when. The answer determines the precision you need, the segments worth cutting, and whether an observational read suffices or an experiment is required.

Sizing estimates built on unnamed, unrevisable assumptions

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

Questions & practice

Describe the characteristics and use cases of the Normal, Poisson, and Binomial distributions.

Medium · statistics and probability

Approach

1. Say what the estimate is of, and over what population it generalises.
2. Sanity-check the answer against a simple bound or a simulated case.
3. Write down the assumption the method needs before you use the method.

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?

How does Bayes' Theorem work, and how is it applied in conditional probability scenarios?

Medium · statistics and probability

Approach

1. Sanity-check the answer against a simple bound or a simulated case.
2. Say what the estimate is of, and over what population it generalises.
3. 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?

Explain the difference between a p-value and a confidence interval, and how you would explain both to a non-technical client.

Medium · statistics and probability

Approach

1. Write down the assumption the method needs before you use the method.
2. Sanity-check the answer against a simple bound or a simulated case.
3. 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?

Describe how the K-means clustering algorithm works and how you determine the optimal number of clusters.

Medium · machine learning and modelling

Approach

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

Follow-up

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

What are the assumptions of linear regression, and how do you detect and handle violations of these assumptions?

Medium · machine learning and modelling

Approach

1. Frame the prediction: the label, the moment of prediction, and the action it triggers.
2. Set a baseline first, so any model has something honest to beat.
3. Check what information would not exist at prediction time, and exclude it.

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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)

1. Filter to status == 'approved', charge_code == 'client_delivery' and engagement_id.notna(); derive week_start by subtracting the weekday offset from work_date.
2. 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.
3. 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().
4. 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.
5. 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?

What are window functions in SQL, and can you provide an example of how to use ROW_NUMBER or LAG?

Medium · sql and data manipulation

Approach

1. Say which table is the grain you start from, and join outward from it.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

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

Explain the difference between a JOIN, a subquery, and a Common Table Expression (CTE) in SQL.

Medium · sql and data manipulation

Approach

1. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
2. State the window function and its partition and ordering out loud before writing it.
3. Compute rates by summing numerator and denominator separately, never by averaging rates.

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?

Longest consecutive bench spell per consultant from weekly activity

Hard · gaps and islands · window functions · run length

You have a dense series (consultant_id, week_start, billable_hours, full_week_leave BOOLEAN) covering 26 weeks for every billable-role consultant, with zero-hour weeks present as 0. Return every maximal run of consecutive weeks where billable_hours = 0, as consultant_id, spell_start_week, spell_end_week and week_count, then each consultant's longest run. Weeks flagged full_week_leave must neither break a run nor count toward its length. Mark runs still open at the last week of the series rather than reporting them as finished.

Approach

1. Remove full_week_leave weeks from the sequence first, then re-number what remains with ROW_NUMBER() OVER (PARTITION BY consultant_id ORDER BY week_start). Deleting before numbering is precisely what makes a leave week transparent to a run instead of a break in it; filtering afterwards would leave a gap in the sequence and split the spell.
2. Flag each surviving week as bench (billable_hours = 0) or active, then compute two row numbers per consultant: one over all surviving weeks, one partitioned additionally by the bench flag. Their difference is constant inside any run of adjacent same-flag weeks, which is the island key.
3. Group the bench weeks by (consultant_id, island key) and take MIN(week_start), MAX(week_start) and COUNT(*). Do not island on week_start minus an interval times a row number: that variant assumes the spine has no holes, and the leave removal has just deliberately put holes in it.
4. Pick the longest spell per consultant with ROW_NUMBER() OVER (PARTITION BY consultant_id ORDER BY week_count DESC, spell_start_week) so ties resolve deterministically, rather than a MAX that cannot carry the accompanying dates.
5. Handle both edges as censoring. A spell touching the last week of the series is still open and its length is a lower bound, so flag it; a spell touching the first week may have started earlier, so either flag it too or exclude it from any length distribution and say which.

Worked solution (40 min)

1. Filter out full_week_leave rows and count the surviving weeks per consultant.
2. Add the bench flag and the two row numbers, then print one consultant's full sequence to see the island key stay constant across a run.
3. Group bench weeks by consultant and island key and aggregate to spell boundaries and lengths.
4. Add the open-at-the-edge flag by comparing spell_end_week to the consultant's last surviving week.
5. Rank spells per consultant and select the longest with the deterministic tiebreak.

Expected result: One row per maximal bench run with its first and last week, its length in weeks and an is_open flag, plus a one-row-per-consultant longest-spell table. A consultant with no bench weeks appears in neither, which is correct and worth stating.

Verify it yourself

• For each consultant, the sum of week_count across all spells equals their count of bench weeks in the leave-filtered series.
• No two spells for the same consultant are adjacent in the re-numbered sequence; adjacency would mean the island key was computed wrongly.
• The longest-spell table has at most one row per consultant and its week_count matches the maximum in the spells table.
• Hand-check one consultant by printing their 26 weeks as a strip of zeros, hours and leave markers, and reading the runs off it.

Follow-up

• A spell begins before your 26-week window. How do you detect that, and what do you report as its length?
• Rebuild this at daily grain. What changes about weekends, holidays and the island key?
• How would you separate true bench from unbilled delivery work on a live engagement, given only charge codes?

How would you optimize a slow-running pandas operations pipeline when working with millions of rows?

Medium · product sense and metrics

Approach

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

Follow-up

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

Define competitive win rate for a quarterly pipeline review

Easy · win rate · censoring · denominators

Pipeline reporting currently shows win rate as won proposals divided by all proposals created. Using fct_proposal (proposal_id, expected_value_usd, stage, loss_reason, is_competitive, created_at, submitted_at, decided_at), write a definition you would defend to a finance lead: numerator, denominator, window and every exclusion. Decide whether to weight by count or by expected_value_usd, and justify the choice. State what happens to proposals still in 'qualifying', 'scoping' or 'submitted' at the snapshot date, and why dropping them is not a neutral act.

Approach

1. Cohort on decided_at, not created_at. A created_at cohort mixes decided and undecided proposals, so the rate for a recent quarter is computed over whichever deals resolved fastest and drifts upward on every refresh.
2. Set the denominator to stage IN ('won','lost'). Excluding 'withdrawn' and 'no_decision' is defensible only if you report their count and value alongside, because they are not missing at random: they skew toward deals that were heading for a loss.
3. Filter is_competitive = TRUE. Sole-sourced follow-on work closes at near certainty and mechanically inflates the rate, so it belongs on its own line rather than inside the headline number.
4. Report count-weighted and value-weighted side by side. They diverge when large proposals lose more often than small ones, which is the diagnostically useful case; the value-weighted version is the one that ties to revenue.
5. Treat open proposals as right-censored rather than absent. Either restrict to cohorts old enough that most proposals have decided, picking that horizon off a Kaplan-Meier cycle-time curve, or publish the rate with the undecided count and value attached to it.

Worked solution (25 min)

1. Compute the naive rate (won / all created) and the proposed rate (won / won+lost, is_competitive = TRUE, cohorted on decided_at) for the same four quarters.
2. For each quarter, count and sum the value of proposals in 'withdrawn' and 'no_decision', and of proposals still open at the snapshot.
3. Compute the value-weighted version and compare it with the count-weighted one.
4. Re-run the most recent quarter as it would have looked 30 days ago and record how much each definition moved.

Expected result: Two numbers per trailing quarter, count-weighted and value-weighted competitive win rate, each with the denominator predicate written out in full, cohorted on decided_at, plus a reported count and value for withdrawn, no_decision and still-open proposals. The horizon is chosen so the undecided share is small rather than set to a round number of days.

Verify it yourself

• The value-weighted and count-weighted rates differ. If they are identical, expected_value_usd is either constant or lost in a join.
• Re-running a given quarter two weeks later moves the decided_at-cohorted number by much less than it moves the created_at version.
• Withdrawn and no_decision appear as reported counts, not as a silent exclusion.

Follow-up

• Win rate rose four points while proposal volume fell by a third. Which do you report first, and what single query separates a genuine quality shift from a volume shift?
• How would you detect partners reclassifying likely losses as 'withdrawn' to protect the rate?

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.

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 when you had to explain a complex machine learning model to a stakeholder who had no technical background.

Medium · behavioural and stakeholder questions

Approach

1. State the situation in two sentences and spend the rest on your reasoning.
2. Quantify the outcome, including what you would not claim credit for.
3. Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

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

Defending your own impact claim without randomisation or clean units

Hard · self-selection · clustered inference · impact measurement

At your review you plan to claim that the realisation dashboard you built recovered 1.4 million dollars. The evidence is that engagement-month realisation rose four points over two quarters among engagements whose leads used it. Adoption was voluntary. There are about sixty client accounts and the top five carry most fees. A new rate card shipped in the same quarter. Write the claim you can defend, the estimate you would actually produce, and what you say when asked for a causal number you cannot get.

Approach

1. Name the probe: whether you can separate the number you want from the number the data supports, under review pressure, without either inflating it or retreating to saying nothing can be known.
2. State both identification problems concretely. Voluntary adoption means adopting leads are plausibly the ones who already manage realisation, so the comparison is confounded at the person level. The rate card changes bill_rate_usd, which sits in the realisation denominator, so part of the four-point move is arithmetic rather than behavioural.
3. Neutralise what you can. Recompute realisation with bill rates snapshotted on work_date, or hold the denominator at the old rate card, so the rate-card change cannot move the metric by construction. Then rerun the comparison.
4. Get the inference right for the unit count. Cluster at client_id, not engagement, because engagements in one account share a partner, a rate card and a team. With sixty accounts and five carrying most fees, the effective cluster count is far below sixty, so report a wild cluster bootstrap interval rather than plain cluster-robust standard errors, which are biased downward in that regime.
5. Report both weightings and explain the divergence: an account-weighted estimate describes the typical account, a value-weighted one describes the revenue, and if they disagree a small number of accounts is carrying the result. Then give the decision-relevant sentence: the defensible range, whether its lower bound still clears the build cost, and what a proper staggered rollout would have bought.

Follow-up

• The pre-period trends for adopters and non-adopters are not parallel. What do you report then?
• You get to design the next rollout. What do you change so the same question is answerable, without randomising individual accounts?

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?