Interview guides · Spotify

Spotify Data Scientist Interview Questions & Guide 2026

Case practice for Spotify data scientist candidates: scope a vague prompt into a measurable question before you analyze it.

The 60-second brief

A Data Scientist at Spotify plays a pivotal role in shaping how millions of users worldwide discover, consume, and connect with audio content. Working at the intersection of product, engineering, and business strategy, data scientists translate massive volumes of streaming data into actionable insights. Whether you are optimizing the recommendation algorithms behind Discover Weekly, analyzing user engagement on podcasts, or designing experiments for new social features, your work directly influences the daily experience of over 500 million active users.

Allocate prep to your weakest link rather than your favourite topic. Of the three things that usually gate the outcome (SQL that is correct under messy joins, sound reasoning about experiments, and structured framing of an open-ended problem), candidates tend to over-invest in modelling theory and under-invest in framing.

Focus areas

Correct discovery slates for position bias
Define a qualified stream and defend it
Diagnose rebuffering by device, network and POP

Overview

At Spotify, data science is not a siloed function. You will operate within cross-functional "squads" alongside product managers, software engineers, and product designers. The scale and complexity of the platform mean that even minor optimizations can lead to significant shifts in user retention, streaming hours, and subscription revenue. This environment demands a unique combination of strong technical capabilities, deep product intuition, and the ability to communicate complex statistical concepts to non-technical stakeholders.

The role is highly impactful because Spotify is fundamentally a data-driven company. From licensing negotiations to creator tools, every major business decision is backed by rigorous analysis and experimentation. Joining the team as a Data Scientist means stepping into a fast-paced, highly collaborative culture where your insights will directly guide the evolution of the world's leading audio streaming platform.

The Spotify interview process

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

Recruiter Phone 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

Technical Screen

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

Hiring Manager Interview

This conversation decides whether you can be handed a problem nobody has finished defining and left alone with it for a few weeks. The manager is listening for how you behave when the brief is thin: what you clarify before starting, and what you settle on your own rather than escalating. Most candidates over-index on technical depth here and under-describe the decisions they actually owned. Say who wanted the work, what you chose not to do, and where you would have stopped and asked. A clean account of your own judgement carries this round further than a longer project list.

What to demonstrate

Whether you can name a decision that was yours alone, as opposed to one the team arrived at
How you respond to a request that arrives with no success metric attached to it
Whether the effort you estimate for a piece of work matches the work you just described doing
What you escalate, and how long you sit on a problem before you do

How to prepare

For each project you plan to raise, write one sentence saying what would not have happened if you had not been on it, and check that the sentence is about an outcome rather than an artefact
List the decisions in your last project that were genuinely yours, and for each one write down the option you rejected and why
Prepare the project that went badly: the point at which you knew, who you told, and what it cost before it was caught

Final Loop

A day of back-to-back interviews samples your floor, not your ceiling. Four hours in, the habits that carry a good answer are the first to go: restating the question before solving it, asking what the data would have to look like, checking a number before quoting it. What the day decides is whether the tired version of you is still someone to leave alone with an ambiguous problem. The round that sinks a candidate is usually not the hardest one. It is the one immediately after the round that went badly.

What to demonstrate

Whether the late rounds get the same clarifying questions as the first one, or whether you start answering immediately to save effort
Whether a weak answer stays in the room it happened in, instead of following you into the next conversation as apology or distraction
Whether the quality of your questions holds up, since fatigue removes curiosity about the problem before it removes knowledge of the method

How to prepare

Rehearse the length, not just the content: book four mock interviews of different types in one afternoon with short gaps, because the one you need to observe is the fourth
Put the two or three questions you ask at the start of any problem on a card in front of you, so that under fatigue it is a habit you run rather than a decision you make
Decide in advance what the gap between rooms is for: water, one line of notes on anything you promised to follow up, and an explicit close on the round that just ended so it does not travel
Prepare a different closing question for each interviewer, so the end of a long day does not produce the same one four times

Focused Interviews

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

What to demonstrate

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

How to prepare

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

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.

Writing SQL without stating NULL and tie-breaking behaviour

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

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

Walk me through how you would define the "best performing artist" on the platform using a limited, unstructured dataset.

Medium · statistics and probability

Approach

Quantify uncertainty explicitly rather than reporting a point estimate alone.
Say what the estimate is of, and over what population it generalises.
Translate the result into the decision it informs, in one plain sentence.

Follow-up

What sample size would you need to detect an effect half this size?
Which assumption here is most likely to be violated in practice?

