ABC Family Data Scientist Interview Questions & Guide 2026

The 60-second brief

At ABC Family, a Data Scientist plays a pivotal role in shaping how millions of viewers engage with family-oriented entertainment and digital media. The team sits at the intersection of content creation, digital streaming, and viewer analytics, transforming raw engagement data into strategic business decisions. By leveraging advanced statistical modeling, machine learning, and deep behavioral analysis, you will directly influence content recommendation engines, user acquisition strategies, and viewer retention initiatives.

In modelling rounds the live question is usually why this model class for this problem, and how you would know six weeks after launch that it is still working. Deriving gradients by hand is rarely what is being probed.

Focus areas

• Measure catalogue breadth beyond head consumption
• Diagnose rebuffering by device, network and POP
• Separate release-calendar spikes from underlying trend

Overview

The impact of this role is both immediate and highly visible across ABC Family's digital platforms. Whether you are optimizing the recommendation algorithms to help families discover their next favorite show, predicting viewer churn, or analyzing demographic trends to guide content development, your insights will drive the core business forward. This position requires a unique blend of technical rigor and creative problem-solving, as you will translate complex data patterns into actionable narratives for creative and executive stakeholders.

Working as a here means tackling challenges at a massive scale while preserving the personal, trust-driven relationship the brand has built with its audience. You will collaborate closely with product managers, software engineers, and creative teams to design experiments, build scalable data pipelines, and deploy predictive models. It is an inspiring and fast-paced environment where your technical expertise directly enhances the media experience for families worldwide.

The ABC Family interview process

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

Recruiter Screen

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What to demonstrate

• Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
• Whether you name what you have not done instead of stretching to cover every line of the posting
• Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

• Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
• Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
• Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Online Assessment

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

Technical Interviews

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

Behavioral Conversations

Rounds of this kind usually include one question about work that did not go well, and it is the part that carries the most information. Anyone can narrate a shipped win. What the interviewer learns from a project that stalled is how you behave without a result to hide behind: whether you noticed the problem yourself, how long it took, and who you told. Answers that route the failure onto a data pipeline or a reorganisation close the topic without answering it, and the follow-up comes back to your own part.

What to demonstrate

• Whether you found the error yourself or someone else found it, and how long it sat before anyone knew
• What you changed afterwards, stated as a check you now run rather than a lesson you now believe
• Whether the mistake you choose has real cost attached, such as a quarter of misdirected roadmap or a metric that was reported upward, instead of one that flatters you

How to prepare

• Choose a failure you caught yourself and be ready to say what tipped you off. A story where someone else caught it is still usable, but you will be asked why you missed it.
• Write down the check you added afterwards and where it lives now, so the correction is a concrete artefact rather than a resolution.
• Rehearse saying the cost out loud. Candidates shrink the number by instinct once the interviewer is in the room.

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.

Testing hours, revenue or completion with a difference in means on a heavy-tailed distribution.

Listening and viewing hours per account are strongly right-skewed and content popularity is close to power-law, so the variance of a sample mean is dominated by a few accounts and the central limit approximation converges slowly at realistic sample sizes. A t-test on mean hours can flip sign when one heavy account's week changes, and an experiment can appear significant because a single title released into one arm's window. Capping at a pre-registered percentile, or decomposing into a rate (did they stream at all) and a conditional intensity, controls the variance, at the stated cost that capping biases toward zero exactly when the true effect lives in the tail.

Crediting a treatment for regression to the mean

Selecting a group because it is extreme (lowest-engagement users, accounts having their worst month, the bottom decile of a score) moves that group's expected next-period value back toward the average even under no treatment, by exactly as much as the selecting measure is imperfectly correlated with its own later value. Compare against units that met the same selection rule and went untreated, or use two pre-periods so the bounce-back is visible before the intervention starts. A pre-post number on a group chosen for being extreme measures the selection rule, not the treatment.

Treating a non-significant result as proof of no effect

Say whether the confidence interval excludes the effect sizes you would have cared about. If it does not, the honest reading is that the test was underpowered, so report the minimum detectable effect the design could have found and what sample size would resolve it.

Questions & practice

How does a random forest algorithm differ from gradient boosted trees, and when would you choose one over the other?

Medium · machine learning and modelling

Approach

1. Pick an evaluation metric that matches the cost of each error type, not a default.
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

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

