Interview guides · HCLTech

HCLTech Data Scientist Interview Questions & Guide 2026

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

The 60-second brief

This guide covers what a Data Scientist at HCLTech is expected to do and how to prepare for the interview.

Product-sense cases reward reasoning from a mechanism to a testable prediction. Reciting every metric you can name reads as pattern matching; naming the single quantity that would move if your explanation were true reads as thinking.

Focus areas

Separate contracted seats from actively used seats
Power experiments for heavy-tailed account revenue
Strip CI, retry and synthetic traffic first

The HCLTech interview process

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

Recruiter Screen

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 Screening

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

Core Interview Stages

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What to demonstrate

Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

Scenario-Based Rounds

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.

Final Discussions

Where a loop ends with a senior leader, that conversation is rarely another skills test. The technical signal already exists by then, so the questions tend to open up: what you would look at first, where a metric you have heard about could mislead, what you would push back on. The decision being made is scope, which in practice means level and how much you would be trusted to own unsupervised. Treating it as a formality is the usual mistake. An open question late in the day is still being scored, and a vague answer reads as someone who has not run anything themselves.

What to demonstrate

Whether your view of the business has anything specific behind it, given that you are working only from what is public and are expected to say so
Whether the scope of work you describe owning matches the scope of the role, instead of sitting a level below it
Whether you can disagree with something concrete and stay useful about it, rather than agreeing with everything said in the room
Whether your questions are ones only this person could answer, as opposed to ones the recruiter already covered

How to prepare

Build one view you could defend for two minutes using only public information: what the funnel probably looks like, which metric likely drives decisions, and where that metric could mislead. Being wrong for a stated reason survives this round; having no view does not
Write down the largest piece of work you have owned from question to decision, who else touched it, and what you decided alone, then check that it reads at the level you are interviewing for
Prepare one thing you would want changed if you joined and phrase it as a question rather than a verdict, so it opens a conversation instead of closing one

Where candidates lose points

Reporting a mean over accounts when account revenue is heavy-tailed

When a small number of accounts hold most of the revenue, the sample mean is dominated by whichever of them happens to be in the sample, and the sample variance keeps growing as more data arrives instead of stabilising. In that regime the usual central-limit-based confidence interval understates uncertainty, and a single renewal or a single large account's batch job can flip the sign of a measured effect. The fixes are to pre-register a winsorisation or capping rule before looking at the outcome, to report account counts crossing a threshold alongside the revenue figure, or to define the estimand on a bounded transform. Choosing the cap after seeing the result is a separate and worse problem, because the cap then encodes the answer.

Comparing accounts that received a sales or customer-success touch against those that did not

Assignment of coverage is deliberate and pulls in both directions at once: the largest accounts get a named owner because they are valuable, and the accounts showing distress get one because they are at risk. The comparison therefore mixes a strong positive selection with a strong negative one, and the naive estimate can come out with either sign depending on which assignment rule dominated during the period examined. Nothing about matching on observed size fixes this, because the risk signal that triggered coverage is usually the same signal that predicts the outcome. It needs either an actual randomised or staggered rollout of coverage, or a design built on a capacity constraint or territory boundary that assigns coverage for reasons unrelated to account health.

Reaching for a model before the target metric exists

Before naming an algorithm, write down the label, the prediction time, and the action that changes when the score crosses a threshold. If you cannot say what decision the output drives, any modelling choice is guesswork dressed up as method.

Reading an observational correlation as a causal effect

Name the confounder you are most worried about and the design that would remove it: an experiment, a difference-in-differences with a checked pre-period trend, an instrument, or a regression discontinuity. When none is available, state which direction the bias likely runs and bound the claim accordingly.

Questions & practice

What is the role of p-values and confidence intervals in evaluating model performance stability?

Medium · statistics and probability

Approach

Sanity-check the answer against a simple bound or a simulated case.
Write down the assumption the method needs before you use the method.
Say what the estimate is of, and over what population it generalises.

