Almedia Data Scientist Interview Questions & Guide 2026

The 60-second brief

A Data Scientist at Almedia operates at the intersection of high-growth product development and complex analytical problem-solving. You are not merely building models; you are a strategic partner responsible for turning raw, often ambiguous data into actionable insights that drive revenue and user engagement. Whether you are optimizing offer displays, designing personalization engines, or diagnosing metric fluctuations, your work directly influences the company’s bottom line.

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

• Separate release-calendar spikes from underlying trend
• Split voluntary churn from involuntary payment failure
• Define a qualified stream and defend it

Overview

This role is critical because Almedia relies heavily on data-driven decision-making to scale its platforms. You will often work with complex datasets—ranging from user acquisition and survey completion to chargeback and offer purchase data—to solve real-world challenges. Success in this role requires a blend of technical rigor, product intuition, and the ability to operate autonomously in a fast-paced, high-stakes environment.

The Almedia interview process

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

Initial 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 Assessment

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

Case Studies

Underneath the business framing, this round is usually asking whether you can turn a fuzzy goal into a quantity that could be computed from data such a business would plausibly hold. That means a metric with a stated numerator, denominator, eligibility rule and time window, plus an honest account of the conditions under which it would mislead you. Answers come apart when a candidate names a familiar metric and never defines it, because every follow-up then lands on an ambiguity that was left open and the candidate has to invent the definition under pressure.

What to demonstrate

• Whether a named metric arrives with its denominator, eligibility rule and window attached rather than assumed
• Whether the measure follows from the mechanism you proposed, or is a recognisable metric retrofitted to it afterwards
• Whether you name a guardrail that would reveal the gain came from somewhere you did not want it to come from
• Whether you can say what data the plan requires and what you would settle for if that logging were never implemented

How to prepare

• Take five metrics you reach for by reflex and write each as one sentence containing numerator, denominator, eligibility rule and time window. The ones you cannot finish are the ones that will fail under follow-up.
• For a product you use daily, write the measurement plan you would propose for a change to it: primary metric, one guardrail, the unit of analysis, and the table the numbers would come from.
• Practise the substitution question. For three metrics you like, write what you would measure instead if the event you depend on were not being logged.

Leadership Discussions

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

• Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
• Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
• Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

• For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
• Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
• For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Where candidates lose points

Treating the account as the person, or the profile as the person.

A household account carries several people, profiles frequently are not switched, and shared-screen, car and speaker playback often lands on a default profile with no user behind it. Personalisation trained on a profile therefore learns a mixture, retention regressions attribute one member's behaviour to another, and a per-account taste statistic describes a household composite. The practical consequence is that apparent personalisation wins can be device or context effects, so any identity-level claim needs a stated unit and an acknowledgement of what that unit actually aggregates.

Reading recommendation quality from click-through or play-through on logged slates.

Slate position drives interaction independently of relevance, and the ranker that produced the log also decided what got exposure, so logged data is confounded by the policy you are trying to evaluate. Inverse-propensity estimators fix this only when the logging propensities are known and every item had a non-zero chance of being shown; without exploration slots that positivity condition fails silently and the estimate is confidently wrong. The visible symptom is a feedback loop: offline metrics improve, consumption concentrates further into the head of the catalogue, and the online test does not reproduce the offline gain.

Dropping rows with missing values without naming the mechanism

Say whether the values are missing at random, missing by a known process, or missing in a way that depends on the outcome, and handle them accordingly. Deleting incomplete rows silently redefines the population whenever missingness correlates with what you are measuring.

Reaching for a model before the target metric exists

Before naming an algorithm, write down the label, the prediction time, and the action that changes when the score crosses a threshold. If you cannot say what decision the output drives, any modelling choice is guesswork dressed up as method.

Questions & practice

How would you approach personalizing the landing page for users, starting from simple segmentation and moving toward a robust machine learning model?

Medium · machine learning and modelling

Approach

1. Check what information would not exist at prediction time, and exclude it.
2. Set a baseline first, so any model has something honest to beat.
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?
• Where could label leakage enter this setup?

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.

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?

Sessionise playback heartbeats into streams under an idle-gap rule

Hard · sessionisation · gaps-and-islands · pandas

