Interview guides · Circana

Circana Data Scientist Interview Questions & Guide 2026

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

The 60-second brief

The role of a Data Scientist at Circana is pivotal to driving data-informed decision-making across the organization. By leveraging advanced statistical methods and machine learning algorithms, you will analyze vast datasets to extract actionable insights that influence product development, marketing strategies, and customer engagement initiatives. This position is not only critical for optimizing existing processes but also for identifying new opportunities for growth and innovation.

If the team owns experimentation, expect depth past a two-sample test: minimum detectable effect and its roughly inverse-square-root dependence on sample size (holding power, significance level and variance fixed), variance reduction from pre-period covariates, interference between units, and when a sequential design is the right call.

Focus areas

Measure catalogue breadth beyond head consumption
Separate release-calendar spikes from underlying trend
Correct discovery slates for position bias

Overview

As a Data Scientist, you will collaborate with cross-functional teams, including product managers, engineers, and marketing specialists, to tackle complex business challenges. You will engage with diverse datasets, contributing to projects that enhance user experience and support strategic objectives. Your work will directly impact the effectiveness of Circana’s products, making this role both challenging and rewarding. Expect to engage with cutting-edge tools and methodologies, working in an environment that values analytical rigor and creativity.

The Circana interview process

Circana candidates report 3 rounds · ≈ 3-5 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 Interview

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

Final HR Round

Behavioural answers from data candidates get audited in a way that answers from other roles do not. When you say a model lifted retention, the next question is the denominator, the window, and how you knew the lift was not seasonal. So attach the measurement to each claim while you tell it: what the metric was before, over what period, and against what comparison. Numbers with no baseline read as rounded-up memory, and one unsupported figure tends to make the rest of the story sound rehearsed.

What to demonstrate

Whether each impact number arrives with a baseline, a window and a comparison, or as a bare percentage
Whether you can name the method that attributed the effect to your work (an experiment, a staged rollout, a seasonal control) or concede the link was correlational
Whether the magnitudes stay internally consistent when the interviewer multiplies them against the scale you described earlier

How to prepare

For each story, write the impact line as metric, value before, value after, window, and how attribution was established. Any line missing two of those five is a follow-up you will answer badly.
Re-derive one headline number from the source table rather than the deck that reported it. Resume numbers drift upward across retellings.
Decide in advance which figures you cannot share, and prepare the ratio or relative change you can give instead, so a confidentiality limit does not read as evasion.

Where candidates lose points

Collapsing cancellation and payment failure into one churn number.

Involuntary churn from expired or declined payment instruments is a large and volatile share of gross churn, and it responds to retry schedules, card-updater coverage and billing provider, not to anything in the product. It also resolves late, so a period that looks involuntary today can be a successful retry next week, and reading the split before the dunning horizon closes overstates it. Compounding this, cancel-at-period-end means the cancellation request and the entitlement end are different timestamps on different rows, so a churn curve keyed on cancel_requested_ts and one keyed on churn_ts disagree by a full billing period.

Comparing consumption week over week across the release calendar and the rights calendar.

A major release, a season drop or a live event produces a spike that dwarfs almost any treatment effect, and the effect is not confined to the new title because it pulls attention from everything else in the same window. Separately, licensed content leaves the catalogue when its window expires, so consumption falls with no product change and the drop is attributed to whatever shipped that week. Both need to be handled by an explicit control: a comparison period chosen for calendar equivalence, a covariate for scheduled releases, or a pre-registered rule for excluding a window, decided before the numbers are seen.

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.

Answering a product-sense question with a list of features

Answer with a decision and the measurement that would settle it: the hypothesis, the primary metric, the guardrails, and the result that would make you not ship. A feature brainstorm cannot be wrong, which is exactly why it earns no points.

Questions & practice

Can you explain the concept of overfitting in machine learning?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Check what information would not exist at prediction time, and exclude it.
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?
Where could label leakage enter this setup?

What metrics would you use to evaluate the performance of a model?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Check what information would not exist at prediction time, and exclude it.
Say how the offline result would be validated online before it is trusted.

Follow-up

How would you choose the decision threshold, and who owns that choice?
Where could label leakage enter this setup?

