Anz Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Anz, you play a pivotal role in harnessing the power of data to drive informed decision-making and enhance user experiences across the business. Your work directly influences a variety of products and services, from optimizing customer interactions to refining operational efficiencies. The insights you generate not only shape strategic initiatives but also contribute to the overall mission of Anz to deliver exceptional value to its customers.

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

Focus areas

• Separate novelty effects from durable behaviour change
• Decompose a metric move by segment and mix
• Size an experiment before anyone launches it

Overview

This role is particularly exciting due to the scale and complexity of the data you will handle. You will engage with vast datasets, employing advanced analytics and machine learning techniques to uncover trends and make predictions. Collaborating closely with cross-functional teams, including engineering and product management, you will have the opportunity to impact key business outcomes and directly contribute to innovative solutions that align with the organization’s goals.

Expect to work on a range of projects that challenge your analytical skills and creativity, from predictive modeling to A/B testing, while navigating a dynamic environment that values collaboration and continuous learning.

The Anz interview process

Anz candidates report 2 rounds · ≈ 2-4 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 Discussions

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

Reading a pooled rate that moved because the mix moved, not because any behaviour changed

A pooled conversion rate is a weighted average, and a shift in the weights can move it in the opposite direction to every one of its parts. A paid campaign that brings low-converting traffic drops overall signup conversion even if desktop, mobile web and app conversion each rose that week, which is Simpson's paradox and it is the single most common cause of an inexplicable dashboard move. The discipline is to decompose before explaining: recompute the rate holding last period's segment weights fixed, and compare that counterfactual to the actual, so the mix effect and the rate effect are separated numerically rather than argued about. Segment on the dimensions that actually reweight, which in this domain are almost always device_type, referrer_channel, country and new versus returning.

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.

Explaining an aggregate move without decomposing the mix shift

Split the change in the aggregate into within-segment movement and movement in segment weights before you explain it. Every segment's rate can fall while the overall rate rises, purely because volume shifted toward segments that already had higher rates.

Writing SQL without stating NULL and tie-breaking behaviour

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

Questions & practice

Describe how to implement a decision tree from scratch.

Medium · machine learning and modelling

Approach

1. Say how the offline result would be validated online before it is trusted.
2. Pick an evaluation metric that matches the cost of each error type, not a default.
3. Set a baseline first, so any model has something honest to beat.

Follow-up

• How would you choose the decision threshold, and who owns that choice?
• Where could label leakage enter this setup?

Split a pooled conversion drop into rate and mix

Medium · mix shift · decomposition · pandas

You have weekly visit-to-signup counts by segment: a DataFrame with week, device_type, referrer_channel, visitors and signups. The pooled rate fell 0.84 percentage points between two consecutive weeks while several individual segments rose. Write a function that, for a caller-supplied list of segment columns, splits the pooled change into a rate effect, a mix effect and an interaction term that sum exactly to the observed change. Return those three scalars plus a per-segment contribution table sorted by absolute contribution, so the largest single driver can be named.

Approach