heartbeats has profile_id, device_id, content_version_id, heartbeat_ts, playhead_seconds, is_paused and reported_at; clients emit one every 30 seconds while in the foreground. Collapse it into stream rows keyed on (profile_id, device_id, content_version_id) under a 300-second idle gap. Emit started_at, ended_at, max_position_seconds and played_seconds, where played_seconds counts advancing playback only and so excludes pause, seek and rebuffer time. Flag streams whose reported_at trails heartbeat_ts by more than an hour. Then re-run at a 60-second gap and report what moved.

Approach

1. Sort by the key and heartbeat_ts, take the per-row time delta inside the key with groupby().diff(), mark a new stream where that delta is null or above 300 seconds, and cumulative-sum the flag into a stream ordinal. This is gaps-and-islands, and it is O(n log n) after the sort with no per-group Python loop.
2. Compute played_seconds from consecutive heartbeats as min(max(playhead_delta, 0), wall_delta), summed inside the stream. The lower clamp discards a backward seek; the upper clamp is what stops a forward seek to the end of a forty-minute episode from crediting forty minutes of playback to one thirty-second interval. Note what this makes the column mean: a rewind and replay inside one stream counts the replayed interval twice, because played_seconds measures time spent advancing the playhead, not distinct coverage of the item.
3. Accept that the first heartbeat of each stream has no predecessor and contributes nothing, which undercounts by up to one heartbeat interval per stream. Quantify that bias as stream count times 30 seconds against the total instead of leaving it unsaid.
4. Take ended_at as the last heartbeat_ts, but keep it null when that heartbeat sits within one idle gap of the data cutoff: such a stream may still be running, and giving it an ended_at manufactures a completed stream.
5. For the offline flag, work on reported_at minus heartbeat_ts rather than on event_date, and report the distinct dates the flagged streams belong to, because that list is the recompute list.
6. Re-run at 60 seconds and report both the stream count and the change in total played_seconds: the count can only rise, and the total falls by exactly the contributions that now straddle a split.

Worked solution (40 min)

1. Sort by (profile_id, device_id, content_version_id, heartbeat_ts); compute wall_delta as the grouped heartbeat_ts diff in seconds and pos_delta as the grouped playhead_seconds diff.
2. new_stream = wall_delta.isna() OR wall_delta above 300; stream_ordinal = new_stream grouped-cumsum; group on the key plus stream_ordinal from here on.
3. contrib = np.minimum(np.maximum(pos_delta, 0), wall_delta), forced to 0 on the first row of each stream; played_seconds is contrib summed per stream.
4. Aggregate started_at as min(heartbeat_ts), ended_at as max(heartbeat_ts), max_position_seconds as max(playhead_seconds), and late_offline as max(reported_at minus heartbeat_ts) above 3600 seconds.
5. Null ended_at where it falls within 300 seconds of the data cutoff.
6. Re-run with the gap as a parameter at 60 seconds, and diff the two summaries on stream count and total played_seconds.

Expected result: One row per stream whose played_seconds never exceeds ended_at minus started_at in seconds, since each interval contributes at most its wall_delta and the wall_deltas inside a stream telescope to exactly that span. At a 60-second gap the stream count is greater than or equal to the 300-second count, and total played_seconds is lower by exactly the sum of the contributions that now straddle a split.

Verify it yourself

• Assert played_seconds at or below (ended_at minus started_at) in seconds for every stream; any violation means the clamp against wall_delta was dropped.
• No stream spans two content_version_ids or two device_ids. Group the heartbeats back by stream ordinal and check nunique equals 1 on both.
• A hand-built three-heartbeat case with a backward seek (playheads 600, 300, 330 at 30-second spacing) yields played_seconds of 30, not 60 (the wall clock) and not -270 (the raw playhead deltas).
• The distinct event_date partitions of the late_offline streams are exactly the days that need recomputing, and that set should be small and old rather than spread evenly across the window.
• Do not assert played_seconds against an item's runtime. The heartbeats input carries no duration_seconds, and the bound does not hold even if you join one: a backward seek followed by a rewatch advances the playhead over the same interval twice inside one stream, and both passes are genuine playback. max_position_seconds is the column that compares against a joined runtime, and then only within the segment-overshoot tolerance of a second or two.

Follow-up

• A client dies mid-playback and stops reporting. What does its stream look like under this rule, and what does that do to mean stream duration?
• Which of these numbers determines a per-stream payout, and what does moving the idle gap do to somebody's money?
• The same profile plays the same track on a phone and a TV at once. Your key separates them. When would you want it not to?

Given multiple tables containing acquisition and chargeback data, how would you join and aggregate them to determine the lifetime value of a cohort?

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

