Interview guides · Bell

Bell Data Scientist Interview Questions & Guide 2026

Take-home style practice for data scientist candidates: a messy dataset, a fixed time budget, and an analysis you write up and defend.

The 60-second brief

As a Data Scientist at Bell, you will sit at the intersection of advanced analytics, product strategy, and large-scale telecommunications data. Your work directly influences how millions of customers experience communication, media, and digital services across Canada. By transforming massive streams of subscriber, network, and marketing data into actionable intelligence, you help shape business decisions that drive subscriber retention, optimize network performance, and personalize customer journeys.

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

Focus areas

Correct discovery slates for position bias
Split voluntary churn from involuntary payment failure
Measure catalogue breadth beyond head consumption

Overview

This role requires a balance of rigorous technical execution and commercial intuition. You will tackle complex problem spaces ranging from marketing attribution and churn prediction to real-time network optimization and consumer behavior modeling. Whether you are building predictive machine learning pipelines or designing experiments to test new product features, your analyses will guide senior stakeholders and product teams in making high-stakes, data-informed choices.

Expect a fast-paced environment where your ability to communicate technical findings to non-technical partners is just as important as your modeling skills. You will work alongside cross-functional teams of software engineers, data engineers, and product managers to bring models from conceptualization into production. Success in this role demands intellectual curiosity, technical versatility, and a deep commitment to delivering measurable business value.

Where to focus your preparation

Treat the sections below as preparation areas and confirm the format with your recruiter.

Preparation focus

No round sequence has been reported for this company, so work the categories below and confirm the format with your recruiter.

What to demonstrate

Breadth across SQL, experimentation and product reasoning
Ability to state assumptions before choosing a method

How to prepare

Drill the practice exercises below and time yourself
Prepare three quantified stories about decisions you drove

Where candidates lose points

Counting plays without a qualification threshold, or changing the threshold without restating history.

Playback arrives as heartbeats, so a play only exists once you decide what counts, and the common 30-second convention is not a neutral analytics choice: in music it is also the boundary at which a play becomes payable, which makes the warehouse definition a payout definition. The threshold interacts violently with content length, so a catalogue of three-minute tracks and one of forty-minute episodes move in opposite directions when you change it, and a skip-heavy surface can add plays while adding no hours. Any metric mixing pre-threshold and post-threshold counts, or pooling short-form and long-form on a per-stream basis, moves by double digits for reasons that have nothing to do with the product.

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

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

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.

Naming a model class before naming the deployment constraints

Set out the latency budget, the label delay, the retraining cadence, the interpretability requirement and the number of labelled examples, then pick the model that fits them. A boosted-tree answer to a problem where each decision must be explained to the affected user is a well-executed answer to the wrong question.

Questions & practice

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

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

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?

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.

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?

Qualified hours per active account-week from its written definition

Easy · pandas · metric-definition · aggregation

You are given fct_stream as a pandas frame (stream_id, account_id, started_at in UTC, played_seconds, is_qualified) and dim_account (account_id, is_test_account), plus an as-of timestamp. Implement this metric exactly: the numerator is sum(played_seconds)/3600 over qualified streams started inside the window; the denominator is the count of distinct (account_id, ISO week) pairs with at least one qualified stream in that week; the window is the four most recent ISO weeks that are complete as of the given timestamp. Exclude test accounts. Return the scalar and the four per-week rows.

Approach

Drop test accounts first, then filter to is_qualified. The denominator is defined on qualified streams, so filtering after grouping quietly lets an account-week in on the strength of an unqualified stream.
Derive the ISO key with started_at.dt.isocalendar() and keep BOTH iso_year and iso_week. The week number alone collides every January and silently merges two different years of a week into one cell.
Fix the window from the as-of timestamp rather than from max(started_at): a week is complete only when its Monday 00:00 UTC plus seven days is at or before the as-of instant, which is what excludes the partially observed current week.
Aggregate once: sum played_seconds for the numerator, and take the number of unique (account_id, iso_year, iso_week) tuples for the denominator, not the number of unique accounts.
Emit the four per-week rows as well, and confirm the scalar equals the account-week-weighted mean of the four weekly ratios.

Worked solution (20 min)

