Interview guides · TELUS Digital AI Community

TELUS Digital AI Community Data Scientist Interview Questions & Guide 2026

SQL practice for TELUS Digital AI Community data scientist candidates: window functions, funnel and retention queries, joins that quietly drop rows.

The 60-second brief

As a Data Scientist at TELUS Digital AI Community, you sit at the intersection of advanced machine learning and practical, large-scale product application. This role is pivotal in transforming raw data into actionable intelligence that drives digital transformation for global clients. You will be responsible for designing and deploying models that solve complex problems, ranging from predictive analytics to natural language processing, ensuring that the AI solutions delivered are not only technically sound but also strategically aligned with business goals.

A large share of questions open as "how would you measure X", where the real work is choosing the metric, fixing its denominator, and defining the population it applies to. Any computation comes last and is frequently not required at all.

Focus areas

Aggregate impression logs without double-counting repeated deliveries
Judge ranking metrics under position bias and feedback loops
Trade engagement gains against negative feedback and prevalence

Overview

The work is characterized by high levels of ownership and technical variety. You will collaborate with cross-functional teams, including product managers, engineers, and stakeholders, to define metrics, optimize model performance, and ensure that AI initiatives deliver measurable value. Because TELUS Digital AI Community operates at a significant scale, you will often find yourself navigating the complexities of data pipelines and model lifecycle management, making this an ideal environment for a Data Scientist who thrives on both the theoretical rigor of statistics and the pragmatic challenges of product-focused development.

The TELUS Digital AI Community interview process

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

Initial Screening

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical Evaluation

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

Cultural Evaluation

Much of what gets scored here happens out loud while you type. Nobody can see your reasoning inside a half-written query, so five silent minutes read as being stuck even when they are not. State the plan in plain language first: which tables, what grain you are aggregating to, and the one filter that defines the population. Then write it. The narration doubles as insurance, because a wrong plan gets caught early and cheaply while a wrong query gets caught at the end with no time left to redo it. A timed statistics section, where one exists, is a separate test with its own clock.

What to demonstrate

Whether the query you write matches the plan you just described
What you do with a hint, meaning whether the correction gets absorbed or the first approach gets defended
Whether you can debug your own wrong output by reading the result set and naming which part of the query produced the anomaly

How to prepare

Solve three problems while screen-sharing into a recording, then watch it back and mark every stretch longer than thirty seconds where you said nothing
Practise compressing the plan into one sentence before typing, then check afterwards whether the finished query actually matched it
Time yourself on statistics questions that carry a business reading, such as what a confidence interval does and does not claim, rather than re-reading notes without a clock

Final Team-Lead Discussions

Where a loop includes a partner from outside the data team, that conversation usually carries the same weight as the technical ones and gets the least preparation. The person opposite you will not follow a derivation and does not need to. They are working out whether having you involved would make their decisions better or slower. The failure mode is not being too technical. It is answering a question about a decision with a description of your method, leaving the translation to them. What they carry into the debrief is the sentence you handed them, not the analysis underneath it.

What to demonstrate

Whether a statistical result arrives as something the partner could act on, with the one caveat that would change their decision kept and the rest left out
Whether you can state what you need from their side, in their terms: instrumentation that does not exist yet, a definition they own, or a holdout they have to agree to
Whether uncertainty is given as a range someone can plan against, rather than as hedging that invites them to ignore the result
Whether you ask what decision is actually on the table before explaining anything

How to prepare

Take a result you know well and write the version for someone who stops reading after one sentence, then the three-minute version, and check the short one is not the long one with the qualifications stripped out
For a past project, list everything you asked a non-technical partner for and how you phrased it, then rewrite each ask so it names what goes unmeasured without it
Practise saying where a result does not apply, out loud, in one sentence that a partner could repeat accurately to someone else

Where candidates lose points

Crediting notifications with the sessions that follow them

Members who open a push notification were already more likely to open the app, so attributing their session to the notification measures intent rather than causation and makes almost any send look profitable. The predictable result is a push-volume increase that shows a large modelled gain and a small real one, paid for later in opt-outs and uninstalls that no single experiment window captures. The only defensible estimate compares a stable send arm against a holdout arm assigned at the decision point, with the held-out decisions logged and suppressed at delivery, over a window long enough to include the opt-out response. Frequency effects are non-linear, so a per-notification incremental rate estimated at one volume does not extrapolate to a higher one.