Using SQL window functions, how would you calculate the time delta between consecutive user actions in a conversion funnel?

Medium · sql and data manipulation

Approach

1. Say which table is the grain you start from, and join outward from it.
2. State the window function and its partition and ordering out loud before writing it.
3. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

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?

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

1. 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.
2. 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.
3. 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.
4. Group by (profile_id, session_number) and aggregate: MIN(started_at), MAX(effective_end), COUNT(*), COUNT(DISTINCT content_version_id), SUM(played_seconds).
5. 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)

1. 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.
2. 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.
3. Add the running SUM to number sessions; confirm the numbers are dense and begin at 1 within each profile.
4. Aggregate and reconcile: SUM(stream_count) across all sessions must equal the input row count.
5. 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?

If we notice a sudden drop in a key conversion metric, what is your step-by-step framework for diagnosing the root cause?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Fix the population and the time window before naming any metric.
3. 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?

Given a catalog of offers with various reward milestones, how would you design the display strategy to balance user engagement and company liability?

Medium · product sense and metrics

Approach

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

Follow-up

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

Describe a project where you had to pivot your approach due to shifting business priorities or data limitations.

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. State what result would change your recommendation, so the answer is falsifiable.
3. Fix the population and the time window before naming any metric.

Follow-up

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

How do you determine the required sample size for an A/B test, and what are the primary risks of stopping an experiment early?

Medium · a/b testing and experimentation

Approach

1. State the primary metric and the minimum effect worth shipping, then size the test.
2. Say whether units interfere with each other, and switch design if they do.
3. Decide the analysis before seeing data, including how long it runs and when you look.

Follow-up

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

Raise the qualification threshold when it is also a payout rule

Hard · metric design · definition change · payouts · history restatement

A proposal raises the qualification threshold behind fct_stream.is_qualified from 30 to 60 played_seconds for content_type = 'music_track'. The same flag feeds the engagement metrics and the pro-rata payout pool split across rights_holder_id. Using fct_stream (content_version_id, played_seconds, is_qualified, started_at) and dim_content_version (content_version_id, content_id, content_type, duration_seconds, rights_holder_id), design the decision. Give the primary metric, guardrails, what you would compute before anyone votes, and how published history is handled. Name who is made worse off.

Approach

1. Open by separating the two jobs the flag is doing: an engagement definition is a measurement choice that can be changed as long as history is restated, while a payout definition is a contractual rule whose change is a transfer of money between counterparties. Proposing one change to a shared flag is proposing both, and the first design decision is whether to split the flag into two named definitions.
2. Compute the redistribution before the argument starts: on one closed month, recompute every rights_holder_id's pro-rata share of the pool under both thresholds and report the distribution of the change. The loss concentrates on short-duration catalogue and on skip-heavy start sources, so the transfer is systematic by counterparty rather than noise.
3. Choose a primary metric that the threshold does not control: qualified hours per active account-week sums played_seconds, so a threshold move changes only which rows enter the sum and not the unit of the answer. Publish alongside it the share of total played_seconds that the threshold excludes, which is the single number that says how consequential the choice is.
4. Set the guardrail on the distortion the threshold creates: median and tenth-percentile duration_seconds of qualified music_track streams, and the count of distinct content_id receiving any qualified stream. A 60-second gate demotes genuinely short forms as well as engineered ones, and catalogue breadth is where that shows first.
5. Fix the history rule before any number is published: recompute at least thirteen months under the new definition and publish only the restated series, because a series that changes definition mid-line will be read as a product event by everyone who was not in this meeting, and the next quarter's review will attribute it to whatever shipped that week.
6. State the trade-off and the loser by name: the higher gate reduces the payout incentive for engineered short items, and it also cuts short tracks, interludes and kids content whose real completion is under 60 seconds. The per-content_type threshold is the alternative; its cost is that per-stream comparisons across content types stop being meaningful, which the metric tree must then forbid.

Worked solution (40 min)

1. Recompute is_qualified at both thresholds over one closed month and report two totals: qualified stream count and the share of total played_seconds that each threshold excludes.
2. Aggregate qualified streams to rights_holder_id under both thresholds, convert each to a pro-rata share of a fixed pool, and report the distribution of share change, calling out the largest losers and their median duration_seconds.
3. Show the mechanism numerically: for a catalogue of 180-second tracks a 60-second gate requires completion_ratio above 0.33 rather than 0.167, so the exclusion rate is a direct function of the duration distribution and is not comparable across content types.
4. Write the primary metric and the excluded-seconds share, then the breadth guardrail as distinct content_id with any qualified stream, before and after.
5. Write the history rule, the restatement horizon and the publication ban on mixed-definition series, and the one-paragraph statement of who loses and by roughly how much.