1. State the algebra before coding: the pooled rate is r = sum over segments of w_s * r_s, with w_s the segment's share of the denominator. Then r1 - r0 decomposes exactly into sum(w_s0 * (r_s1 - r_s0)) for rate, sum((w_s1 - w_s0) * r_s0) for mix, and sum((w_s1 - w_s0) * (r_s1 - r_s0)) for interaction. The identity is per-segment, so it holds for any numbers you put in the four slots.
2. Pivot both weeks onto a common segment index with an outer join so a segment that appeared or vanished is kept rather than dropped, then decide what rate to give a segment with no visitors in one of the weeks, and document the choice. The identity stays exact either way because the missing week's weight is 0, but the attribution does not. Filling the missing rate with 0 sends an appearing segment's entire w_s1 * r_s1 into the interaction term, since w_s0 = 0 makes both the rate term and the mix term (w_s1 - w_s0) * r_s0 identically zero; a vanishing segment then splits as -w_s0 * r_s0 in rate, -w_s0 * r_s0 in mix and +w_s0 * r_s0 in interaction.
3. The convention used below instead imputes the missing week's rate as that week's pooled rate. A vanishing segment then lands wholly in mix at -w_s0 * r_s0, with rate and interaction cancelling; an appearing segment puts w_s1 * r_pooled0 in mix (volume arriving at the average rate) and only w_s1 * (r_s1 - r_pooled0) in interaction (its rate differing from that average). Impute by which week the segment is missing from, never by argument order, or the swap identities below stop holding.
4. Guard the division where visitors is 0 so no NaN enters the vectors, because a single NaN poisons every sum. A segment with zero visitors in both weeks contributes exactly 0 and can be dropped; a segment missing from only one week does not contribute 0, and where its contribution lands is settled by the convention above, not by the guard.
5. Compute the three components as vectors over segments, then sum. Keep the vectors, because the per-segment contribution table is what turns the decomposition into an explanation.
6. Assert that the three components sum to the observed pooled change within floating-point tolerance. This identity is exact, so a mismatch means an implementation bug, not a modelling judgement.

Worked solution (25 min)

1. Aggregate to one row per (week, segment tuple) with summed visitors and signups, then split into w0 and w1 frames and align with an outer join, filling missing visitors and signups with 0.
2. Compute w_s = visitors / visitors.sum() within each week, and r_s = signups / visitors only where visitors > 0. Where a week's visitors are 0, set that week's r_s to that week's pooled rate, the stated convention; never leave it NaN.
3. rate_effect = (w0 * (r1 - r0)).sum(); mix_effect = ((w1 - w0) * r0).sum(); interaction = ((w1 - w0) * (r1 - r0)).sum().
4. contribution = w0*(r1-r0) + (w1-w0)r0 + (w1-w0)(r1-r0) per segment, which reduces to w1r1 - w0r0; sort by abs and return the head.
5. assert abs(rate + mix + interaction - (pooled1 - pooled0)) < 1e-12.

Expected result: Three scalars summing exactly to the observed pooled change, plus a segment table whose contribution column also sums to that change. In the stated case rate and mix carry opposite signs: several segments converted better while volume moved toward a weaker-converting one, which is what makes the pooled move look inexplicable until it is split.

Verify it yourself

• On synthetic data where every segment appears in both weeks, every segment rate is held constant and only the weights move, rate_effect and interaction are exactly 0.
• On synthetic data where every segment appears in both weeks, weights are held constant and only rates move, mix_effect and interaction are exactly 0.
• The per-segment contribution column sums to the pooled change, matching the sum of the three scalars.
• Swapping the two weeks negates the total, as it must, but not the parts. Assert the three identities that actually hold: the interaction term is exactly invariant, because both of its factors change sign; the swapped rate effect equals -(rate_effect + interaction); and the swapped mix effect equals -(mix_effect + interaction). They still sum to the negated pooled change. Expecting all three to flip sign is the common wrong assertion.
• Plant a segment present only in week 1, holding 3 percent of week-1 visitors at a rate well below pooled. Under the documented pooled-rate convention its mix contribution is w_s1 * r_pooled0 and its interaction contribution is w_s1 * (r_s1 - r_pooled0), with a rate contribution of exactly 0; under a zero-fill convention its mix contribution is exactly 0 and the whole w_s1 * r_s1 lands in interaction. Assert whichever one the function documents, and fail if mix is asserted non-zero while the code zero-fills.

Follow-up

• The mix effect accounts for 0.71 of the 0.84 point drop, driven by paid_social volume. What is your recommendation, and what would change it?
• Why is a two-way split into a counterfactual rate and a residual also exact, and when would you prefer it to the three-way version?
• Segmenting on device and channel leaves a large interaction term. What does that tell you about the choice of segments?

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?

How would you optimize a SQL query that is running slowly?

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. Say which table is the grain you start from, and join outward from it.

Follow-up

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

Write a Python script to clean and preprocess a dataset.

Medium · sql and data manipulation

Approach

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

Follow-up

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

