Bidgely Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Bidgely, you are at the intersection of energy analytics and artificial intelligence. Bidgely is a leader in AI-powered energy disaggregation, using machine learning to turn smart meter data into actionable insights for utility providers and consumers. Your work directly impacts how millions of users understand their energy consumption, helping to drive global sustainability initiatives through data-driven precision.

Coding rounds for this role are usually data-manipulation shaped rather than data-structure shaped: group-bys, joins, time windows, ranking within a partition. Confirm the format before spending a week on graph traversal.

Focus areas

• Decompose a metric move by segment and mix
• Define numerator, denominator and window precisely
• Pick a randomisation unit that respects interference

Overview

In this role, you will tackle complex challenges like Non-Intrusive Load Monitoring (NILM), where you must infer individual appliance usage from aggregate energy signals. You will move beyond simple model building to design end-to-end product metrics, analyze the efficacy of energy-saving programs, and solve real-world problems at scale. Success here requires a blend of rigorous statistical thinking, product intuition, and the ability to translate technical findings into business strategy.

The Bidgely interview process

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

Technical Screening

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

Data Science Challenge

An added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.

What to demonstrate

• Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
• Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
• How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact

How to prepare

• Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
• For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
• Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.

Machine Learning Theory

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What to demonstrate

• Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
• Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
• How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

• Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
• Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
• Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

Statistics Assessment

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

• Whether your row counts survive each join, and whether you notice on your own when they do not
• Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
• Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
• Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

• Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
• Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
• Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Business Case Studies

This round runs as a working session, so part of what it decides is whether you are useful to think with. The interviewer will interrupt: a hint that the data you assumed does not exist, a challenge to your metric, a nudge toward a branch you skipped. Treating those as interference is the common failure. Reason out loud while your thinking is still provisional so there is something to react to, and when a redirect arrives, take it instead of defending the path you had already started down.

What to demonstrate

• Whether your reasoning is audible while it is still unsettled, or only after you have privately decided
• What you do with a hint: absorb it and adjust, or argue for the original route
• Whether your clarifying questions have answers that would change your approach, as opposed to filling silence
• Whether you can be wrong about something in the middle of the case and keep moving without restarting

How to prepare

• Run practice cases with a partner instructed to interrupt twice: once to remove a data source you assumed existed, once to reject the metric you chose. Practise absorbing both without going back to the start.
• Before each practice case, write down the clarifying questions you plan to ask, then check afterwards whether any answer actually changed what you did. Drop the ones that did not.
• Explain an analysis you already know well to someone outside the field and have them stop you at every point where the reasoning jumped a step.

Deep-Dive Sessions

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

Counting on an identity key that changes underneath the metric

visitor_id is per browser and per device, and it resets on cookie clearance, private browsing and platform privacy changes, so the distinct-visitor count drifts upward for reasons unrelated to reach. Any rate with visitors in the denominator therefore decays over time even when behaviour is constant, and any rate with visitors in the numerator inflates. The stitching at signup makes it worse in both directions: a user who signed up on mobile and returns on desktop is two visitors and one user, while a shared device is one visitor and several users. Decide which key each metric is counted on, write it into the definition, and when comparing a period before and after a platform privacy change, expect a level shift in every visitor-keyed metric and do not attribute it to the product.

Comparing cohort retention curves of different maturities, or building the curve from users who are still present

A cohort four weeks old has no week-8 value, so an average taken across cohorts silently drops young cohorts from the later columns and keeps them in the earlier ones. The curve then bends upward at the tail, and the reading that 'retention is improving over time' is an artefact of which cohorts survived to be measured. The same error appears in the denominator when retention is computed over users active in the current period rather than over the full original cohort, which conditions on survival and guarantees a flattering number. The fix is a triangle: fix the cohort at signup, bound every window on both sides, and only compare cells where every cohort has had the full elapsed time, publishing the rest as blank rather than as a partial average.

Treating a non-significant result as proof of no effect

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

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

How do you balance model precision versus recall when optimizing for a consumer-facing energy recommendation engine?

Medium · machine learning and modelling

Approach

1. Check what information would not exist at prediction time, and exclude it.
2. Frame the prediction: the label, the moment of prediction, and the action it triggers.
3. Set a baseline first, so any model has something honest to beat.