Attribute streams to prior impressions without merge_asof

Medium · pandas · joins · attribution

You have impressions (impression_id, profile_id, content_version_id, rendered_at, surface, slate_position, viewport_visible_ms) and streams (stream_id, profile_id, content_version_id, started_at). Attribute each stream to the most recent impression of the same profile and content version with rendered_at at or before started_at, a gap of at most 30 minutes, and viewport_visible_ms above zero; break ties on rendered_at by the smaller slate_position. pd.merge_asof and any groupby-apply over individual rows are off limits. Return streams with impression_id added, plus the attributed share by surface.

Approach

Filter impressions to viewport_visible_ms above zero first. A row that never scrolled into view is not an exposure, and leaving it in lets an unseen impression win the as-of match over a seen one.
Stack both frames into one long frame with a ts column and an is_stream flag, then sort by (profile_id, content_version_id, ts, is_stream, negative slate_position) so that at an identical timestamp impressions sort before the stream, and among tied impressions the smallest slate_position sorts last.
Forward-fill the candidate impression_id and rendered_at with groupby(['profile_id','content_version_id']).ffill(). The grouping is what stops the fill leaking across profiles; a global ffill on a sorted frame is the usual wrong answer.
Apply the 30-minute window as a post-filter on the filled candidate, and let a stream that fails it go unattributed rather than falling back to an older row. Every candidate precedes the stream, so the gap grows monotonically as you walk backwards: if the nearest in-view impression is more than 1800 seconds old, no earlier one is inside the window either, and null is the correct answer. The window cannot be pushed onto the impressions frame beforehand in any case, because "within 30 minutes" is defined against a particular stream's started_at and one impression is a candidate for many streams. Dropping impressions rendered more than 30 minutes before the earliest stream is a safe prune, but it is an optimisation, not the rule.
Slice the stream rows back out and compute the attributed share by surface, stating that unattributed streams (resume, direct link, radio seed) are not a surface and belong in their own bucket.

Worked solution (30 min)

imp = impressions[impressions.viewport_visible_ms > 0]; build an impression side with ts = rendered_at, is_stream = 0 and a sort helper neg_pos = -slate_position, and a stream side with ts = started_at, is_stream = 1, neg_pos = 0.
Concatenate both, then sort_values(['profile_id','content_version_id','ts','is_stream','neg_pos']).
Add cand_id and cand_ts columns carrying impression_id and rendered_at on impression rows and NaN on stream rows, then groupby(['profile_id','content_version_id'])[['cand_id','cand_ts']].ffill().
Slice to is_stream == 1, compute gap = (ts - cand_ts) in seconds, and set cand_id to NA where the gap is null or above 1800.
Merge cand_id back onto streams by stream_id, then group the attributed rows by the winning impression's surface for the share.

Expected result: streams with an impression_id column, row count unchanged, non-null only where an in-view impression of the same profile and content version exists within 1800 seconds before started_at, plus a per-surface share over a stated denominator of attributed streams rather than all streams.

Verify it yourself

Every attributed pair satisfies profile_id equality, content_version_id equality, and a gap between 0 and 1800 seconds. Assert all three rather than eyeballing the head.
On a 10,000-row sample the result matches pd.merge_asof(by=['profile_id','content_version_id'], tolerance=30 minutes, direction='backward', allow_exact_matches=True) once slate_position ties are made unique.
The output row count equals len(streams) exactly; a frame that grew matched several impressions to one stream.
Setting viewport_visible_ms to 0 on the winning impression of a known stream moves that stream to an earlier impression or to null, never leaves it unchanged.

Follow-up

The same item was rendered in two slates inside the window. Your tie-break picks one. What does that do to per-surface credit, and what would a fractional rule change?
How would you validate this against the impression_id already carried on fct_stream, and what would a systematic disagreement tell you?
At 2 million impressions and 500 thousand streams this fits in memory. What changes at 200 million?

Sessionise playback into listening sessions with an idle gap

Medium · window functions · gaps and islands · sessionisation