Paying accounts with no active seat in 28 days

Medium · anti-join · null-semantics · retention-risk

dim_account holds account_id, account_type, lifecycle_status, seats_licensed. fct_event holds account_id, user_id, occurred_at_utc, is_core_action, and its account_id is NULL for every signed-out and pre-signup event. Find accounts with lifecycle_status = 'active' and account_type <> 'internal' that had no distinct user complete a core action in the trailing 28 days. Return account_id, seats_licensed and days since that account's most recent core action, with NULL where the account has never emitted one. Order by seats_licensed descending.

Approach

1. Build the recent-activity set first: fct_event rows with is_core_action = TRUE, occurred_at_utc >= now() - interval '28 days', and an explicit account_id IS NOT NULL. Making the NULL exclusion explicit in the CTE is what lets you reason about the anti-join afterwards.
2. Express the exclusion with NOT EXISTS (correlated on account_id) or a LEFT JOIN with an IS NULL guard. Do not use NOT IN against this column: it is nullable, and SQL's three-valued logic turns the whole predicate UNKNOWN, returning zero rows.
3. Compute last-seen separately as MAX(occurred_at_utc) per account over all history, LEFT JOINed on, so an account that has never emitted a core action (NULL) is distinguishable from one that went quiet six weeks ago. Those two cases have different causes and different owners.
4. Rank by seats_licensed, or better by the account's current mrr_cents_constant_fx if you are allowed the subscription table, because a silent fifty-seat account is a renewal conversation and a silent one-seat account is noise.
5. Before shipping, check whether the never-seen group is a cluster by signup date or surface. A block of accounts with no events at all is usually an instrumentation gap, not a set of customers who stopped using the product.

Worked solution (25 min)

1. Count NULL account_id rows in fct_event over the window so you know the trap is live in this data rather than hypothetical.
2. Write the active-account spine and the 28-day activity CTE.
3. Write the anti-join with NOT EXISTS, then deliberately run the NOT IN version and record that it returns zero rows.
4. Add the all-time MAX(occurred_at_utc) LEFT JOIN and derive days_since as a date difference.
5. Split the output into 'quiet' and 'never seen' and eyeball the never-seen group for a shared signup window or surface.

Expected result: One row per silent active account with seats_licensed and days_since_last_core_action, ordered by seats_licensed descending. Accounts that never emitted a core action show NULL and are reported as a separate group rather than as the worst churn risks.

Verify it yourself

• Silent accounts plus accounts with at least one active user equals the total count of active non-internal accounts.
• The NOT IN variant returns zero rows, which is the demonstration that a NULL is present.
• No account_id appears twice in the output.
• Every days_since_last_core_action is either NULL or greater than 28.

Follow-up

• How would you distinguish a genuinely idle account from one whose events lost their account_id after an instrumentation change?
• Would you count on fct_event.account_id or resolve user_id through dim_user instead, and what does each choice miss?
• Licensed-seat utilisation is the continuous version of this. How would you turn this boolean into that ratio?

Given a dataset, how would you identify features that are most predictive of the target variable?

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

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?

Describe your approach to developing a recommendation system for a retail website.

Medium · product sense and metrics

Approach

1. Fix the population and the time window before naming any metric.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

• Which segment would you cut first, and what would that rule out?
• What would you do if the primary metric and the guardrail moved in opposite directions?

How would you structure an analysis to determine the impact of a marketing campaign?

Medium · product sense and metrics

Approach

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

If you were tasked with improving a product's user retention, what data would you analyze?

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

Follow-up

• Which segment would you cut first, and what would that rule out?
• What would you do if the primary metric and the guardrail moved in opposite directions?

Walk us through your process for conducting an A/B test.

Medium · a/b testing and experimentation

Approach

1. Name the randomisation unit first; it decides the variance and what the test can detect.
2. State the primary metric and the minimum effect worth shipping, then size the test.
3. Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

• What would you conclude if the result is positive but the test is underpowered?
• How would you handle interference between treated and control units?

What are some common techniques for handling missing data?