Follow-up

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

Rebuild per-visitor ordering without groupby convenience methods

Easy · pandas · vectorisation · window logic

You have a DataFrame of 2 million fct_event rows with visitor_id, occurred_at_utc and event_id, unsorted and containing duplicate timestamps within a visitor. Produce three new columns: event_rank, the 1-based position of the event within its visitor ordered by occurred_at_utc; seconds_since_prev, the gap to that visitor's previous event, NULL for the first; and is_first_for_visitor. You may use sort_values, shift, cumsum, numpy and boolean masking. You may not use groupby.transform, groupby.apply, groupby.cumcount, groupby.rank or merge_asof. Break timestamp ties on event_id.

Approach

1. Sort once by ['visitor_id', 'occurred_at_utc', 'event_id'] and reset the index. The whole exercise reduces to row arithmetic on a sorted frame, and the tiebreak on event_id is what makes the result reproducible across runs.
2. Mark visitor boundaries with is_first = df['visitor_id'].ne(df['visitor_id'].shift()). This is the single fact every other column derives from.
3. Compute seconds_since_prev as the diff of the timestamp column, then overwrite it with NaT/NaN wherever is_first is True. The shift crosses the boundary between visitors and will otherwise hand the first row of each visitor the last event of the previous one.
4. Build event_rank from a running counter that resets at boundaries: take a global cumulative position (np.arange(len(df))) and subtract, per row, the global position at which that visitor started. Get the start position by forward-filling the positions where is_first is True, which is a cumsum-free reset and is O(n).
5. Verify against the forbidden method once, as a test rather than as the implementation, and confirm the two agree on every row.

Follow-up

• The frame does not fit in memory. How does your approach change if you can only process one visitor-partitioned chunk at a time?
• occurred_at_utc is client-supplied and sometimes runs backwards within a visitor. Does your seconds_since_prev go negative, and should it?
• How would you extend this to reset the counter at every change of surface as well as visitor?

Sessionise an event stream with gap and midnight rules

Hard · sessionisation · vectorisation · event streams

Sessionise a raw event stream. Input: a DataFrame with visitor_id, user_id (often NULL), occurred_at_utc and event_name, unsorted, up to 5 million rows. A session breaks when the gap from that visitor's previous event exceeds 30 minutes, and is force-closed at UTC midnight so no session spans two calendar dates. A gap of exactly 30 minutes does not break. Emit one row per session with session_id, visitor_id, the user_id as of the last event in the session, started_at_utc, ended_at_utc, session_date, duration_seconds and event_count. Vectorise; do not loop per visitor.

Approach

1. Sort by ['visitor_id', 'occurred_at_utc', 'event_id'] once, then express the whole problem as one boolean vector: a row starts a new session when the visitor changed, or the gap exceeds 30 minutes, or the UTC date differs from the previous row's UTC date. Cumsum that vector and you have the session key.
2. Get the comparison direction right on the gap: the rule is strictly greater than 1800 seconds, so an event at exactly 1800 seconds continues the session. Write it as gap > pd.Timedelta(minutes=30), and make the tie a test case rather than an assumption.
3. Derive the midnight break from the date change, not from inserting synthetic boundary rows. A date change implies a break even when the gap is two seconds, which is precisely the force-close rule and is why the two conditions are ORed rather than one subsuming the other.
4. Aggregate with a single groupby on the session key: min and max of occurred_at_utc, size for event_count, and last for user_id, which is correct because the frame is already sorted so 'last' is the final event in the session. That is the identity-as-of-session-end rule.
5. Compute duration_seconds as (max - min).dt.total_seconds(), which makes a single-event session 0 seconds. Say so explicitly, because a downstream mean session duration is sensitive to whether single-event sessions are 0 or excluded.

Worked solution (35 min)