What metrics would you use to evaluate a binary classification model with highly imbalanced classes?

Medium · machine learning and modelling

Approach

1. Pick an evaluation metric that matches the cost of each error type, not a default.
2. Frame the prediction: the label, the moment of prediction, and the action it triggers.
3. 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?
• What would you monitor after launch to know the model is still valid?

Duration-decile-weighted completion rate with fixed reference weights

Hard · metric-definition · standardisation · pandas

Implement this metric. Numerator: qualified streams with completion_ratio at or above 0.9. Denominator: qualified streams with a non-null duration_seconds, so live events are out. Compute the rate inside each (content_type, duration decile) cell, then aggregate with catalogue-mix weights fixed from a reference month. You get streams and content for the last eight weeks plus ref_streams for the reference month. Return the weighted index and the unweighted global rate for each of the eight weeks, and the share of reference weight your cells actually covered.

Approach

1. Fix the decile boundaries from the reference month, within content_type, over that month's qualified streams. Not over the catalogue, and not per week: the weights and the cells have to be defined on the same population or the weighted sum is adding rates over cells the weights do not describe.
2. Store the boundaries explicitly and bin every week against them with pd.cut, with open-ended outer edges, so a duration longer than anything in the reference month still lands in the top cell instead of becoming NaN and quietly leaving the denominator.
3. Weights are the reference month's share of qualified streams per (content_type, decile) cell, summing to one across all cells. Apply them to each week's cell rates and report the covered weight separately, because a week missing a cell entirely gives a renormalised index, and renormalising silently is how the series gains a step change nobody can explain.
4. Keep the unweighted rate beside it. The pair is the deliverable: the weighted line is the answer, and the gap between the two is the size of the mix effect you removed, which is the first thing anyone reading it will ask about.
5. Sanity-test the whole construction by feeding the reference month back in as the current week; the weighted and unweighted rates must then be identical to floating-point error.

Worked solution (40 min)

1. Join content onto ref_streams, filter to is_qualified with duration_seconds not null, and take within-content_type deciles of duration_seconds at quantiles 0.1 through 0.9, replacing the outer edges with negative and positive infinity.
2. Weights: value counts of (content_type, decile) over the reference month, divided by that month's total qualified, non-null-duration streams.
3. For each of the eight weeks, filter and join identically, bin with pd.cut against the stored per-type boundaries, and compute each cell rate as the mean of (completion_ratio at or above 0.9), keeping the cell's stream count alongside.
4. Weighted index = sum(weight times rate) over cells present that week, divided by the sum of weight over those same cells; record that divisor as covered_weight.
5. Unweighted rate = the week's overall mean of the same indicator. Assemble the eight-row output.

Expected result: An eight-row frame of week_start, weighted_rate, unweighted_rate and covered_weight, where covered_weight is at most 1.0 and the two rates coincide only in a week whose cell mix matches the reference month's.

Verify it yourself

• Feeding ref_streams in as the current week returns weighted_rate equal to unweighted_rate to floating-point error, with covered_weight exactly 1.0.
• Duplicating every stream in one cell changes the unweighted rate and leaves the weighted rate unchanged. This is the point of the metric and the fastest proof the weighting is wired up.
• Each reference decile holds about ten percent of that content_type's reference streams. A cell holding thirty percent means ties in duration_seconds collapsed the quantile edges, which is common where many items share a rounded runtime.
• No row with content_type of live_event, or with a null duration_seconds, survives into either the reference frame or any weekly frame.

Follow-up

• The weighted index is flat and the unweighted rate fell four points. What shipped?
• When would you refresh the reference month, and what do you owe the series when you do?
• Podcast episodes and film have very different completion shapes. Would you ever report one number across them at all?

Explain the difference between lists and tuples in Python, and when you would use each.

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. Say which table is the grain you start from, and join outward from it.

Follow-up

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

How do you select the most important features when building a high-dimensional predictive model?

Medium · sql and data manipulation

Approach

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

Follow-up

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

How do window functions like ROW_NUMBER and DENSE_RANK differ, and how would you use them to track viewer session history?

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. Compute rates by summing numerator and denominator separately, never by averaging rates.
3. Say which table is the grain you start from, and join outward from it.

Follow-up

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

Write a SQL query to find the second-highest-paid employee within each department.