fct_stream has profile_id, stream_id, content_version_id, started_at, ended_at (nullable when the client stopped reporting) and played_seconds. Collapse consecutive streams by the same profile into listening sessions: a new session begins when a stream starts more than 30 minutes after the latest end reached by any of that profile's earlier streams. One profile can play on two devices at once, so streams overlap and 'the previous stream' is not well defined — the idle gap is measured against the running maximum end, not against the row immediately before. Treat a NULL ended_at as started_at plus played_seconds. Return one row per session with profile_id, session_start, session_end, stream_count, distinct content_version_id count and total played_seconds. Use window functions; a self-join on fct_stream is not an acceptable answer.

Approach

In a first CTE compute effective_end = COALESCE(ended_at, started_at + played_seconds * INTERVAL '1 second'). This both fills the abandoned-client case and bounds it, since played_seconds is advancing playback only and cannot run past the true end.
Take prev_max_end = MAX(effective_end) OVER (PARTITION BY profile_id ORDER BY started_at, stream_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), and flag is_new_session = (prev_max_end IS NULL OR started_at > prev_max_end + INTERVAL '30 minutes'). LAG(effective_end) is the reflex here and it is wrong under concurrent devices: with A 10:00-12:00, B 10:05-10:10 and C starting 10:45, LAG hands C the end of B at 10:10, so C opens a new session 35 minutes into a session still running until 12:00. The NULL branch is not defensive noise either: the first row of every profile has no preceding frame and would otherwise fall outside any session.
Number the sessions with SUM(is_new_session::int) OVER (PARTITION BY profile_id ORDER BY started_at, stream_id ROWS UNBOUNDED PRECEDING). A running count of boundaries is the gaps-and-islands device. Specify ROWS rather than letting the default RANGE frame apply, because RANGE lumps peer rows that share a started_at into one frame position — the same reason the ORDER BY carries stream_id as a tie-break.
Group by (profile_id, session_number) and aggregate: MIN(started_at), MAX(effective_end), COUNT(*), COUNT(DISTINCT content_version_id), SUM(played_seconds).
The running maximum makes the sessions disjoint in wall-clock time, but it does not make SUM(played_seconds) an elapsed-time figure: two devices playing at once contribute overlapping seconds, so the total can exceed session_end minus session_start. Report it as consumption and say which of the two the requester asked for.

Worked solution (30 min)

Materialise the effective_end CTE and check for rows where effective_end < started_at, which means negative played_seconds or a clock-skewed ended_at and must be quarantined before anything else.
Add the running MAX and the boundary flag; verify that exactly one row per profile has prev_max_end IS NULL and that it is flagged as a session start.
Add the running SUM to number sessions; confirm the numbers are dense and begin at 1 within each profile.
Aggregate and reconcile: SUM(stream_count) across all sessions must equal the input row count.
Run the overlap fixture — one profile with A 10:00-12:00, B 10:05-10:10 and C at 10:45 — and confirm it collapses to one session. With LAG(effective_end) in place of the running MAX it splits into two, and the second session's start, 10:45, sits inside the first session's span.

Expected result: One row per (profile_id, session_number). Session numbers are dense from 1 within each profile, SUM(stream_count) equals the input row count exactly, and a profile's sessions are disjoint in wall-clock time: each session_start exceeds the previous session's session_end by more than 30 minutes.

Verify it yourself

Input row count equals SUM(stream_count); a shortfall means rows fell out of the window partition or the NULL branch of the boundary flag.
For any profile, each session's session_start exceeds the previous session's session_end by more than 30 minutes. This holds only because the boundary is measured against the running maximum end — under LAG(effective_end) the overlap fixture above produces a session whose session_start, 10:45, precedes the prior session_end of 12:00.
Changing the gap from 30 to 60 minutes reduces the session count or leaves it unchanged, and never raises it.

Follow-up

Two devices on one profile play simultaneously. Is that one session or two, and what changes in the query for each answer?
Re-run at a 10-minute and a 60-minute idle gap. How far does session count move, and which number do you publish and defend?
Can SUM(played_seconds) for a session ever exceed session_end minus session_start, and what does it mean when it does?

Rebuffer ratio by point of presence, excluding start failures

Easy · ratio metrics · delivery quality · conditional aggregation