Follow-up

Which assumption here is most likely to be violated in practice?
How would you explain this result to someone who does not know statistics?

Share an example of a project where your initial model failed and how you pivoted your approach under tight deadlines.

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Set a baseline first, so any model has something honest to beat.
Say how the offline result would be validated online before it is trusted.

Follow-up

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

Implement seven-day activation rate from its written definition

Easy · metrics · pandas · activation

Given dim_account (account_id, created_at, is_internal, is_current) and fct_api_request (account_id, request_at, http_status, api_key_id, traffic_class), implement activation_rate(accounts, requests, week_start). Definition: the numerator is accounts whose first request with http_status < 400, api_key_id not null and traffic_class != 'synthetic_monitor' occurs no later than 168 hours after created_at; the denominator is non-internal accounts created during the ISO week starting week_start. All timestamps are tz-aware UTC. Return the rate and both counts, and refuse to report a week until every account in it has had its full 168 hours.

Approach

Reduce dim_account to one row per account_id before joining anything. It is a type 2 dimension, so several versions of the same account exist; joining the versioned table to requests multiplies the denominator by the number of plan changes an account happened to make.
Build the denominator first and freeze it: is_internal == False and week_start <= created_at < week_start + 7 days. Everything after this is a filter on the numerator only, because an account that never sent a request must still sit in the bottom of the fraction.
Filter requests to qualifying rows and only then take groupby('account_id').request_at.min(). The first qualifying request is not the same object as the global first request filtered afterwards, and the two answers differ for every account whose first call was a 4xx.
Left-join the first qualifying timestamp onto the cohort and test (first_ok - created_at) <= Timedelta(hours=168). NaT propagates to False in that comparison, which is the behaviour you want, but assert it rather than assume it.
Guard reportability explicitly: if week_start + 7 days + 168 hours exceeds the maximum request_at in the data, the week is censored and will read as a drop, so return None or raise rather than emit a number.

Worked solution (20 min)

cohort = accounts.loc[~accounts.is_internal & accounts.created_at.between(week_start, week_start + pd.Timedelta(days=7), inclusive='left'), ['account_id','created_at']].drop_duplicates('account_id')
ok = requests[(requests.http_status < 400) & requests.api_key_id.notna() & (requests.traffic_class != 'synthetic_monitor')]
first_ok = ok.groupby('account_id', as_index=False).request_at.min(); m = cohort.merge(first_ok, on='account_id', how='left'); assert len(m) == len(cohort)
activated = (m.request_at - m.created_at) <= pd.Timedelta(hours=168); return {'rate': float(activated.mean()), 'numerator': int(activated.sum()), 'denominator': len(m)}

Expected result: A dict whose denominator is the count of distinct non-internal accounts created in that ISO week, numerator <= denominator, and rate = numerator / denominator; accounts with no qualifying request sit in the denominator with NaT and evaluate to False.

Verify it yourself

len(m) == cohort.account_id.nunique(); the merge must not add rows.
Dropping the synthetic_monitor filter can only raise the numerator, never lower it.
Widening the window from 168h to 336h is monotone: the numerator must not decrease.
An account whose only qualifying request falls at exactly created_at + 168h counts as activated, since the comparison is inclusive.

Follow-up

Median time-to-first-call is more informative. What breaks if you take the median over activated accounts only, and what estimator fixes it?
How would you decide whether 168 hours is the right window rather than 72 or 336?
An account signs up, does nothing for 20 days, then integrates heavily. Where does it land in this metric, and is that what you want?

Optimize a slow-running SQL query that joins multiple large tables containing millions of rows.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Say which table is the grain you start from, and join outward from it.
Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

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

How do you use Common Table Expressions (CTEs) and self-joins to analyze user retention cohorts?

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Compute rates by summing numerator and denominator separately, never by averaging rates.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

