Interview guides · CCC Intelligent Solutions

CCC Intelligent Solutions Data Scientist Interview Questions & Guide 2026

CCC Intelligent Solutions data scientist prep: probability, sampling distributions, and stating the assumption behind every test you run.

The 60-second brief

A Data Scientist at CCC Intelligent Solutions plays a pivotal role in transforming data into actionable insights that drive strategic decisions and improve product offerings. The position centers on developing analytical models that improve operational efficiency and customer experience across the company's solutions, which include claims management, risk assessment, and predictive analytics. As a member of the company's interdisciplinary teams, you will tackle complex data challenges that affect both CCC Intelligent Solutions and its clients.

Product-sense cases reward reasoning from a mechanism to a testable prediction. Reciting every metric you can name reads as pattern matching; naming the single quantity that would move if your explanation were true reads as thinking.

Focus areas

Correct late-entered timesheets before trending recent weeks
Reconstruct pipeline stage history from mutable rows
Cluster inference at the account, not the engagement

Overview

The role is critical not only because of the scale and complexity of the data involved but also due to the strategic influence it has on the company's direction. You will work on projects that utilize machine learning, statistical analysis, and data visualization to provide insights that inform critical business decisions. Expect to work with new technologies and methods while collaborating with product managers, engineers, and other stakeholders to make sure the company's data-driven strategies are implemented effectively.

The CCC Intelligent Solutions interview process

CCC Intelligent Solutions candidates report 6 rounds · ≈ 4-6 weeks. The stages below are what candidates describe, not a published process.

HR Screening

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

What to demonstrate

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

How to prepare

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

Technical Assessments

Much of what gets scored here happens out loud while you type. Nobody can see your reasoning inside a half-written query, so five silent minutes read as being stuck even when they are not. State the plan in plain language first: which tables, what grain you are aggregating to, and the one filter that defines the population. Then write it. The narration doubles as insurance, because a wrong plan gets caught early and cheaply while a wrong query gets caught at the end with no time left to redo it. A timed statistics section, where one exists, is a separate test with its own clock.

What to demonstrate

Whether the query you write matches the plan you just described
What you do with a hint, meaning whether the correction gets absorbed or the first approach gets defended
Whether you can debug your own wrong output by reading the result set and naming which part of the query produced the anomaly

How to prepare

Solve three problems while screen-sharing into a recording, then watch it back and mark every stretch longer than thirty seconds where you said nothing
Practise compressing the plan into one sentence before typing, then check afterwards whether the finished query actually matched it
Time yourself on statistics questions that carry a business reading, such as what a confidence interval does and does not claim, rather than re-reading notes without a clock

Team Interviews

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.

Live Coding Sessions

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

Theoretical Questions

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.

Behavioral Assessments

This round decides whether you owned a decision or watched one happen nearby. Interviewers for data roles listen for the point where the analysis stopped being a report and started changing what someone did, so build each story around that hinge: what was going to happen by default, what you found, and what happened instead. The most common weakness is a story that ends at delivery. If you can name the decision your work changed and the number that moved because of it, most follow-ups become easy.

What to demonstrate

Whether the decision was yours to influence, or whether you are narrating a team outcome in the first person
The counterfactual: what would have been done without your analysis, and why that default was worse
How far your involvement ran past the handoff, and whether you checked that the change did what you predicted

How to prepare

Pick three projects and write one sentence for each naming the decision-maker, the choice in front of them, and what they chose after seeing your work. If you cannot name a person and a choice, the story is not ready for this round.
Reconstruct the baseline for your strongest project from the original query or dashboard rather than memory, so the before-number survives a follow-up asking where it came from.
Prepare an honest version of a project where your recommendation was overruled, including what you did with the analysis afterwards.

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.

Using calendar hours, or a current rate card, as the denominator

Available hours is not days x 8. It must subtract approved leave and region-specific public holidays, scale by fte_fraction, and prorate for hire_date and termination_date inside the period; skipping this produces cross-region utilisation gaps that are purely holiday-calendar artifacts and a fake dip every time a cohort of new hires starts. Equally, bill and cost rates change over time, so joining today's dim_consultant row to last year's hours restates history. Use the rate snapshotted on the time entry, or join dim_consultant on work_date BETWEEN effective_from AND COALESCE(effective_to, 'infinity').

Accepting a metric definition without asking about the denominator

