Tapad Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Tapad, you play a pivotal role in transforming complex data into actionable insights that drive the company's strategic initiatives. Your work directly influences the development of innovative advertising and marketing solutions, helping clients maximize their reach and effectiveness in a rapidly evolving digital landscape. By utilizing advanced analytical techniques and machine learning models, you contribute to the enhancement of product offerings that leverage vast amounts of data to improve user experiences and business outcomes.

Scope your preparation by the data you would actually touch, because the title will not tell you. A seat that lives in event logs and weekly readouts rewards fluency in aggregation and metric definitions; a seat that owns a model in production rewards fluency in train/serve skew, retraining cadence and drift monitoring. The fastest way to find out which one you are interviewing for is to ask what the team shipped last quarter and what it gets paged about.

Focus areas

• Reason about bid shading under first-price auctions
• Separate invalid traffic from genuine performance shifts
• Design geo holdouts that survive budget interference

Overview

This role is not only critical for the success of Tapad, but also presents an exciting opportunity for you as a candidate. You will engage with diverse datasets and work on challenging problems that require both creativity and technical expertise. The collaborative environment allows you to work closely with cross-functional teams, including engineering and product management, to design and implement data-driven strategies that address real-world business challenges.

Expect to immerse yourself in a dynamic work environment where your analytical skills and innovative thinking are valued. The complexity of the problems you tackle and the scale at which you operate make this role both demanding and rewarding.

The Tapad interview process

Tapad candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Phone 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 Interviews

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

Team Interaction

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.

Where candidates lose points

Comparing conversion rates between users who were exposed to an ad and users who were not.

Exposure is the output of a targeting and bidding system that ranks users by predicted conversion propensity, so the exposed group has a higher baseline conversion rate before the ad renders. The measured difference therefore estimates the quality of the targeting model, not the effect of the advertising, and it is biased upward by an amount that grows as the model gets better. The valid control is the set of users the system would have shown the ad to but deliberately did not, captured through a ghost-bid or public-service-announcement holdout that records auction wins without serving the advertiser's creative. Anyone who proposes matching or covariate adjustment to fix this should be asked what unobserved feature the bidder used that the matching model does not have.

Reallocating budget using last-click attribution.

Last-click assigns full credit to whichever touchpoint sits closest to the conversion in time, which structurally favours channels that harvest existing demand — retargeting an already-interested user, or catching a branded search — over channels that create demand in the first place. Optimising against it therefore moves money toward tactics that would have converted many of those users anyway, and the reported cost per action improves at the exact moment true incremental performance gets worse. The signature of this failure is a portfolio where every channel's attributed conversions sum to well above the advertiser's total conversion count. The counter is to treat attributed numbers as a budget-splitting convention and to source the actual reallocation decision from holdout-based incrementality.

Solving silently instead of narrating the reasoning

Say which branch you are taking and why you chose it over the alternative, for example checking the denominator first because it changes what the comparison means. A correct answer that arrives with no visible path scores below a rigorous one that needed a hint.

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.

Questions & practice

Explain the bias-variance tradeoff.

Medium · statistics and probability

Approach

1. Say what the estimate is of, and over what population it generalises.
2. Translate the result into the decision it informs, in one plain sentence.
3. Sanity-check the answer against a simple bound or a simulated case.

Follow-up

• How would you explain this result to someone who does not know statistics?
• What sample size would you need to detect an effect half this size?

Discuss the importance of feature engineering in model performance.

Medium · machine learning and modelling

Approach

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

Follow-up

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

Detect duplicate conversion reports across pixel and server sources

Easy · deduplication · data-quality · conversions · pandas

You are given a pandas DataFrame conversions holding raw conversion_event rows: conversion_id, advertiser_id, conversion_ts, received_ts, conversion_type, conversion_value_usd, order_id, device_id, click_tracking_id, source, dedup_key. The is_duplicate column has been removed because producing it is the task. Roughly 30% of rows carry a null dedup_key. Return a boolean Series aligned to the input index marking duplicate reports of one real-world conversion, plus a per-advertiser table giving the duplication rate and the dedup_key coverage behind it. State your keying rule and its fallbacks.

Approach