Follow-up

How does the query change if the join becomes one-to-many?
How would you verify this result without re-running the same query?

Net revenue retention on a cohort frozen twelve months back

Hard · retention · as-of join · cohort arithmetic

fct_subscription_period carries subscription_period_id, account_id, arr_cents, plan_code, term_start_date, term_end_date, booked_at, amendment_type, superseded_by_id (the subscription_period_id of the version that replaced this one, null on the live version of a lineage) and is_current. Compute net revenue retention for month M: the summed arr_cents at M for the set of accounts holding arr_cents > 0 at M-12, divided by that same set's arr_cents at M-12. An account can hold more than one live subscription, a churned account contributes zero rather than dropping out, and nothing signed after M-12 may enter either side. Return the ratio plus the expansion, contraction and churn components in cents.

Approach

Write one reusable as-of ARR snapshot parameterised by a date: rows whose term brackets the date AND whose booked_at is at or before the date, then only the version of each lineage that is still live at that date, then sum arr_cents per account. The booked_at guard matters because an amendment signed in advance otherwise co-exists with the term it replaces and double counts the account.
Collapse the lineage on superseded_by_id, not on any attribute of the contract. Keep a row when superseded_by_id IS NULL, or when the successor it points at was booked after the date. Ranking with ROW_NUMBER() OVER (PARTITION BY account_id, plan_code ...) instead is wrong in both directions: an amendment that moves the account from one plan_code to another puts the old and new versions in different partitions, so both are rank 1, both bracket the date, and the account's arr_cents is counted twice; and two genuinely concurrent subscriptions that happen to share a plan_code land in one partition, so one of them is deleted.
Resolve the successor with a LEFT JOIN back to fct_subscription_period on subscription_period_id, and treat a missing successor as not superseded. An inner join would silently delete an account's ARR on a dangling pointer, which is a data-quality bug in the source, not a retention movement.
Never use is_current for the M-12 side. is_current describes today; using it at the historical snapshot backdates the present contract onto last year's cohort and makes retention look like 100 percent by construction.
Freeze the cohort from the M-12 snapshot where arr_cents > 0, then LEFT JOIN the M snapshot onto it and COALESCE the missing side to zero. An inner join deletes exactly the churned accounts, which is the single largest way this number gets overstated.
Return a ratio of sums, not a mean of per-account ratios. The two are different estimands: contraction is floored at zero while expansion is unbounded, so the mean of ratios is both biased relative to the aggregate and far noisier on a skewed revenue base.
Decompose per account on the delta: positive delta is expansion, negative delta with a non-zero M value is contraction, a zero M value is churn. The three components must reconcile to numerator minus denominator.
Prove no leakage: any account whose first contract began after M-12 must be absent from both sides, and the cohort row count must be identical in the numerator and denominator.

Worked solution (45 min)

Write arr_asof(d) as a CTE or lateral: from fct_subscription_period s take rows with term_start_date <= d AND term_end_date >= d AND booked_at <= d, LEFT JOIN fct_subscription_period succ ON succ.subscription_period_id = s.superseded_by_id, keep the row when s.superseded_by_id IS NULL OR succ.subscription_period_id IS NULL OR succ.booked_at > d, then sum arr_cents per account_id across every surviving version.
Sanity-check the lineage rule on one amended account before going further: at a date after the amendment, the account must contribute exactly one version per lineage even when the amendment changed plan_code, term dates or both.
Materialise base = arr_asof(M-12) filtered to arr_cents > 0, and curr = arr_asof(M).
LEFT JOIN curr onto base on account_id and COALESCE(curr.arr_cents, 0) AS arr_now.
Compute nrr = sum(arr_now)::numeric / NULLIF(sum(base.arr_cents), 0), and the three components with SUM(...) FILTER on the sign of arr_now - base.arr_cents and on arr_now = 0.
Reconcile: assert sum(arr_now) - sum(base.arr_cents) = expansion - contraction - churn, and assert the cohort account count is identical on both sides.

