Interview guides · DoubleVerify

DoubleVerify Data Scientist Interview Questions & Guide 2026

Data scientist prep for DoubleVerify: practice defining a advertising and attribution metric, picking its guardrails and defending the tradeoff.

The 60-second brief

As a Data Scientist at DoubleVerify, you play a pivotal role in enhancing the integrity of digital advertising. Your expertise in data analysis and machine learning contributes directly to the development of innovative solutions that ensure campaigns are effective and transparent. You will work on complex datasets to derive actionable insights, ultimately impacting product features that safeguard brand safety and measurement accuracy for clients.

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

Diagnose pacing: underdelivery, overdelivery, budget exhaustion
Separate invalid traffic from genuine performance shifts
Design geo holdouts that survive budget interference

Overview

This role is critical because it combines analytical rigor with strategic influence. You will collaborate closely with cross-functional teams, including engineering and product management, to implement data-driven strategies that enhance user experience and operational efficiency. The complexity of the data you handle and the scale at which you operate make this position both challenging and rewarding. You will engage with real-world problems, such as detecting fraudulent activities in advertisements or optimizing campaign performance, which are central to DoubleVerify's mission in the advertising technology landscape.

The DoubleVerify interview process

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

Initial Screening Call

A screening call is a matching exercise run by someone who will not evaluate your statistics. They are checking that the work described on your resume is work you personally did, and that its scope matches the level the role is written for. Logistics get settled in the same half hour so nobody spends an interviewer's afternoon on a mismatch. The answer that fails is the one narrated in the plural. If every sentence is 'we built' and 'the team decided', there is nothing specific to write down about you. Name the piece that was yours, the decision you made inside it, and what changed after.

What to demonstrate

Whether the ownership implied by your resume survives one round of follow-up about who actually did which part
Whether your described scope (data size, stakeholders, what shipped) matches the seniority the role is written at
Whether timeline, location and compensation expectations make the rest of the loop worth scheduling

How to prepare

Rewrite your top three resume bullets in the first person singular, each with the decision you made and what moved afterwards, then say them out loud once so the 'we' does not return under pressure
Attach one number to each project: the baseline, the change, and the window it was measured over. Where impact was never measured, say that plainly rather than inventing a figure
Settle your compensation range before the call and give it as a range with a reason behind it, such as current total comp or a competing timeline, instead of deflecting the question twice

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

Take-Home Assignment

A take-home is graded as an argument, not as a notebook. Somebody reads the submission without you in the room, so every choice has to survive on the page: why the question was framed this way, and what was deliberately left out. The gap between a strong and a weak submission is almost never model quality. It is whether the writeup names the specific question it answers and commits to a recommendation, including what evidence would overturn it. A high-accuracy model attached to no conclusion reads as effort that stopped before the decision.

What to demonstrate

Whether the question you answered is stated outright, and whether it is the question the prompt posed rather than an easier neighbour of it
Whether the recommendation is specific enough to act on, with the uncertainty attached to it instead of parked in a caveats section at the end
Whether analytical choices such as the metric definition, the population filter and the time window are justified in the prose, not merely visible in code

How to prepare

Take a dataset you have already worked with, write the one-paragraph conclusion first, then check whether the analysis you were planning actually supports it and cut whatever does not
Practise stating a metric in one sentence that fixes the population, the time window and the denominator, then confirm your query computes exactly that sentence and nothing adjacent to it
Hand a draft to someone outside the problem and ask them to tell you back what you recommended and why; anything they cannot recover is not on the page yet

Behavioral Interviews

This round decides whether you owned a decision or watched one happen nearby. Interviewers for data roles listen for the point where the analysis stopped being a report and started changing what someone did, so build each story around that hinge: what was going to happen by default, what you found, and what happened instead. The most common weakness is a story that ends at delivery. If you can name the decision your work changed and the number that moved because of it, most follow-ups become easy.

What to demonstrate

