April Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at April, you play a vital role in transforming data into actionable insights that drive business decisions and enhance user experiences. This position is crucial for leveraging analytical techniques to support various teams, from product development to marketing. You will engage with complex datasets, utilizing statistical models and machine learning algorithms to uncover trends and solve intricate problems that impact the company's strategy and operations.

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

• Separate novelty effects from durable behaviour change
• Turn a vague request into a measurable question
• Pick a randomisation unit that respects interference

Overview

Your work will directly influence key projects, such as improving customer engagement through personalized recommendations, optimizing pricing models, and enhancing operational efficiency. Collaborating with cross-functional teams, you will ensure that data-driven insights are at the forefront of decision-making processes. The complexity and scale of the data you will handle at April make this role not only challenging but also immensely rewarding, as you contribute to innovative solutions that shape the future of the company.

Candidates can expect a dynamic environment where their analytical skills and creativity will be essential in tackling real-world challenges. The role offers the opportunity to make significant contributions that align with mission of providing exceptional services to its users.

The April interview process

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

Initial Questionnaire

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.

Technical Assessments

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

Where candidates lose points

Watching an experiment daily and stopping when it crosses significance

A fixed-sample test controls type I error at one pre-declared look. Checking repeatedly and stopping at the first p < 0.05 inflates the false positive rate to roughly 0.15 to 0.20 for ten looks, and it rises further with more frequent checks, because the p-value takes a random walk that will eventually dip below the threshold under the null. The usual defences are a fixed horizon declared before launch, group-sequential boundaries such as O'Brien-Fleming that spend alpha across a planned number of looks, or always-valid confidence sequences that are correct under continuous monitoring. Compounding it, the effect size reported conditional on having crossed the threshold is biased away from zero, and the bias is larger the lower the power was, so an underpowered test that 'won' typically overstates the lift it found.

Treating last-touch attribution as the causal value of a channel

The attribution label on dim_user is the output of a rule that assigns full credit to whichever touch happened to be recorded last inside a lookback window, and that rule systematically rewards channels that sit close to the conversion, especially branded search and retargeting, which largely intercept demand that already existed. Reallocating spend on those labels moves budget toward the channels that are best at being last, which is why attributed return on ad spend often improves while total signups do not. Nothing in the touchpoint data can settle this, because the counterfactual of not running the channel was never observed. The credible reads are a geo holdout or a scheduled pause, sized in advance on the total-signups metric rather than on the attributed one, and the honest framing in the meantime is that the label describes correlation with conversion and not incremental contribution.

Reaching for a model before the target metric exists

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

Reading experiment results before checking the arm split

Compare observed arm counts against the intended allocation ratio, not an assumed even split, and set the alarm far below the conventional 0.05: at 0.05 roughly one healthy experiment in twenty trips it, which is why sample-ratio checks usually run at p < 0.001 or stricter. The test's power scales with sample size, so it misses a real diversion on a small experiment and fires on an imbalance too small to move the estimate on a very large one. A flag means go find the assignment or logging fault before reading any outcome, not report a mismatch.

Questions & practice

How would you implement a decision tree algorithm from scratch?

Medium · machine learning and modelling

Approach

1. Set a baseline first, so any model has something honest to beat.
2. Frame the prediction: the label, the moment of prediction, and the action it triggers.
3. Say how the offline result would be validated online before it is trusted.

Follow-up

• How would you choose the decision threshold, and who owns that choice?
• What would you monitor after launch to know the model is still valid?

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.

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?

Implement seven-day activation from its written definition

Medium · metric definition · cohorts · pandas · joins

Implement the seven-day activation rate. Inputs: dim_user with user_id, account_created_at_utc and is_internal; fct_event with user_id, occurred_at_utc and is_core_action. A user activates when core-action events carrying a non-NULL user_id fall on at least two distinct UTC dates inside [account_created_at_utc, account_created_at_utc + 7 days). The denominator is every non-internal user whose account_created_at_utc lands in the cohort week, including users with no events at all. Return one row per cohort week with numerator, denominator and rate, publishing only weeks whose last signup is at least eight days old.