1. Declare a keying ladder before writing code and apply the tiers in priority order so each row is matched by exactly one rule: tier 1 (advertiser_id, dedup_key) where dedup_key is present, tier 2 (advertiser_id, order_id) where it is not, tier 3 a bounded behavioural rule. Keep the tier label on the output so the result is auditable rather than a bare boolean.
2. Within tier 1, sort by received_ts then conversion_id and mark every row after the first in a group. Keeping the earliest-received row is the right survivor because it is the one already counted in published numbers; keeping the latest silently restates history every time a late report lands.
3. For tier 3, sort within (advertiser_id, device_id, conversion_value_usd) by conversion_ts and collapse rows whose gap to the current survivor is under 60 seconds, carrying the survivor forward rather than chaining diffs. A plain diff chain collapses a run of rows 40 seconds apart into one, which is a different and wrong rule.
4. Implement tiers 1 and 2 with sort plus groupby.cumcount, never with a self-join: a self-join on advertiser_id is quadratic in the largest advertiser and will not finish on a real month of data. Tier 3 cannot be written that way. Because a flagged row does not become the survivor, each comparison depends on which earlier rows were already collapsed, so cumcount (which flags every row after the first regardless of gap) and a shifted-diff (which compares against the previous row, not the survivor) both compute a different rule. Run tier 3 as one sequential O(n) pass over the sorted arrays - a Python loop over the numpy columns, or numba if the tier is large - which is affordable because tier 3 holds only the rows with neither dedup_key nor order_id.
5. Report duplication rate alongside dedup_key coverage per advertiser. Coverage is what makes the rate interpretable: at 30% nulls the measured duplication rate is a lower bound, and the gap is an advertiser tagging problem, not a conversion problem.

Worked solution (20 min)

1. Add a tier column: 1 where dedup_key.notna(), 2 where dedup_key.isna() and order_id.notna(), 3 otherwise. Assert the three masks partition the frame.
2. Tier 1 and 2: df.sort_values(['received_ts','conversion_id']).groupby(key).cumcount() > 0 gives the duplicate flag; write it back by index so the output stays aligned.
3. Tier 3: sort within (advertiser_id, device_id, conversion_value_usd) by conversion_ts, then walk the sorted arrays once, holding the current survivor's conversion_ts per group and flagging each row whose gap to that survivor is under 60 seconds; a flagged row does not advance the survivor. Keep it a single sequential pass - it is O(n) but not vectorisable, so budget it as the slow tier and keep it small by filling dedup_key and order_id upstream.
4. Aggregate per advertiser_id: total_rows, flagged_duplicates, inflation_rate = flagged/total, dedup_key_coverage = share of rows with non-null dedup_key, and the share of flags contributed by each tier.

Expected result: A boolean Series with the same index and length as the input, plus a per-advertiser frame with total_rows, flagged_duplicates, inflation_rate, dedup_key_coverage and per-tier flag shares. Summing conversion_value_usd over unflagged rows is strictly lower than the raw sum, and the drop equals the value carried by the flagged rows.

Verify it yourself

• Every tier-1 and tier-2 group retains exactly one survivor: (~flag).groupby(key).size().max() == 1.
• No row is flagged by more than one tier, and the tier column covers every row exactly once.
• Tier 3 on a hand-built group of four rows spaced 40 seconds apart flags rows 2 and 3 but leaves row 4 (120 seconds after the survivor) unflagged. A shifted-diff or cumcount implementation flags all three, which is how you catch the wrong rule.
• Advertisers whose rows all come from a single source (source.nunique() == 1) should show a near-zero rate; a high rate there means the rule is collapsing genuinely distinct purchases.

Follow-up

• Tier 3 will collapse two genuine purchases of the same item a minute apart. How do you bound that false-positive rate, and for a CPA report would you rather over-collapse or under-collapse?
• An advertiser populates dedup_key with order_id on browser_pixel rows and with a session id on server_api rows. What does your ladder do, and how would you detect that from the data alone?
• If the duplication rate is 8%, what is the effect on attributed CPA, and in which direction does it move the number the advertiser sees?

How would you optimize a given SQL query?

Medium · sql and data manipulation

Approach

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

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

Solve a problem involving data manipulation using Python or R.

Medium · sql and data manipulation

Approach

1. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
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 would you verify this result without re-running the same query?
• How does the query change if the join becomes one-to-many?

Time-weighted daily budget from slowly changing line item versions

Hard · scd type 2 · window functions · interval weighting · pacing

line_item is SCD type 2: one row per version with line_item_id, daily_budget_usd, pacing_mode, status, valid_from_ts, valid_to_ts (null on the current row) and is_current. ad_impression has line_item_id, served_ts, billed_price_micros_usd and is_billable. For one UTC date, compute budget delivery rate per line item: spend divided by a time-weighted daily budget, where the day's budget is each overlapping version's daily_budget_usd weighted by the fraction of the 24 hours that version was in force. Return spend, weighted budget, the weight coverage, and attainment.

