Interview guides · dentsu

dentsu Data Scientist Interview Questions & Guide 2026

Data scientist interview prep for dentsu: original practice on experiment design, SQL and metric reasoning, with worked solutions.

The 60-second brief

As a Data Scientist at dentsu, you sit at the intersection of advanced analytics, consumer behavior, and media strategy. You are responsible for transforming complex, high-velocity data into actionable insights that drive marketing effectiveness for some of the world’s largest brands. Your work directly influences how media budgets are allocated, how audiences are segmented, and how creative content is optimized for maximum impact.

When randomisation is off the table, the skill being checked is naming an identification strategy together with the assumption it rests on: parallel trends for difference-in-differences, relevance and exclusion for an instrument, overlap and conditional ignorability for matching. Say the assumption out loud and say how you would try to break it.

Focus areas

Defend attribution windows and model choice
Reconstruct the funnel from bid request to conversion
Design geo holdouts that survive budget interference

Overview

The role is both technical and consultative. You will not only build predictive models and perform statistical analysis but also act as a bridge between data infrastructure and business stakeholders. Success at dentsu requires a mindset that balances rigorous scientific methodology with the fast-paced, creative demands of the advertising and media ecosystem. You will be expected to thrive in an environment where clarity is sometimes iterative, requiring you to proactively define your own problem statements.

The dentsu interview process

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

Application Review

Whoever runs this call is usually not a practitioner. They take notes, and a hiring manager skims those notes later, so the real question is whether your work survives being written down by someone outside the field. Test every project sentence against that: could a non-specialist repeat it correctly without knowing what a propensity score is? Carry a plain-language version of each project and one reason you want this particular role that you could not copy onto another application. Vagueness at this stage reads as inexperience, even when the underlying work was genuinely deep.

What to demonstrate

Whether a non-specialist can restate your projects accurately, since their paraphrase is what reaches the hiring manager
Whether your reason for wanting the role points at the work itself rather than the company's reputation
Whether your language signals the level being screened for: what you decided yourself versus what you were handed

How to prepare

Write a two-sentence, jargon-free version of each major project: the question nobody could answer, and the decision your work changed. Read it to someone outside data and have them repeat it back
Point your 'why this role' answer at something concrete in the job description or the product surface you would be working on, and keep it to two sentences
Have two questions ready about measurement: which metric the team is held to, and who acts on an analysis once it lands

Technical Assessment

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

What to demonstrate

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

How to prepare

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

Final Interviews

A loop is not scored one interview at a time. The people you meet compare notes afterwards, usually in a meeting you are not in, and the outcome turns on what each of them can say about you when asked. That rewards something other than survival: every room needs one specific thing worth repeating, and none of them can contradict another. The common way to lose is to tell the same project four times with different numbers in it, or to be uniformly fine in a way that leaves nobody with anything to argue for.

What to demonstrate

Whether your account of a project survives being told twice, with the same scale, the same metric definition and the same numbers each time
Whether each interviewer leaves with one concrete claim they could make on your behalf later, rather than an absence of complaints
Whether a question you already answered in an earlier room gets the same answer at the same depth, without visible impatience

How to prepare

Write a one-page fact sheet for your two or three main projects that fixes the numbers you will quote: rows of data, the metric as a single sentence, the effect you measured and how long the work took. Say them aloud from the sheet until they come out identical every time
For each kind of room you expect, decide the one sentence you want that interviewer repeating in a debrief, then check during the mock that you said it outright instead of implying it
Rehearse answering the same project question twice in one sitting, the second time as though you had not just answered it, because the thing that needs fixing is the flatness that creeps into a repeated story

Where candidates lose points

Treating a bid as if it does not affect the price paid, under a first-price auction.

In a second-price auction the winner pays the runner-up's bid, so bidding true value is close to optimal and bid and price are usefully separable. Under first-price clearing, which is now the common case in open exchanges, the winner pays exactly what it bid, so raising the bid raises both the win probability and the cost of every win that would have been won anyway. A bid-increase experiment can therefore show more impressions, more conversions and worse profit simultaneously, and reading only the volume metrics will call it a success. The related second-order problem is that your own bid increase raises the clearing prices your control cells face, which contaminates the comparison unless the cells are separated by market.

Randomising users into treatment and control while both arms draw from the same campaign budget.

The arms compete in the same auctions and against the same budget, so treatment winning more impressions directly starves control, and the measured gap includes that cannibalisation rather than only the ad effect. The test looks methodologically clean, the randomisation is genuinely valid, and the lift is still partly manufactured. This is an interference violation, not a randomisation failure, so checking balance on covariates will not catch it. The fixes are to randomise at a unit that contains the budget, such as a geographic market, or to give each arm its own budget and its own pacing, and then be explicit that you are now comparing two separately-budgeted campaigns.