Approach

1. Build the denominator first, from dim_user alone, filtered on is_internal = False. Deriving it from the join is the standard way to lose every user who never fired an event, which is exactly the population the metric is about.
2. Join events to users on user_id with a left join from the user side, then apply the window as a half-open interval: occurred_at >= created AND occurred_at < created + 7 days. The right bound is exclusive, so an event at exactly created + 7 days does not count.
3. Count distinct UTC dates per user, not distinct events. Floor occurred_at_utc to date before the nunique, and do it in UTC rather than local time so the threshold does not move with the user's country.
4. Apply the >= 2 threshold, aggregate to cohort week, and compute the rate by re-summing numerator and denominator per week rather than averaging any per-user or per-day rate. Fix the week anchor explicitly: cohort_week is the Monday of the signup week in UTC, which is what Postgres DATE_TRUNC('week') returns and what any SQL version of this metric will produce. In pandas, subtract dt.weekday days from the floored timestamp. If you reach for periods instead, the anchor that matches is to_period('W') (equivalently 'W-SUN'), whose weeks end Sunday and therefore start Monday; to_period('W-MON') labels weeks that end on Monday, so it runs Tuesday through Monday and its start_time is a Tuesday. Mixing the two shifts every cohort label by one day and silently moves Mondays into the previous week.
5. Suppress immature weeks: drop any cohort week whose maximum account_created_at_utc is within 8 days of the data cut, and return them as absent rather than as a partial number.

Worked solution (30 min)

1. users = dim_user[~dim_user.is_internal].copy(); created = users['account_created_at_utc']; users['cohort_week'] = created.dt.floor('D') - pd.to_timedelta(created.dt.weekday, unit='D'), which is the Monday-start week. The period spelling that agrees with it is created.dt.to_period('W').dt.start_time; 'W-MON' does not agree and is off by a day.
2. ev = events[events.is_core_action & events.user_id.notna()]; merge onto users on user_id with how='inner' for the numerator side only.
3. Filter to the half-open window, add ev_date = occurred_at_utc.dt.date, group by user_id and count distinct dates, keep users with >= 2.
4. numer = users.merge(activated_user_ids, how='left', indicator=True) then group by cohort_week and sum the indicator; denom = users.groupby('cohort_week').size().
5. rate = numer / denom; drop weeks where users.groupby('cohort_week')['account_created_at_utc'].max() > data_max - 8 days.

Expected result: One row per mature cohort week with numerator, denominator and rate, where denominator equals the count of non-internal signups in that week regardless of activity, numerator is less than or equal to denominator, and the most recent 1 to 2 weeks are absent rather than reported low.

Verify it yourself

• For a synthetic user with two core actions 90 minutes apart on the same UTC date, activation is False.
• For a synthetic user with one core action on day 0 and one at exactly created + 7 days, activation is False; move the second to created + 6 days 23 hours and it becomes True.
• Pin the week anchor with two users created at Sunday 23:59 UTC and the following Monday 00:01 UTC: they must land in different cohort weeks, and the Sunday user's cohort_week must be the Monday six days before them. to_period('W-MON') puts both in one period, which is the off-by-one this check exists to catch.
• The denominator summed across all published weeks equals the count of non-internal users created in that date range.
• Re-summing numerator and denominator to a monthly figure gives a different answer from averaging the weekly rates; the re-summed one is the metric.

Follow-up

• The threshold is 2 distinct days. What changes in the reported history if someone moves it to 3, and how would you publish that change?
• Invited seats and SSO-provisioned users have no pre-signup session. Should they be in this denominator at all, and what does including them do to the rate for sales-assisted accounts?
• How would you produce the same metric at account grain, and which of the two would you put on the dashboard?

What libraries do you commonly use for data analysis and why?

Medium · sql and data manipulation

Approach

1. Check whether any join is one-to-many before aggregating, or the sums inflate.
2. Compute rates by summing numerator and denominator separately, never by averaging rates.
3. State the window function and its partition and ordering out loud before writing 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?

Can you explain the significance of the "pandas" library in data manipulation?