Expected result: The answer separates the engagement definition from the payout rule and says a shared flag conflates them. Primary: qualified hours per active account-week, threshold-invariant in unit, published with the share of played_seconds excluded. Guardrails: duration distribution of qualified music_track streams and count of distinct content_id with any qualified stream. History restated at least thirteen months with no mixed-definition series published. The losers are short-form catalogue owners, named before the vote with the redistribution quantified.

Verify it yourself

• The redistribution figure exists before any recommendation is made, so the decision is priced rather than argued.
• The primary metric's unit does not change when the threshold changes, which is what makes it publishable across the transition.
• The exclusion share is reported per content_type, since a single pooled exclusion rate hides that the gate bites unevenly by duration.
• The restated history is the only series published, and the restatement horizon is stated as a number of months rather than left to judgement.

Follow-up

• Would a per-account allocation of the pool rather than pro-rata change your recommendation, and which rights holders swap places between the two schemes?
• Who has to be told before this ships, and what is the smallest honest description of the change you would give them?
• A stakeholder proposes running the two thresholds in parallel for a quarter. What does that solve and what does it not?

Weekly listening hours fell; check the calendar first

Easy · seasonality · data-completeness · metric-hygiene

A dashboard reports qualified hours per active account-week — sum(played_seconds)/3600 over fct_stream rows with is_qualified, divided by distinct (account_id, ISO week) pairs having a qualified stream — down 9% against the prior week. It is being read on a Wednesday and the current week is still open. Two public holidays fell inside the prior week. Using fct_stream (started_at, event_date, played_seconds, is_qualified, account_id, play_territory), establish whether anything real changed. Deliverable: a corrected comparison plus a written rule for how this chart is read from now on.

Approach

1. Stop comparing an open week to a closed one. Either truncate the prior week at the same weekday the current week has reached, or exclude the open week entirely. A Wednesday read covers three of seven days and the four missing days include both weekend days.
2. Establish the day-of-week profile before judging any week: qualified hours by ISO weekday across the last eight weeks. A truncated week is not a scaled-down full week, it is a systematically weekday-heavy one, and the ratio distorts in a specific direction — the numerator loses the two highest-hour days while the denominator only loses accounts who stream exclusively at weekends.
3. Test partition completeness separately from seasonality. Recompute a day that closed two weeks ago and compare it against the value stored at the time. Offline playback uploads in bulk when the device reconnects, so a recent event_date keeps accruing for days; and if the pipeline stamps event_date at ingest rather than from started_at, those hours land on the wrong day entirely, which is a different defect with a different fix.
4. Separate the holiday from a trend. Cut the prior week by play_territory and split territories that observe the holidays from those that do not, then recompute the week-over-week change on the non-observing set. A holiday that lifts consumption in two large markets raises the prior week's baseline, so the current week reads as a fall with no behaviour changing anywhere.
5. Restate the comparison as complete-week against complete-week with the territory cut shown, and write the reading rule down: the current week is suppressed until it closes plus a stated late-arrival lag, and holiday weeks are annotated on the chart rather than explained in a thread each time.

Follow-up

• The holiday raised hours but also raised the active-account denominator. Which way does that push the ratio, and is the sign consistent with what you observed?
• What evidence sets the late-arrival lag, rather than picking a round number of days?

Suggested practice checklist

For a candidate whose interviews will centre on A/B testing, metric movement and causal claims. Design comes before arithmetic, arithmetic before analysis, and the week ends by rehearsing the readout rather than the derivation.

Day 1 — Design one test end to end on paper

• Take a single feature change and write the full design: randomization unit, the exact point of exposure, the primary metric with its grain, guardrails, allocation, planned duration, and the decision rule committed before any data exists.
• Write why the randomization unit must sit at or above the level where treatment can spill over, and give one case where user-level randomization is still contaminated (shared accounts or devices, or two participants in the same marketplace).
• State in advance what you will do if the primary metric is flat while a secondary metric is significant.

Deliverable: A one-page test design with a decision rule written before launch.

Day 2 — Power arithmetic until it is automatic

