Interview guides · Arcadis

Arcadis Data Scientist Interview Questions & Guide 2026

Arcadis Data Scientist interview guide: reported rounds, practice questions with worked solutions and a 7-day prep plan.

The 60-second brief

A Data Scientist at Arcadis plays a pivotal role in leveraging data to drive insights, innovation, and solutions across various projects. This position is critical to enhancing operational efficiency, improving decision-making processes, and ultimately delivering value to clients. By analyzing complex datasets, Data Scientists help shape sustainable solutions that align with Arcadis' mission of improving quality of life.

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 rebuffering by device, network and POP
Correct discovery slates for position bias
Split voluntary churn from involuntary payment failure

Overview

The impact of this role transcends numerical analysis; it directly influences the development of products and services that address significant challenges in urban development, infrastructure, and environmental sustainability. You'll collaborate with multidisciplinary teams, utilizing advanced analytical techniques to tackle real-world problems, such as optimizing resource allocation and predicting project outcomes. Given the scale and complexity of projects at Arcadis, your contributions as a Data Scientist will be both challenging and rewarding, providing you with opportunities to make a tangible difference.

The Arcadis interview process

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

HR Screening

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What to demonstrate

Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
Whether you name what you have not done instead of stretching to cover every line of the posting
Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Technical Interviews

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

Leadership Interview

Rounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.

What to demonstrate

Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options

How to prepare

Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.

Technical Assessments

Before anything else, this round is a reading test. You are given a small schema and a question phrased in business language, and most of the difficulty sits in the gap between them. Who counts as an active user, does a refunded order still count as an order, is that date column an event time or a load time. Weak answers start typing immediately and compute something precise about the wrong population. Strong ones pin the definition in one sentence, name the column that encodes it, then write the query. On a timed assessment with nobody to tell, write the definition in a comment anyway.

What to demonstrate

Whether an ambiguous term becomes a specific column and filter before any computation happens
Whether you read the schema for keys and cardinality rather than only for column names
Whether the result answers the question at the grain it was asked at, per user or per session or per day

How to prepare

Take three metrics you already use and write down the exact filter and exact grain behind each, then practise stating one of them in a single sentence out loud
On a schema you have never seen, spend the first minute writing what one row of each table means and which key it is unique on, then predict which joins can duplicate rows
Rehearse a version where the definition changes halfway through, and edit the query you have instead of starting over

Behavioral Questions

Behavioural answers from data candidates get audited in a way that answers from other roles do not. When you say a model lifted retention, the next question is the denominator, the window, and how you knew the lift was not seasonal. So attach the measurement to each claim while you tell it: what the metric was before, over what period, and against what comparison. Numbers with no baseline read as rounded-up memory, and one unsupported figure tends to make the rest of the story sound rehearsed.

What to demonstrate

Whether each impact number arrives with a baseline, a window and a comparison, or as a bare percentage
Whether you can name the method that attributed the effect to your work (an experiment, a staged rollout, a seasonal control) or concede the link was correlational
Whether the magnitudes stay internally consistent when the interviewer multiplies them against the scale you described earlier

How to prepare

For each story, write the impact line as metric, value before, value after, window, and how attribution was established. Any line missing two of those five is a follow-up you will answer badly.
Re-derive one headline number from the source table rather than the deck that reported it. Resume numbers drift upward across retellings.
Decide in advance which figures you cannot share, and prepare the ratio or relative change you can give instead, so a confidentiality limit does not read as evasion.

Case Studies

A case round is decided by whether you leave the interviewer with a recommendation, not by how much analysis you narrate on the way there. The prompt is open on purpose, so the first job is to convert it into a decision someone could act on: ask what would be done differently depending on the answer. From there name the quantity that would settle it, state the assumptions you need, and commit. Candidates who cover more ground than anyone expected and still end on "it depends" score below candidates who scoped narrowly and said what they would do.

What to demonstrate

Whether the version of the question you choose to answer is genuinely narrower than the prompt and still worth answering
Whether the recommendation arrives as an action with a number attached, rather than as a summary of what you looked at
Whether assumptions are stated at the moment you rely on them, instead of collected into a disclaimer at the end
Whether you notice when a branch you are exploring would not change the decision either way