Using report volume as a measurement of how much violating content exists

Reporting is a member behaviour, not an observation of the content. Report counts rise when the report control is made easier to reach, when a coordinated campaign targets an account, and when the audience shifts toward people who object; they fall when violating content is shown mainly to members who agree with it. A ranker that gets better at matching bad content to receptive audiences will drive reports down and harm up at the same time. Prevalence must come from a random sample of served impressions with recorded selection probabilities, labelled by humans against the written policy, and reported with an interval. Reports are useful as a detection signal and as a demand-side complaint rate, not as a denominator-anchored measure of harm.

Accepting a metric definition without asking about the denominator

Pin down the denominator, the eligibility filter and the time window before computing anything: conversion rate per session, per user, per eligible user and per new user are four different numbers with different behaviour. Restate the definition in one sentence and get agreement before you analyse.

Averaging per-user rates to produce a population rate

Decide which quantity you want: the mean of per-user ratios and the ratio of summed numerator to summed denominator are different estimands, and heavy users dominate one but not the other. For a ratio metric, aggregate numerator and denominator separately and use the delta method for its variance.

Questions & practice

How do you balance trade-offs between precision and recall in a production environment?

Medium · machine learning and modelling

Approach

Say how the offline result would be validated online before it is trusted.
Check what information would not exist at prediction time, and exclude it.
Set a baseline first, so any model has something honest to beat.

Follow-up

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

Simulate the false positive rate of a skewed A/A test

Medium · simulation · heavy tails · type i error

Per-member weekly impressions are strongly right-skewed. Simulate a population from Lognormal(mu=2.0, sigma=1.8), draw N members, split them at random into two arms, and run Welch's t-test on the per-member means. This is an A/A, so the null holds by construction and any rejection is a false positive. Report the empirical rejection rate at a nominal 5 percent for N of 4,000 and 100,000, each under a 50/50 and a 90/10 split, with the Monte Carlo standard error. Add a permutation test on the difference in means as a comparison arm. Say which cell is distorted and why.

Approach

Build the harness so the null is true by construction: draw one population, then assign arms by a random permutation. Nothing differs between arms except the split, so every rejection is an error and there is no effect size to argue about.
Choose B before running and derive the Monte Carlo standard error from it: sqrt(p(1-p)/B), which at p near 0.05 and B = 10,000 is about 0.22 percentage points. Any claim that 5.4 percent differs from 5.0 percent has to survive plus or minus roughly 0.43 points at 95 percent, so pick B for the difference you intend to resolve, not by habit.
Run the four cells. Equal-sized arms are close to robust here because the skewness contributions of the two arms largely cancel in the t statistic; the 90/10 split is where the cancellation fails and the test is genuinely off nominal at small N. Report the numbers rather than asserting the direction in advance.
Validate the harness before trusting the result: swap the lognormal for a Normal population and confirm every cell returns about 5 percent. If the Normal cells are off, the bug is in the code, not in the tail.
Add the permutation arm: shuffle the arm labels, recompute the difference in means, and take the two-sided p as the share of shuffles at least as extreme. Under a random split exchangeability holds exactly, so this arm should sit at nominal in every cell and acts as the reference.
Close with the operational consequence: the fix for the distorted cell is either balanced allocation, a permutation or bootstrap reference distribution, or a pre-registered cap, and a cap changes the estimand so it must be declared before the read, not chosen after seeing it.

Follow-up

A pre-registered winsorisation at the 99th percentile fixes the level. What question is the capped metric now answering, and is it still the one the product owner asked?
Repeat this with the ratio metric impressions per session rather than a per-member mean. What changes in the variance calculation?
How much power did the 90/10 split cost relative to 50/50 at the same total N, and when is the ramp still worth it?

Sessionise an event stream with a thirty-minute inactivity gap

Medium · sessionisation · window functions · vectorisation