Reading experiment results before checking the arm split

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

Over-explaining the method and under-explaining the implication

Lead with the answer and what you would do about it, then give the approach when asked. Roughly one sentence of method per three of implication is the right ratio for a stakeholder-facing answer; the interviewer already knows what a regression is.

Questions & practice

How do you validate that your model performance is not just overfitting to historical data?

Medium · machine learning and modelling

Approach

Set a baseline first, so any model has something honest to beat.
Check what information would not exist at prediction time, and exclude it.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

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

Point-in-time join of impressions to versioned line items

Medium · scd2 · point-in-time-join · numpy · pandas

impressions has 20 million rows: impression_id, served_ts, line_item_id, billed_price_micros_usd, is_billable. line_item is SCD type 2 with 400,000 versions: line_item_id, bid_strategy, bid_value_usd, daily_budget_usd, pacing_mode, status, valid_from_ts, valid_to_ts (null on the current row), is_current. A version covers the half-open interval from valid_from_ts to coalesce(valid_to_ts, infinity). Attach to every impression the attributes of the version in force at its served_ts. You may not use merge_asof and you may not materialise the impression-by-version cross product. Return the joined frame and a count of impressions no version covers.

Approach

Validate the dimension before joining: per line_item_id, sort by valid_from_ts and assert the intervals are non-overlapping, and assert at most one is_current row per key. Overlapping versions make the as-of answer ambiguous and are the usual root cause behind duplicated-revenue reports, so finding them is part of the job.
Encode the composite as-of key into one sortable integer so a single np.searchsorted does the work: factorise line_item_id to codes shared across both frames, dense-rank all timestamps from both frames into a small integer, then form code * (n_ranks + 1) + rank. That keeps the composite inside int64 and makes lexicographic order on (key, time) equal to numeric order.
Sort the dimension by that composite, run np.searchsorted(dim_keys, imp_keys, side='right') - 1, and you have a candidate version index for every impression in one vectorised call rather than 400,000 per-group searches.
Reject candidates that do not actually cover the impression: the candidate must belong to the same line_item_id, and served_ts must be less than coalesce(valid_to_ts, far future). Report the uncovered count instead of dropping those rows; impressions before a key's first version normally indicate a backfill gap in the dimension, not bad events.
Materialise attributes with a positional .take on the matched index rather than a merge. One row in, one row out by construction, so fan-out is impossible regardless of what the dimension contains.

Follow-up

Two versions overlap by one second because the writer used an inclusive end timestamp. What does your join return, and how would you make that fail loudly instead of quietly picking one?
The pacing report needs the budget in force at every hour boundary, not just at impression time. What changes in the construction?
Estimate the memory and time cost of the naive merge-on-key-then-filter at these row counts, and say where it breaks first.

Check predicted conversion-rate calibration with a reliability table

Medium · calibration · model-evaluation · statistics · numpy

scored has 4 million rows at bid_request_log grain for one campaign: predicted_cvr, win_flag, and converted, which is observed only where win_flag is true and a conversion landed within 7 days. The win rate is about 2% and the conversion rate among won impressions about 0.4%. Without using a library calibration helper, build a 20-bin equal-frequency reliability table with bin count, mean predicted_cvr, observed rate and an interval around it, then compute count-weighted expected calibration error. Say whether the model is miscalibrated, in which direction, and over which population your answer holds.

Approach

Restrict to win_flag == True and state the precondition plainly: converted is only defined where the ad served, so the table measures calibration on the won slice. That is the population the advertiser pays for, but it is selected by the bid and the auction, so the result cannot be extended to no-bid or lost opportunities where the prediction is never tested.
Bin with equal frequency (pd.qcut with duplicates='drop'), not equal width. Prediction distributions in this setting are heavily right-skewed with point masses, so equal-width bins put most of the mass in one bin and leave the tail bins with too few conversions to say anything. Report how many bins survived if edges collapsed.
Per bin compute n, mean predicted_cvr, observed rate, and a Wilson score interval. At a 0.4% base rate the normal approximation produces negative lower bounds and understates uncertainty in the low bins, so the interval choice is not cosmetic here.
Compute ECE as the count-weighted mean of |observed - predicted| across bins, and report it next to the base rate: 0.002 of absolute error is small against a 5% base rate and enormous against 0.4%.
Read the direction from the sign pattern rather than the scalar. Observed below predicted across the top bins is head over-prediction, typically from training on a differently-selected population or from scoring against a conversion window that has not matured; a flat observed rate across rising predictions means the score has little ranking power in this range, which is a different problem from miscalibration.