Join is_test_account onto the stream frame, drop the test rows, and keep only is_qualified == True.
Build iso_year and iso_week from started_at with .dt.isocalendar(), and a week_start timestamp (Monday 00:00 UTC) for the completeness test.
Select the four distinct (iso_year, iso_week) keys with the largest week_start whose week_start + 7 days is at or before the as-of timestamp.
Numerator: played_seconds.sum()/3600 over those weeks. Denominator: drop_duplicates on (account_id, iso_year, iso_week) and take the row count.
Group by the ISO key for the four per-week rows (hours, account_weeks, ratio), and divide the totals for the scalar.

Expected result: One float equal to total qualified hours divided by the count of distinct (account_id, iso_year, iso_week) pairs, plus a four-row frame whose hours column sums to the numerator and whose account_weeks column sums to the denominator.

Verify it yourself

The scalar equals the account_weeks-weighted mean of the four weekly ratios, and does not equal their unweighted mean unless every week has identical account_weeks.
The denominator is at most four times the number of distinct qualified accounts in the window, with equality only if every such account streamed in all four weeks.
A stream started on 2025-12-30 lands in iso_year 2026, iso_week 1. If it lands in 2025 week 1, the year column was dropped somewhere.
Leaving test accounts in raises the numerator and the denominator together, so the ratio barely moves. Verify the exclusion on the two counts, not on the ratio.

Follow-up

A profile-level version of this metric reads lower per unit than the account-level one. Which would you use for a personalisation readout, and what does each unit actually aggregate?
Offline playback is uploaded in bulk days after it happened. What does that do to the most recent complete week, and how would you decide when a week is safe to publish?
How would this metric move if the qualification threshold rose from 30 to 60 seconds, separately for a short-form-heavy and a long-form-heavy catalogue?

Write a SQL query using SQL window functions to calculate the running total of data usage per subscriber over the last rolling thirty days.

Medium · sql and data manipulation

Approach

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

Follow-up

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

Net revenue per active account-month without fanning out periods

Hard · fan-out · revenue recognition · grain

fct_subscription_period has account_id, period_start_ts, period_end_ts, net_amount_usd and payment_status. fct_stream has account_id, started_at, played_seconds and is_qualified. Compute net revenue per active account-month for March 2026: the numerator is subscription revenue over periods with payment_status in ('paid','retried_paid'), recognised pro rata by the days of each period falling inside March; the denominator is distinct accounts with at least one qualified stream in March. A colleague's draft joins the two tables on account_id, filters streams to qualified March rows, and sums net_amount_usd; its numerator is roughly forty times too large. Explain the factor and write the correct query.

Approach

Name the mechanism and decompose the number. The join is one period to many streams, so each period row is duplicated once per matching stream and its net_amount_usd is added that many times: the naive sum equals the un-prorated period sum weighted by each account's March qualified-stream count, which is the net_amount_usd-weighted mean stream count over the accounts the inner join keeps. The distance from there to the correct numerator has two further parts, in opposite directions — pro-rating shrinks the correct figure, while the inner join has dropped every period belonging to an account that streamed nothing in March. A weighted mean near thirty streams against a pro-rata factor near 0.75 is what puts the draft around forty times high; the number is not arbitrary, which is why it looks plausible enough to ship.
Adopt the rule that produces the fix: two fact tables never meet at row grain. Aggregate each to account grain first and join the aggregates, or — better here — do not join at all, because the numerator and the denominator are independent scalars that share no key.
State the interval convention before writing the arithmetic. Treating periods as half-open [period_start_ts, period_end_ts) makes consecutive periods tile without overlap; overlap_days = GREATEST(0, LEAST(period_end_ts, DATE '2026-04-01') - GREATEST(period_start_ts, DATE '2026-03-01')) in days, and the contribution is net_amount_usd * overlap_days / total_days_of_period. An annual period contributes roughly 31/365 of its amount.
Build the denominator on its own: COUNT(DISTINCT account_id) FROM fct_stream WHERE is_qualified AND started_at >= '2026-03-01' AND started_at < '2026-04-01'. Do not restrict it to paying accounts — the metric deliberately holds subscription and advertising revenue to one engaged-account denominator, and filtering to payers is what makes a tier migration look like two unrelated numbers moving.
State the closing rule with the result: the month must be at least 45 days closed, because refunds and chargebacks are recorded against periods late and an open month always reads high.

Worked solution (35 min)