events has member_id (int64), occurred_at_utc (datetime64[ns], UTC) and action_kind, roughly 5 million rows in arbitrary order. Close a session after 30 minutes of inactivity for that member. Assign session_id using vectorised pandas only: no groupby.apply, no Python loop over members. Then compute per session the span in seconds between first and last event, the event count, and whether the session qualifies (span of at least 30 seconds and at least one non-negative engagement or authored item). Return the session table and the count of distinct members with at least one qualified session.

Approach

Sort by (member_id, occurred_at_utc) once. Every later step assumes that order, so do it explicitly rather than relying on the input arriving sorted.
Compute the gap as events.groupby('member_id').occurred_at_utc.diff(). The groupby is the whole point: a plain .diff() over the frame measures the gap between the last event of one member and the first of the next, which silently merges two members into one session at every boundary.
new_session = gap.isna() | (gap > Timedelta('30min')). The isna arm opens the first session of each member. Decide and state whether a gap of exactly 30 minutes opens a new session; either convention is fine but it has to be written down because it moves the count.
session_ordinal = new_session.groupby(events.member_id).cumsum(), then build a session key from (member_id, ordinal) with factorize so the id is a compact int rather than a string concat over 5 million rows.
Aggregate once with a single groupby(session_key).agg: min and max timestamp, size, and a boolean any over the qualifying action mask. Derive span_seconds from the aggregated min and max, not row by row.
Flag the known weakness out loud: span between first and last event is a lower bound on foreground time, and a one-event session gets span 0, so it can never qualify under a 30-second rule. That is a definitional choice about single-event sessions, not a bug.

Worked solution (30 min)

ev = events.sort_values(['member_id','occurred_at_utc'], kind='mergesort').reset_index(drop=True)
gap = ev.groupby('member_id', sort=False).occurred_at_utc.diff(); new = gap.isna() | (gap > pd.Timedelta(minutes=30))
ordinal = new.groupby(ev.member_id, sort=False).cumsum(); ev['session_key'] = pd.factorize(pd.MultiIndex.from_arrays([ev.member_id, ordinal]))[0]
ev['is_qualifying_action'] = ev.action_kind.isin(QUALIFYING); s = ev.groupby('session_key').agg(member_id=('member_id','first'), t0=('occurred_at_utc','min'), t1=('occurred_at_utc','max'), n=('session_key','size'), any_action=('is_qualifying_action','any'))
s['span_s'] = (s.t1 - s.t0).dt.total_seconds(); s['qualified'] = (s.span_s >= 30) & s.any_action
s.loc[s.qualified, 'member_id'].nunique()

Expected result: One row per session with member_id, t0, t1, span_s, n, qualified; the number of sessions equals the number of True values in new; and a single integer count of distinct members holding at least one qualified session.

Verify it yourself

len(s) equals new.sum(), since exactly one session opens per True.
ev.groupby('session_key').member_id.nunique().max() == 1, so no session crosses members.
Within every session the maximum consecutive gap is at most 30 minutes; recompute it from ev and take the global max.
Every session with n == 1 has span_s == 0 and qualified == False, which confirms the single-event convention is doing what you said it does.

Follow-up

Where does the 30-minute threshold come from, and how would you choose it from the data rather than inheriting it?
The client emits a heartbeat every 10 seconds while the app is foregrounded. How does that change both the session boundaries and the span calculation?
A member has two devices active at once. What does your session_id mean now, and does the qualified-session count still answer the question it is used for?

Write a query to identify the top 10% of power users based on their activity over the last quarter.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Compute rates by summing numerator and denominator separately, never by averaging rates.
Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

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

Describe how you would use SQL window functions to calculate rolling averages or identify user churn trends.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing 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?

Compare engagement on first delivery versus later re-deliveries

Medium · window functions · dedup · position bias

fct_feed_impression(impression_id, event_date, viewer_member_id, content_id, served_at_utc, rank_position) records every delivery, and the same item can be served to the same viewer many times. fct_engagement_event(impression_id, action_type, is_negative_feedback, undone_at_utc) links an action back to the impression it came from. For one week, compute positive engagement rate per impression split by delivery ordinal for a fixed viewer-content pair: first delivery, second, third, fourth or later. Return the ordinal bucket, impressions, engaged impressions and rate.

Approach