Expected result: A single row with nrr as a ratio, plus expansion_cents, contraction_cents and churn_cents as positive magnitudes satisfying numerator minus denominator equals expansion minus contraction minus churn, over a cohort whose membership is fixed at M-12.

Verify it yourself

At each snapshot date, the surviving row count per account equals the number of distinct live subscription lineages on that account, not the number of contract versions bracketing the date.
For an account whose amendment changed plan_code, the snapshot returns one version and its snapshot ARR equals the amended arr_cents; swapping in a ROW_NUMBER partition on (account_id, plan_code) returns two versions for that account and inflates the side it sits on, which is the fastest way to see the bug.
The three components reconcile to the numerator-denominator delta to the cent.
The distinct account count is the same in the numerator and the denominator.
No account with a first term_start_date after M-12 appears anywhere in the output.
Re-running after new accounts sign leaves the ratio unchanged, which is the defining property of a frozen cohort.

Follow-up

Net revenue retention can rise while the business shrinks. Show one mechanism and name the guardrail that catches it.
How do you handle an account that co-terms two subscriptions into one mid-window, so the subscription count changes but the money does not?
Finance computes this from invoiced amounts and gets a different number. Which is right for which question?

What framework would you use to define success for a newly launched AI-driven feature in a legacy enterprise product?

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you design a product metric to measure the long-term engagement of an enterprise client dashboard?

Medium · product sense and metrics

Approach

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

How do you prioritize multiple competing project deliverables when working across different client accounts?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.
Decompose the metric into the rates that drive it, and say which one you would check first.

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?

If a key business metric suddenly drops by fifteen percent over a weekend, how would you structure a diagnostic investigation?

Medium · product sense and metrics

Approach

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

Follow-up

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

How do you determine the required sample size and statistical power before launching an experiment?

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
Name the guardrails that would stop a launch even on a positive primary result.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

How do you calculate and interpret statistical significance when dealing with highly skewed user engagement distributions?

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Name the randomisation unit first; it decides the variance and what the test can detect.
Say whether units interfere with each other, and switch design if they do.

Follow-up

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

Set the operating point for a capacity-bound renewal-risk worklist

Medium · precision at k · operating point · leakage · selection bias

A customer-success team of six can contact at most 40 accounts a week. You have an account-level renewal-risk score and the tables behind it: fct_subscription_period (account_id, term_end_date, arr_cents, amendment_type, booked_at, is_current), fct_support_ticket (account_id, severity, reopened_count, breached_sla, linked_incident_id, opened_at) and fct_api_request. Define the metric that grades this worklist, set the operating point, and state what happens to accounts below the line. The team will not use a score they cannot explain, so say how that constrains what you build.

Approach

Grade the list the way it is used. The team works 40 accounts a week, so the metric is precision at k = 40, not AUC. A global AUC over thousands of accounts rewards correct ordering among accounts nobody will ever call, which is ordering that cannot change an outcome.
Restrict the candidate pool before ranking anything: accounts whose term_end_date falls within the next 90 days. An account eleven months from renewal cannot churn in the measurement window, so it can occupy a slot in the list while being structurally incapable of producing the outcome.
Report two numbers because they disagree. Precision at 40 counts accounts; ARR captured at 40 counts money. A list that catches many small churns and misses one large one scores well on the first and badly on the second, so state which the team is optimising before the ranking is built.
Audit leakage by timestamp, feature by feature. Every feature must carry a timestamp proving it existed before the scoring date: a downgrade amendment's booked_at, a ticket's opened_at, a churn reason code written at close. Drop anything you cannot date, and be specific that reason codes and cancellation amendments are written after the renewal conversation started.
Attach a policy to the threshold, because a threshold without one is not a decision. Accounts ranked 41 and below get no contact this week; that is an accepted cost, and the weekly readout must include the churn that landed below the line so the operating point can be revisited rather than assumed. Favour a small inspectable model, because a stronger model the team ignores has an effect of zero.