Reproduce the bug deliberately: compute the naive joined sum, and beside it the un-prorated SUM(net_amount_usd) over the same period rows restricted to accounts with at least one qualified March stream. Their ratio is the fan-out multiple, and it is the only ratio in this problem that has a closed form.
Write the overlap expression and verify that one period's contributions, summed across every month it touches, reconstruct its net_amount_usd exactly.
Compute the denominator independently and combine the two as one-row CTEs joined with CROSS JOIN, so neither side can fan out the other.
Re-run for February and March and confirm no revenue is counted in both, which is the boundary test for the half-open convention.

Expected result: A single row of numerator_usd, active_accounts and their quotient. Each period's pro-rata contributions summed over all months it spans reconcile to its net_amount_usd within floating-point rounding.

Verify it yourself

Naive joined sum divided by the un-prorated SUM(net_amount_usd) over the same period rows — restricted to the accounts the inner join keeps, meaning those with at least one qualified March stream — equals the net_amount_usd-weighted mean qualified-stream count over those accounts. Do not expect that identity against the correct numerator: that one is pro-rated to March and also carries the periods of accounts that streamed nothing, so the two differ by more than a single factor.
A period's contributions across every calendar month it touches — thirteen months for an annual period that does not begin on the 1st — sum to its net_amount_usd.
No period contributes anything to March when period_end_ts <= 2026-03-01 or period_start_ts >= 2026-04-01.

Follow-up

One account holds two overlapping periods after a plan migration. Whose revenue counts for March, and does your pro-rata still sum to each period's own total?
Advertising revenue belongs in this numerator. What grain does it arrive at, and what breaks in your query when you add it?
How would you turn this into a monthly time series without rewriting the date literals, and what would you do about periods that span three months?

How would you design a metric to measure the long-term health of a new streaming feature on our mobile platform?

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you evaluate the success of a newly launched marketing campaign targeting high-value data subscribers?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.

Follow-up

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

A key engagement metric dropped by fifteen percent week-over-week. Walk me through how you would diagnose the root cause.

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.
Fix the population and the time window before naming any metric.

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?

What framework would you use to decide whether to roll out a major redesign of our self-service customer portal?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
State your assumptions explicitly before working the problem.
Work from the decision backwards to the evidence you would need.

Follow-up

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

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?

Consumption fell the week a new ranker shipped

Hard · confounding · catalogue-supply · causal-inference

Qualified hours fell 4% in the seven days after a home-row ranker shipped to 100%, and a rollback is being demanded for tomorrow. You have fct_stream (played_seconds, is_qualified, content_version_id, start_source, started_at, play_territory), fct_impression (ranker_version, content_version_id, rendered_at), dim_content_version (content_id, parent_id, rights_expires_at, available_territories, catalogue_added_ts, is_active_version), and no holdout — the rollout went from 5% straight to 100%. Determine whether the ranker caused the fall. Deliverable: a defensible causal read, or an explicit statement of what cannot be concluded and what you would run instead.

Approach

Check supply before demand. Sum the prior period's qualified hours on content_version_ids whose rights_expires_at falls inside the window, whose is_active_version flipped, or whose available_territories lost a territory. If titles carrying several points of hours left the catalogue that Monday, the fall is partly accounted for before the ranker is even discussed.
Separate the mechanically unavailable from the merely deranked. Hours lost on items still available but no longer surfaced belong to the ranker; hours lost on items that cannot legally be served do not, and no ranking change recovers them. Only the first category is evidence for a rollback.
Recover a comparison the rollout destroyed. With no holdout, use the 5% phase as a pre-period and territories unaffected by the expiry as a control, running difference-in-differences with the expiry as the treatment. State the parallel-trends assumption out loud and test it on the four pre-window weeks; abandon the estimator if it fails rather than reporting it with a caveat.
Measure displacement instead of gross hours. For accounts whose usual items left, did total hours fall or did they substitute within the catalogue? Fully substituted removal costs nothing in hours and shows up only in the affected rights holder's payout, which is a different problem with a different owner.
Ask separately what the ranker did to exposure: impression share by content_id under each ranker_version, and top-1% exposure concentration. A ranker that concentrates exposure can depress hours through a mechanism nobody has named yet, and this is what distinguishes that story from the rights story.
Refuse the causal claim the rollout cannot support. Say plainly that a 5%-to-100% ramp across a confounded week yields no unbiased effect estimate, and propose the switchback or re-randomised holdout that would, with its cost stated.

Follow-up