Medium · sql and data manipulation

Approach

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

Follow-up

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

Weekly engaged accounts from qualified streams on distinct days

Easy · aggregation · active accounts · qualification threshold

fct_stream has one row per playback with account_id, started_at (UTC), played_seconds, is_qualified (true when played_seconds >= 30) and event_date, a date partition key derived from started_at. dim_account has account_id and is_test_account. Write one query returning, for the seven days ending 2026-03-31 inclusive, the count of weekly engaged accounts: distinct non-test accounts with at least one qualified stream on two or more distinct UTC dates inside that window. Return a single row. Count at the account level, not the profile level.

Approach

1. Filter fct_stream on event_date BETWEEN '2026-03-25' AND '2026-03-31' and is_qualified = true. Filtering on started_at alone leaves the engine no partition to prune, so it reads every day the table holds; event_date is the partition key and is what belongs in the predicate.
2. Drop test accounts before the date count, with a join or semi-join to dim_account on is_test_account = false. Excluding them afterwards is too late, because a test account that streamed on two days has already been counted as engaged.
3. GROUP BY account_id and take COUNT(DISTINCT event_date) AS active_days. The distinct is the entire metric: an account with forty qualified streams inside one calendar day has one active day.
4. Filter HAVING COUNT(DISTINCT event_date) >= 2, then wrap in an outer COUNT(*). The group-by has already made account_id unique, so an outer COUNT(DISTINCT account_id) adds a sort for nothing.
5. Sanity-bound the answer before reporting it: it must be no larger than the count of distinct non-test accounts with any qualified stream in the window.

Worked solution (20 min)

1. Compute the ceiling first: COUNT(DISTINCT account_id) over non-test accounts with any qualified stream in the window.
2. Build a CTE of (account_id, COUNT(DISTINCT event_date) AS active_days) over the filtered rows.
3. Select COUNT(*) from that CTE where active_days >= 2.
4. Re-run the final filter at >= 1 and >= 3 to see the shape of the distribution and confirm the metric is not being carried by accounts sitting exactly on the boundary.

Expected result: A single integer. It must satisfy count(>=3) <= count(>=2) <= count(>=1), where count(>=1) is the ceiling computed in step one.

Verify it yourself

• The three thresholds are monotone: raising the active-days requirement never raises the count.
• Removing the is_test_account filter raises the count or leaves it unchanged, never lowers it.
• No account_id appears more than once in the pre-aggregate CTE.

Follow-up

• If you counted distinct dates per profile and then rolled up to the account, would the number rise or fall, and which unit does the metric actually want?
• An account plays one 40-second track at 23:58 and another at 00:03. That is two distinct UTC dates and therefore engaged. Is that the intent, and what would you change if not?
• Offline plays upload in bulk several days late with started_at backdated. Which part of this query silently changes when they land?

Read a test that won early and faded late

Medium · peeking · sequential testing · novelty effect

A ranker test was declared for 21 days at 80 percent power for a 1.0 percent lift in qualified hours per account-week, alpha 0.05 two-sided, with every eligible account ramped on day one. The team watched a daily dashboard. It crossed p < 0.05 on day 3 reading plus 2.9 percent, read plus 1.7 percent at p = 0.002 on day 9, and at day 21 reads plus 0.6 percent at p = 0.09. The same dashboard reports weekly slices of plus 2.1, plus 0.4 and minus 0.7 percent. Reconcile the cumulative readings against the slices and against the declared power before interpreting either, say what the day-21 p-value is worth given the daily looks, and say what you would run next.

Approach