Medium

Approach

1. Say what you would check first and why it is the highest-information step.
2. Work from the decision backwards to the evidence you would need.
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?

Diagnose a 50.5 to 49.5 split before reading the result

Easy · sample ratio mismatch · exposure logging · chi-square

An experiment randomised on visitor_id ran for 11 days. fct_experiment_exposure holds 104,320 control rows and 102,180 treatment rows for the experiment_id, all with is_in_analysis_population = TRUE, against a configured 50/50 split. The treatment arm shows a 3.1% relative lift in visit-to-signup conversion at p = 0.004, and the team wants to ship. Decide whether that lift can be reported, show the arithmetic behind your decision, and name the three checks you would run first on the exposure logging.

Approach

1. Test the split instead of eyeballing it. Chi-square goodness of fit against 103,250 expected per arm gives 2 x 1070^2 / 103,250 = 22.2 on 1 degree of freedom, p about 2.5e-6. The conventional alarm threshold is p < 0.001, so this is a sample ratio mismatch.
2. Treat the mismatch as invalidating, not as a caveat. The arms are no longer exchangeable, so the 3.1% lift has no causal reading and rebalancing on covariates afterwards does not restore it.
3. Walk the logging path in the order units flow through it: assignment service, then the exposure event fired on the treated surface, then the filters that build the analysis population. Each stage can drop one arm preferentially.
4. Name the three suspects this stack actually has. Exposure emitted by client code the treatment arm reaches later or not at all when its bundle errors; an extra redirect or render in one arm that loses slow connections; and a post-assignment filter (is_bot_flagged, is_internal, consent_state) that correlates with variant.
5. Check whether the imbalance is uniform by splitting counts on session_date, surface, app_version and device_type. A mismatch confined to one client build or to day one points at a deployment fault that can be fixed and re-run, not at a design fault.

Worked solution (15 min)

1. N = 206,500, expected 103,250 per arm, observed deviation 1,070, which is 0.518% of the total pushed into one arm.
2. Chi-square = 2 x 1070^2 / 103,250 = 22.18 on 1 df, p about 2.5e-6.
3. Compare with the p < 0.001 threshold and stop: the readout is void.
4. Re-pull the same counts grouped by session_date and app_version to localise where the rows were lost.
5. Deliver one line: the split is off by 1,070 visitors, a roughly one-in-400,000 event under correct randomisation, so the experiment is being fixed and re-run rather than read.

Expected result: Chi-square(1) = 22.2, p about 2.5e-6, far past the p < 0.001 mismatch threshold. The correct deliverable is an exposure-logging investigation and a re-run, not a caveated 3.1% lift.

Verify it yourself

• Cross-check the statistic as a z on one arm count: under a fair split that count has SD sqrt(N/4) = sqrt(51,625) = 227 visitors, so the 1,070 deviation is 4.71 SD, and 4.71^2 = 22.2 reproduces the chi-square.
• Chi-square scales linearly with N at a fixed ratio, so the same 50.518% split at N = 2,000 returns 22.18 / 103.25 = 0.21, p = 0.64. That reconciles the arithmetic and nothing more; it is not evidence that the alarm is independent of N. What rules out the large N is that the p-value is uniform under correct randomisation at every sample size, so p = 2.5e-6 is a one-in-400,000 draw whether N is 2,000 or 2,000,000. Large N buys the power to resolve a 0.5% skew; it does not manufacture one.
• The mismatch p-value should be orders of magnitude smaller than the lift's p-value; if they are comparable, recheck the arithmetic.
• Arm counts must sum to the total exposure rows for that experiment_id. If they do not, the analysis-population filter is already dropping rows before you looked.

Follow-up

• The imbalance is entirely in one app_version on android. Can you salvage the other surfaces, and under what argument?
• How would you monitor this across every running experiment without drowning in false alarms from a daily chi-square at alpha 0.05?
• The missing rows turn out to be disproportionately visitors with consent_state = 'unknown'. What does that do to the estimate even after the logging bug is fixed?