Approach

1. Select versions that overlap the day with valid_from_ts < day_end AND COALESCE(valid_to_ts, 'infinity'::timestamptz) > day_start. Use strict inequality on both sides so a version that ends exactly at midnight does not also claim the following day, which would make the weights sum above 1.
2. Clip each version to the day before weighting: start at GREATEST(valid_from_ts, day_start) and end at LEAST(COALESCE(valid_to_ts, 'infinity'::timestamptz), day_end). Weighting on the unclipped interval gives a month-long version a weight of 30 rather than 1.
3. Derive the weight as EXTRACT(EPOCH FROM (clipped_end - clipped_start)) / 86400.0 and sum daily_budget_usd * weight per line item. Keep SUM(weight) in the output as coverage: it should be 1.0 for a line item live all day and below 1 for one created or ended mid-day, and anything above 1 means the interval predicate is wrong.
4. If valid_to_ts is unreliable on superseded rows, derive the interval end with LEAD(valid_from_ts) OVER (PARTITION BY line_item_id ORDER BY valid_from_ts) instead, and say which source you trust. Mixing the two produces overlapping intervals that double-count budget.
5. Aggregate spend in its own CTE at impression grain, filtered to is_billable and bucketed on served_ts in UTC, then join the two pre-aggregated results on line_item_id. Joining the SCD table to impressions before aggregating multiplies every impression by the number of versions.
6. Compute attainment as spend / NULLIF(weighted_budget, 0), and decide explicitly how to treat versions whose status was paused or ended for part of the day, since including a paused stretch in the denominator reads as underdelivery that never had a chance to happen.

Worked solution (40 min)

1. Declare day_start and day_end as explicit TIMESTAMPTZ bounds, then select line_item rows where valid_from_ts < day_end AND COALESCE(valid_to_ts, 'infinity'::timestamptz) > day_start.
2. Add w := EXTRACT(EPOCH FROM (LEAST(COALESCE(valid_to_ts, 'infinity'::timestamptz), day_end) - GREATEST(valid_from_ts, day_start))) / 86400.0 to each surviving row.
3. Build budget AS (SELECT line_item_id, SUM(daily_budget_usd * w) AS weighted_budget, SUM(w) AS coverage FROM clipped GROUP BY 1).
4. Build spend AS (SELECT line_item_id, SUM(billed_price_micros_usd) / 1e6 AS spend FROM ad_impression WHERE is_billable AND served_ts >= day_start AND served_ts < day_end GROUP BY 1), entirely independent of the dimension.
5. FULL OUTER JOIN budget and spend on line_item_id, compute spend / NULLIF(weighted_budget, 0), and inspect the rows where coverage differs from 1.0 as a separate list.

Expected result: Per line item: spend, weighted_budget, coverage and attainment. A budget of $1,000 in force from 00:00 to 18:00 and $2,500 from 18:00 to 24:00 gives weighted_budget = 10000.75 + 25000.25 = $1,375, and spend of $1,210 gives 88.0% attainment at coverage 1.0.

Verify it yourself

• For any line item live the whole day, the clipped intervals tile the 24 hours with no overlap and no gap, so SUM(w) is exactly 1.0.
• Exactly one row per line_item_id has is_current = TRUE. If more do, the SCD writer is broken and every weight downstream is wrong.
• Spend from this query equals a plain aggregate over ad_impression with no line_item join at all. A higher figure is proof the version join fanned out.
• A line item with a single version covering the whole day returns attainment identical to the naive spend / daily_budget_usd calculation, which is the degenerate case the weighting must reduce to.

Follow-up

• A line item shows 180% attainment. Give three explanations that are not overspend.
• How does the same calculation change for lifetime_budget_usd across a multi-week flight rather than a daily budget?
• pacing_mode is 'even' but hourly spend is front-loaded and stops at 14:00. Which query distinguishes budget exhaustion from a pacing throttle, and which column settles it?

Present a case where data analysis led to a significant business decision.

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

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

What factors would you consider when analyzing user engagement metrics?

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

How do you prioritize tasks when working on multiple projects?

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you approach optimizing an advertising campaign using data insights?

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

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

Walk us through your thought process when designing an experiment to test 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. 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 conclude if the result is positive but the test is underpowered?
• How would you handle interference between treated and control units?

What is the ROC curve, and why is it important in evaluating models?

Medium

Approach

1. Clarify what is being asked and what a complete answer would contain.
2. State your assumptions explicitly before working the problem.
3. Say what you would check first and why it is the highest-information step.