Whether the decision was yours to influence, or whether you are narrating a team outcome in the first person
The counterfactual: what would have been done without your analysis, and why that default was worse
How far your involvement ran past the handoff, and whether you checked that the change did what you predicted

How to prepare

Pick three projects and write one sentence for each naming the decision-maker, the choice in front of them, and what they chose after seeing your work. If you cannot name a person and a choice, the story is not ready for this round.
Reconstruct the baseline for your strongest project from the original query or dashboard rather than memory, so the before-number survives a follow-up asking where it came from.
Prepare an honest version of a project where your recommendation was overruled, including what you did with the analysis afterwards.

Onsite Interviews

A day of back-to-back interviews samples your floor, not your ceiling. Four hours in, the habits that carry a good answer are the first to go: restating the question before solving it, asking what the data would have to look like, checking a number before quoting it. What the day decides is whether the tired version of you is still someone to leave alone with an ambiguous problem. The round that sinks a candidate is usually not the hardest one. It is the one immediately after the round that went badly.

What to demonstrate

Whether the late rounds get the same clarifying questions as the first one, or whether you start answering immediately to save effort
Whether a weak answer stays in the room it happened in, instead of following you into the next conversation as apology or distraction
Whether the quality of your questions holds up, since fatigue removes curiosity about the problem before it removes knowledge of the method

How to prepare

Rehearse the length, not just the content: book four mock interviews of different types in one afternoon with short gaps, because the one you need to observe is the fourth
Put the two or three questions you ask at the start of any problem on a card in front of you, so that under fatigue it is a habit you run rather than a decision you make
Decide in advance what the gap between rooms is for: water, one line of notes on anything you promised to follow up, and an explicit close on the round that just ended so it does not travel
Prepare a different closing question for each interviewer, so the end of a long day does not produce the same one four times

Where candidates lose points

Reading a recent week's cost per action or return on ad spend before the conversion window has closed.

Spend for a period is final within hours, but conversions attributed to that period keep arriving for as long as the click window allows plus ingestion lag, and offline uploads can land weeks later. The denominator of cost per action is therefore systematically incomplete for recent dates, so recent CPA is biased high and recent ROAS biased low, and a dashboard comparing a fresh week to a matured week will show a regression that does not exist. The correct handling is to freeze a period only after click_window_days plus the observed ingestion lag, and to publish a maturation curve so readers can see how much of a given day's conversions have landed so far.

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.

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.

Defining the cohort on a post-treatment condition

Ask how rows entered the table. Filtering on something that treatment itself influences, such as users who finished onboarding or accounts still active at ninety days, breaks comparability between arms; define the population at an entry point that precedes exposure and keep everyone in it.

Questions & practice

What are precision and recall, and how do they relate to model evaluation?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Say how the offline result would be validated online before it is trusted.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

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

How would you implement a binary search algorithm?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Check what information would not exist at prediction time, and exclude it.
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 a device event stream with a 30-minute gap

Medium · sessionisation · event-streams · algorithms · pandas

events holds about 5 million unsorted rows: device_id (nullable, roughly 18% null), event_ts, event_type in {impression, click, landing_arrival, conversion}, line_item_id, value_usd (nullable). Define a session as a maximal run of events for one device_id in which consecutive events are at most 30 minutes apart. Assign a session_id to every sessionisable row and return a per-session summary with device_id, start_ts, end_ts, n_impressions, n_clicks, converted and total value_usd. Do not use a library sessionisation helper, and do not scan the frame more than once after sorting.

Approach