Worked solution (25 min)

Subset to won rows, record n_won and the observed base rate, and confirm converted has no nulls within that subset.
bins = pd.qcut(predicted_cvr, 20, labels=False, duplicates='drop'); report the realised bin count.
groupby(bins).agg(n, pred_mean, obs_rate), then compute Wilson bounds elementwise from n and obs_rate at z = 1.96.
ece = ((n / n.sum()) * (obs_rate - pred_mean).abs()).sum(); print it alongside the base rate and the ratio of the two.

Expected result: A table of at most 20 rows with bin, n, pred_mean, obs_rate, wilson_lo, wilson_hi, each bin holding close to n_won/20 rows by construction, plus a scalar ECE reported against the base rate.

Verify it yourself

sum(n) equals the number of won rows, and the count-weighted mean of pred_mean equals the overall mean predicted_cvr to floating point.
The count-weighted mean of obs_rate equals the overall observed conversion rate among won rows; a mismatch means rows were lost in binning or the weights are wrong.
Every Wilson bound lies inside [0, 1], including the lowest bin where a normal approximation would return a negative lower bound.
Shuffling the row order leaves the table unchanged, confirming the binning depends only on predicted_cvr and not on input order through tie handling.

Follow-up

The pooled curve looks calibrated but ctv supply is badly off. How do you test for segment-level miscalibration without fishing across dozens of cuts?
The model retrains daily while conversions take up to 7 days to land. What does that do to the head of the reliability curve, and in which direction?
How would you turn this diagnostic into a bid correction, and what would you verify before shipping it?

How do you optimize a complex SQL query for large-scale data processing?

Medium · sql and data manipulation

Approach

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.
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 does the query change if the join becomes one-to-many?

Sessionise device bid requests with a thirty-minute inactivity gap

Medium · window functions · gaps-and-islands · null handling

bid_request_log holds one row per (auction_id, line_item_id) with auction_id, request_ts TIMESTAMPTZ, device_id TEXT (nullable), device_type and exchange_id. For a single UTC day, assign each distinct auction opportunity a session number per device, starting a new session whenever more than 30 minutes have elapsed since that device's previous request. Return device_id, session_number, session_start_ts, session_end_ts, request count and distinct exchange count. A large share of rows carry no device_id; state how you treat them and why that choice is not arbitrary.

Approach

Collapse to auction grain first with SELECT DISTINCT auction_id, device_id, request_ts. The table repeats each opportunity once per line item considered, so sessionising it raw counts a single request as many as there are eligible line items, and the multiplier varies by targeting breadth.
Exclude device_id IS NULL rather than partitioning on it. The null share is a property of the supply source, not noise, so one null bucket becomes a synthetic device with millions of requests and no gaps. Report the excluded share as its own number in the same output.
Get the previous request per device with LAG(request_ts) OVER (PARTITION BY device_id ORDER BY request_ts), then flag a boundary when the lag is null or the gap exceeds interval '30 minutes'.
Turn the boundary flags into session numbers with a running SUM over the identical window. This is the gaps-and-islands pattern: the flag must be an integer 0 or 1, and the SUM must repeat the same PARTITION BY and ORDER BY, or the islands will not line up with the boundaries.
Specify the frame explicitly as ROWS UNBOUNDED PRECEDING. The default frame for an ordered aggregate is RANGE UNBOUNDED PRECEDING, which gives every row sharing a timestamp the same running total, so simultaneous requests from one device collapse into one session number.
Group by (device_id, session_number) and take MIN and MAX of request_ts for the bounds, COUNT(*) for volume and COUNT(DISTINCT exchange_id) for breadth.

Worked solution (25 min)

Build ev AS (SELECT DISTINCT auction_id, device_id, request_ts FROM bid_request_log WHERE request_ts in the UTC day) and compare its row count with the raw count to see the per-auction multiplier.
Split off device_id IS NULL, count it, and record its share of ev; run everything below on the non-null remainder.
Add prev_ts := LAG(request_ts) OVER (PARTITION BY device_id ORDER BY request_ts) and new_session := CASE WHEN prev_ts IS NULL OR request_ts - prev_ts > interval '30 minutes' THEN 1 ELSE 0 END.
Add session_number := SUM(new_session) OVER (PARTITION BY device_id ORDER BY request_ts ROWS UNBOUNDED PRECEDING).
Group by (device_id, session_number) with MIN(request_ts), MAX(request_ts), COUNT(*) and COUNT(DISTINCT exchange_id).
Pick the device with the highest session count, list its raw timestamps, and read the gaps by hand against the output.