Follow-up

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

Cut the variance of a market-level holdout with pre-period data

Medium · cuped · variance-reduction · stratification

A 28-day holdout randomises 80 geo_dma markets 40/40; treated markets run the campaign and held-out markets get no delivery. The outcome is deduplicated conversion_event rows per market over the flight plus a 7-day window. Market sizes are heavily skewed and the unadjusted design detects only a 12% relative lift at 80% power. Twelve months of pre-period market conversions are available. Specify a variance-reduction plan, state the precision you expect, and state what would disqualify a covariate.

Approach

1. Apply CUPED: define Y_adj = Y - theta*(X - mean(X)) where X is pre-period conversions for the same market and theta = Cov(Y,X)/Var(X), estimated on the pooled arms. Variance falls by a factor of (1 - rho^2), where rho is the correlation between pre-period and in-period outcomes.
2. Estimate rho from history before the test runs, by regressing a past 28-day market window on its own preceding pre-period covariate. At rho = 0.9 the variance multiplier is 0.19, the standard error multiplier is 0.436, and the 12% MDE falls to roughly 5.2%.
3. Disqualify any covariate measured at or after assignment. In-flight impressions, in-flight spend and in-flight clicks are all downstream of treatment; adjusting on them shifts the point estimate rather than only shrinking its variance.
4. Stratify as well as adjust: form matched pairs on pre-period conversion volume, randomise within pair, and then analyse with pair fixed effects or a paired difference. Pairing that is not carried into the estimator buys nothing at all.
5. Handle the skew at design time by analysing the ratio metric, conversions per thousand eligible bid requests, or by weighting on pre-period volume, and commit to the choice before seeing the arms.
6. Pre-register theta's estimation rule and the covariate window, so neither can be tuned after the outcome becomes visible.

Worked solution (30 min)

1. On 12 months of history, build market-level 28-day outcome windows with matching pre-period covariates and compute rho; assume it returns 0.90.
2. Compute the variance multiplier 1 - rho^2 = 0.19 and the standard error multiplier sqrt(0.19) = 0.436.
3. Scale the unadjusted MDE: 12% * 0.436 = about 5.2% relative lift at 80% power, equivalent to 1/0.19 = 5.3 times the market count.
4. Form 40 matched pairs on pre-period volume, randomise within pair, and write the estimator as a paired difference on CUPED-adjusted outcomes.
5. Replay the whole pipeline as an A/A on two historical windows and confirm the adjusted estimate is centred on zero with the predicted standard error.

Expected result: rho of about 0.9 gives an 81% variance reduction, a 56% standard error reduction, and an MDE improvement from 12% to roughly 5.2% relative. Matched-pair assignment adds further reduction only if the analysis carries the pairing.

Verify it yourself

• Adjusted and unadjusted point estimates must agree well within one standard error; a larger shift means the covariate is post-treatment or the assignment is imbalanced.
• theta-hat should equal the OLS slope of Y on X in the pooled data.
• The realised variance ratio between adjusted and unadjusted outcomes should land near 1 - rho-hat^2; if it does not, rho was estimated on a window that does not match the flight length.
• An A/A replay across historical windows should produce 95% intervals covering zero about 95% of the time.

Follow-up

• CUPED moves the point estimate by 30% of its own standard error. What do you conclude?
• Would you use pre-period conversions or pre-period spend as the covariate, and why?
• How would the plan change if only 20 markets were available?

One advertiser's conversions doubled the week their SDK shipped

Medium · deduplication · conversion logging · definition change

An advertiser's daily conversion_event rows roughly doubled starting 14 August, with no change in their order volume as they report it. That week they added a server_api feed alongside their existing browser_pixel. conversion_event has conversion_id, advertiser_id, conversion_ts, received_ts, conversion_type, order_id, device_id, user_id_hash, click_tracking_id, source, dedup_key and is_duplicate, where is_duplicate is written by our dedup job. Quantify the true conversion count, name the failure precisely, and state what you would tell the account team.

Approach

1. Compare row counts against distinct dedup_key counts per advertiser per day. If rows double while distinct keys stay flat, the extra rows are duplicates that the dedup job never collapsed, and the real conversion level never moved.
2. Establish why the job failed rather than that it failed: compute the null rate of dedup_key by source, and for non-null keys compare the value format between browser_pixel and server_api on the same order_id. Case, prefixing and whitespace differences are the usual cause and are invisible in a count of non-null keys.
3. Use order_id as an independent second key. Distinct order_id per advertiser per day that is flat across 14 August confirms the real outcome count is unchanged and gives you the recovery rule.
4. Size the blast radius downstream: duplicated conversions inflate attributed conversions, deflate CPA and inflate ROAS for every line item serving that advertiser, and the attribution_credit rows already written carry the inflation.
5. Write the restatement rather than just the diagnosis — which advertiser, which dates, the direction and size of the correction, and whether any optimisation decision was already taken on the inflated numbers.