Separate null device_id rows first and report their share. Null identity is a supply-source property, not random missingness, so grouping them under one null key would fabricate a single session spanning millions of unrelated events; excluding them means the summary describes identified traffic only, and that has to be said out loud.
Sort once by (device_id, event_ts) with a deterministic tiebreak such as an event_type rank then a row id. Without a tiebreak, an impression and its click sharing a timestamp order differently between runs and the summary is not reproducible.
Compute the boundary mask as (device_id != device_id.shift()) | (event_ts - event_ts.shift() > 30min) and take its cumsum as session_id. This is a grouped run-length in two vectorised operations: O(n log n) for the sort and O(n) afterwards.
Create indicator columns (is_impression, is_click, is_conversion) before the groupby, then summarise with one named aggregation pass. Building each count from its own filtered groupby re-scans the frame per metric and violates the single-pass constraint.
Assert the structural invariant rather than eyeballing it: within a device, sessions ordered by start_ts must be disjoint and increasing, every within-session consecutive gap at most 30 minutes, every between-session gap strictly greater.

Worked solution (25 min)

Split on device_id.notna(), record the null share, and keep the null rows aside rather than dropping them silently.
Add an event_type rank column, sort by (device_id, event_ts, type_rank, row_id), and reset the index.
boundary = (device_id != device_id.shift()) | (event_ts.diff() > pd.Timedelta('30min')); session_id = boundary.cumsum().
Add is_impression/is_click/is_conversion indicators, then one groupby('session_id').agg giving device_id first, start_ts min, end_ts max, the three sums, converted as is_conversion.max() > 0, and value_usd sum.
Run the invariant assertions per device on the summary before returning it.

Expected result: A session_id covering every non-null-device row exactly once, and a summary frame with one row per session. The summed n_impressions, n_clicks and remaining event counts across sessions equal the sessionised row count, and the null-device rows are reported as an excluded count.

Verify it yourself

len(summary) == sessionised.session_id.nunique(), and the per-session event counts sum to the sessionised row count.
For each device, sessions sorted by start_ts are strictly non-overlapping; every within-session consecutive gap is at most 30 minutes and every between-session gap exceeds it.
Shuffling the input rows and rerunning produces identical session boundaries. If it does not, the sort tiebreak is underspecified.

Follow-up

A conversion arrives three hours after the click and lands in its own session. Is that the right answer, and how does a session boundary differ from an attribution window?
One device shows negative gaps from a clock four hours off. What does your boundary mask do with it, and what would you prefer it did?
Make the 30 minutes a parameter. How would you choose it from the data rather than from convention?

Given a dataset, write SQL queries to find specific insights (e.g., user engagement rates).

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
Say which table is the grain you start from, and join outward from it.
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?

Write a function to compute the k-th largest element in an array.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

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

Explain the time complexity of your solutions.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
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?

Attributed cost per action without double counting spend or credit

Hard · fan-out · attribution · bucketing

attribution_credit holds one row per (conversion_id, touchpoint_id, attribution_model, model_version), with touchpoint_ts, line_item_id, credit_fraction, credited_value_usd and click_window_days, the model's click lookback in whole days, which is constant within one (attribution_model, model_version) pair; credit_fraction sums to 1.0 across a conversion's touchpoints within one model and one version. ad_impression has line_item_id, served_ts, billed_price_micros_usd and is_billable. For a UTC date range, return per line_item_id and day: spend, attributed conversions, attributed CPA, attributed ROAS, and a maturity flag derived from the model's own click window, for one named attribution_model and model_version. Both sides must land in the same daily buckets. State which timestamp each side is bucketed on and why.

Approach