fct_stream carries cdn_pop, device_type, network_type, played_seconds, rebuffer_seconds and end_reason (enum including 'playback_error'). For streams with started_at in the trailing 24 hours, return one row per (cdn_pop, device_type, network_type) with two figures: rebuffer_ratio = SUM(rebuffer_seconds) / SUM(played_seconds), computed only over rows with played_seconds > 0; and start_failure_rate = the share of all rows in the cell with played_seconds = 0 and end_reason = 'playback_error'. Restrict output to cells with at least 500 streams and order by rebuffer_ratio descending.

Approach

Do it in one pass with conditional aggregation: SUM(rebuffer_seconds) FILTER (WHERE played_seconds > 0) over SUM(played_seconds) FILTER (WHERE played_seconds > 0), or the equivalent SUM(CASE WHEN ...) on engines without FILTER. Two separate scans risk the numerator and denominator disagreeing about which rows are in the cell.
Wrap the denominator in NULLIF(..., 0) so a cell in which every stream failed before first frame returns NULL rather than raising a division error — and so it is visibly absent rather than silently zero.
The failure rate's denominator is every row in the cell, including the zero-play ones. That is the whole point of the pair: the rows excluded from the ratio have to be counted somewhere, or a point of presence that fails before first frame reports a perfect ratio.
Apply the 500-stream minimum with HAVING COUNT(*) >= 500 before ordering, otherwise a cell with three streams and one stall tops the list at a ratio no one should act on.

Follow-up

Two points of presence show the same rebuffer ratio but one has twice the start-failure rate. Which do you escalate, and what does the second number tell you that the first cannot?
How would you decide whether a cell's ratio moved beyond what day-to-day variation explains, given that cell sizes differ by orders of magnitude?

Estimate the yearly revenue of a product based on limited data.

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Fix the population and the time window before naming any metric.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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

How do you prioritize tasks when working on multiple projects?

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you approach building a recommendation system for an e-commerce platform?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Fix the population and the time window before naming any metric.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

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

Describe your methodology for analyzing customer churn.

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Name one primary metric, then the guardrail that stops it being gamed.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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 would you handle missing data in a dataset?

Medium

Approach

Work from the decision backwards to the evidence you would need.
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.

Follow-up

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

Design a switchback around a shared popularity feature

Hard · interference · switchback · spillover · clustered design

A ranker change boosts items using a global popularity feature computed from every account's streams over a trailing 24 hours. An account-randomised A/B is proposed. Treated accounts' plays feed that feature, so control accounts are shown items the treatment arm made popular. Design an experiment that identifies the effect on qualified hours. Specify the randomisation scheme, slice length, burn-in, the analysis unit and its standard error, and how you would measure the interference you cannot remove. The surface refreshes hourly and 74 percent of qualified hours fall between 17:00 and 24:00 local.

Approach

Name the violation: SUTVA fails because one account's assignment changes another's exposure through the shared feature. The account-randomised estimate is attenuated by an unknown factor, so a null cannot distinguish no effect from complete spillover.
Rule out cluster designs by reading the feature's partition key rather than by reaching for geography. Popularity computed globally has no containing cluster smaller than the whole population: a treated account in one territory moves the same feature value a control account in another territory reads. The general rule is that any cluster or saturation scheme must be randomised at or above the unit the shared feature is computed over, which here leaves only time.
Choose a switchback: randomise time, put the entire population on one arm per slice, and the shared feature becomes internally consistent within a slice. The price is that only outcomes resolving inside a slice are measurable, so this design reads hours and cannot read retention.
Set slice length against carryover rather than convenience. A 24-hour popularity window means each slice inherits up to 24 hours of the previous arm's influence, so burn-in must be at least 24 hours and a one-hour slice is meaningless. The engineering trade worth proposing is shortening the feature window for the duration of the test.
Block assignment against the daypart. With 74 percent of hours inside a seven-hour evening band, unblocked random slices will hand one arm more evenings than the other; randomise within blocks of complete days and verify each arm's realised share of evening and weekend hours.
Measure the residual spillover with a saturation ladder built on the same slice schedule, since time is the only unit above a global feature. Run some slices at intermediate treated shares and read control accounts' outcomes as a function of the slice's treated share: the slope is the spillover, and it is identified precisely because the feature is global.
Analyse at the slice, not the account. With tens of slices per arm, use a permutation test over the actual slice-assignment mechanism rather than a t-test on millions of correlated account rows, and allow for correlation between adjacent slices.