How to prepare

Take six open prompts and write only the scoping move for each: the one-sentence question you would actually answer and the decision it feeds. Give yourself three minutes per prompt and stop there.
Put a five-minute warning into every practice case and force a closing statement that names the action, the result that would justify it, and the result that would reverse it.
Record one case and count how long you talked before naming a measurable quantity. Past roughly five minutes, what you are calling scoping is narration.

Where candidates lose points

Testing hours, revenue or completion with a difference in means on a heavy-tailed distribution.

Listening and viewing hours per account are strongly right-skewed and content popularity is close to power-law, so the variance of a sample mean is dominated by a few accounts and the central limit approximation converges slowly at realistic sample sizes. A t-test on mean hours can flip sign when one heavy account's week changes, and an experiment can appear significant because a single title released into one arm's window. Capping at a pre-registered percentile, or decomposing into a rate (did they stream at all) and a conditional intensity, controls the variance, at the stated cost that capping biases toward zero exactly when the true effect lives in the tail.

Comparing consumption week over week across the release calendar and the rights calendar.

A major release, a season drop or a live event produces a spike that dwarfs almost any treatment effect, and the effect is not confined to the new title because it pulls attention from everything else in the same window. Separately, licensed content leaves the catalogue when its window expires, so consumption falls with no product change and the drop is attributed to whatever shipped that week. Both need to be handled by an explicit control: a comparison period chosen for calendar equivalence, a covariate for scheduled releases, or a pre-registered rule for excluding a window, decided before the numbers are seen.

Naming a model class before naming the deployment constraints

Set out the latency budget, the label delay, the retraining cadence, the interpretability requirement and the number of labelled examples, then pick the model that fits them. A boosted-tree answer to a problem where each decision must be explained to the affected user is a well-executed answer to the wrong question.

Optimising accuracy on a heavily imbalanced target

State the base rate first, then choose the metric from the relative cost of a false positive against a false negative: precision and recall at the operating threshold, PR-AUC, or expected cost. At a 1 percent positive rate, predicting the majority class for everyone scores 99 percent accuracy and is worthless.

Questions & practice

How would you optimize a logistic regression model?

Medium · machine learning and modelling

Approach

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

Follow-up

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

Explain the concept of overfitting in machine learning and how to prevent it.

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Pick an evaluation metric that matches the cost of each error type, not a default.
Check what information would not exist at prediction time, and exclude it.

Follow-up

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

Qualified hours per active account-week from its written definition

Easy · pandas · metric-definition · aggregation

You are given fct_stream as a pandas frame (stream_id, account_id, started_at in UTC, played_seconds, is_qualified) and dim_account (account_id, is_test_account), plus an as-of timestamp. Implement this metric exactly: the numerator is sum(played_seconds)/3600 over qualified streams started inside the window; the denominator is the count of distinct (account_id, ISO week) pairs with at least one qualified stream in that week; the window is the four most recent ISO weeks that are complete as of the given timestamp. Exclude test accounts. Return the scalar and the four per-week rows.

Approach

Drop test accounts first, then filter to is_qualified. The denominator is defined on qualified streams, so filtering after grouping quietly lets an account-week in on the strength of an unqualified stream.
Derive the ISO key with started_at.dt.isocalendar() and keep BOTH iso_year and iso_week. The week number alone collides every January and silently merges two different years of a week into one cell.
Fix the window from the as-of timestamp rather than from max(started_at): a week is complete only when its Monday 00:00 UTC plus seven days is at or before the as-of instant, which is what excludes the partially observed current week.
Aggregate once: sum played_seconds for the numerator, and take the number of unique (account_id, iso_year, iso_week) tuples for the denominator, not the number of unique accounts.
Emit the four per-week rows as well, and confirm the scalar equals the account-week-weighted mean of the four weekly ratios.

Worked solution (20 min)