1. Reconcile the dashboard against itself before interpreting anything on it. With a day-one ramp, exposure is roughly flat across the window, so each cumulative estimate must be the day-weighted average of the slices beneath it, and the declared MDE pins the standard error at the horizon. Both the point estimates and the p-values are checkable arithmetic; a cumulative that does not match its own slices is computed on a different population and neither number is usable.
2. Separate the arithmetic from the decision. Because the test ran to its pre-declared 21-day horizon and nobody stopped, the day-21 p-value is a valid fixed-horizon p-value needing no correction. What the daily looks damaged is the decision rule, not this number.
3. Quantify what stopping would have cost. Under the null, repeated significance testing on accumulating data at alpha 0.05 gives a true error rate near 14 percent at five looks, 19 percent at ten and roughly 25 percent at twenty, so shipping on day 3 was a bet at those odds.
4. Recognise the day-3 estimate as conditioned on having crossed. An effect read at the moment it becomes significant is selected for being large, so it is biased upward before any decay argument is made.
5. Re-index the effect on days since each account's first exposure rather than on calendar date. Novelty appears as a per-account effect decaying with exposure age; a calendar decay that vanishes under re-indexing is a release or a mix shift.
6. Separate novelty from primacy by tenure. Accounts created after the change never saw the old surface, so a genuine novelty effect is absent or much smaller in them, while primacy has the opposite sign and grows with exposure.
7. Distinguish the cumulative from the steady state. A cumulative estimate over a decaying profile is an average of a launch effect the product delivers once and a post-novelty effect it delivers forever. Neither is the other, and the second is the one a ship decision needs.
8. Conclude at the declared horizon: not significant at 0.05, a cumulative effect around plus 0.6 percent whose post-novelty component is not separable from zero, and any further looking requires an alpha-spending boundary fixed in advance.

Follow-up

• Specify a boundary that lets the team look daily and still ship at 5 percent. What does an O'Brien-Fleming boundary cost in final-look MDE compared with a Pocock boundary?
• The team proposes re-running the test powered for plus 0.6 percent. What is wrong with that target, and which quantity should the follow-up be powered on instead?
• What would primacy look like on this dashboard, and which metric would register it first?

Trade retention against revenue on an ad-supported tier

Medium · metric design · guardrail conflict · advertising · lagged effects

The free_ad_supported tier proposes raising ad breaks per delivered hour. The company metric is net revenue per active account-month; the stated guardrails are advertising seconds per delivered content hour and trailing 90-day retention of ad-supported accounts. Using fct_stream (account_id, ad_breaks_served, ad_seconds_played, played_seconds, is_qualified) and fct_subscription_period (account_id, net_amount_usd, period_end_ts, renewal_outcome), design the decision: primary metric, guardrails, and an explicit exchange rate saying how much retention you would spend for a revenue point. A four-week test is all you are offered.

Approach

1. Say plainly that this is a real conflict, not a monitoring exercise: raising ad load moves the primary metric up and the guardrail down through the same mechanism, so no definition of either metric makes both improve and the job is to price the trade, not to find a metric that hides it.
2. Decompose the primary so the conflict is visible in the algebra: net revenue per active account-month equals ad seconds per delivered content hour times delivered content hours per active account-month times net revenue per ad second, and the change raises the first term while lowering the second, with the third term falling too if added inventory clears at a lower price.
3. Establish that a four-week test cannot read the guardrail: the retention response is lagged past the window, so the test is powered on the numerator and will return a significant revenue gain against a retention estimate whose confidence interval spans the effect sizes that would kill the launch. State the required holdout instead, held at least 90 days at a size chosen to detect the retention drop that would break even.
4. Convert both sides into one currency before arguing: expected lifetime value per ad-supported account equals net revenue per active account-month times expected retained months, so a 1 percent revenue gain is worth taking only if the retention cost is under roughly 1 percent of expected retained months, and write that break-even as the exchange rate.
5. Keep one guardrail non-tradeable: advertising seconds per delivered content hour gets a hard ceiling that no revenue gain buys past, because an exchange rate alone permits an unbounded walk up the ad-load curve one defensible step at a time.
6. If the four weeks must produce a decision, name the surrogate explicitly and its bias: weeks three and four qualified hours per account-week among treated accounts, which understates the retention harm because the accounts most likely to leave are still present and still consuming during the window.

Worked solution (35 min)

1. Write the decomposition of net revenue per active account-month into ad seconds per content hour, content hours per account-month and revenue per ad second, and mark the sign of the ad-load change on each term.
2. Write the lifetime-value identity for an ad-supported account and differentiate it into the break-even condition: the launch is positive only when the proportional revenue gain exceeds the proportional loss in expected retained months.
3. Plug in one concrete pair: a 3 percent revenue-per-account-month gain breaks even against roughly a 3 percent fall in expected retained months, which on a 14-month expected tenure is about 0.4 months, and translate that into the 90-day retention drop it corresponds to.
4. Specify the measurement plan: four-week test reads the revenue term and the ad-seconds guardrail only; a separately sized holdout of ad-supported accounts is held 90 days and is the only thing allowed to move the retention number.
5. Write the ship rule with the hard ceiling first, then the exchange rate, then the holdout condition, and state which of the three is not negotiable.