Worked solution (45 min)

Read the feature definition to confirm its partition and window rather than assuming: global versus per-territory decides whether cluster randomisation is even available, and whether a saturation design can be run on anything but time.
Cost the design under the native 24-hour window: a 24-hour burn-in forces 48-hour slices, so 28 days gives 672 / 48 = 14 slices, seven per arm, each contributing 24 measured hours. Write down explicitly that this is unreadable for anything but a very large effect.
Propose shortening the feature window to 4 hours for the test. A 4-hour burn-in then allows 8-hour slices: 672 / 8 = 84 slices over 28 days, each contributing 4 measured hours.
Allocate the 84 slices across treated shares rather than spending all of them on the two extremes: 24 slices at 0 percent treated, 24 at 100 percent, and 12 each at 25, 50 and 75 percent. The primary contrast is 0 versus 100 with 24 slices an arm instead of 42, which widens its MDE by sqrt(42 / 24) = 1.32. That 32 percent is the price of being able to measure the spillover at all rather than assuming it away.
Block the slices into consecutive day pairs so every treated share receives the same number of 17:00 to 24:00 bands and the same number of weekend bands, and verify the realised balance before unblinding.
Compute the slice-level outcome as qualified hours per active account inside each slice's measured portion, then estimate the 0 versus 100 contrast with a permutation test over 10,000 re-randomisations of the blocked assignment.
Regress control accounts' qualified hours per active account on the slice's treated share across the 0, 25, 50 and 75 percent rungs. The slope is the spillover the switchback exists to avoid, measured on the same schedule and the same metric.

Expected result: A blocked switchback rather than an account-randomised A/B. Under the native 24-hour feature window the design yields only seven slices per arm over 28 days and is not readable; shortening the feature window to 4 hours for the test gives 8-hour slices with a 4-hour burn-in and 84 slices over 28 days, analysed at the slice level with a permutation test. Twenty-four of those slices go to each extreme and 36 to intermediate treated shares, widening the primary contrast's MDE by a factor of 1.32 in exchange for a direct spillover estimate. The spillover is read as the slope of control accounts' hours against the slice's treated share, and it is measured on a time ladder rather than on territory clusters because a globally computed feature admits no saturation unit smaller than the whole population. The limitation stated up front is that this measures within-slice consumption and cannot measure retention.

Verify it yourself

An A/A switchback over the same slice schedule must reject at close to the nominal rate. Rejection above nominal means the burn-in is too short or adjacent slices are more correlated than the test allows.
Split each measured window into halves: after adequate burn-in the two halves must give the same estimate, and a gradient between them means carryover is still bleeding through.
Each arm's share of qualified hours inside 17:00 to 24:00 and its share of weekend hours must both sit within one percentage point of 50 percent across the realised schedule.
Control accounts' hours must be flat against the slice's treated share if the shared feature carries no spillover, and a positive slope across the 0, 25, 50 and 75 percent rungs is the spillover measured directly. Read a flat line only to the width of its confidence interval: 12 slices a rung is a diagnostic, not a precise estimate, so report the interval and say what magnitude of spillover it still admits rather than calling the null zero.
The ladder must sit on the same unit the feature is computed over. A saturation design run on territory clusters while popularity is global is uninformative by construction, because every cluster reads the same feature value whatever its local treated share, so a null there says nothing about spillover.

Follow-up

A stream starts at 07:55 and ends at 08:40 across a slice boundary. Write the rule, and say which direction your choice biases the estimate.
The change is also expected to move 30-day retention. Can this design say anything about that, and if not, what is the least-bad alternative?
Bound the attenuation in the account-randomised test instead: with 50 percent of accounts treated, what share of the popularity feature is treatment-derived, and what does that imply about the observed effect relative to the true one?

Completion rate fell while every duration decile improved