Worked solution (30 min)

Build the weekly candidate set: accounts from the current fct_subscription_period version whose term_end_date falls within 90 days of the scoring date.
Assemble features only from rows timestamped before the scoring date: trailing 28-day 5xx rate and successful production request volume from fct_api_request, sev1 and sev2 ticket counts plus reopened_count from fct_support_ticket filtered on opened_at, and seat utilisation from the contract version current at that date.
Label the outcome as the absence of a successor fct_subscription_period row with amendment_type in ('renewal','upsell','downgrade') within 45 days of term_end_date.
Backtest week by week: take the top 40, and report precision at 40, ARR captured at 40, and the churned ARR that landed below rank 40.

Expected result: A weekly backtest table carrying precision at 40, ARR captured at 40, and churned ARR missed below the line, over a candidate pool restricted to accounts renewing within 90 days. The answer ties the operating point k = 40 to team capacity, states the leakage rule that every feature must carry a timestamp earlier than the scoring date, and accepts explicitly that accounts below rank 40 receive no contact.

Verify it yourself

No feature may derive from a row timestamped at or after the scoring date; re-run the backtest dropping each feature in turn to confirm none carries post-hoc information.
A 90-day renewal window on an annual-contract base should hold roughly a quarter of paying accounts; a pool the size of the whole base means the term_end_date restriction is not being applied.
Churn must be dated at term_end_date, not at the date the record was updated, or losses shift into whichever week the operations team did its paperwork.

Follow-up

Coverage is assigned by this list, so contacted and uncontacted accounts differ systematically in exactly the risk signal that drives the outcome. How would you ever measure whether the outreach worked?
Annual contracts produce few churn events per quarter. At what point is the honest answer that there is not enough signal to model this, and what would you deliver instead?

Blended gross margin fell while every segment improved

Medium · mix shift · simpsons paradox · as-of join

Blended gross margin across paying accounts fell from 71 percent to 66 percent over two quarters, yet margin improved inside every plan_tier and every deployment_model. You have fct_usage_daily (account_id, sku_code, usage_date, net_amount_cents, cogs_cents), fct_subscription_period (account_id, plan_tier, term_start_date, term_end_date) and dim_account (account_id, deployment_model, is_internal, effective_from, effective_to). Produce an exact decomposition of the five-point move into mix, rate and interaction terms, and name the segment shift that carries it.

Approach

Write blended margin as a revenue-weighted average of segment margins, m = sum over i of w_i * m_i, where w_i is the segment's share of net_amount_cents. Margin is a ratio of sums, so only revenue weights reproduce the blended figure.
Apply the exact three-term decomposition: delta_m = sum(delta_w_i * m_i0) + sum(w_i0 * delta_m_i) + sum(delta_w_i * delta_m_i), read as mix, rate and interaction. It is exact by construction, so the three terms must sum to the observed change with no residual.
Build segments from as-of joins: take the fct_subscription_period version whose term brackets each usage_date and the dim_account version whose effective_from and effective_to bracket it, rather than joining on is_current and backdating today's attributes over last year's usage.
Run the decomposition on more than one segmentation, at minimum plan_tier, deployment_model and sku_code, since the mix that actually moved may not be the one anybody already suspected.
Once the driving segment is identified, determine whether the shift came from acquisition landing new accounts in a lower-margin segment or from existing accounts migrating, because those have different owners and different remedies.

Follow-up

The interaction term is sometimes large. What does it mean operationally, and when would you prefer a log-mean decomposition that distributes it across the other two terms?
Revenue-weighted average margin is one construction. What changes if leadership wants margin weighted by account count, and which question does each version answer?

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.

Getting an analysis acted on