Number the deliveries with ROW_NUMBER() OVER (PARTITION BY viewer_member_id, content_id ORDER BY served_at_utc, impression_id). The impression_id tiebreak makes the numbering deterministic when two rows share a timestamp, which matters because re-deliveries in one scroll can land in the same millisecond.
Bucket the ordinal to 1, 2, 3 and 4-or-later rather than reporting a long tail. The far tail is sparse and its rate swings on a handful of viewers.
Deduplicate the engagement side to one row per impression_id before joining. A viewer can like and comment on the same impression, and counting both makes engaged impressions exceed impressions in the bucket.
Aggregate by summing numerator and denominator per bucket and dividing once. Averaging per-viewer rates answers a different question and is dominated by light viewers, who have one impression and a rate of 0 or 1.
Say plainly that the resulting curve is not a causal read on re-delivery. The ranker decides what to re-serve and re-serves what it predicts will be engaged with, and rank_position also differs systematically across ordinals. Controlling for rank_position narrows the gap without closing it.

Worked solution (30 min)

CTE ranked: the week's impressions with ROW_NUMBER() OVER (PARTITION BY viewer_member_id, content_id ORDER BY served_at_utc, impression_id) AS ordinal.
CTE eng: SELECT DISTINCT impression_id FROM fct_engagement_event WHERE event_date in the window AND is_negative_feedback = FALSE AND impression_id IS NOT NULL, applying whichever undone rule you declared.
LEFT JOIN eng onto ranked on impression_id and set bucket = LEAST(ordinal, 4).
GROUP BY bucket: COUNT() AS impressions, COUNT(eng.impression_id) AS engaged, 1.0 * COUNT(eng.impression_id) / COUNT() AS rate.
Check the shape of the ordinal distribution before interpreting the rates: bucket 1 should hold exactly the distinct viewer-content pair count for the week.

Expected result: Four rows (ordinals 1, 2, 3, 4-or-later) with impressions, engaged impressions and rate. Bucket 1 impressions equals the number of distinct (viewer_member_id, content_id) pairs in the week, and the four buckets sum to the week's total impression rows.

Verify it yourself

The four buckets' impression counts sum to the total impression rows in the window.
Bucket 1's count equals COUNT over distinct (viewer_member_id, content_id) pairs.
Engaged impressions never exceed impressions in any bucket; if they do, the engagement side was not deduplicated to one row per impression_id.
Flipping the undone rule changes the numerators but leaves every denominator identical.

Follow-up

Re-deliveries show a higher rate than first deliveries. Does that mean re-showing content is good?
Design the exploration slot or experiment that would actually answer the question this query cannot.
The same query cut by rank_position shows slot 1 at four times slot 20. What is that number measuring?

Explain the process of designing a product metric from scratch to measure user engagement.

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you define the success metrics for a new AI-powered feature?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
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

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?

If you noticed a sudden drop in a core product metric, what steps would you take to diagnose the root cause?

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
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

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 if the results of an A/B test are statistically significant?

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
State the primary metric and the minimum effect worth shipping, then size the test.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

What would you conclude if the result is positive but the test is underpowered?
What would you do if you could not randomise at all?

What experimentation pitfalls have you encountered when running A/B tests?

Medium · a/b testing and experimentation

Approach

Name the randomisation unit first; it decides the variance and what the test can detect.
State the primary metric and the minimum effect worth shipping, then size the test.
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 conclude if the result is positive but the test is underpowered?

Measure the authoring that a feed change suppressed

Hard · unobservable outcome · proxy bias · creator supply

A feed change raises creator reach concentration: impressions to the top 1 percent of authors go from 38 to 46 percent of all impressions. The worry is that mid-tail members stop publishing because their items no longer reach anyone. The quantity that matters, items a member would have published but did not, is in no table; fct_content_item holds rows only for items that exist. Using dim_member (first_publish_at_utc), fct_content_item (author_member_id, item_kind, created_at_utc) and fct_feed_impression (author_member_id, viewer_member_id), build the best available proxy, state the direction and likely size of its bias, and say which decisions it can and cannot support.

Approach