Expected result: Primary: net revenue per active account-month. Guardrails: advertising seconds per delivered content hour with a hard non-tradeable ceiling, and trailing 90-day retention of ad-supported accounts read from a dedicated holdout, not the four-week test. Exchange rate stated as a break-even: the proportional revenue gain must exceed the proportional loss in expected retained months, with the worked figure around 3 percent against roughly 0.4 months on a 14-month tenure. The candidate must say the conflict is genuine.

Verify it yourself

• The decomposition has ad load raising one term and lowering at least one other, which is the formal statement that the conflict cannot be defined away.
• The break-even is expressed as a comparison of two proportional changes, so it does not depend on getting the absolute lifetime value right.
• The four-week test is explicitly not permitted to clear the retention guardrail, and the reason given is power and lag rather than a preference.
• The ad-seconds ceiling is stated as a bound rather than a term in the exchange rate, which is what stops an incremental walk up the curve.

Follow-up

• Net revenue per ad second falls as load rises because added inventory clears cheaper. How does that change your break-even, and does it make the decision easier or harder?
• How large must the 90-day holdout be to detect a one-percentage-point retention drop, and what does that cost in forgone revenue while it runs?
• The four-week result is a 3 percent revenue gain with a retention estimate of minus 0.4 points plus or minus 1.5. What do you recommend, in one sentence, and what do you refuse to claim?

Qualified streams drop the week a client shipped

Easy · instrumentation · release-debugging · segmentation

Qualified streams in fct_stream (is_qualified, played_seconds, max_position_seconds, app_version, device_type, started_at, ended_at, end_reason) fell 11% on phone in the seven days after version 8.4.0 reached staged rollout. Smart TV and desktop are flat. The mobile team says nothing in the release touched playback. Using fct_stream and dim_profile only, decide within one day whether engagement fell or whether the client stopped reporting playback correctly, and say what evidence settles it. Deliverable: a one-page finding with the decomposition and a recommendation on whether to halt the rollout.

Approach

1. Split the 11% into stream count and per-stream played_seconds. Cut phone streams by app_version into 8.3.x and 8.4.0 on the same dates and compare starts per active profile-day against played_seconds per start. A real engagement fall moves starts; a telemetry fall moves played_seconds per start while starts hold.
2. Check the accrual mechanics that change silently: share of rows with ended_at null, share with end_reason in ('unknown','app_kill'), and the played_seconds distribution in 5-second buckets from 0 to 60. A heartbeat regression piles rows up just under 30 seconds and raises the null-ended_at share, because is_qualified is a threshold on a counter rather than on the fact of playback.
3. Use the staged rollout as the control, but use it correctly. Within phone and within the same dates, prefer profiles that have streams on both versions so each profile is its own before-and-after. Rollout order is not random — early upgraders skew toward frequent users — so report the within-profile change and treat the between-version gap as an upper bound.
4. Compare max_position_seconds per start against played_seconds per start. max_position_seconds is the furthest playhead reached and does not depend on how time is accrued, so if the playhead still travels the same distance while played_seconds falls, playback happened and the counter is wrong. That comparison is decisive; the others are only suggestive.
5. Recommend on the basis of which number moved. Halt the rollout for an engagement regression only if starts fell. If accrual broke, the halt is a data-integrity call, the affected days need a caveat on every series built on played_seconds, and the music payout path that reads is_qualified has to be told the same day.

Follow-up

• If played_seconds under-reports on 8.4.0, how would you restate the qualified-stream series across the rollout window rather than blanking it?
• Staged rollout is ordered by store and device, not randomised. What does that do to a between-version estimate, and how would you bound the bias?
• The per-stream payout boundary is the same 30-second threshold. Who needs to know, and before what deadline?

Suggested practice checklist

Instead of guessing where the week should go, day one measures it under a fixed rubric and allocates the remaining hours in proportion to the gaps. The method is deliberately rigid: the allocation is written down before any studying starts and is not renegotiated when a topic turns out to be unpleasant.

Day 1 — Diagnostic, scored before you study anything