1. df = df.sort_values(['visitor_id','occurred_at_utc','event_id']).reset_index(drop=True).
2. new_visitor = df.visitor_id.ne(df.visitor_id.shift()); gap = df.occurred_at_utc.diff(); new_day = df.occurred_at_utc.dt.date.ne(df.occurred_at_utc.dt.date.shift()).
3. is_start = new_visitor | (gap > Timedelta(minutes=30)) | new_day; df['session_key'] = is_start.cumsum().
4. g = df.groupby('session_key'); out = g.agg(visitor_id=('visitor_id','first'), user_id=('user_id','last'), started_at_utc=('occurred_at_utc','min'), ended_at_utc=('occurred_at_utc','max'), event_count=('event_id','size')).
5. out['session_date'] = out.started_at_utc.dt.date; out['duration_seconds'] = (out.ended_at_utc - out.started_at_utc).dt.total_seconds(); assign session_id from the sorted index.

Expected result: One row per session where session_count equals is_start.sum(), event_count sums exactly to len(input), every session's started_at and ended_at share one UTC date, and duration_seconds is at least 0 and strictly less than 86400.

Verify it yourself

• out['event_count'].sum() equals len(df); a mismatch means rows were lost to a join or a dropna.
• started_at_utc.dt.date equals ended_at_utc.dt.date on every row.
• For a planted visitor with events at 12:00:00 and 12:30:00 exactly, one session is emitted; move the second to 12:30:01 and two are emitted.
• For a planted visitor with events at 23:59:50 and 00:00:10, two sessions are emitted on consecutive dates despite a 20-second gap.

Follow-up

• Sessions are used as the denominator of a conversion rate. How does moving the inactivity gap from 30 to 45 minutes move that rate, and in which direction?
• A visitor's clock is 40 minutes ahead, so their events arrive with future occurred_at values. What does your sessioniser do, and what would you rather it did?
• The same person signs up mid-session on mobile and continues on desktop. How many sessions and how many users does your output show, and is that the right answer?

Given two tables (Users and Energy_Usage), find the top 5% of users with the highest energy spikes using common table expressions.

Medium · sql and data manipulation

Approach

1. Check whether any join is one-to-many before aggregating, or the sums inflate.
2. State the window function and its partition and ordering out loud before writing it.
3. 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?
• What breaks if events arrive late or out of order?

How would you handle missing sensor data in a large-scale time-series database?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Read an experiment from first exposure, not assignment

Hard · experiments · window-functions · srm

fct_experiment_exposure holds experiment_id, unit_type, unit_id, variant, user_id, assigned_at_utc, first_exposed_at_utc, is_in_analysis_population and planned_end_utc. fct_event holds user_id, occurred_at_utc, is_core_action, and carries events up to a known data cut, :data_cut_utc. For one experiment randomised on unit_type = 'user', return per variant: exposed units, units with at least one core action in the seven days after that unit's own first exposure, the rate, and the variant share of exposed units. Only units whose seven-day window has fully elapsed as of the data cut belong in the readout. Units appearing under more than one variant are excluded from both arms and counted separately.

Approach

1. Run the contamination pass as an aggregate, not a window: SELECT unit_id FROM fct_experiment_exposure WHERE experiment_id = :exp GROUP BY unit_id HAVING COUNT(DISTINCT variant) > 1, then anti-join it away. PostgreSQL rejects COUNT(DISTINCT variant) OVER (PARTITION BY unit_id) outright, since DISTINCT is not implemented for window functions; if you want the test inline, MIN(variant) OVER (PARTITION BY unit_id) <> MAX(variant) OVER (PARTITION BY unit_id) is the equivalent that does run.
2. Do not resolve contamination by keeping the earliest variant. A unit that saw both arms carries treatment from both, so assigning it to either one biases that arm.
3. Define the population as is_in_analysis_population = TRUE AND unit_type = 'user' AND first_exposed_at_utc < planned_end_utc AND first_exposed_at_utc + interval '7 days' <= :data_cut_utc. The horizon filter is what makes the readout reproducible next week instead of drifting with every re-run; the data-cut filter is the one that actually buys seven days of follow-up, since a unit exposed an hour before the horizon otherwise contributes an hour of observation to a seven-day rate.
4. Measure the outcome on a per-unit relative window: LEFT JOIN fct_event on user_id with is_core_action = TRUE and occurred_at_utc in [first_exposed_at_utc, first_exposed_at_utc + interval '7 days'). LEFT JOIN so units with no outcome stay in the denominator at zero rather than being deleted by an inner join.
5. Check the sample ratio before reading the effect: variant share of exposed units against the intended split, tested as a binomial. Run it on the truncated population as well as on the full exposed set, because if one arm exposes later on average the data-cut filter removes more of that arm and can manufacture a ratio mismatch the randomisation did not have. A mismatch on the full set means the exposure data is not a valid randomisation and invalidates the readout rather than being a footnote under it.
6. Report the per-variant rate, the absolute difference, and the fact that the variance unit is unit_id. That is straightforward here only because the grain is already one row per user; a per-session outcome under user randomisation would need a delta-method or bootstrap standard error instead.