Pin attribution_model AND model_version before aggregating anything. The table stores several models, and several versions of each, for the same conversion. An unfiltered SUM(credit_fraction) therefore reports conversions multiplied by the number of (model, version) pairs present, which is usually a clean integer multiple and so survives a sanity glance.
Bucket credit on touchpoint_ts, never on conversion_ts. Spend is incurred when the impression served, so aligning the outcome to the touchpoint puts numerator and denominator on the same cohort. Bucketing on conversion_ts shifts every conversion forward by a variable lag and produces day-over-day CPA swings that are pure bucketing artefacts and cannot be corrected downstream.
Aggregate the two sides in separate CTEs at their own grain, then FULL OUTER JOIN on (line_item_id, day). Joining ad_impression to attribution_credit directly matches each impression against every credit row that references it and multiplies spend by the path count.
Use a full outer join deliberately: a day with spend and no credit is a genuine CPA of infinity worth surfacing, and a day with credit and no spend means the touchpoint predates the spend window, which is a signal about window choice rather than a row to discard.
Attributed conversions is SUM(credit_fraction), a fractional quantity, not COUNT(*). Counting rows credits one conversion once per touchpoint and inflates the total by the mean path length, which makes CPA look far better than it is.
Flag recent days as immature. Spend is final within hours while credit keeps arriving for the model's click_window_days plus ingestion lag, so the most recent click_window_days plus roughly two days have CPA biased high and ROAS biased low. Read that window off the filtered rows rather than hard-coding a number: click_window_days is constant within a pinned (attribution_model, model_version), so MAX(click_window_days) over the pinned filter is a read of that constant. Verify the precondition first, because if MIN and MAX differ the column is not constant, the flag is being computed against a window that does not exist, and it has to come from the model configuration instead.

Worked solution (40 min)

Run SELECT attribution_model, model_version, MIN(click_window_days), MAX(click_window_days), COUNT(*) FROM attribution_credit GROUP BY 1, 2 first, so you know what is actually in the table before filtering to one pair, and so you have checked that the window really is constant inside the pair you are about to pin.
Build credit AS (SELECT line_item_id, (touchpoint_ts AT TIME ZONE 'UTC')::date AS d, SUM(credit_fraction) AS conv, SUM(credited_value_usd) AS value, MAX(click_window_days) AS win FROM attribution_credit WHERE attribution_model = :model AND model_version = :version AND touchpoint_ts >= :range_start AND touchpoint_ts < :range_end GROUP BY 1, 2).
Build spend AS (SELECT line_item_id, (served_ts AT TIME ZONE 'UTC')::date AS d, SUM(billed_price_micros_usd) / 1e6 AS spend FROM ad_impression WHERE is_billable AND served_ts >= :range_start AND served_ts < :range_end GROUP BY 1, 2).
FULL OUTER JOIN on (line_item_id, d), coalescing both key columns in the select list so rows present on only one side keep their identity.
Compute cpa = spend / NULLIF(conv, 0), roas = value / NULLIF(spend, 0), and is_mature = d < current_date - (win + 2), where win is the click_window_days read off the pinned model and version. On a spend-only day win is null, so is_mature is null rather than TRUE; decide whether to fall back to the model's configured window or to leave the flag unknown, and say which.
Re-run the same range a week later and diff the CPA column to measure the actual maturation curve rather than assuming the lag.

Expected result: Per (line_item_id, day): spend_usd, attributed_conversions as a fractional SUM(credit_fraction), cpa, roas and is_mature. A day with $4,400 spend and 137.5 attributed conversions returns a CPA of $32.00; $19,800 of credited value gives a ROAS of 4.5.

Verify it yourself

SUM(credit_fraction) across all line items for any single conversion_id equals 1.0 within the chosen model and version. A value of 2.0 or 3.0 means the model filter is missing or partial.
MIN(click_window_days) equals MAX(click_window_days) inside the pinned (attribution_model, model_version). If they differ, the constancy precondition is false, MAX is silently picking the longest window, and every is_mature value in the output is unsupported.
Total spend across the output equals an ungrouped SUM over billable impressions for the same dates; a larger figure proves the credit join fanned the impressions out.
Attributed conversions over the whole range does not exceed the count of non-duplicate conversion_event rows for that advertiser, since credit cannot exceed one per conversion.
Recomputing the last seven days a week later moves CPA downward. If nothing moves, the credit table is not being backfilled and the pipeline is stale.

Follow-up

Last Tuesday's CPA improved 15% overnight with no change in delivery. What happened, and what would you have shipped to prevent the question?
The same conversion has credit rows under last_click and under data_driven. Can you sum them? What do you give a client who wants one number?
model_version changed on the 14th. How do you present a series that spans that boundary without implying a performance change?

Discuss how you would analyze the impact of a recent marketing campaign.