Medium · simpsons-paradox · mix-shift · decomposition

Duration-normalised completion rate — qualified streams with completion_ratio >= 0.9 over qualified streams with non-null duration_seconds — fell from 0.41 to 0.36 across six weeks. Computed inside each (content_type, duration decile) cell, every cell is flat or up. Using fct_stream (completion_ratio, is_qualified, content_version_id, started_at, start_source) joined to dim_content_version (content_type, duration_seconds, catalogue_added_ts, parent_id), reconcile the two numbers and say which the content team should act on. Deliverable: a decomposition separating mix shift from within-cell change, with the size of each in completion-rate points.

Approach

Name the mechanism before computing anything. completion_ratio is mechanically decreasing in duration_seconds, so the global rate is a duration-mix-weighted average of cell rates and moves whenever the weights move with every cell rate held fixed. That is not a paradox, it is the aggregation doing what it was always going to do.
Run the two-term decomposition: total change equals sum over cells of (w_new - w_old) * r_old for mix, plus sum over cells of w_new * (r_new - r_old) for within. Those two terms are exhaustive — the sum telescopes back to the observed change exactly — because this arrangement already carries the whole weight-by-rate interaction inside the within term. There is no third quantity to add. State the convention anyway: the mirror arrangement, mix at r_new plus within at w_old, is equally exact and parks the interaction in mix instead, and the two attributions differ by exactly sum (w_new - w_old) * (r_new - r_old) whenever weights and rates moved together. Report in completion-rate points so the argument is about magnitudes rather than adjectives.
Find which weights moved and why. Cell share of qualified streams by week, joined to catalogue_added_ts and parent_id, will usually name a season of long episodes, a podcast push, or a shift in content_type as the cause within one query.
Distinguish a mix shift the product caused from one the calendar caused, by cutting the weight change by start_source. A ranker that began surfacing long-form is a product decision with a durable consequence; a release landing in the window reverses by itself, and the two warrant opposite responses.
Answer what was actually asked. The within-cell term is the quality signal and it is flat to up, so the content team should not chase it. The headline needs the fixed reference-month weights the metric definition already specifies, and the unweighted version should come off the dashboard rather than be explained every six weeks.

Follow-up

Fixed reference weights stop the mix moving the number, but a genuine, permanent shift toward long-form then never appears. How do you cover that gap?
The deciles are cut from some population. What happens to the decomposition if the decile boundaries themselves move between the two periods?

Suggested practice checklist

For someone who can already write the query and train the model but stalls when asked what to measure or whether a change is worth making. Metric definition and case structure come first; the technical work is kept as maintenance rather than the centre of the week.

Day 1 — Metric anatomy

For three products you use daily, write one primary metric, two input metrics that plausibly move it, and one guardrail that would catch a cheap way of moving the primary at the cost of the product.
For one of them, specify the metric precisely enough that two analysts would return the same number: numerator, denominator, unit of observation, time window, and how returning and deleted accounts are treated.
Pick a ratio metric and write what happens to it when the denominator shrinks for reasons unrelated to the numerator, with a concrete example of that happening.

Deliverable: A one-page metric tree for one product, with the primary metric written as an unambiguous spec.

Day 2 — Diagnosing a drop without guessing

Take the prompt "weekly active users fell 8 percent week over week" and write the segmentation plan before proposing any cause: platform, region, tenure cohort, acquisition channel, and whether the movement sits in the numerator or in a changed denominator.
List the instrumentation failures that manufacture fake drops (a client release that stopped firing an event, a bot filter change, a shifted date boundary or timezone) and write the query that rules out each one.
Rehearse stating the boring explanations first, seasonality and day-of-week composition, before reaching for a product cause.

Deliverable: A drop-diagnosis checklist short enough to recite from memory in under a minute.

Day 3 — Should we build it

Take a feature idea and write it as a bet: what you believe is true, what would have to be true for it to pay off, the metric that would confirm it, and the effect size that would justify the engineering cost.
Size the opportunity top-down and bottom-up, then reconcile the two numbers in writing instead of quoting whichever is friendlier.
Write the counter-metric that would make you kill the feature even if it wins on the primary metric.