If we run an experiment on a new playlist recommendation algorithm and see an increase in total streams but a decrease in user retention, how would you interpret these results?

Medium · machine learning and modelling

Approach

Set a baseline first, so any model has something honest to beat.
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.

Follow-up

Where could label leakage enter this setup?
What would you monitor after launch to know the model is still valid?

Measure catalogue concentration with a Gini written from scratch

Medium · concentration · catalogue · pandas

One calendar month of fct_stream (content_version_id, played_seconds, is_qualified) joins to dim_content_version (content_version_id, content_id, content_type). Roll versions up to content_id, then report, overall and per content_type: the number of content_ids, the share of qualified hours held by the top one percent, and the Gini coefficient over content_ids by hours. Write the Gini from its definition rather than importing one. State which content_ids are in the population you measure over, and defend that choice.

Approach

Roll versions to content_id before anything else. A remaster or a dubbed rendition is a separate content_version_id sharing a content_id, so leaving it split spreads one work's hours across rows and reports the catalogue as less concentrated than it is.
Decide and state the population: content_ids with at least one qualified stream this month measures concentration among what was played, while the full territory-eligible catalogue adds the zero-hours tail and pushes both statistics up. Either is defensible; the two are not comparable to each other.
Implement Gini on the ascending-sorted hours vector as 2 times sum(i times x_i) over (n times sum(x)) minus (n + 1) over n, with i one-based. Verify it on two hand-made vectors before pointing it at real data.
For the top one percent, take ceil(0.01 times n) content_ids by hours descending over the total. Say what you did at the boundary, because with a few thousand titles the rounding rule moves the answer visibly.
Compute each content_type inside its own population, and note that the per-type Ginis do not aggregate to the overall one: concentration is not additive across strata, and the overall figure carries between-type inequality the per-type figures exclude.

Worked solution (25 min)

Filter to is_qualified, join content_id and content_type, then group by content_id summing played_seconds and dividing by 3600.
Write gini(x): drop negatives, sort ascending, n = len(x), return 2 times (arange(1, n+1) times x).sum() over (n times x.sum()) minus (n + 1) over n. Assert gini([1,1,1]) is 0 and gini([0,0,1]) is 2/3.
Top one percent: k = ceil(0.01 times n); sort hours descending and divide the k-largest sum by the total.
Repeat both statistics inside each content_type group, keeping each type's own n.
Assemble a frame of scope, n_content_ids, top1pct_share and gini, with one overall row plus one row per content_type.

Expected result: A frame with one overall row and one row per content_type, where every Gini lies in [0, 1), every top-one-percent share is at least 0.01, and the overall Gini is not the mean of the per-type Ginis.

Verify it yourself

Gini of a constant vector is exactly 0, and Gini of a vector with one non-zero entry out of n is (n - 1) over n.
A separately computed Lorenz curve reproduces the same number through G = 1 minus twice the trapezoid area under it.
Appending the zero-hours eligible catalogue raises both the Gini and the top-one-percent share. If either falls, the append landed in the wrong column.
Hours summed across the per-type rows reproduce total qualified hours, confirming the type join dropped no content_id.

Follow-up

This month's Gini rose by 0.03. Name three things other than a ranking change that could produce that.
How would you measure breadth so a ranking team could act on it, rather than reporting one summary number?
A rights window expired mid-month and removed 400 titles. How do you keep the month-over-month comparison honest?

Write a Python script to parse a nested JSON log of user interactions and output a flat CSV structure containing only specific interaction events.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Compute rates by summing numerator and denominator separately, never by averaging rates.

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?

Given a dataset of user song skips, use Python (Pandas) to identify which tracks have a skip rate higher than the average skip rate for their respective genres.

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
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 would you handle missing values and duplicate records in a large-scale streaming dataset before feeding it into an analysis pipeline?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Paid retention triangle with monthly and annual curves kept apart

Medium · cohorts · retention · subscriptions

fct_subscription_period has subscription_period_id, account_id, period_index, billing_interval, period_start_ts, period_end_ts, payment_status, cancel_requested_ts and renewal_outcome. dim_account has account_id, first_paid_ts and is_test_account. Build a paid-retention triangle: label non-test accounts with the calendar month of first_paid_ts, and for month offsets 0 through 6 report the share of the cohort still holding a period with payment_status in ('paid','retried_paid') that contains the instant first_paid_ts + offset months. The anchor is each account's own first payment, not the first day of the offset calendar month. Report monthly and annual billing_interval as separate curves. Exclude cohorts not yet mature at offset 6.