Medium · product sense and metrics

Approach

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

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 do you prioritize tasks when working on multiple projects?

Medium · product sense and metrics

Approach

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

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 approach designing an A/B test for a new advertising feature?

Medium · a/b testing and experimentation

Approach

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

Follow-up

What would you do if you could not randomise at all?
How would you handle interference between treated and control units?

How would you build a recommendation system for an e-commerce platform?

Medium

Approach

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

Follow-up

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

Set a stopping rule for a test that is read daily

Medium · peeking · sequential-testing · multiple-comparisons

A 21-day line-item test was sized for a fixed horizon. An analyst refreshes a dashboard every morning showing one primary metric, conversions per device, and six guardrails. On day 4 the primary shows p = 0.03 and the analyst wants to stop and ship. Give the type I error actually incurred by this monitoring pattern, propose a defensible stopping rule, and state how you would treat the six guardrails differently from the primary.

Approach

Quantify the inflation rather than gesturing at it. Repeated fixed-horizon tests at a nominal 0.05 give roughly 14% type I error at 5 equally spaced looks and about 25% at 20. The naive 1 - 0.95^20 = 64% is wrong, because successive looks are strongly correlated partial sums of the same data.
Offer the two valid families. A group-sequential plan with an alpha-spending function fixes the number and timing of looks in advance: O'Brien-Fleming spends almost nothing early (nominal p about 0.000005 at the first of five looks, 0.041 at the last), while Pocock spends a constant nominal 0.0158 at every look. Always-valid inference, such as a mixture sequential probability ratio test or a confidence sequence, permits unlimited looks at a cost of roughly 20-40% more sample at the planned horizon.
Raise the day-4 problem that is not about alpha at all: the conversion window has not closed. Conversions keep arriving for click_window_days plus ingestion lag, so a day-4 read uses an immature numerator and is not the metric the test was sized on.
Treat guardrails asymmetrically. One pre-registered primary metric needs no multiplicity correction. Guardrails are harm checks, and Bonferroni-correcting them lowers power to detect harm, which is conservative in the wrong direction; run each at 0.05 one-sided against a pre-set non-inferiority margin.
If the team instead wants to claim a win on whichever of the seven metrics moves, that is a real multiplicity problem: control the false discovery rate with Benjamini-Hochberg, or use Bonferroni at 0.05/7 = 0.0071, and fix the rule before the test starts.
Correct the estimate as well as the error rate. Conditional on stopping at the first boundary crossing, the observed effect is biased away from zero, so report a boundary-adjusted or median-unbiased estimate rather than the raw difference.

Worked solution (25 min)

Establish the repeated-testing error by table or simulation: at 20 equally spaced looks against a nominal two-sided 0.05 boundary, the probability of at least one crossing under the null is about 0.25.
Choose the design: pre-register four interim looks plus a final one under an O'Brien-Fleming spending function, giving z-boundaries of roughly 4.56, 3.23, 2.63, 2.28 and 2.04.
Set the first readable look only after click_window_days plus the observed ingestion lag has elapsed from the start of the flight, rather than after an arbitrary number of days.
Write the guardrail rule separately: six one-sided tests at 0.05 against pre-set margins, no multiplicity correction, with any crossing escalating to a human decision rather than triggering an automatic stop.
If the test does stop at an interim look, report the boundary-adjusted estimate alongside the raw difference and state the gap.

Expected result: About 25% type I error under the current pattern versus the intended 5%. A five-look O'Brien-Fleming plan holds the overall rate at 0.05 with a final-look nominal p of 0.041 and costs roughly 3% extra maximum sample; under that plan the day-4 look would have required p below 0.000005 to justify stopping.

Verify it yourself

Simulate 10,000 null A/A runs under the actual look schedule and confirm the crossing rate matches the claimed error rate.
The O'Brien-Fleming final-look boundary should sit near z = 2.04, not 1.96; if it is 1.96 the spending function is not being applied.
Plot the conversion maturation curve and confirm the share of a day's conversions that have landed by day 4 is well below complete.
Pocock at five looks should give a constant nominal 0.0158 at every look; a table showing 0.05 at every look is the uncorrected design.