Medium · sql and data manipulation

Approach

1. Say which table is the grain you start from, and join outward from it.
2. Check whether any join is one-to-many before aggregating, or the sums inflate.
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?

Rebuild sessions from raw events with a thirty-minute gap

Medium · window-functions · sessionisation · gaps-and-islands

From fct_event (event_id, visitor_id, occurred_at_utc) alone, rebuild sessions: a new session begins when the gap from that visitor's previous event exceeds 30 minutes, and every session is force-closed at UTC midnight so none spans two calendar dates. Return one row per session with visitor_id, session_start, session_end, event_count and session_date. Do not read fct_session; the point is to reproduce it. Assume duplicate occurred_at_utc values exist for the same visitor.

Approach

1. Get the previous timestamp per visitor with LAG(occurred_at_utc) OVER (PARTITION BY visitor_id ORDER BY occurred_at_utc, event_id). The event_id tiebreaker is required, not stylistic: with duplicate timestamps an unstable ordering makes the boundary flags non-deterministic between runs.
2. Set a boundary flag when prev IS NULL, or occurred_at_utc - prev > interval '30 minutes', or occurred_at_utc::date <> prev::date. The third disjunct is the midnight rule, expressed as a date change rather than a clock comparison so it holds across any gap length.
3. Number the islands with SUM(flag::int) OVER (PARTITION BY visitor_id ORDER BY occurred_at_utc, event_id ROWS UNBOUNDED PRECEDING). Because event_id is unique the ordering is total, so no two rows are peers and RANGE UNBOUNDED PRECEDING would compute exactly the same numbers here. Write ROWS anyway: it is the half of the guard that survives someone later simplifying the ORDER BY back to occurred_at_utc alone, at which point the default RANGE frame gives every row sharing a timestamp one shared running total.
4. GROUP BY visitor_id and the island number, then MIN(occurred_at_utc) AS session_start, MAX(...) AS session_end, COUNT(*) AS event_count, session_start::date AS session_date.
5. Reconcile against fct_session on one sample day. The counts should agree except for server-emitted events carrying no client session, so a systematic difference beyond those is a bug in the gap rule or in the ordering.

Worked solution (30 min)

1. Pick one high-volume visitor and dump their ordered event timestamps for a day to trace by hand.
2. Add LAG with the tiebreaker and eyeball the computed gaps.
3. Add the three-part boundary flag and confirm the first event of each date is flagged.
4. Add the running SUM with an explicit ROWS frame, then group and aggregate.
5. Compare total event_count against the raw input count, and compare session counts to fct_session for the sample day.

Expected result: One row per (visitor_id, island). SUM(event_count) equals the input row count exactly, with no events lost or duplicated. No session_end falls on a different date from its session_start.

Verify it yourself

• Total events in equals total events out.
• MAX(session_end::date - session_start::date) is 0 across all rows.
• For a visitor ordered 09:00, 09:10, 09:55, 23:58, 00:02, the result is four sessions, not three.
• No two sessions for the same visitor have overlapping [session_start, session_end] ranges.
• Swapping ROWS for RANGE while event_id stays in the ORDER BY changes nothing; dropping event_id from both ORDER BY clauses does change the boundaries for a visitor with duplicate timestamps, which locates the guard in the tiebreaker rather than in the frame keyword.

Follow-up

• Why 30 minutes? What does a 5-minute rule do to sessions-per-visitor and to any per-session conversion rate?
• The same person uses phone then laptop. Two visitor_ids, two sessions. What breaks if you sessionise on user_id instead?
• The midnight rule splits an overnight session. Which metrics does that bias, and in which direction?

Given a dataset of user behaviors, how would you identify key trends?

Medium · product sense and metrics

Approach

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

Follow-up

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

Provide an example of a complex problem you solved using data.

Medium · product sense and metrics

Approach

1. State what result would change your recommendation, so the answer is falsifiable.
2. Restate the decision this analysis has to support, and who acts on the answer.
3. Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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

What steps would you take to solve a discrepancy in data reports?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Fix the population and the time window before naming any metric.
3. 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?
• Which segment would you cut first, and what would that rule out?