Approach

Take the cohort key from dim_account.first_paid_ts, not from MIN(period_start_ts). The period table includes trial periods as zero-amount rows with payment_status = 'trial_no_charge', so the minimum start shifts trialling accounts a cohort early and inflates the youngest cohort's offset-0 denominator.
Anchor every offset on that same first_paid_ts; the calendar month is only the row label on the triangle. Anchoring survival on the first day of the offset month instead breaks offset 0 for everyone who did not first pay on the 1st — an account that first paid on the 20th holds no paid period covering that month's 1st, so its offset-0 cell reads as churned and the curve rises from offset 0 to offset 1. A retention chart whose first step goes up is almost always this. Two conventions to write down while you are here: PostgreSQL clamps timestamp + INTERVAL '1 month' at month end, so an account first paid on the 31st is tested on the 28th or 30th at some offsets; and the whole construction assumes first_paid_ts falls inside its own first paid period, which fails if the platform charges shortly before period_start_ts, in which case anchor on that period's period_start_ts instead.
Build the offsets 0..6 as an explicit ordered set and cross-join it to the cohort list. A grid built by aggregating survivors alone loses any (cohort, offset) cell with no survivors, so a collapsing cohort reads as a missing row instead of a zero.
Test survival as interval containment, not date equality: EXISTS a period for that account with payment_status in ('paid','retried_paid') where first_paid_ts + offset months falls inside [period_start_ts, period_end_ts). One annual row contains seven consecutive anniversaries on its own; an equality join against period_start_ts finds it at offset 0 and nowhere else.
Partition everything by billing_interval and never pool. An annual account has had no opportunity to churn before day 365, so pooling makes an annual-heavy cohort read as retentive when what it actually is, is un-renewed. billing_interval lives on the period and not on the account, so take the cohort's interval from the period containing first_paid_ts and hold it fixed for all seven offsets; an account that switches monthly to annual at offset 4 otherwise appears in both curves and is counted twice in the denominators.
Apply maturity last, and against the anchor rather than the label: keep a cohort only when the last day of that cohort month plus six months is at or before the last fully closed day in the data, so every account in it has actually reached its offset-6 anniversary. Otherwise the newest cohorts print 0% at the far offsets for a reason that is entirely calendar.

Worked solution (40 min)

Build the cohort list as (cohort_month, billing_interval, account_id, first_paid_ts) over non-test accounts, taking billing_interval from the period that contains first_paid_ts, and record each cohort's size.
Cross-join cohorts to offsets 0..6 to produce the dense grid before any survivor logic touches it.
Attach a survivor flag per (account_id, offset) with the EXISTS containment test at first_paid_ts + offset months, then aggregate to a share within (cohort_month, billing_interval, offset).
Read offset 0 before anything else: it must be 1.0 in every cell. Where it is not, list those accounts' first_paid_ts beside their first period's [period_start_ts, period_end_ts) — the anchor and the period data disagree, and the fix is in the anchor, not in the survival test.
Apply the maturity filter and pivot offsets into columns so the triangle can be read a row at a time.

Expected result: A grid of (cohort_month, billing_interval, offset 0..6) retention shares. Offset 0 is 1.0 in every cell, because the anchor is the account's own first payment and the period containing it is paid by definition; the annual curves sit at 1.0 through offset 6 unless a period's payment_status was later reversed, since no annual account has reached a renewal decision by month six.

Verify it yourself

Offset 0 equals 1.0 in every cell. Anything less means first_paid_ts falls outside the first paid period it belongs to — a charge recorded shortly before period_start_ts is the usual cause — and the anchor moves to that period's period_start_ts.
Cohort sizes sum to the distinct count of non-test accounts with a first_paid_ts in the covered months, and no account appears in both the monthly and the annual curve.
Each monthly curve is non-increasing in offset unless win-backs are in scope, in which case you can point to the specific rows that produce the rise.

Follow-up

An account migrates plan mid-cohort with renewal_outcome = 'migrated_plan'. Retained or churned, and what does your choice do to the revenue story told beside this chart?
A period fails and a retry succeeds as a new row. Does your survival test find the account at that offset, and should it?
Can you produce a curve for annual accounts that is comparable to the monthly one before twelve months have elapsed, and what do you give up?

How would you build a model to predict user churn for Spotify Premium subscribers? What features would you prioritize?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.

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 would you define and measure the "success" of a newly launched collaborative playlist feature?

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
Name the guardrails that would stop a launch even on a positive primary result.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