Say plainly that the estimand is counterfactual and no observational table contains it, so everything that follows is an approximation with a named error, not a measurement.
Define the closest observable as a hazard rather than a count: among members with at least one original item in weeks w-4 to w-1, the probability of publishing another original in week w, cut by their prior distinct-viewer reach decile. Take it as a treatment-minus-control difference by week, which strips out the seasonal and tenure drift that makes the level uninterpretable.
State the biases in both directions rather than the convenient one. It omits members who never published and now never will, which understates the cost and is the largest affected group. It counts members who published less because a repost affordance satisfied them rather than because they were discouraged, which overstates it. And it conditions on prior publishing, a variable the treatment itself affects, so the conditioning sets diverge between arms and the bias grows with the length of the experiment.
Bound the omitted group instead of ignoring it: report first-publish rate among never-published members separately, with all such members in each arm as the denominator, so the unobservable population at least has an observable boundary condition on one side.
Write a decision rule that respects the asymmetry. A clearly negative hazard difference supports a hold, because the observable part already shows a cost and the unobserved part is biased toward being worse. A flat hazard difference does not support a launch, because the members most likely to be discouraged are excluded from the denominator by construction.

Worked solution (40 min)

Write the estimand in counterfactual terms and mark it unobservable in one line, so the rest of the answer is explicitly an approximation.
Define the proxy: weekly publishing hazard by prior-reach decile among members with an original in the previous four weeks, reported as a treatment-minus-control difference week by week.
Build the bias table: for each of the three biases, the affected population, the direction on the estimate, and a rough size argument from that population's share of members.
Add the boundary metric, first-publish rate among never-published members with all such members as the denominator, and state that the two metrics bracket the cost from opposite sides.
Write the asymmetric decision rule: the proxy can veto a launch, it cannot clear one.

Expected result: A counterfactual estimand marked unobservable, a treatment-minus-control weekly publishing hazard by prior-reach decile as the proxy, three named biases each with direction and affected population, a first-publish boundary metric with its own denominator, and an asymmetric decision rule under which the proxy can block but not approve.

Verify it yourself

The proxy denominator is stated and excludes never-publishers, and that exclusion is named as the dominant bias rather than a footnote.
The three bias directions do not all point the same way; an answer where every bias happens to understate the cost has not been worked through.
The hazard difference is reported by week, so the growing conditioning bias in a long-running experiment shows up as drift instead of hiding inside a pooled number.

Follow-up

The publishing hazard is flat in treatment and the first-publish rate is down 4 percent. What do you conclude?
How long can this experiment run before the conditioning-on-prior-publishing problem makes the proxy useless?
What instrument would you build to observe the estimand directly, and what would it cost to run?

Weekly active members fell 1.8 percent: find the flow

Easy · dau decomposition · segment cuts · data completeness

Weekly members with at least one qualified session fell from 12.40M to 12.18M week over week, a 1.8 percent drop. You have dim_member, fct_feed_impression, fct_engagement_event and fct_content_item. The release log shows nothing shipped that week. Produce an ordered diagnostic checklist and run it far enough to name which flow moved: new, retained, reactivated or resurrected. Deliverable is a one-page readout stating the flow, the segment carrying most of the absolute member delta, and the residual you could not explain.

Approach

Confirm the number before explaining it. Check partition completeness for the last two event_date partitions in fct_feed_impression and fct_engagement_event against the trailing four-week median rows per hour, and check whether a metric-definition commit landed. A 1.8 percent move is well inside what one late or partial partition produces, and explaining an artefact is the most expensive failure mode here.
Rule out the calendar before the product. Confirm both weeks have identical weekday composition, no holiday in either, and compare the same week in prior years rather than only against last week, since this metric has a weekly and an annual shape.
Decompose into the four DAU flow classes (new, retained, reactivated, resurrected) for every day in both weeks and verify they sum exactly to DAU each day. Read which class moved: a fall in new is acquisition and cannot be a same-week product regression, a fall in retained is.
Cut the moved class by client_platform, app_version, country_code, tenure bucket and acquisition_channel, one dimension at a time, and rank the cuts by absolute member delta rather than percentage. A 20 percent drop inside a segment holding 1 percent of members cannot produce a 1.8 percent platform move.
Separate mix from rate: recompute the platform number holding the prior week's segment weights fixed. The difference between that and the actual move is the mix term, and it is a different product conversation from a within-segment decline.
Write the readout as flow plus segment plus residual, and name the single query that would close the residual instead of asserting a cause you have not isolated.