Join is_test_account onto the stream frame, drop the test rows, and keep only is_qualified == True.
Build iso_year and iso_week from started_at with .dt.isocalendar(), and a week_start timestamp (Monday 00:00 UTC) for the completeness test.
Select the four distinct (iso_year, iso_week) keys with the largest week_start whose week_start + 7 days is at or before the as-of timestamp.
Numerator: played_seconds.sum()/3600 over those weeks. Denominator: drop_duplicates on (account_id, iso_year, iso_week) and take the row count.
Group by the ISO key for the four per-week rows (hours, account_weeks, ratio), and divide the totals for the scalar.

Expected result: One float equal to total qualified hours divided by the count of distinct (account_id, iso_year, iso_week) pairs, plus a four-row frame whose hours column sums to the numerator and whose account_weeks column sums to the denominator.

Verify it yourself

The scalar equals the account_weeks-weighted mean of the four weekly ratios, and does not equal their unweighted mean unless every week has identical account_weeks.
The denominator is at most four times the number of distinct qualified accounts in the window, with equality only if every such account streamed in all four weeks.
A stream started on 2025-12-30 lands in iso_year 2026, iso_week 1. If it lands in 2025 week 1, the year column was dropped somewhere.
Leaving test accounts in raises the numerator and the denominator together, so the ratio barely moves. Verify the exclusion on the two counts, not on the ratio.

Follow-up

A profile-level version of this metric reads lower per unit than the account-level one. Which would you use for a personalisation readout, and what does each unit actually aggregate?
Offline playback is uploaded in bulk days after it happened. What does that do to the most recent complete week, and how would you decide when a week is safe to publish?
How would this metric move if the qualification threshold rose from 30 to 60 seconds, separately for a short-form-heavy and a long-form-heavy catalogue?

What data structures would you use to store and manipulate large datasets?

Medium · sql and data manipulation

Approach

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.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

Follow-up

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

Sessionise playback into listening sessions with an idle gap

Medium · window functions · gaps and islands · sessionisation

fct_stream has profile_id, stream_id, content_version_id, started_at, ended_at (nullable when the client stopped reporting) and played_seconds. Collapse consecutive streams by the same profile into listening sessions: a new session begins when a stream starts more than 30 minutes after the latest end reached by any of that profile's earlier streams. One profile can play on two devices at once, so streams overlap and 'the previous stream' is not well defined — the idle gap is measured against the running maximum end, not against the row immediately before. Treat a NULL ended_at as started_at plus played_seconds. Return one row per session with profile_id, session_start, session_end, stream_count, distinct content_version_id count and total played_seconds. Use window functions; a self-join on fct_stream is not an acceptable answer.

Approach

In a first CTE compute effective_end = COALESCE(ended_at, started_at + played_seconds * INTERVAL '1 second'). This both fills the abandoned-client case and bounds it, since played_seconds is advancing playback only and cannot run past the true end.
Take prev_max_end = MAX(effective_end) OVER (PARTITION BY profile_id ORDER BY started_at, stream_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), and flag is_new_session = (prev_max_end IS NULL OR started_at > prev_max_end + INTERVAL '30 minutes'). LAG(effective_end) is the reflex here and it is wrong under concurrent devices: with A 10:00-12:00, B 10:05-10:10 and C starting 10:45, LAG hands C the end of B at 10:10, so C opens a new session 35 minutes into a session still running until 12:00. The NULL branch is not defensive noise either: the first row of every profile has no preceding frame and would otherwise fall outside any session.
Number the sessions with SUM(is_new_session::int) OVER (PARTITION BY profile_id ORDER BY started_at, stream_id ROWS UNBOUNDED PRECEDING). A running count of boundaries is the gaps-and-islands device. Specify ROWS rather than letting the default RANGE frame apply, because RANGE lumps peer rows that share a started_at into one frame position — the same reason the ORDER BY carries stream_id as a tie-break.
Group by (profile_id, session_number) and aggregate: MIN(started_at), MAX(effective_end), COUNT(*), COUNT(DISTINCT content_version_id), SUM(played_seconds).
The running maximum makes the sessions disjoint in wall-clock time, but it does not make SUM(played_seconds) an elapsed-time figure: two devices playing at once contribute overlapping seconds, so the total can exceed session_end minus session_start. Report it as consumption and say which of the two the requester asked for.