What are the statistical implications of running multiple concurrent A/B tests on the same user base, and how would you mitigate potential interference?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Name the guardrails that would stop a launch even on a positive primary result.
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?

Define success before a startup-latency infrastructure change

Easy · metric design · quality metrics · guardrails · mix effects

An infrastructure change is expected to cut startup_latency_ms on tv_app playback. You must define success before the rollout. Available: fct_stream (startup_latency_ms, rebuffer_seconds, played_seconds, peak_bitrate_kbps, end_reason, start_source, device_type, cdn_pop, network_type). Propose one primary outcome metric, the latency statistic you will report and at which quantile, and two guardrails. State how you will stop a mix shift across device_type and cdn_pop from reading as an improvement, and name one way a latency win could be bought rather than earned.

Approach

Separate the input from the outcome out loud: startup_latency_ms is the thing the change moves, but success is whether consumption improved, so the primary metric is qualified hours per active account-week on tv_app and latency is the mechanism metric that explains it.
Choose the latency statistic for the shape of the distribution: startup latency is right-skewed with a long tail from cold caches and poor networks, so report p95 and p99 rather than a mean, because the mean moves with tail mass that a handful of streams contribute and the tail is where abandonment lives.
Kill the mix effect by construction: compute the latency quantiles inside (device_type, cdn_pop, network_type) cells and aggregate with cell weights fixed from a pre-period, so that a rollout reaching wifi and well-provisioned points of presence first cannot show a win that is entirely composition.
Guardrail one on how a latency win is bought: peak_bitrate_kbps and the rebuffer ratio, sum(rebuffer_seconds)/sum(played_seconds) with played_seconds = 0 rows excluded from both sides, because starting playback at a lower ladder rung lowers time to first frame and costs picture quality instead.
Guardrail two on what the ratio hides: start-failure rate over streams with end_reason = 'playback_error' and played_seconds = 0, counted separately, since a point of presence that fails before first frame contributes to neither side of the rebuffer ratio and would otherwise report perfectly.
State the exclusion: cut or exclude start_source = 'resume', because resumed playback starts from a warm buffer and a rollout that shifts the resume share moves pooled latency without any engineering change.

Worked solution (20 min)

Write the primary metric with numerator, denominator and window, restricted to device_type = 'smart_tv' and its streaming-stick equivalents, and state that it is the outcome the change is judged on.
Specify the latency readout: p95 and p99 of startup_latency_ms computed within (device_type, cdn_pop, network_type) cells, aggregated with pre-period cell weights, reported daily against the same cells pre-rollout.
Construct one numerical counterexample showing the mix trap: two points of presence with unchanged within-cell latency but a shift in traffic share between them, and compute that the pooled mean moves while every cell is flat.
Write the two guardrails: peak_bitrate_kbps distribution plus rebuffer ratio with the played_seconds = 0 exclusion stated, and start-failure rate over end_reason = 'playback_error'.
State the exclusion rule for start_source = 'resume' and the reason, and write the one-line ship rule combining primary and guardrails.

Expected result: Primary: qualified hours per active account-week on TV devices. Mechanism metric: p95 and p99 startup_latency_ms within (device_type, cdn_pop, network_type) cells with fixed pre-period weights. Guardrails: peak_bitrate_kbps together with the rebuffer ratio excluding played_seconds = 0, and a separately counted start-failure rate over end_reason = 'playback_error'. The bought win is opening at a lower bitrate rung.

Verify it yourself

The worked mix counterexample shows every cell flat while the pooled mean moves, which confirms the weighting is doing real work rather than being ceremony.
Streams with played_seconds = 0 appear in exactly one metric, the start-failure rate, and in neither side of the rebuffer ratio.
The bitrate guardrail is stated as a distribution, not a mean, so a shift of the low tail onto a lower ladder rung is visible.
Resume-sourced streams are either excluded or reported as their own cut, so a change in resume share cannot move the headline latency.

Follow-up

p99 falls but the mean rises. What do you conclude, and which cut would you look at first?
The change only reaches accounts on app versions above a floor. How does that change what your comparison can claim?
How large a latency improvement would you need before you expect qualified hours to move at all, and how would you size that before running it?

Trial conversion fell for cohorts that have not matured

Hard · cohort-maturity · mix-shift · acquisition-quality