• Compute required sample size per arm for a binary metric with the normal approximation, n is approximately 2 times (z for alpha/2 plus z for power) squared times p(1 minus p) divided by delta squared, for baselines of 2, 10 and 40 percent at a 5 percent relative lift, and note that for a fixed relative lift the requirement falls as the baseline rises because delta grows proportionally with p.
• Redo the calculation for a continuous metric using variance in place of p(1 minus p), and show why a heavy-tailed quantity such as revenue per user needs either far more traffic or a capped version with a stated cap.
• Convert one of the results into weeks given a weekly eligible traffic figure, then list the two honest ways to shorten it (accept a larger detectable effect, or reduce variance) and write why quietly lowering the power target is a decision to miss more real wins, not a speedup.

Deliverable: A small script or sheet that maps baseline, minimum detectable effect, alpha and power to sample size and weeks, cross-checked against a published calculator.

Day 3 — Variance and the unit-of-analysis problem

• Take a ratio metric whose denominator is not the randomization unit (clicks per session, randomized by user) and compute the standard error twice, once naively at session level and once by the delta method or a user-level bootstrap, then record how much the naive version understates it.
• Implement CUPED on simulated data: choose a pre-period covariate X measured before assignment, estimate theta as Cov(Y, X) divided by Var(X), and analyse Y minus theta times (X minus its mean) in place of Y. Confirm the variance of the adjusted outcome equals the raw variance multiplied by one minus the squared correlation between Y and X, so a correlation of 0.45 removes about 20 percent of the variance and not 80.
• Now run that simulation a few hundred times and confirm the adjusted effect estimate is unbiased for the same effect rather than numerically identical to the raw one. Within any single run the two differ, sometimes by a large fraction of the true effect, because the two arms' pre-period covariate means never coincide exactly in a finite sample; they agree in expectation, which is the property that matters and the one to state out loud.

Deliverable: A notebook showing the adjusted estimator with a measurably smaller variance than the raw one, plus a repeated-simulation table showing the two estimators agreeing on average while differing run by run.

Day 4 — Validity threats you can actually test for

• Run a sample ratio mismatch check as a chi-square goodness-of-fit test against the intended allocation, and write the three causes you would chase first (assignment logged before exposure, an arm-specific redirect or load failure, bot filtering applied asymmetrically).
• Simulate peeking: generate A/A data, test daily at alpha 0.05 across 14 looks, record the inflated false positive rate, then apply an alpha-spending boundary or commit to a fixed horizon and confirm the rate returns to nominal.
• Write how you would separate a novelty effect from a durable lift using the treatment effect plotted against days since first exposure, and what shape would change your recommendation.

Deliverable: One table showing the peeking false positive rate before and after correction, plus a written SRM triage list.

Day 5 — When randomization is not available

• Write the identifying assumption for difference-in-differences (parallel trends in the absence of treatment), then plot pre-period trends for two candidate control groups and justify rejecting one of them.
• Design a switchback test for a change where user-level randomization would leak across participants, choosing a time-block length against the carryover you expect and saying how you would detect carryover in the data.
• List what an interrupted time series or a synthetic control buys you and the one thing neither can rule out: an unobserved shock that coincides with the launch.

Deliverable: A one-page memo recommending a single quasi-experimental design and naming its weakest assumption explicitly.

Day 6 — The readout query

• Write the assignment-to-exposure join that returns exactly one row per unit per experiment, and handle units appearing in both arms by excluding and counting them rather than silently keeping one.
• Compute the per-arm metric, its variance and the relative lift with a confidence interval in SQL, then reproduce the identical numbers in a notebook as a cross-check.
• Add a segment breakdown and write the sentence that keeps it from being p-hacking: segments declared in advance, everything else reported as exploratory and corrected for multiplicity.

Deliverable: A single query that outputs the full readout table, matched to a notebook recomputation.

Day 7 — Present it to someone who will not read the appendix

• Give a 10-minute readout of a real or simulated experiment in the order decision, number, uncertainty, caveat.
• Have your listener ask "can we ship it" in the case where the primary is flat and a guardrail moved, and answer with a recommendation rather than a request for more data.
• Rewrite your opening line so the recommendation lands before any methodology.

Deliverable: A one-page readout whose first line is the recommendation.

Rigour against a deadline

Nearly every data role forces a trade between the analysis you want and the one that fits the decision window. Prepare a case where you deliberately shipped something less rigorous, named the weakness to the person relying on it, and said what would change your answer. The naming is the part interviewers listen for.