• Sit a 100-minute timed diagnostic in four blocks: 30 minutes of SQL across three prompts, 25 minutes of short-answer statistics, 25 minutes on one modelling or case prompt, and 20 minutes delivering one behavioural story aloud.
• Score each block from 0 to 3 on a fixed rubric where 3 is correct and fluent, 2 is correct but slow or prompted, 1 is partially correct, and 0 is stuck, grading the output rather than how the attempt felt.
• Allocate the hours for days two to five roughly in proportion to 3 minus the score in each block, write the allocation down, and commit to not revising it midweek.

Deliverable: A scored rubric and a fixed hour allocation for the rest of the week.

Day 2 — Largest gap: find the boundary rather than the subject

• Break the weakest area into five named sub-skills (for query work: grain control, window frames, date arithmetic, set logic with NULLs, and reading a query plan) and rate each one, so the rest of the week targets a sub-skill instead of a subject.
• Solve three problems chosen to sit just above where the rating drops off, and for each write the first move you failed to make.
• Re-solve one of them from memory four hours later, on paper, with nothing open.

Deliverable: A five-item sub-skill map with the two blocking sub-skills circled.

Day 3 — Largest gap: drill the blocking sub-skill

• Do eight short repetitions of the same shape rather than eight different problems, so what you practise is the pattern and not the puzzle.
• Write the rule you now hold in one sentence, then test it against a case built to break it: a ranking function over a column with ties, or a two-sample test on observations that are obviously dependent.
• Have someone else read your one-sentence rule and find the precondition you left out.

Deliverable: One rule statement with its preconditions attached and one counterexample that would have caught the incomplete version.

Day 4 — Second gap, plus maintenance on your strongest area

• Run the same sub-skill map and boundary protocol on the second-largest gap, compressed into half the day.
• Spend 25 timed minutes on your strongest area to stop it decaying, choosing the hardest problem you can still finish rather than an easy warm-up.
• Compare how the two areas fail: whether you lose time on recall, on setup, or on arithmetic, because the fix differs for each.

Deliverable: A second sub-skill map plus a one-line diagnosis of how each area fails you.

Day 5 — The gap that is not a skill

• Record yourself answering one technical and one behavioural prompt, then count two things in the playback: how many seconds before your first clarifying question, and how many sentences you started without knowing where they ended.
• Rewrite your three most-used stock phrases into shorter versions, and practise saying "I do not know, here is how I would find out" without softening it into a guess.
• Deliver one answer again with a hard 90-second limit to force structure before detail.

Deliverable: Two recordings with a counted improvement in time-to-first-question.

Day 6 — Retest under day-one conditions

• Sit the same 100-minute diagnostic structure with new prompts of comparable difficulty and score it on the identical rubric.
• Compare block by block, and for any block that did not move, change the method rather than adding hours: a block stuck at 1 usually means the practice was too varied, not too short.
• Write which single block you would still lose the offer on.

Deliverable: A second scored rubric placed next to the first, with one named remaining risk.

Day 7 — Full loop under interview conditions

• Run a 60-minute mock covering the two blocks that moved least, with an interviewer instructed to interrupt and change direction.
• Write your recovery script for the moment you go blank: restate the question, state your assumption, name the first thing you would check.
• Reduce the week to the rule statements you wrote, each with its preconditions attached, then say every one of them out loud without reading it and cut any you cannot state in a single sentence, since a rule you have to reconstruct mid-answer will not survive being interrupted.

Deliverable: A one-page card holding the recovery script and only the rules you could state from memory.

Where your data came from

Data people depend on systems owned by other teams, and much of the job is negotiating for instrumentation, access, or a fix to a broken pipeline. Prepare an example of getting something changed upstream that you did not control. Describe what you asked for, what you traded, and how you worked while you waited.

How do you translate complex technical findings to non-technical stakeholders, such as content producers or marketing managers?

Medium · behavioural and stakeholder questions

Approach

1. Close with what you would do differently, concretely.
2. Name the disagreement or constraint, and how you resolved it with evidence.
3. State the situation in two sentences and spend the rest on your reasoning.

Follow-up

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

Give an example of a project where the initial data was highly messy or incomplete, and explain how you structured your approach.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

• How did you know the outcome was caused by your change?
• What would you do differently if you ran that project again?

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

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