Describe how you would approach a project that requires integrating multiple data sources.

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you design an A/B test to evaluate a new feature?

Medium · a/b testing and experimentation

Approach

1. Name the guardrails that would stop a launch even on a positive primary result.
2. Name the randomisation unit first; it decides the variance and what the test can detect.
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?
• How would you handle interference between treated and control units?

Explain the difference between supervised and unsupervised learning.

Medium

Approach

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

Follow-up

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

Tell a novelty effect apart from a durable one

Medium · novelty effect · exposure cohorts · composition

A redesigned navigation shows a 6% lift in weekly core actions in calendar week one, 3% in week two and 1% in week three, on a continuously enrolling user-randomised experiment. Enrolment ran throughout, so later exposure cohorts are younger accounts. Using fct_experiment_exposure (unit_id, variant, first_exposed_at_utc), dim_user.account_created_at_utc and fct_event, determine whether this is novelty decay, a composition artefact, or a real effect that shrank, and state what evidence would settle it.

Approach

1. Separate calendar time from exposure time first. With continuous enrolment, calendar week two mixes units in their second week of exposure with units in their first, and the tenure mix changes daily, so a calendar trend is not evidence of decay.
2. Rebuild the readout as a triangle indexed on days since first_exposed_at_utc. For each exposure cohort compute the treatment effect at exposure-day k, then average across cohorts at fixed k using only cohorts that have reached k. Real decay shows as a falling curve in k; a composition artefact flattens out.
3. Use the one clean discriminator novelty offers: it requires a prior version to be novel against. Split on whether the unit existed before experiment start. Users who signed up afterwards never saw the old navigation, so a stable effect among them is not novelty, and an effect confined to pre-existing users is.
4. Check the primacy direction as well. An early negative effect that recovers is the mirror image and argues for shipping rather than against it, and it is invisible without the same exposure-indexed curve.
5. Say what would settle it. A long-run holdback, a few percent of traffic kept on control for eight weeks or more and read from exposure-week six onward. No three-week test can distinguish a decaying effect from one that has reached a lower plateau.

Worked solution (30 min)

1. Assign each exposed unit an exposure cohort from date(first_exposed_at_utc) and compute treated and control means per cohort per exposure-day k.
2. Average the per-cohort effects at each k only across cohorts that have observed day k, leaving later k blank rather than averaging over whoever has reached it.
3. Split the same triangle on pre-launch versus post-launch account_created_at_utc from dim_user.
4. Compare the curves: decay confined to the pre-launch split is novelty, decay in both is a real effect shrinking, and a flat exposure-time curve under a falling calendar curve is composition.
5. Recommend the holdback and state the earliest exposure-week at which a plateau claim becomes supportable.

Expected result: The decision rests on the exposure-indexed curve, not the calendar one. Novelty is indicated only when decay is present among units that existed before launch and absent among units created after it. Identical decay in both splits means a real effect shrinking; no decay in exposure time means the calendar trend was composition. A three-week test cannot separate decay from a plateau, and a long-run holdback can.

Verify it yourself

• Within a cohort, denominators must not fall as k grows. If they do, the population is being conditioned on survival rather than held fixed at exposure.
• Re-aggregating the exposure-indexed effects with the observed enrolment weights must reproduce the reported calendar-week effects, which proves the two views are the same data.
• Verify with a query that no unit in the post-launch split has a pre-launch session, otherwise that split is not clean and the discriminator is worthless.

Follow-up

• The holdback costs a few percent of traffic on the winning arm. How do you justify that, and when would you retire it?
• Weekly core actions per user is a ratio whose numerator and denominator live at different grains. Which standard error are you computing, and why is the naive one wrong?
• What would you conclude if the effect were 6%, 3%, 1% in exposure time as well, among post-launch signups only?

A conversion rate that fell in one regulatory region

Medium · consent · identity-keys · denominator · geography