How do you balance the need for rigorous research with the pressure to deliver results quickly?

Medium · behavioural and stakeholder questions

Approach

1. Name the disagreement or constraint, and how you resolved it with evidence.
2. Close with what you would do differently, concretely.
3. 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?

Scope a one-line request about home row performance

Easy · scoping · stakeholder communication · metric definition

A director messages you: "Is the new home row working?" Nothing else. A new ranker_version has been serving a fraction of profiles for eleven days. You have fct_impression (surface, slate_position, ranker_version, is_exploration_slot, logging_propensity, experiment_assignment_id, was_clicked) and fct_stream (impression_id, start_source, is_qualified, played_seconds). You get a fifteen-minute call before they go into a rollout meeting. Deliverable: the three questions you ask before writing any SQL, the single primary metric you commit to with its guardrail, and the questions you tell them this data cannot answer.

Approach

1. The probe is whether you convert a vague request into a decision before producing a number. Ask what happens at each answer — rollback, widen, iterate — because a question whose answer changes nothing is a report request and should be scoped as one.
2. Pin the unit of analysis out loud. fct_impression is at (profile, slate, slot) grain and experiment_assignment_id is per assignment, so the comparison must be aggregated to the assignment unit first; comparing impression-level rates lets a change in slate length move the metric on its own.
3. Commit to one primary metric from the tree — qualified hours per active account-week for assigned accounts — and name the guardrail pair explicitly: share of qualified streams with start_source = 'autoplay_continuation', and median completion_ratio within content_type. A ranker can lift qualified stream counts by queueing short items that clear the 30-second threshold, and the guardrail is the only thing that catches it.
4. State the refusals with structural reasons, not time reasons: eleven days gives no matured cohort, so month-6 retention and net revenue per active account-month are unanswerable; and logging_propensity is populated only where is_exploration_slot = true, so the positivity condition for an off-policy estimate fails outside those slots.
5. Write the scope back in one paragraph — decision, metric, guardrail, the date the read becomes valid — and get it agreed in the thread before querying, so the number that arrives is the number that was asked for.

Follow-up

• They reply "just give me click-through by slate position." What do you say, and what would that number actually tell them?
• The eleven days include a weekend and a large release landing on day six. Does that change the metric you commit to, or only the read date?
• What would have to be true for you to be willing to answer the retention question from this experiment?

Defend a flat result on a flagship discovery launch

Medium · defending findings · statistical power · experiment readout

A new ranker ran to 50 percent of accounts for four weeks. Your read: qualified hours per active account-week is +0.4 percent with a 95 percent interval of [-0.9 percent, +1.7 percent]. Meanwhile the share of qualified streams with start_source = 'algorithmic_slate' rose six points and the share with start_source = 'search' fell five. The team reads the slate shift as the win and wants to ship. The launch review is Friday. Deliverable: a five-minute verbal position and the one table you put on the screen.

Approach

1. The probe is whether you can hold a position under social pressure without overclaiming in the other direction. Separate the two claims explicitly: the start-source shift is well measured and real; the hours effect the launch was justified on is not distinguishable from zero at this sample size. Those are different statements and only one is contested.
2. Do the power arithmetic before the meeting, using the observed per-account variance on a right-skewed hours metric. Arrive able to say "at this n we could not have detected less than X percent", which is a fact, instead of "it didn't work", which is an opinion the room can simply disagree with.
3. Show the substitution as arithmetic, not interpretation: decompose treatment hours by start_source and show slate hours rising by approximately what search hours fell. A start-source mix shift with no change in total is exactly what displacement looks like, and the decomposition sums to the total so there is nothing to argue about.
4. Refuse the symmetric overclaim. The interval's upper bound is commercially meaningful, so "the ranker does nothing" is not supportable either; say that out loud, because it is what makes the rest of your position credible.
5. Offer the falsifiable next step with a price: a longer read powered for the hours effect, or CUPED on a pre-period consumption covariate measured before assignment, with the required account-weeks stated. Put the disagreement on the pre-registered metric, not on the team's judgement.

Follow-up

• The PM argues start-source mix is a leading indicator of retention. How would you test that claim, and how long would it take?
• Suppose the interval had been [+0.1 percent, +1.9 percent]. Does your position change, and by how much?
• The decision goes against your read and the ranker ships. What do you do on Monday?