Follow-up

• The advertiser cannot change their server payload for a quarter. What dedup rule would you run in the meantime, and what does it get wrong?
• How would you detect this class of failure automatically for the other several thousand advertisers?
• If the two sources disagree on conversion_value_usd for the same order, which one wins and why?

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.

Describe a time when you faced a significant challenge in a project. How did you overcome it?

Medium · behavioural and stakeholder questions

Approach

1. Close with what you would do differently, concretely.
2. Name the disagreement or constraint, and how you resolved it with evidence.
3. Quantify the outcome, including what you would not claim credit for.

Follow-up

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

Explaining an incrementality interval to a non-technical budget owner

Medium · communication · uncertainty · incrementality

A 20-market geo holdout has closed. Incremental return on ad spend is estimated at 1.4 with a 90% interval of 0.6 to 2.2. The same account's last-click attributed ROAS is 4.1. A vice president with no statistics background has ten minutes and must decide whether to renew a $3M annual budget. Deliverable: the explanation you give verbally plus one slide. You may not use the words confidence interval, p-value, or significance, and you must give a recommendation rather than only a range.

Approach

1. The interviewer is probing whether you can carry uncertainty into a decision instead of either hiding it or hiding behind it, so convert the interval into money at the real budget first: $3M at 0.6 to 2.2 is $1.8M to $6.6M of incremental revenue, with $4.2M as the central estimate.
2. Get the break-even ROAS from the advertiser's gross margin and mark where it falls inside the range. That single number turns a statistical interval into two decision regions, and it is the only quantity the executive needs to reason about.
3. Explain the 4.1 without calling it wrong: it counts every conversion that followed an ad, including the ones that would have happened anyway, while the test counts only the ones that would not have. Both are computed correctly and they answer different questions.
4. State what would narrow the range and what it costs, in the same units: roughly four times the markets or four times the flight length to halve the width, against the cost of running longer at current spend.
5. Pre-commit to the action at each end of the range so the recommendation is falsifiable: what you do if the true value is near 0.6, and what you do if it is near 2.2.

Follow-up

• The vice president asks whether it is working, yes or no. What is your one-sentence answer?
• Break-even is 1.0 and the range is 0.6 to 2.2. Do you renew at full budget, and what would you change about the flight?

Disclosing an invalid-traffic reclassification that changes invoiced numbers

Hard · restatement · data-integrity · ethics · communication

A measurement vendor reclassifies sophisticated invalid traffic for a six-week period. On re-read, 4.1% of one advertiser's delivered impressions flip from valid to sivt, and their reported CPA rises about 6% across two already-invoiced months. You did not cause it, the contract's make-good language is ambiguous, and a colleague suggests waiting to see whether the advertiser notices. Deliverable: the note you send the advertiser, what you say to finance and the account team beforehand, and the position you take internally on waiting.

Approach

1. The interviewer is probing whether you will bound an uncomfortable number before negotiating about it, so recompute the six weeks with the restated ivt_status first, splitting givt from sivt, and produce the impact by campaign and by invoiced month: impressions flipped, billed amount on those exact rows, and the CPA delta.
2. Sequence disclosure so one number exists before anyone speaks externally: finance first, because they own the credit and the invoices; the account team second, because they own the relationship; the advertiser third, with the figure both internal parties have already approved.
3. Write the note in the restatement shape rather than as an apology: what was reclassified and by whom, which dates, the direction and size, the billing position, and what the advertiser needs to do, which is nothing.
4. Take the position on waiting explicitly and in commercial terms, not only ethical ones. The reclassification is visible in the vendor's own dashboard, so waiting converts a correction the advertiser receives from you into a discrepancy they discover themselves, which is the version that costs the renewal.
5. Close it structurally: treat invalid-traffic rates as restatable on read with a published reclassification lag, and subscribe to the vendor's reclassification feed so the next event is detected rather than discovered.

Follow-up

• The account team says this disclosure will cost us the renewal. Does that change what you send?
• The contract is silent on reclassification make-goods. How would you price one, and what precedent are you setting?
• What do you do if finance instructs you not to send the note?