Deliverable: A one-page product memo ending in a decision rather than a list of considerations.

Day 4 — The places aggregate numbers lie

Construct a Simpson's paradox numerically: two segments where the treatment wins within each segment yet loses overall, and identify the shift in segment weights that causes it.
Take a heavy right-tailed quantity such as revenue per user and write why the mean is the wrong summary, which percentile you would report instead, and what a moving mean with a stable median tells you.
Write your definition of a session for the product from day one, then name two real behaviours it misclassifies.

Deliverable: One page holding a worked Simpson's paradox table and a session definition with its two known failure cases.

Day 5 — Technical maintenance, aimed at metrics

Solve four timed SQL prompts that all end in a ratio metric, so the question of grain stays live in every answer.
Compute a 95 percent confidence interval for a proportion on a small sample, and state why the normal approximation is unreliable when either np or n(1 minus p) falls below roughly 10, along with which interval you would use instead.
Take one metric from your day-one tree, write the query that computes it correctly, then write the query that computes it wrong in the most plausible way and explain how you would notice.

Deliverable: Four solved prompts plus a matched correct and plausible-wrong query for one metric.

Day 6 — Turning engineering work into data science stories

Write three project stories as situation, decision, trade-off, outcome, each carrying one number and one thing you got wrong.
For the story you will lead with, prepare an answer to "what would you do differently" that names a decision you made, not a constraint you were handed.
Practise the sentence that reframes a systems project as a question project: the question the work answered, ahead of the pipeline it shipped.

Deliverable: Three written stories with the lead story delivered aloud and timed under four minutes.

Day 7 — Mock case and gap list

Run a 40-minute mock case with someone playing a product manager who pushes back on your metric choice, and record it.
Listen back and mark every moment you proposed a solution before the success metric existed.
Rewrite those moments as the question you should have asked, and rehearse the first 90 seconds of the case until scoping comes before solving.

Deliverable: A recorded case plus a rewritten opening 90 seconds.

Getting an analysis acted on

Work that nobody used is a common and unflattering pattern in data careers, and interviewers probe for it. Have a story about an analysis that changed a decision, and be specific about how you got it in front of the person who could act. Also have one about work that went nowhere, with your reading of why.

What do you do when you disagree with a colleague’s approach?

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

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

Give an example of how you influenced a team decision.

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
Name the disagreement or constraint, and how you resolved it with evidence.
Pick a story where you drove the decision, not one where you observed it.

Follow-up

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

Disagree with a product manager about a completion metric

Medium · disagreement · metric design · influence

A product manager proposes making duration-normalised completion rate the team's primary metric for the quarter, and wants a plain global unweighted version "so it's simple enough to put on a wall." You expect the unweighted version to move several points on catalogue mix alone, with no change in how satisfying anything was. You have one meeting, they own the roadmap, and you will work with them for years. Deliverable: the argument, the evidence you bring, and the fallback you accept if they still want the simple version.

Approach

The probe is whether you can lose the decision without damaging either the metric or the relationship. Bring the failure already reproduced: recompute the unweighted global rate over the last two quarters and point at the weeks it moved several points where the only input that changed was which content people played.
Decompose the variance instead of objecting. Attribute the movement in the unweighted rate to between-cell mix — (content_type, duration decile) — versus within-cell movement. A decomposition is an argument; an assertion that mix matters is a preference.
Concede the real cost honestly: the mix-weighted version is harder to explain and harder to recompute. Bring the mitigation rather than dismissing the objection — weights fixed from a stated reference month, published once, so anyone can reproduce the number without redoing the weighting.
Offer the fallback deliberately and in advance: ship both, with the unweighted rate labelled a diagnostic and the weighted one as the decision metric, and pre-agree the one observation that would mean the simple version has misled the team.
Leave the decision with them, with the consequence written down before it happens. That is what makes a later correction a shared prediction coming true rather than a retrospective argument about who was right.

Follow-up

They ship the simple version and it moves four points during a heavy release week. How do you raise it without saying you told them so?
Is there a case where the unweighted rate is genuinely the right primary metric?
Suppose you are wrong and the mix effect turns out to be small. What would you change about how you argued this?