Worked solution (30 min)

Materialise the effective_end CTE and check for rows where effective_end < started_at, which means negative played_seconds or a clock-skewed ended_at and must be quarantined before anything else.
Add the running MAX and the boundary flag; verify that exactly one row per profile has prev_max_end IS NULL and that it is flagged as a session start.
Add the running SUM to number sessions; confirm the numbers are dense and begin at 1 within each profile.
Aggregate and reconcile: SUM(stream_count) across all sessions must equal the input row count.
Run the overlap fixture — one profile with A 10:00-12:00, B 10:05-10:10 and C at 10:45 — and confirm it collapses to one session. With LAG(effective_end) in place of the running MAX it splits into two, and the second session's start, 10:45, sits inside the first session's span.

Expected result: One row per (profile_id, session_number). Session numbers are dense from 1 within each profile, SUM(stream_count) equals the input row count exactly, and a profile's sessions are disjoint in wall-clock time: each session_start exceeds the previous session's session_end by more than 30 minutes.

Verify it yourself

Input row count equals SUM(stream_count); a shortfall means rows fell out of the window partition or the NULL branch of the boundary flag.
For any profile, each session's session_start exceeds the previous session's session_end by more than 30 minutes. This holds only because the boundary is measured against the running maximum end — under LAG(effective_end) the overlap fixture above produces a session whose session_start, 10:45, precedes the prior session_end of 12:00.
Changing the gap from 30 to 60 minutes reduces the session count or leaves it unchanged, and never raises it.

Follow-up

Two devices on one profile play simultaneously. Is that one session or two, and what changes in the query for each answer?
Re-run at a 10-minute and a 60-minute idle gap. How far does session count move, and which number do you publish and defend?
Can SUM(played_seconds) for a session ever exceed session_end minus session_start, and what does it mean when it does?

How would you estimate the cost of a new infrastructure project using data analysis?

Medium · product sense and metrics

Approach

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

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?

Given a dataset of traffic patterns, how would you approach identifying areas for improvement?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
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

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

Discuss a complex problem you solved using data. What was your approach?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
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?

Present a hypothetical scenario where you need to optimize resource allocation for a construction project. What steps would you take?

Medium · product sense and metrics

Approach

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.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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

What programming languages are you proficient in, and how have you applied them?

Medium

Approach

Say what you would check first and why it is the highest-information step.
Work from the decision backwards to the evidence you would need.
State your assumptions explicitly before working the problem.

Follow-up

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

Raise the qualification threshold when it is also a payout rule

Hard · metric design · definition change · payouts · history restatement

A proposal raises the qualification threshold behind fct_stream.is_qualified from 30 to 60 played_seconds for content_type = 'music_track'. The same flag feeds the engagement metrics and the pro-rata payout pool split across rights_holder_id. Using fct_stream (content_version_id, played_seconds, is_qualified, started_at) and dim_content_version (content_version_id, content_id, content_type, duration_seconds, rights_holder_id), design the decision. Give the primary metric, guardrails, what you would compute before anyone votes, and how published history is handled. Name who is made worse off.

Approach