Pooled signup conversion fell while every segment rose

Medium · mix-shift · simpsons-paradox · decomposition

Weekly visit-to-signup conversion, counted on distinct fct_session.visitor_id with is_bot_flagged = TRUE and consent_state = 'denied' sessions excluded, fell from 4.4% to 3.9% week over week. Split by device_type and referrer_channel, all twelve cells are flat or up. A paid_social campaign launched on Monday. Using fct_session and fct_event, quantify how much of the 0.5-point fall is mix and how much is within-segment rate, then state what you would tell the growth lead.

Approach

1. Write the pooled rate explicitly as the sum over segments of weight times segment rate, and materialise both weeks' weights and rates into one table. Until that table exists there is nothing to decompose, only opinions.
2. Compute three quantities and report all three: the rate effect holding the prior week's weights fixed, the mix effect holding the prior week's rates fixed, and the interaction residual. Reporting only the first two hides a term that can be material when both weights and rates move a lot.
3. Rank segments by their individual mix contribution, computed as the change in that segment's weight multiplied by its prior-period rate. This is what lets you say one cell caused the move rather than gesturing at the campaign.
4. Verify the new traffic is human and countable before accepting the mix story: check is_bot_flagged coverage on the new channel, the distribution of duration_seconds and event_count for its sessions, and whether its consent_state profile differs from the rest.
5. Deliver the conclusion as a definition change rather than a diagnosis: a pooled rate over a mix that moves is not comparable week over week, so the recurring report should carry per-channel rates plus absolute signups, with the pooled figure demoted or dropped.

Follow-up

• Paid social converts at roughly a quarter of organic but absolute signups rose. Is the campaign working, and what would you need to answer that properly?
• Would you reach the same conclusion if the campaign had moved the mix by two points instead of sixteen? Where is your threshold and why?
• How would you present this to someone who has been watching the pooled number in a weekly meeting for a year?

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.

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.

Describe a situation where you had to navigate conflicting priorities.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

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?

Turn an ambiguous onboarding question into a measurable metric

Easy · scoping · metric definition · stakeholder

Two days before a planning review, a director asks whether onboarding is working. You have dim_user (account_created_at_utc, signup_surface, is_internal), fct_event (is_core_action, flow_id, flow_instance_id, event_name, occurred_at_utc, received_at_utc) and fct_session. No further meeting with the director is possible before you start work. Deliver three clarifying questions you would send in writing, the metric you will compute in the meantime with its numerator, denominator, window and exclusions, and one sentence naming the question you are deliberately not answering.

Approach

1. Recognise what is being probed: whether you convert a goal into a computable predicate without stalling for requirements or guessing in silence. Listing clarifying questions is the generic answer; shipping a defensible default alongside them is the strong one, because the review is in two days and it will happen with or without you.
2. Infer the decision behind the request. A question about whether onboarding works, arriving before a planning cycle, usually means whether to staff it next quarter. That points at a rate with visible headroom over several cohorts, not at a descriptive dashboard.
3. Write the three questions so that each one changes the SQL. Which population, all signups or only self-serve from dim_user.signup_surface. What counts as working, reaching a core action or completing the onboarding flow_id. Against what bar, last quarter's cohorts or a stated target.
4. Propose the default explicitly: seven-day activation on weekly signup cohorts. Numerator, users with is_core_action = TRUE events on at least two distinct UTC dates inside [account_created_at_utc, account_created_at_utc + 7 days). Denominator, the signup cohort with is_internal = FALSE. Publish with an eight-day lag, and state that the two-distinct-days threshold is a frozen choice rather than a discovery.
5. Name the exclusion in the same breath as the number. The series shows whether users activate; it does not establish that onboarding caused the level, which needs a staged rollout or an experiment.

Follow-up

• The director replies that they meant the onboarding flow specifically, not activation. What changes in the query and in the caveats?
• Your cohort metric needs an eight-day lag and the review is in two days. What do you present, and how do you label it?
• Two of your three questions come back unanswered. Which one do you refuse to proceed without?