Follow-up

Leadership wants a daily read regardless. Which of the two valid families do you pick, and what does it cost?
One guardrail crosses at p = 0.04 on day 12. Do you stop?
How does the conversion maturation curve change the earliest day the primary metric is even readable?

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

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

Instead of guessing where the week should go, day one measures it under a fixed rubric and allocates the remaining hours in proportion to the gaps. The method is deliberately rigid: the allocation is written down before any studying starts and is not renegotiated when a topic turns out to be unpleasant.

Day 1 — Diagnostic, scored before you study anything

Sit a 100-minute timed diagnostic in four blocks: 30 minutes of SQL across three prompts, 25 minutes of short-answer statistics, 25 minutes on one modelling or case prompt, and 20 minutes delivering one behavioural story aloud.
Score each block from 0 to 3 on a fixed rubric where 3 is correct and fluent, 2 is correct but slow or prompted, 1 is partially correct, and 0 is stuck, grading the output rather than how the attempt felt.
Allocate the hours for days two to five roughly in proportion to 3 minus the score in each block, write the allocation down, and commit to not revising it midweek.

Deliverable: A scored rubric and a fixed hour allocation for the rest of the week.

Day 2 — Largest gap: find the boundary rather than the subject

Break the weakest area into five named sub-skills (for query work: grain control, window frames, date arithmetic, set logic with NULLs, and reading a query plan) and rate each one, so the rest of the week targets a sub-skill instead of a subject.
Solve three problems chosen to sit just above where the rating drops off, and for each write the first move you failed to make.
Re-solve one of them from memory four hours later, on paper, with nothing open.

Deliverable: A five-item sub-skill map with the two blocking sub-skills circled.

Day 3 — Largest gap: drill the blocking sub-skill

Do eight short repetitions of the same shape rather than eight different problems, so what you practise is the pattern and not the puzzle.
Write the rule you now hold in one sentence, then test it against a case built to break it: a ranking function over a column with ties, or a two-sample test on observations that are obviously dependent.
Have someone else read your one-sentence rule and find the precondition you left out.

Deliverable: One rule statement with its preconditions attached and one counterexample that would have caught the incomplete version.

Day 4 — Second gap, plus maintenance on your strongest area

Run the same sub-skill map and boundary protocol on the second-largest gap, compressed into half the day.
Spend 25 timed minutes on your strongest area to stop it decaying, choosing the hardest problem you can still finish rather than an easy warm-up.
Compare how the two areas fail: whether you lose time on recall, on setup, or on arithmetic, because the fix differs for each.

Deliverable: A second sub-skill map plus a one-line diagnosis of how each area fails you.

Day 5 — The gap that is not a skill

Record yourself answering one technical and one behavioural prompt, then count two things in the playback: how many seconds before your first clarifying question, and how many sentences you started without knowing where they ended.
Rewrite your three most-used stock phrases into shorter versions, and practise saying "I do not know, here is how I would find out" without softening it into a guess.
Deliver one answer again with a hard 90-second limit to force structure before detail.

Deliverable: Two recordings with a counted improvement in time-to-first-question.

Day 6 — Retest under day-one conditions

Sit the same 100-minute diagnostic structure with new prompts of comparable difficulty and score it on the identical rubric.
Compare block by block, and for any block that did not move, change the method rather than adding hours: a block stuck at 1 usually means the practice was too varied, not too short.
Write which single block you would still lose the offer on.

Deliverable: A second scored rubric placed next to the first, with one named remaining risk.

Day 7 — Full loop under interview conditions

Run a 60-minute mock covering the two blocks that moved least, with an interviewer instructed to interrupt and change direction.
Write your recovery script for the moment you go blank: restate the question, state your assumption, name the first thing you would check.
Reduce the week to the rule statements you wrote, each with its preconditions attached, then say every one of them out loud without reading it and cut any you cannot state in a single sentence, since a rule you have to reconstruct mid-answer will not survive being interrupted.