Visit-to-signup conversion fell 1.3 points over six weeks. Signups cut by dim_user.country_code put the fall in one regulatory region where a consent banner shipped in week one, but absolute signups from that region are flat. fct_session carries consent_state, visitor_id, is_bot_flagged and session_date and no country column, so the denominator cannot be cut the same way. Using fct_session and fct_event, decide whether behaviour changed or the denominator did, state what these tables cannot settle, and name the one column that would settle it.

Approach

1. Name the asymmetry before computing anything. The numerator is user-keyed and therefore cuttable by country; the denominator is visitor-keyed and is not. Dividing a region-filtered numerator by an unfiltered denominator produces a quantity that is not a rate, and presenting it as a regional conversion rate is the first mistake available here.
2. Attack the denominator on the dimension you do have. Compute distinct visitor_id per week and sessions per distinct visitor_id per week: a consent banner that blocks or shortens the identity cookie raises the distinct-visitor count and lowers sessions per visitor, which depresses any visitor-keyed rate with no behaviour behind it.
3. Split on consent_state. Sessions with consent_state = 'denied' can enter the denominator but can never be joined forward to a signup, so a rising denied share mechanically drives the pooled rate down by roughly its own share. Report the granted-only rate and the denied share as two separate numbers rather than one blended figure.
4. Cross-check with measures that do not depend on the visitor key at all: absolute weekly signups, which are given as flat, and signups per session rather than per visitor.
5. State the limit honestly. Without country on the session or on its entry event, the regional attribution rests on the numerator alone, and the correct request is that one column, not a more elaborate model on top of the data you have.

Follow-up

• If granted-only conversion is the metric going forward, what selection bias have you accepted, and in which direction does it point?
• How would you handle the six weeks of already-published history once the new definition is adopted?
• What is the smallest instrumentation change that restores a cuttable denominator without collecting more personal data than before?

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.

Being wrong in public

A number you shipped turned out to be wrong, and someone had already acted on it. That is one of the most useful stories a data person can carry. What is being scored is how fast you noticed, who you told first, and what you changed in the process so the same class of error could not repeat quietly.

How do you handle missing data in a dataset?

Medium · behavioural and stakeholder questions

Approach

1. Pick a story where you drove the decision, not one where you observed it.
2. Name the disagreement or constraint, and how you resolved it with evidence.
3. State the situation in two sentences and spend the rest on your reasoning.

Follow-up

• What would you do differently if you ran that project again?
• What did you decide not to do, and why?

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?

Walk through an analysis you got wrong and what changed

Easy · postmortem · data quality · self-assessment

Describe an analysis of yours that turned out to be wrong after somebody had already acted on it. You have four minutes. The account must name the defect mechanically, the join, the filter, the window or the identity key, rather than describing it as a communication problem. It must also say who did what because of the wrong number, how the error surfaced, how long it stood, and what control you put in place so that class of error cannot reach a decision again. Do not pick an error nobody acted on.

Approach

1. Recognise what is being probed: whether you can be specific about your own failure without minimising it or performing contrition. The discriminator is whether the defect has a mechanism the listener could reproduce in their own warehouse.
2. Choose the case by blast radius rather than by comfort. An error nobody acted on tests nothing, and picking one signals that you are managing the interview instead of answering it.
3. Structure the account in six beats: the number, the decision it drove, the defect, the detection, the correction, the control. Keep the defect to one reproducible sentence, for example an inner join to fct_subscription_period that dropped accounts with no subscription row and so computed retention over payers only.
4. State the direction of the bias, not only its existence. A filter or join that removes rows usually moves a metric predictably, and knowing which way shows you diagnosed the mechanism rather than patched the symptom.
5. Be exact about detection and elapsed time. 'A colleague noticed' and 'the row-count assertion failed before publication' are different answers about the same organisation, and the second one is the one your control is supposed to produce next time.
6. End on the control, its cost, whether it has fired since, and one thing it does not cover.

Follow-up

• What did the control cost, and has it fired since? If it never has, how do you know it works?
• How long did the wrong number stand before anyone questioned it, and what does that say about the review path it went through?
• What is the equivalent mistake you are most likely to make in this role, given the tables you would be working in?