Work that nobody used is a common and unflattering pattern in data careers, and interviewers probe for it. Have a story about an analysis that changed a decision, and be specific about how you got it in front of the person who could act. Also have one about work that went nowhere, with your reading of why.

Describe a situation where you disagreed with a cross-functional partner on a project direction and how you resolved it.

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

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

State the measured impact of your own work honestly

Hard · impact · causal inference · self-assessment

You are asked for the business impact of a renewal-risk worklist you shipped nine months ago. Customer success used it, and renewals in the covered segment came in 6 points above the prior year. Coverage was assigned by the team itself: they worked the top of your list and also the accounts they were already worried about. Produce the impact claim you are willing to defend, the number you refuse to claim, and the design you would have asked for at the start.

Approach

The interviewer is probing whether you can separate what you shipped from what you caused, and whether you would have built the measurement in rather than reconstructing it afterwards. Both halves are being scored.
Name the confound precisely. Coverage assignment is doubly selected: the largest accounts get an owner because they are valuable, and distressed accounts get one because they are at risk. The naive covered versus uncovered comparison mixes a strong positive selection with a strong negative one and can come out with either sign depending on which rule dominated. Matching on account size does not fix it, because the risk signal that triggered coverage is the same signal that predicts the outcome.
Split the claims by what each needs to be true. Ranking quality is defensible from precision at k on out-of-time renewals. Adoption is defensible from timestamps showing what share of listed accounts were contacted. The outcome claim is not defensible without a design, and saying so is the point of the exercise.
Look for identification before giving up on it. A capacity cut-off, a territory boundary, or a period in which the list existed but was unstaffed can assign coverage for reasons unrelated to account health, and any of those supports a bounded estimate.
State the design you would ask for now and its price: a randomly withheld slice of the list, held for two renewal quarters, with the expected cost in renewals stated openly. That cost is what it takes to be able to answer this question at all.
Give a bounded number rather than none. Six points with an explicit statement of how much of it you can attribute is more useful than either claiming the whole figure or declining to quantify anything.

Follow-up

Your manager wants the 6 points in a promotion packet. What wording do you accept, and what do you strike?
What would have had to be true for the naive covered versus uncovered comparison to be valid?
If the holdout costs the team real renewals, how do you justify asking for it, and to whom?

Walk through an analysis you later discovered was wrong

Easy · data quality · error ownership · metering

Six weeks ago you reported that consumption fell 9 percent in the last week of the month, and a team spent a sprint investigating the cause. The fall was an artefact: rows in fct_usage_daily land late and are restated in place, and you queried before the tail had settled. Describe how you found the error, what you told the people who acted on it, and the control you put in place so this class of mistake cannot reach a dashboard again. Be specific about how the settling window was measured.

Approach

The interviewer is probing whether you self-report errors before someone else finds them, and whether your fix is structural rather than a promise to be more careful. Say plainly that the number was wrong and that a sprint was spent on it, before describing any diagnosis.
Establish the artefact quantitatively instead of asserting that data lands late. For each usage_date, compare the total as of first_written_at against the settled total and read the settling time off that curve, for example 97 percent of final by day three and 99.5 percent by day five.
Correct the record the same day, in the channel the original number went out in, to the same audience. The cost of the wasted sprint belongs in the correction, not in a footnote.
Make the fix structural: exclude a trailing lag window from every reportable figure, and make the reporting view return no rows inside that window rather than returning partial ones. A dashboard that shades unsettled days still gets read as a decline.
State what generalises. Any fact table restated in place has this failure mode, so the guard belongs at the source rather than on the one dashboard that embarrassed you. A strong answer ends with the class of error closed; a generic one ends with a lesson learned.

Follow-up

How did you choose the completeness threshold behind the lag window, and what would make you recalibrate it?
What did you say to the team that lost the sprint, and what did they say back?
Is there a legitimate case for showing the unsettled tail at all, and to whom?