Deliverable: A one-page card holding the recovery script and only the rules you could state from memory.

What the behavioural round is actually scoring

Interviewers here are not checking whether you can describe a project. They want the decision you made, why you made it under the information you had, and what changed afterwards that someone else could measure. A story that ends at 'I built a model' has no ending. Say what the model caused, or what you stopped doing because of it.

Give an example of how you influenced a team decision.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

What would you do differently if you ran that project again?
How did you know the outcome was caused by your change?

Disagreeing with a proposal to widen the view window

Medium · disagreement · attribution · windows · influence

A product manager proposes changing the default view window on attribution_credit from 1 day to 7 days, to bring reported performance in line with competitors. Their mock shows reported CPA falling 22%. You believe nothing real changes. You have read access to attribution_credit, conversion_event and ad_impression, and two days before the decision review. Deliverable: the analysis you run and the one-page argument you take into the review, including what you concede and the condition under which you would support the change.

Approach

The interviewer is probing whether you can disagree from inside the other person's numbers rather than from principle, so reproduce their 22% first under the same attribution_model and conversion cohort with view_window_days set to 7, and say out loud that it is correct. The disagreement is now about interpretation, which is a debate you can win.
Partition the newly credited volume, which is the whole argument. Group by conversion_id under both windows and split newly credited conversions into those that already carried credit on another touchpoint, which is reshuffling between channels, and those that carried no credited touchpoint at all, which is genuinely new claiming. Report the split as a percentage.
Test the substantive claim on the newly claimed share: look for holdout evidence that view-through exposure at 1 to 7 day latency produces measurable lift. If no such evidence exists, say so plainly and label that share unvalidated rather than incremental.
Price the cost the proposal does not mention: changing the default changes model_version, which restates history, breaks every published time series, and moves advertiser-facing numbers that some advertisers reconcile against their own order tables.
Offer a conditional yes with a mechanism: support a window set from the observed conversion-latency distribution and corroborated by holdout lift, shipped as a parallel model_version alongside the existing one rather than as a replacement.

Follow-up

The product manager says competitors already report this way and we look worse by comparison. Does that change your answer?
How would you choose a defensible view window from data rather than from convention?

Correcting a published CPA win that was window maturation

Medium · ownership · communication · conversion-lag

Three weeks ago you published a note saying a bid-strategy migration cut attributed CPA by 18%. A colleague points out that you compared the first seven days after migration against a fully matured pre-period, while conversions for the recent dates were still arriving. Recomputed at equal maturity the improvement is 3%, inside noise. The note has been quoted in a quarterly business review deck. Deliverable: what you do in the next 24 hours, what you write and to whom, and the structural change that prevents a repeat.

Approach

The interviewer is probing sequencing under embarrassment, so establish the corrected magnitude before writing anything: recompute both periods restricted to conversions received within the same number of days after conversion_ts, so a frozen window is compared to a frozen window.
Build the account's maturation curve from received_ts minus conversion_ts, reporting the share of a click-day's eventual conversions landed by day 1, 3, 7 and 14. It both explains the error and becomes the artefact that prevents the next one.
Enumerate the decisions taken since publication that cited the 18%: budget moved, line items paused, the deck slide, any advertiser-facing claim. Cost each one, because the correction's urgency is set by what was acted on, not by how wrong the number was.
Send one message, not a drip, with the corrected number in the first sentence, the affected dates, the direction of the error, and an explicit statement of which decisions it does and does not reverse. Notify whoever presents the deck first.
Ship the guard: a freeze rule in the query or dashboard so no period is reportable until click_window_days plus observed ingestion lag have elapsed, with immature dates badged rather than hidden.

Follow-up

The corrected number had gone the other way and the migration looked better than you published. Does anything about your response change?
How do you decide whether this warrants a written postmortem or a one-line correction?