Trial-to-paid conversion — cohort accounts with a period_index = 1 row in fct_subscription_period whose payment_status is 'paid' or 'retried_paid', over cohort size — is reported weekly by trial_start_ts cohort and has fallen from 38% to 29% across five weeks. Trial length is 14 days. You have dim_account (trial_start_ts, signup_channel, signup_platform, signup_country, is_test_account, first_paid_ts) and fct_subscription_period (account_id, period_index, payment_status, dunning_attempts, period_start_ts). Establish whether conversion fell and for whom. Deliverable: an age-aligned series plus a composition-adjusted estimate of the real change.

Approach

Align cohort age first. No period_index = 1 row can exist before the trial ends, so cumulative conversion is identically zero through day 13 and the whole curve lives between day 14 and the close of the dunning horizon — roughly 14 plus 21 days. The three most recent cohorts sit inside that window and cannot read anything but low. Plot each cohort's cumulative conversion against days since trial_start_ts and read every cohort at one common age.
Quantify how much maturity alone explains. Take the conversion-by-age curve from fully matured cohorts, read off the fraction of final conversion it has reached at each age, and multiply that fraction by a mature cohort's final rate to predict what a young cohort should be showing today. Whatever the prediction reproduces is not a regression and should never have been plotted as one.
Decompose the residual across signup_channel, signup_platform and signup_country. Paid social and carrier bundles convert at very different rates from organic, so a weight shift between them moves the blended figure with every channel's own rate flat.
Make the composition adjustment explicit rather than rhetorical: reweight recent cohorts to the channel-by-country mix of a fixed reference cohort and recompute, then publish the reweighted series beside the raw one and name which channels moved and by how much.
Only then look for a within-channel product cause, and rule out the instrumentation confounders first: is_test_account leakage, attribution opt-out inflating the organic bucket at a platform-specific rate, and app-store payment_status rows arriving later than card-direct ones.
Deliver a number with an interval rather than a direction: the age-aligned, composition-adjusted change, with the cohorts too young to read named explicitly as excluded.

Follow-up

Attribution opt-out shifts accounts into the unattributed bucket at a platform-specific rate. How does that corrupt your reweighting, and what do you do about it?
If the residual concentrates in one paid channel, what do you need from marketing before calling it acquisition quality rather than a product regression?
How many mature cohorts do you need before the composition-adjusted change clears the noise floor?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

The project you argued against

Saying no well is a senior skill and it is rarely rehearsed. Think of a time you told someone their analysis was not worth doing, or that the experiment could not answer their question at the sample size available. Explain what you offered instead. Refusal without an alternative reads as obstruction rather than judgement.

Tell me about a time you had to work in a highly multidisciplinary environment with product managers and engineers who had conflicting priorities.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

State the impact of your last year without inflation

Hard · self-assessment · attribution · counterfactual reasoning

You are asked what your work was worth over the last year. Two candidates are on the table. First: a dunning-schedule change you analysed, shipped in March with no holdout, after which the involuntary share of gross churn fell three points. Second: a metric-definition change you drove, which nobody can attribute revenue to. Deliverable: the impact claim you make for each, the counterfactual attached to each, and which one you lead with in a performance review — with the reason.

Approach

The probe is whether you apply the same causal standard to your own work as to a product experiment. Attach the counterfactual before the claim: involuntary share of gross churn responds to card-updater coverage, billing_provider mix, retry schedules and the dunning horizon you read it at, any of which can move three points with the schedule change contributing nothing.
Say which evidence would separate those, then say plainly which you actually have. A staggered rollout by billing_provider, a holdout, or at minimum a flat pre-period series would each support a different strength of claim; with none of them, the honest claim is a contribution to a favourable movement, stated as such.
Check the classification itself before claiming the movement is real: the split between cancelled_voluntary and failed_involuntary must be computed only after the dunning horizon has closed for every period in the month, or retries still in flight are counted as churn and the share reads too involuntary in one direction and corrects in the other.
Price your contribution rather than the outcome. The value of analysis that changed a decision is the decision's delta multiplied by the probability the decision would not have been taken without it, and being explicit about that second factor is most of what honesty means here.
Make the definition-change claim concrete rather than apologetic: name the decisions that would have been taken on the wrong number, the reports it reconciled, the recurring argument it closed, with dates. Then lead with whichever claim survives questioning, not whichever carries the larger number, because an inflated first claim makes the second unbelievable.

Follow-up

Your manager writes up the churn improvement and credits it to you. Do you correct it, and to whom?
How would you have designed the March rollout so that attribution was possible, at what cost in delay?
Name something you worked on last year that had no impact, and say what you learned from that rather than from the wins.

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

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