Follow-up

If the entire drop sits in the new class, how far back must the acquisition change have happened, and why is a same-week product explanation almost certainly wrong?
A qualified session needs 30 or more foreground seconds. How would you check whether a client timing change altered how strict that threshold effectively is?
What evidence would make you call this seasonal rather than a regression, and what would you tell the product owner in the meantime?

Suggested practice checklist

Roughly 90 minutes a night on weekdays with one longer weekend block. The plan deliberately cuts scope rather than compressing everything, on the assumption that finishing one thing a night beats half-starting four.

Day 1 — Fix the scope and set a baseline

Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list for everything else and keep it visible all week.
Take one 20-minute SQL prompt and one 10-minute metric question cold, and write the single sentence that says what blocked each attempt, since that sentence is what decides which two topics get the most evenings.
Set the week's one rule: one problem finished to completion every night, including the night you only have 40 minutes.

Deliverable: A one-page scope with an explicit not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One query pattern, written three times

Choose the single pattern most likely to appear (a cohort retention grid, or a funnel counted by user) and write it three times from a blank file rather than editing the previous attempt.
On the third attempt, write the grain of every CTE as a comment before writing its body.
Stop at 90 minutes even if the third version is imperfect, and write the one thing you would fix with another hour.

Deliverable: Three independent versions of the same query plus a note on what changed between them.

Day 3 — Only the statistics you will be asked to defend

Write, in under 200 words, how you would decide whether a difference between two groups is real: the test, its assumptions, and what you would switch to when an assumption fails.
Compute a 95 percent confidence interval for a difference in proportions by hand on realistic numbers, then write in one sentence what changes if the two samples are paired rather than independent.
Write your answer to "what does a p-value mean", check it against a definition, and delete the version that describes it as the probability the hypothesis is true.

Deliverable: A 200-word written answer and one hand-computed interval you can reproduce under pressure.

Day 4 — One case, and the assumptions holding it up

Answer one product case aloud in 20 minutes with a recording running, then listen back with a pen and mark every claim you asserted without saying what it rested on: an assumed user behaviour, an assumed data source, an assumed baseline rate, an assumed grain.
Pick the three assumptions the recommendation actually depends on, write how you would check each one against data, and say which one being wrong would flip the recommendation rather than merely weaken it.
Write the four-step structure you used onto a card small enough to hold in working memory when you are nervous.

Deliverable: One recording, three load-bearing assumptions each with a written check, and a four-step structure card.

Day 5 — Your own work, timed

Write a 90-second version and a four-minute version of your main project, and time both out loud rather than reading them.
Prepare answers to the two follow-ups that always come: what you would do differently, and how you knew it worked.
Put one number in the first sentence and be able to say exactly where that number came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in a longer weekend block

Run a 60-minute mock covering query work, a case and a behavioural question in a single sitting with no breaks, because sustained attention is the thing evenings have not trained.
Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

Write the 20-minute warm-up you will actually do on the morning of the interview: one query you can already write from a blank file, one metric you can define out loud, and nothing you have never seen before.
Re-read only your own notes from this week, and open no new material.
Write down the logistics: the tool you will be asked to work in, whether lookups are allowed, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the case structure, the project numbers, and the logistics.

Bring numbers, including the ones you are unsure of

An answer without a quantity is hard to interrogate, so interviewers keep probing until they find one. Come with the baseline, the change, the window it was measured over, and how confident you were. If the effect never got measured, say so and say what you would have measured. Fabricated precision is worse than an honest gap.

Tell me about a time you had to explain a complex model to a non-technical stakeholder.

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Close with what you would do differently, concretely.
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 did you decide not to do, and why?

Choose between three requests with one analyst week

Medium · prioritisation · identification · sample size

You have one analyst week and three open requests. Ranking wants an off-policy estimate of a candidate ranker, but log_propensity is NULL on 92 percent of home-feed impressions. Growth wants a week-1 retention read on a registration cohort whose day 7 to 13 window closes in four days. Integrity wants the audit sample re-sized because the prevalence interval cannot detect a 20 percent change. Give the order, what each requester gets and when, and the request you refuse outright with the reason.

Approach