Design the switchback: slice length, burn-in long enough to clear carryover on a personalised surface, and how the standard error handles correlation between adjacent slices.
If the rollback is ordered regardless of your analysis, what do you measure during the rollback to get a second, independent read?

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.

Where your data came from

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

How would you prioritize a backlog of data science feature requests coming from different product stakeholders?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Quantify the outcome, including what you would not claim credit for.
Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

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

Announce a stream-definition change that shifts payouts

Hard · cross-functional communication · metric definition · payouts

You find that the 60-second idle gap used to sessionise heartbeats into fct_stream rows splits one continuous listen into two streams whenever a phone backgrounds briefly on cellular. Correcting the gap lowers qualified stream counts on phones by an estimated four percent; total played_seconds is unchanged. Per-stream counts drive rights-holder payout shares. Deliverable: what you verify before telling anyone, the order in which you take it to the engineering owner, finance and content partnerships, and your recommendation on restating history.

Approach

The probe is whether you can tell a technical correction from a commercial decision and keep them apart in the room. Verify the split streams are genuinely one listen before anything else: same profile_id, same content_version_id, contiguous max_position_seconds across the boundary, and a gap distribution with a spike at the background-timeout duration rather than a smooth tail.
Compute the distributional effect, not the average. The four percent aggregate is not what anyone will argue about; recompute under the corrected gap and report which rights_holder_id groups gain and lose share, because short-form catalogue on mobile is where the splits concentrate and that is not spread evenly across counterparties.
Defend the new rule on its own terms rather than on the direction of the number. The idle gap is a choice, so the argument is evidence that the two rows describe one continuous listen — never that the corrected count is lower and therefore more conservative, which invites the symmetric accusation next time the fix goes the other way.
Name the ownership boundary out loud: engineering owns the sessionisation rule, finance and partnerships own whether payouts are restated. Conflating them is how a correct fix gets blocked by a commercial objection it should never have been exposed to.
Sequence the conversations so the number stops moving before it leaves the building: engineering owner first to confirm the rule and land the fix, finance second to size the restatement, partnerships last. Recommend restating history for internal metrics so trends stay comparable, and recommend against retroactive payout adjustment unless the contracts require it — naming who must answer that contractual question rather than answering it yourself.

Follow-up

Partnerships asks you to hold the fix until after the quarter closes. What do you do, and who else needs to know you were asked?
How do you present a change that raises some counterparties' shares and lowers others', in the same meeting, to people who will compare notes afterwards?
The four percent estimate itself has an interval spanning roughly two to seven percent. Does that change the recommendation or only the sequencing?

Arbitrate three requests for the same analyst week

Easy · prioritisation · stakeholder management · scoping

Three requests land on the same Monday for the same week of your time. Growth wants trial-to-paid conversion recut by signup_channel before a budget meeting on Thursday. The playback team wants rebuffer ratio cut by cdn_pop, device_type and network_type after a bitrate ladder change went out Friday. Catalogue wants a displacement estimate for a title that arrived on a rights window last month. You have roughly four working days. Deliverable: your ordering with the criterion behind it, what you tell the two people who do not get the week, and one thing you ship to all three by Tuesday.

Approach

The probe is your criterion, not your ordering. Rank by how reversible the pending decision is and how fast the evidence decays, and say so; ranking by requester seniority is the default answer and reads as one.
Put the playback request first on those grounds: a bitrate ladder change is live and degrading real playback now, and rebuffer ratio is defined on a trailing 24 hours, so the evidence is perishable and the query is small. Cheap and perishable beats expensive and durable.
Ask each requester what they do differently at each plausible answer. The trial-to-paid recut is a defined tree metric — trailing 28 days, read at trial length plus 21 days so dunning retries have resolved — so it is largely a filter plus an hour confirming that unattributed stays its own line rather than being reallocated pro rata.
Treat the displacement estimate as the only genuinely new analysis and scope it rather than squeezing it: it needs a matched comparison group and a pre-registered window, because consumption of the rest of the catalogue moves on the release calendar regardless. Defer it with a date and a stated shape, never silently.
Reply to all three in one thread with the ordering and the criterion visible, so a stakeholder who disagrees argues with the criterion instead of escalating about their position in the queue.

Follow-up

The catalogue lead escalates to your manager on Tuesday. Walk me through that conversation.
How does the ordering change if the bitrate ladder change was already rolled back on Saturday?
All three deadlines are genuine and none can move. What do you actually cut?