Worked solution (35 min)

1. Count units per variant and units with more than one variant; report the contaminated count before anything else.
2. Build the exposed population CTE with both the horizon filter and the seven-days-before-data-cut filter, assert one row per unit_id, and record per variant how many units the second filter removed.
3. LEFT JOIN the bounded outcome events and flag conversion with a boolean aggregate.
4. Aggregate per variant to exposed, converted, rate and share, and run the binomial check on the share, both before and after the truncation.
5. Re-run with a fixed calendar window from experiment_start_utc and record the difference as the size of the dilution you avoided.

Expected result: One row per variant with exposed, converted, rate and share, plus a scalar count of excluded contaminated units. Every retained unit was first exposed at least seven days before the data cut, so each one has a full seven days of observation and the rates are directly comparable.

Verify it yourself

• Exposed counts across variants sum to the population count minus the contaminated units.
• MAX(first_exposed_at_utc) among retained units is at least seven days before :data_cut_utc.
• Variant shares sum to 1.
• No unit_id contributes to two variant rows.
• The truncation drops a similar share of each arm; if it does not, note the imbalance alongside the SRM result rather than reading the effect.
• Swapping the relative window for a calendar one changes the rates; if it does not, exposure timing is balanced and you can say so.

Follow-up

• Some units were assigned days before they were exposed. What does analysing the assigned set instead do to the estimated effect, and in which direction?
• The split is 51/49 on 400,000 exposed units. Do you read the result?
• The treatment arm exposes on average two days later than control. What does that do to a fixed calendar outcome window, and which arm does it favour?

Design a metric to measure the long-term impact of a utility company’s demand-response program.

Medium · product sense and metrics

Approach

1. Name one primary metric, then the guardrail that stops it being gamed.
2. Fix the population and the time window before naming any metric.
3. 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?
• What would you do if the primary metric and the guardrail moved in opposite directions?

How would you define a success metric for a new energy-saving feature?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Decompose the metric into the rates that drive it, and say which one you would check first.
3. 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?
• Which segment would you cut first, and what would that rule out?

How do you prioritize your work when balancing long-term research projects with urgent product requests?

Medium · product sense and metrics

Approach

1. State what result would change your recommendation, so the answer is falsifiable.
2. Decompose the metric into the rates that drive it, and say which one you would check first.
3. 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?
• Which segment would you cut first, and what would that rule out?

If we notice a sudden drop in user engagement on our energy dashboard, how would you diagnose the root cause?

Medium · product sense and metrics

Approach

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

Follow-up

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

How do you determine the required sample size for an A/B test?

Medium · a/b testing and experimentation

Approach

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

Follow-up

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

What are the most common experimentation pitfalls when testing a new UI feature on our mobile app?

Medium · a/b testing and experimentation

Approach

1. Name the guardrails that would stop a launch even on a positive primary result.
2. Decide the analysis before seeing data, including how long it runs and when you look.
3. State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

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

Reminder volume where the guardrail opposes the primary metric

Medium · engagement · guardrails · trade-offs

The growth team proposes tripling weekly reminder email volume. The north star is weekly active accounts completing a core action, counted on account_id from fct_event (is_core_action, account_id, occurred_at_utc, surface). Its stated guardrail is week-over-week repeat rate together with notification opt-out and unsubscribe rates. Reminders will move the primary up and the guardrail down, by design, and both effects are real. Define the decision rule before the test runs: what magnitudes make this a ship and what makes it a stop. Deliverable: the rule, including the exchange rate you are using between the two quantities.

Approach