Open by separating the two jobs the flag is doing: an engagement definition is a measurement choice that can be changed as long as history is restated, while a payout definition is a contractual rule whose change is a transfer of money between counterparties. Proposing one change to a shared flag is proposing both, and the first design decision is whether to split the flag into two named definitions.
Compute the redistribution before the argument starts: on one closed month, recompute every rights_holder_id's pro-rata share of the pool under both thresholds and report the distribution of the change. The loss concentrates on short-duration catalogue and on skip-heavy start sources, so the transfer is systematic by counterparty rather than noise.
Choose a primary metric that the threshold does not control: qualified hours per active account-week sums played_seconds, so a threshold move changes only which rows enter the sum and not the unit of the answer. Publish alongside it the share of total played_seconds that the threshold excludes, which is the single number that says how consequential the choice is.
Set the guardrail on the distortion the threshold creates: median and tenth-percentile duration_seconds of qualified music_track streams, and the count of distinct content_id receiving any qualified stream. A 60-second gate demotes genuinely short forms as well as engineered ones, and catalogue breadth is where that shows first.
Fix the history rule before any number is published: recompute at least thirteen months under the new definition and publish only the restated series, because a series that changes definition mid-line will be read as a product event by everyone who was not in this meeting, and the next quarter's review will attribute it to whatever shipped that week.
State the trade-off and the loser by name: the higher gate reduces the payout incentive for engineered short items, and it also cuts short tracks, interludes and kids content whose real completion is under 60 seconds. The per-content_type threshold is the alternative; its cost is that per-stream comparisons across content types stop being meaningful, which the metric tree must then forbid.

Worked solution (40 min)

Recompute is_qualified at both thresholds over one closed month and report two totals: qualified stream count and the share of total played_seconds that each threshold excludes.
Aggregate qualified streams to rights_holder_id under both thresholds, convert each to a pro-rata share of a fixed pool, and report the distribution of share change, calling out the largest losers and their median duration_seconds.
Show the mechanism numerically: for a catalogue of 180-second tracks a 60-second gate requires completion_ratio above 0.33 rather than 0.167, so the exclusion rate is a direct function of the duration distribution and is not comparable across content types.
Write the primary metric and the excluded-seconds share, then the breadth guardrail as distinct content_id with any qualified stream, before and after.
Write the history rule, the restatement horizon and the publication ban on mixed-definition series, and the one-paragraph statement of who loses and by roughly how much.

Expected result: The answer separates the engagement definition from the payout rule and says a shared flag conflates them. Primary: qualified hours per active account-week, threshold-invariant in unit, published with the share of played_seconds excluded. Guardrails: duration distribution of qualified music_track streams and count of distinct content_id with any qualified stream. History restated at least thirteen months with no mixed-definition series published. The losers are short-form catalogue owners, named before the vote with the redistribution quantified.

Verify it yourself

The redistribution figure exists before any recommendation is made, so the decision is priced rather than argued.
The primary metric's unit does not change when the threshold changes, which is what makes it publishable across the transition.
The exclusion share is reported per content_type, since a single pooled exclusion rate hides that the gate bites unevenly by duration.
The restated history is the only series published, and the restatement horizon is stated as a number of months rather than left to judgement.

Follow-up

Would a per-account allocation of the pool rather than pro-rata change your recommendation, and which rights holders swap places between the two schemes?
Who has to be told before this ships, and what is the smallest honest description of the change you would give them?
A stakeholder proposes running the two thresholds in parallel for a quarter. What does that solve and what does it not?

Weekly listening hours fell; check the calendar first

Easy · seasonality · data-completeness · metric-hygiene

A dashboard reports qualified hours per active account-week — sum(played_seconds)/3600 over fct_stream rows with is_qualified, divided by distinct (account_id, ISO week) pairs having a qualified stream — down 9% against the prior week. It is being read on a Wednesday and the current week is still open. Two public holidays fell inside the prior week. Using fct_stream (started_at, event_date, played_seconds, is_qualified, account_id, play_territory), establish whether anything real changed. Deliverable: a corrected comparison plus a written rule for how this chart is read from now on.

Approach