Sort by identification before sorting by importance. A request that cannot be answered correctly with any amount of effort is not competing for the week; it is competing for a different conversation.
Rule out the off-policy estimate on positivity grounds and say it in one line: log_propensity NULL means serving was deterministic top-k, so the unserved slots had zero logging probability, and no inverse-propensity or doubly-robust estimator is identified there. The fix is a randomised exploration slot or an online test, which is a platform change and not an analyst task.
Rule out the retention read on timing: days 7 to 13 must have closed for every member in the cohort. Reading it four days early truncates the window for the late-registering half of the cohort and biases the estimate by registration day, which correlates with acquisition channel.
Do the integrity re-size, because it is tractable in hours and unblocks a standing measurement. At a two-sided alpha of 0.05 and 80 percent power, detecting a move from 0.50 percent to 0.40 percent needs roughly 70,000 labelled impressions per comparison period under simple random sampling, before any design effect from unequal weights. That number reframes the request: the honest deliverable may be a stratified design plus a longer comparison window, not a bigger weekly sample.
Convert each refusal into a dated deliverable so the week does not end in three unhappy teams: ranking gets the exploration-slot design and the randomised fraction it needs; growth gets the date the cohort closes plus the activation rate on days 0 to 6, which is already readable and is a leading indicator of the number they want.

Follow-up

Ranking says a biased estimate is better than none. Answer them in two sentences.
How large an exploration fraction would you request, and what does it cost in served quality?
If integrity cannot fund 70,000 labels a week, what do you offer instead?

Explain a prevalence interval to a non-technical executive

Easy · uncertainty · prevalence · executive communication

A weekly impression-weighted violating-content prevalence estimate came in at 0.42 percent, 95 percent interval 0.28 to 0.61, against 0.51 percent (0.35 to 0.72) the week before. The audit sample is 4,000 served impressions drawn with unequal, recorded selection probabilities across risk strata, labelled by humans against written policy. An executive asks whether the number went down and wants one figure for a board slide. In five minutes: answer the question, say what goes on the slide, and state what you would need to give a sharper answer next quarter.

Approach

Answer the question in one sentence before explaining anything: the point estimate is lower, the intervals overlap across most of their range, and the week-over-week change is not distinguishable from zero.
Show why with one arithmetic step rather than vocabulary. At n = 4,000 and p near 0.004 the simple-random-sampling standard error is sqrt(p(1-p)/n), about 0.10 percentage points, so an SRS interval would run roughly plus or minus 0.20 points and a 0.09 point move sits well inside it. Two facts about the reported interval belong in your head rather than on the slide. Its asymmetry comes from the construction, not from the weights: Wilson, Clopper-Pearson and logit intervals are built on a bounded scale, so near p = 0 the upper limit sits further from the point estimate than the lower one. The 1/p_i weights act on width only, through a design effect that multiplies the variance. Here the reported width of 0.33 points implies a standard error near 0.085 (0.33 divided by 3.92), so the design effect is about 0.7, which is what oversampling high-risk strata buys when selection probability correlates with the outcome. Uninformative weights would instead give a design effect of 1 + CV squared of the weights, above 1, and an interval wider than the SRS one rather than narrower.
Replace the bare point estimate with a number that is stable at board cadence: the trailing four-week pooled estimate, formed by re-summing the weighted numerator and the weighted denominator across weeks. Averaging the four weekly rates gives a different and wrong number when weekly sample sizes differ.
Price the precision the executive is implicitly asking for. Halving the interval width needs roughly four times the labelled sample, so 16,000 labels a week to go from a half-width near 0.17 points to one near 0.085. The cheaper lever is allocation rather than volume: the design already uses unequal, recorded, strictly positive selection probabilities and is already running a design effect near 0.7, so re-fitting the strata on current classifier scores and moving more of the 4,000 into the strata carrying the violating mass pushes that number down further without a fourfold labelling bill.
State plainly what this number is not, because the executive will meet substitutes. Report volume and enforcement volume are member and operations behaviours; they can fall while prevalence rises if the ranker gets better at matching violating content to receptive audiences.

Follow-up

The executive wants a weekly trend line on the slide anyway. What do you draw, and what do you label the band?
How long would it take to detect a 20 percent reduction in prevalence at the current sample size?
Why not score every impression with the classifier instead of paying for human labels?