1. Name the conflict precisely rather than calling it a balance: the reminder buys one week of an account returning and spends permission to contact that account, and permission is not renewable, so the two quantities are not comparable as percentage points.
2. Put both sides into one unit before arguing about thresholds. Value the primary gain as incremental core-action weeks over the horizon; value an opt-out as the forgone email-driven active weeks over that account's remaining expected lifetime.
3. Read opt-out as a stock, not a flow: accumulate it over the test, because a weekly opt-out rate that looks small is a cumulative curve that only ever rises within a fixed set of contactable accounts.
4. Run long enough for the novelty to decay and use the late number in the trade: compare week-1 lift with week-4 lift, state the decay you observed, and refuse to price the decision on a week-1 read.
5. Write the outcome as two numbers and a default action, including what happens when the result lands between them — hold and test a smaller volume increment rather than shipping on ambiguity.

Worked solution (30 min)

1. Measure the starting stock: current opt-out and unsubscribe rate per 1,000 contactable accounts, and the share of weekly active accounts whose session began from referrer_channel = 'email'.
2. Estimate the two effects separately over four weeks: incremental weekly active accounts per 1,000 extra sends, and incremental cumulative opt-outs per 1,000 extra sends.
3. Convert opt-outs into forgone active weeks: email-driven active weeks per account per year multiplied by remaining expected account lifetime, and set that against the incremental active weeks bought.
4. Compare week-1 with week-4 lift, state the decay rate, and carry the week-4 figure into the trade.
5. Write the rule as two thresholds plus a default action for the middle case.

Expected result: A written ship/stop rule containing a numeric exchange rate between one incremental weekly active account and one opt-out, derived from an observed lifetime and an observed decay rather than asserted.

Verify it yourself

• The cumulative opt-out curve within a fixed set of contactable accounts must be non-decreasing. A dip means the denominator is being recomputed each week and the metric is not a stock.
• Week-4 incremental lift should be materially below week-1. If it is not, either the horizon is too short to see decay or the arms are contaminated by cross-sends.
• Repeat rate must be computed on the same accounts across consecutive weeks, not on the weekly active set, whose composition changes every week by construction.

Follow-up

• Opt-out is flat but week-over-week repeat rate falls. What is the most likely mechanism, and does it change the decision?
• You do not have a year of data to estimate remaining account lifetime. What do you substitute, and how do you keep the answer honest about that?
• Does randomising on account rather than on user change either the readout or the size of the test?

Decide whether a one-day core-action drop is real

Easy · seasonality · day-of-week · late-arriving-data

A daily dashboard counts distinct fct_event.user_id with is_core_action = TRUE, filtered on occurred_at_utc, and is read at 09:00 UTC. This morning it shows yesterday down 22% against the day before. fct_event is partitioned on received_at_utc. You have fct_event, fct_session and dim_user with thirteen months of history. Deliver a one-paragraph verdict, escalate or do not escalate, with the evidence that settles it, before anyone proposes a product hypothesis.

Approach

1. Identify which two weekdays the comparison actually spans, then pull the same weekday-pair transition for the last 52 weeks and place the observed 22% inside that distribution. A day-over-day comparison in a product with a weekday pattern is a comparison of two different populations, so the reference class is the same transition historically, not the prior day.
2. Measure partition completeness rather than assuming it. For each of the last 30 days compute the share of that day's occurred_at_utc rows that had landed by 09:00 UTC the following morning, split by surface; mobile clients buffer events offline, so the freshest partition is systematically short and the shortfall is not uniform across surfaces.
3. Recompute the same series keyed on received_at_utc. If the drop survives on both keys it is not a lateness artefact; if it exists only on occurred_at_utc it is the partition filling in.
4. Check the two exclusion flags before segmenting anything: a change in is_bot_flagged coverage or a batch of is_internal accounts entering or leaving moves a distinct-user count with no user behaviour behind it.
5. Only if the movement survives all of the above, begin the segment decomposition. Say explicitly in the verdict which of these four checks the movement passed, so the next reader does not repeat them.

Follow-up

• What publication lag would you set for this dashboard, and how would you derive the number rather than pick it?
• If you switch the metric to received_at_utc, what does that break for anyone comparing to historical figures?
• How would you detect the same problem automatically, so a human does not have to notice it each morning?

Suggested practice checklist