Pin down the denominator, the eligibility filter and the time window before computing anything: conversion rate per session, per user, per eligible user and per new user are four different numbers with different behaviour. Restate the definition in one sentence and get agreement before you analyse.

Writing SQL without stating NULL and tie-breaking behaviour

Before calling a query finished, say what it does with NULLs, ties and empty groups. NOT IN against a subquery containing a single NULL returns no rows at all, and RANK, DENSE_RANK and ROW_NUMBER differ precisely on ties, so name which one the question requires.

Questions & practice

What is the bias-variance tradeoff?

Medium · statistics and probability

Approach

Quantify uncertainty explicitly rather than reporting a point estimate alone.
Write down the assumption the method needs before you use the method.
Sanity-check the answer against a simple bound or a simulated case.

Follow-up

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

Describe a project where you applied machine learning. What challenges did you face?

Medium · machine learning and modelling

Approach

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

Follow-up

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

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?

Explain time complexity and provide examples of different complexities.

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

Write a function to compute the factorial of a number recursively.

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
State the window function and its partition and ordering out loud before writing it.
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?
What breaks if events arrive late or out of order?

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?

Given a dataset, how would you determine its usability for a specific project?

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.
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?
Which segment would you cut first, and what would that rule out?

Provide an example of how you would analyze customer behavior data.

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.

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?

How do you prioritize your tasks when working on multiple projects?

Medium · product sense and metrics

Approach

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

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?

What metrics would you use to assess the performance of a recommendation system?

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

Follow-up

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

Describe your approach to designing an experiment to test a hypothesis.

Medium · a/b testing and experimentation

Approach

Name the randomisation unit first; it decides the variance and what the test can detect.
Say whether units interfere with each other, and switch design if they do.
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?

Explain the difference between supervised and unsupervised learning.

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
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

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

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

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

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.
For each quarter, count and sum the value of proposals in 'withdrawn' and 'no_decision', and of proposals still open at the snapshot.
Compute the value-weighted version and compare it with the count-weighted one.
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?

Win rate jumped after a pipeline hygiene push

Medium · denominator effects · selection · pipeline

Competitive win rate by value jumped from 38% to 51% in one quarter. The metric sums expected_value_usd from fct_proposal where stage = 'won' over the same sum where stage IN ('won','lost'), filtered to is_competitive = TRUE and decided_at in the window. That quarter, partners were asked to clear stale opportunities out of the pipeline. You also have fct_proposal_stage_event. Quantify how much of the jump is selling and how much is the denominator changing shape, and state what you would monitor from now on.

Approach

Stop looking at the ratio and plot its parts: won value and lost value by decision week, plus counts and value landing in the excluded stages 'withdrawn' and 'no_decision'. A ratio move with a flat numerator is a denominator story.
Date the intervention and look for a spike in excluded-stage terminations around it. Those deals are not missing at random: opportunities that go stale and get cleaned up are disproportionately ones that were heading for a loss.
Reconstruct history from fct_proposal_stage_event rather than reading the mutable fct_proposal.stage column, since the cleanup overwrote the current stage in place. Identify proposals that moved from 'submitted' or 'scoping' to 'withdrawn' after the push, and how long they had been open.
Bound the effect: recompute win rate with withdrawn and no_decision counted as losses. The truth lies between that lower bound and the reported figure; report both numbers rather than picking one.
Check the second lever: is_competitive is also mutable, so reclassifying a deal as sole-sourced removes it from the rate entirely. Count flips of that flag in the window.
Propose the standing monitor: share of decided value terminating in excluded stages, published beside win rate, so the denominator can never move silently again.

Follow-up

Some withdrawals are genuine, for example a client cancelling a procurement. How would you separate those from cleanup?
What would you have asked for before the hygiene push to make this measurable afterwards?
Does the same exclusion logic distort proposal cycle time, and in which direction?

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.

Bring numbers, including the ones you are unsure of

An answer without a quantity is hard to interrogate, so interviewers keep probing until they find one. Come with the baseline, the change, the window it was measured over, and how confident you were. If the effect never got measured, say so and say what you would have measured. Fabricated precision is worse than an honest gap.

Describe a situation where you had to learn something new quickly.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

How did you know the outcome was caused by your change?
What did you decide not to do, and why?

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

Medium · scoping · metric definition · exclusions

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

Approach

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

Follow-up

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

Three requests, one week, and the one you defer

Medium · prioritisation · stakeholder management · scope reduction

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

Approach

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

Follow-up

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