Stop comparing an open week to a closed one. Either truncate the prior week at the same weekday the current week has reached, or exclude the open week entirely. A Wednesday read covers three of seven days and the four missing days include both weekend days.
Establish the day-of-week profile before judging any week: qualified hours by ISO weekday across the last eight weeks. A truncated week is not a scaled-down full week, it is a systematically weekday-heavy one, and the ratio distorts in a specific direction — the numerator loses the two highest-hour days while the denominator only loses accounts who stream exclusively at weekends.
Test partition completeness separately from seasonality. Recompute a day that closed two weeks ago and compare it against the value stored at the time. Offline playback uploads in bulk when the device reconnects, so a recent event_date keeps accruing for days; and if the pipeline stamps event_date at ingest rather than from started_at, those hours land on the wrong day entirely, which is a different defect with a different fix.
Separate the holiday from a trend. Cut the prior week by play_territory and split territories that observe the holidays from those that do not, then recompute the week-over-week change on the non-observing set. A holiday that lifts consumption in two large markets raises the prior week's baseline, so the current week reads as a fall with no behaviour changing anywhere.
Restate the comparison as complete-week against complete-week with the territory cut shown, and write the reading rule down: the current week is suppressed until it closes plus a stated late-arrival lag, and holiday weeks are annotated on the chart rather than explained in a thread each time.

Follow-up

The holiday raised hours but also raised the active-account denominator. Which way does that push the ratio, and is the sign consistent with what you observed?
What evidence sets the late-arrival lag, rather than picking a round number of days?

Suggested practice checklist

Roughly 90 minutes a night on weekdays with one longer weekend block. The plan deliberately cuts scope rather than compressing everything, on the assumption that finishing one thing a night beats half-starting four.

Day 1 — Fix the scope and set a baseline

Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list for everything else and keep it visible all week.
Take one 20-minute SQL prompt and one 10-minute metric question cold, and write the single sentence that says what blocked each attempt, since that sentence is what decides which two topics get the most evenings.
Set the week's one rule: one problem finished to completion every night, including the night you only have 40 minutes.

Deliverable: A one-page scope with an explicit not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One query pattern, written three times

Choose the single pattern most likely to appear (a cohort retention grid, or a funnel counted by user) and write it three times from a blank file rather than editing the previous attempt.
On the third attempt, write the grain of every CTE as a comment before writing its body.
Stop at 90 minutes even if the third version is imperfect, and write the one thing you would fix with another hour.

Deliverable: Three independent versions of the same query plus a note on what changed between them.

Day 3 — Only the statistics you will be asked to defend

Write, in under 200 words, how you would decide whether a difference between two groups is real: the test, its assumptions, and what you would switch to when an assumption fails.
Compute a 95 percent confidence interval for a difference in proportions by hand on realistic numbers, then write in one sentence what changes if the two samples are paired rather than independent.
Write your answer to "what does a p-value mean", check it against a definition, and delete the version that describes it as the probability the hypothesis is true.

Deliverable: A 200-word written answer and one hand-computed interval you can reproduce under pressure.

Day 4 — One case, and the assumptions holding it up

Answer one product case aloud in 20 minutes with a recording running, then listen back with a pen and mark every claim you asserted without saying what it rested on: an assumed user behaviour, an assumed data source, an assumed baseline rate, an assumed grain.
Pick the three assumptions the recommendation actually depends on, write how you would check each one against data, and say which one being wrong would flip the recommendation rather than merely weaken it.
Write the four-step structure you used onto a card small enough to hold in working memory when you are nervous.

Deliverable: One recording, three load-bearing assumptions each with a written check, and a four-step structure card.

Day 5 — Your own work, timed

Write a 90-second version and a four-minute version of your main project, and time both out loud rather than reading them.
Prepare answers to the two follow-ups that always come: what you would do differently, and how you knew it worked.
Put one number in the first sentence and be able to say exactly where that number came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in a longer weekend block

Run a 60-minute mock covering query work, a case and a behavioural question in a single sitting with no breaks, because sustained attention is the thing evenings have not trained.
Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

Write the 20-minute warm-up you will actually do on the morning of the interview: one query you can already write from a blank file, one metric you can define out loud, and nothing you have never seen before.
Re-read only your own notes from this week, and open no new material.
Write down the logistics: the tool you will be asked to work in, whether lookups are allowed, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the case structure, the project numbers, and the logistics.

Where your data came from

Data people depend on systems owned by other teams, and much of the job is negotiating for instrumentation, access, or a fix to a broken pipeline. Prepare an example of getting something changed upstream that you did not control. Describe what you asked for, what you traded, and how you worked while you waited.

Describe a situation where you had to influence a team decision.