Four days spend equal time on query work, statistics, modelling and product judgement at deliberately shallow depth, which produces a scored map of where you actually stand. The last three days spend everything on the two areas the role weights most, and close by re-running day one to measure movement.

Day 1 — Breadth pass: query fluency

• Solve six prompts spanning aggregation, joins, window functions and date arithmetic in 60 minutes total, stopping at 10 minutes each whether or not it works, and mark every prompt as solved, solved slowly, or stuck.
• For each unsolved prompt write the single blocking sentence (I lost the grain, I did not know the frame clause, I could not express the date boundary) instead of reading the solution.
• Translate one pandas transformation you know well into SQL and one SQL query into pandas, checking that both return the same row count and the same totals.

Deliverable: A scored six-row table, one line per prompt, saved for the day-seven re-run.

Day 2 — Breadth pass: statistics and inference

• Answer ten short questions in writing with nothing open: what a p-value is conditional on, what a 95 percent interval covers across repeated samples, when a paired test is the right one, what the bootstrap estimates, why multiple comparisons inflate false positives, how controlling the family-wise error rate differs from controlling the false discovery rate, what power depends on, what a missed real effect costs a product, the three situations where the central limit theorem does not rescue you (small n, very heavy tails, dependent observations), and what a standard error is the standard deviation of.
• Grade yourself against a reference and count only the answers that were exactly right, not the ones that were nearly right.
• Rewrite the two weakest answers the following morning from memory in full sentences.

Deliverable: Ten graded answers with an honest count of exact hits.

Day 3 — Breadth pass: modelling