Expected result: One row per (device_id, session_number). A device with requests at 09:00, 09:05, 09:50 and 09:52 yields session 1 spanning 09:00 to 09:05 with 2 requests and session 2 spanning 09:50 to 09:52 with 2 requests, because the 45-minute gap exceeds the threshold.

Verify it yourself

The sum of per-session request counts equals the count of distinct auctions with a non-null device_id for that day.
Within each device, session_number starts at 1 and increases by exactly 1, and session_start_ts of session n+1 is strictly greater than session_end_ts of session n.
Re-running LAG inside each session shows no internal gap above 30 minutes.
Raising the threshold to 60 minutes reduces the session count and never increases it.

Follow-up

A device shows 400 requests in 12 minutes across 30 exchanges. Is that a session or an invalid-traffic signature, and what would you look at next?
The null device_id rate differs sharply between mobile_app and mobile_web supply. What does that asymmetry do to any per-device metric computed on this table?
Where does 30 minutes come from? What in the inter-request gap distribution would justify a different threshold?

If a client’s conversion rate drops unexpectedly, how would you investigate the root cause using our internal data?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Name one primary metric, then the guardrail that stops it being gamed.
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?
What would you do if the primary metric and the guardrail moved in opposite directions?

These questions evaluate how you apply your technical skills to the specific challenges of the media and advertising industry.

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
Restate the decision this analysis has to support, and who acts on the answer.
Name one primary metric, then the guardrail that stops it being gamed.

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 do you translate complex technical findings into non-technical recommendations for a client?

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.
Name one primary metric, then the guardrail that stops it being gamed.

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?

Explain how you would segment a customer base to improve the targeting of a digital ad campaign.

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Fix the population and the time window before naming any metric.
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?

These questions test your ability to handle real-world data manipulation and your mastery of the tools required to extract insights from large datasets.

Medium

Approach

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

Follow-up

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

Define creative fatigue and set a retirement threshold

Medium · creative · engagement · thresholds

Creatives rotate within a line item; creative_id appears on ad_impression and ad_click, and ad_impression.device_id is nullable with a null share that varies systematically by supply source. The team wants an automatic rule that retires a fatigued creative. Deliverable: define the fatigue metric with its exact denominator, state which population it is computed on, choose a retirement threshold, and say what historical data you would use to set that threshold rather than picking a round number.

Approach

Separate the quantity from its usual proxy. A creative's click-through rate declines over calendar time for at least three reasons that are not fatigue: the supply mix it wins into drifts, a bid or pacing change moves it to cheaper placements, and audience composition changes. Fatigue is specifically the decay of response as the same person sees the same creative repeatedly.
Measure at that grain. For impressions carrying a non-null device_id (or user_id_hash on logged-in supply), assign an exposure sequence number per (creative_id, identity), then compute the valid-click rate — numerator ad_click with ivt_status = 'valid', denominator billable valid impressions, both bucketed on the impression's served_ts — at exposure n against exposure 1. The fatigue index is the ratio of the two.
State the population and its bias in the definition, not in a footnote: the index is computed only on identified supply, and identity coverage differs systematically between app, web and connected-TV inventory, so the index under-observes fatigue wherever coverage is thin. Publish the identified-impression share next to it so a coverage shift is not read as a fatigue shift.
Set the threshold from outcome data rather than from the index alone. Take eight to twelve weeks of creatives, plot value per $1,000 spend against the fatigue index, and choose the index value at which the creative stops beating the line item's next-best available creative. The threshold is a comparison against an alternative, not an absolute decay number.
Guardrail the automation itself: rotation churn (creatives retired per line item per week) and budget delivery rate. An aggressive rule can retire the only creative matching a required ad_format and push the line item straight into underdelivery, which is a worse outcome than a tired banner.

Worked solution (30 min)

Write the exposure-sequence query: partition by creative_id and identity, order by served_ts, row_number as exposure index.
Compute valid-click rate by exposure bucket (1, 2-3, 4-6, 7+) with invalid-traffic and billability filters applied to both numerator and denominator.
Compute the identified-impression share by device_type to document where the index is blind.
Join historical retirements to value per $1,000 spend and locate the index value where the creative stops beating its next-best alternative.
Write the rule, the minimum-volume gate, and the two automation guardrails.

Expected result: Fatigue index = valid-click rate at exposure 4+ divided by the rate at exposure 1, computed on identified supply only, with the identified share published alongside; a threshold justified by the crossover against next-best creative value per $1,000; a minimum-volume gate and guardrails on rotation churn and pacing attainment.

Verify it yourself