Medium · behavioural and stakeholder questions

Approach

Name the disagreement or constraint, and how you resolved it with evidence.
State the situation in two sentences and spend the rest on your reasoning.
Close with what you would do differently, concretely.

Follow-up

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

Announce a stream-definition change that shifts payouts

Hard · cross-functional communication · metric definition · payouts

You find that the 60-second idle gap used to sessionise heartbeats into fct_stream rows splits one continuous listen into two streams whenever a phone backgrounds briefly on cellular. Correcting the gap lowers qualified stream counts on phones by an estimated four percent; total played_seconds is unchanged. Per-stream counts drive rights-holder payout shares. Deliverable: what you verify before telling anyone, the order in which you take it to the engineering owner, finance and content partnerships, and your recommendation on restating history.

Approach

The probe is whether you can tell a technical correction from a commercial decision and keep them apart in the room. Verify the split streams are genuinely one listen before anything else: same profile_id, same content_version_id, contiguous max_position_seconds across the boundary, and a gap distribution with a spike at the background-timeout duration rather than a smooth tail.
Compute the distributional effect, not the average. The four percent aggregate is not what anyone will argue about; recompute under the corrected gap and report which rights_holder_id groups gain and lose share, because short-form catalogue on mobile is where the splits concentrate and that is not spread evenly across counterparties.
Defend the new rule on its own terms rather than on the direction of the number. The idle gap is a choice, so the argument is evidence that the two rows describe one continuous listen — never that the corrected count is lower and therefore more conservative, which invites the symmetric accusation next time the fix goes the other way.
Name the ownership boundary out loud: engineering owns the sessionisation rule, finance and partnerships own whether payouts are restated. Conflating them is how a correct fix gets blocked by a commercial objection it should never have been exposed to.
Sequence the conversations so the number stops moving before it leaves the building: engineering owner first to confirm the rule and land the fix, finance second to size the restatement, partnerships last. Recommend restating history for internal metrics so trends stay comparable, and recommend against retroactive payout adjustment unless the contracts require it — naming who must answer that contractual question rather than answering it yourself.

Follow-up

Partnerships asks you to hold the fix until after the quarter closes. What do you do, and who else needs to know you were asked?
How do you present a change that raises some counterparties' shares and lowers others', in the same meeting, to people who will compare notes afterwards?
The four percent estimate itself has an interval spanning roughly two to seven percent. Does that change the recommendation or only the sequencing?

Walk through an analysis you shipped that was wrong

Medium · error ownership · data quality · postmortem

Describe a case where you delivered a result that was later shown to be wrong, and it had already been acted on. Cover how the error surfaced, whether you or someone else found it, what the wrong number caused, and what you changed afterwards. Prepare an example whose root cause was a definition, a denominator or a join — not a transcription slip. The interviewer will push on the mechanism, not the apology. Deliverable: a four-minute account that ends with a specific control now running in a pipeline.

Approach

The probe is whether your account has a mechanism in it. Choose an error that generalises — a denominator that silently changed population, a join that fanned rows, a metric partitioned on event_date while offline playback arrived days late and landed in the wrong partition — rather than one that only teaches you to check your typing.
State the blast radius factually and early: which decision was taken, how long the number stood, what it cost. A candidate who softens this is answering a different and easier question, and the interviewer can hear the substitution.
Explain how it surfaced without adjusting who found it. The generalisable detail is why your own checks did not catch it, which is a statement about your checks rather than about your luck.
Name the control you added and where it now lives: a row-count assertion after the fan-out join, a reconciliation that recomputes a closed day after late-arriving offline playback and alerts above a threshold, a denominator assertion inside the query. A fix that lives in a pipeline is different in kind from a resolution to be more careful.
Close with whether the control has fired since, or how you tested that it would. That single sentence is what separates a fix from an intention, and interviewers ask for it when candidates do not offer it.

Follow-up

Why didn't your own review catch it? Be specific about what you did check.
What class of error would that control still not catch, and what would you add next?
Have you found an error in someone else's published analysis since? How did you raise it?