• Take one tabular dataset end to end in 90 minutes: a leakage-safe split, a baseline that is not a model (majority class or historical mean), one regularized linear model, one gradient-boosted tree, and a single evaluation metric chosen before you look at any result.
• Write why that metric fits the cost structure: precision at a fixed recall for alerting, calibration for anything feeding a price or a threshold, ranking metrics for retrieval, and note that area under the ROC curve is insensitive to class balance in a way that can flatter a rare-positive problem.
• Name the leak you were most likely to introduce (an encoding fit on all rows before splitting, or a feature computed after the label's timestamp) and write the check that would have caught it.

Deliverable: A notebook whose first cell states the metric and the baseline, plus two lines on what beat what and by how much.

Day 4 — Breadth pass: product judgement

• Answer three case prompts aloud at 15 minutes each, timing how long passes before you state a success metric.
• For one case write the first segmentation you would run and the row counts you expect per segment, so that a tiny segment cannot quietly drive the conclusion.
• Take a metric definition you did not write, from a public dashboard, a textbook, or documentation you already have open, and list every place two analysts implementing it would diverge: which rows the denominator admits, whether the unit is an account or a person, what the time window is anchored to, and what happens to data that arrives late. Then write the one question that would close the largest of those gaps.

Deliverable: Three recorded case answers plus an ambiguity list for a metric someone else defined, ending in the single question you would ask about it.

Day 5 — Depth, first area

• Rank the four areas by how many bullet points in the role description each one covers, pick the top one, and spend the entire day inside it.
• Work the six hardest problems you can find in that area and for each write the generalizable move you should have reached for first, rather than the answer.
• Re-solve the two you failed the same evening with notes closed.

Deliverable: Six generalizable moves written as instructions to yourself, not as solutions.

Day 6 — Depth, second area, and the seam between them

• Repeat the depth protocol on the second-ranked area with the same six-problem structure.
• Construct one problem that requires both areas at once, for example a metric redefinition whose effect you must validate with a test whose readout you then have to query.
• Solve your own combined problem end to end and note where the handoff between the two areas cost you time.

Deliverable: One combined problem, solved end to end, with the handoff failure written down.

Day 7 — Integration and re-measurement

• Re-run the six prompts from day one under the same clock and compare both correctness and time.
• Run a 60-minute mixed mock that moves between areas without warning, since switching cost is what breadth passes do not train.
• Write the two areas you would still fail on, and the sentence you will use in the interview when you hit one of them.

Deliverable: A before-and-after score table plus a written plan for the two remaining gaps.

Stories where someone disagreed with you

Have two ready. In one, the data was on your side and you had to move someone who outranked you. In the other, the pushback was correct and you changed position. The second is the harder story and it lands better, because it shows you separate being right from being attached to an answer. Name the person's actual objection.

Explain the concept of p-values to a non-technical stakeholder.

Medium · behavioural and stakeholder questions

Approach

1. Pick a story where you drove the decision, not one where you observed it.
2. Quantify the outcome, including what you would not claim credit for.
3. 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?

Explain a wide interval to a non-technical executive

Medium · communication · uncertainty · pricing

A pricing change is under consideration. Your best estimate of its effect on trial-to-paid conversion is a 1.8pp drop, with a 95% interval from a 4.6pp drop to a 1.0pp rise, read from a geo holdout rather than a randomised test. An executive preparing a board slide asks you for 'the number'. You have ninety seconds and one slide, and the words confidence interval, p-value and significance are not usable with this audience. Deliver the slide headline, the single supporting line, and what you say aloud.

Approach

1. Recognise what is being probed: whether you can carry uncertainty into a decision instead of either hiding it or hiding behind it. The generic answer promises to explain the interval in plain English; the strong one replaces the question 'what is the number' with 'across this range, where does the decision change'.
2. Find the threshold before you draft anything. Ask what the pricing case assumes, then compute the conversion drop at which the higher price stops adding revenue: price uplift on the conversions kept against the revenue lost from conversions forgone. That single figure is what makes the range legible.
3. Restate the estimate and both bounds in the unit the audience already reasons in. Convert percentage points into monthly first-paid conversions at current trial volume, then into mrr_cents_constant_fx, so the slide reads as money per month rather than as statistics.
4. Place the range against the break-even and say which part of it sits on each side. If most of the range clears the threshold, that is a recommendation to proceed with a monitoring plan; if the range straddles it, that is a recommendation to narrow the range first.
5. Name what would narrow it and what that costs in weeks, then give one recommendation with an explicit condition for revisiting it. Uncertainty stated without a next step is read as indecision and the midpoint gets used anyway.

Follow-up

• The executive says to give the midpoint and they will manage the risk. What do you do?
• How does the slide change if the interval were a 4.6pp to 0.2pp drop, with no positive outcomes in range?
• Why is a geo holdout the credible read here rather than the attributed channel numbers you already have?

Handle a request for numbers supporting a decision already made

Hard · integrity · framing · stakeholder

A senior leader has already decided to sunset a plan tier and asks you for the analysis showing it is the right call. Accounts on that tier carry 6% of MRR at constant FX and have the highest licensed-seat utilisation in the book. The leader's support matters to your next review cycle, and the decision is being presented in four days. Deliver what you produce, what you decline to produce, and the exact sentence you will say in the meeting where the number appears on a slide.

Approach

1. Recognise what is being probed: whether you can find the legitimate request inside an illegitimate framing instead of either complying or refusing on principle. The generic answer promises to push back; the strong one produces something genuinely useful and states its limits in the room, without ambushing anybody.
2. Separate the decision from the justification. Sunsetting the tier may be correct for reasons the data does not hold, such as support cost, roadmap surface area or sales motion. What you decline is a one-sided document. What you produce is the case read both ways, which also happens to be more useful to the leader.
3. Build the symmetric analysis: MRR at risk at constant FX, the share of affected accounts with a plausible migration path given seats_licensed and billing_term, the recovery rate assumed for that migration and where it came from, and the downside case in which high-utilisation accounts treat the sunset as a reason to re-evaluate the vendor entirely.
4. Surface the inconvenient fact privately and early. The highest seat utilisation in the book is a retention signal, and the leader should hold it before the room does, so they can incorporate it rather than be caught by it.
5. Agree the meeting sentence in advance with the leader, so that nobody is surprised. Something to the effect that the tier is 6% of MRR and its accounts are the most heavily used in the book, and that the case for sunsetting rests on cost and focus rather than on revenue. That is true, it supports the decision on its real grounds, and it stops the deck claiming the numbers endorse it.
6. Decide your own line before you need it: what you will not put your name to, and that the route if asked anyway is your own manager rather than a confrontation in the meeting.

Follow-up

• The deck circulates with your analysis included and the downside case removed. What do you do, and by when?
• What changes if the honest analysis says the sunset is clearly the wrong call?
• How do you write the same memo when the leader is your skip-level and the meeting is tomorrow?