Invalid-traffic and billability filters are applied to numerator and denominator alike, so the rate does not shift by the invalid share.
Both sides are bucketed on the impression's served_ts, not on click_ts, so the cohorts describe the same impressions.
Holding the supply mix fixed (same placement_id and ad_format) reproduces the same index, which is the test that it is not measuring mix drift.
The identified-impression share is stable across the comparison window; if it moved, the index is not comparable.

Follow-up

Click rate at exposure five is half of exposure one, but post-click conversion rate per click is flat. Fatigued or not?
How do you treat a creative whose line item had frequency_cap_per_day changed mid-flight?
What is the rule for a creative with fewer than 5,000 identified impressions?

Bid raised fifteen percent, pooled win rate went down

Medium · win rate · simpsons paradox · auction mechanics

A line item's bid_value_usd was raised 15% at 09:00 UTC. Over the following week its pooled auction win rate — bid_request_log rows with win_flag=TRUE over rows with response_status='bid' — fell from 12.4% to 11.1%, while impressions won and spend both rose. The trading team wants to roll the change back. You have bid_request_log with exchange_id, publisher_id, ad_format, auction_type, floor_price_cpm_usd, no_bid_reason, bid_cpm_usd, clearing_price_cpm_usd. Explain the direction of the pooled rate and recommend keep or roll back.

Approach

Notice the internal contradiction before touching data: more wins and more spend with a lower win rate means the bid denominator grew faster than wins. The question is therefore which bids are new, not whether the bid worked.
Stratify on the variable the bid change directly acts on — floor_price_cpm_usd bucket, crossed with auction_type — and compute per-stratum win rate and bid share before and after. A higher bid converts no_bid_reason='below_floor' rows into bids in exactly the high-floor, low-win-rate strata.
Run a Kitagawa decomposition of the pooled change into a within-stratum component (Σ share_post × Δrate) and a mix component (Σ Δshare × rate_pre), and report both numbers. Pooled direction is not evidence about either one on its own.
Confirm the mechanism in the no-bid ledger: the fall in below_floor no-bids should approximately equal the growth in bid rows in the high-floor strata. If it does not, something else expanded eligibility, such as a pacing_throttle release.
Judge the change on economics, not on the rate. Under first_price clearing the bid increase also raises clearing_price_cpm_usd on inframarginal wins, so compare incremental spend against incremental conversions rather than counting impressions.

Follow-up

Under first-price clearing, how much of the extra spend went to impressions the line item would have won at the old bid?
What would you expect the same decomposition to look like under second_price, and why is the inframarginal cost argument weaker there?
The pooled rate is a dashboard tile. What would you replace it with so this cannot recur?

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.

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.

These questions focus on your adaptability, communication style, and ability to work in a collaborative, global agency environment.

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

How did you know the outcome was caused by your change?
What did you decide not to do, and why?

Describe a time you had to choose between two different statistical models; what was your decision criteria?

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
Quantify the outcome, including what you would not claim credit for.
State the situation in two sentences and spend the rest on your reasoning.

Follow-up

How did you know the outcome was caused by your change?
What did you decide not to do, and why?

Claiming impact for three projects with honest counterfactuals

Easy · self-assessment · impact · measurement

You are writing a performance self-review. Over two quarters you shipped a bid-shading recalibration, a rewrite of the click-through-rate query that fixed an invalid-traffic filter applied to only one side of the ratio, and an incrementality test that led to a line item being paused. Deliverable: for each, the number you would claim, the counterfactual you claim it against, and the portion of credit that belongs to someone else. One of the three has no defensible dollar figure. Say which, and what you claim instead.

Approach

The interviewer is probing whether you apply the same causal standard to your own work that you apply to an advertiser's, so write the counterfactual for each claim before the number: what would the metric have been over the same period without this work?
For the shading recalibration, compare clearing price and profit on won impressions at matched supply mix across exchange_id, ad_format, device_type and geo_country, and report the profit change rather than the win-rate change; under first-price clearing a higher win rate can accompany worse profit.
For the query fix, claim the magnitude of the bias removed rather than a revenue number: the gap between the old rate, which filtered invalid traffic on the numerator only, and the corrected symmetric rate, plus the decision that was being made on the wrong figure.
For the incrementality test, claim the decision, not the savings. The paused budget's fate was never measured, so any dollar figure would assume that money vanished rather than moved. This is the item with no defensible number.
Name specific collaborators and what they contributed, and pre-empt the obvious challenge by stating the confound you know about, such as seasonality or supply mix drift, and the bound you put on it.

Follow-up

Your manager points out the paused line item's spend simply moved to another line item. Does your claimed impact survive?
How do you claim credit for work whose value is a bad decision that did not happen?