Interview guides · Apex Systems

Apex Systems Data Scientist Interview Questions & Guide 2026

Apex Systems data scientist prep: probability, sampling distributions, and stating the assumption behind every test you run.

The 60-second brief

The role of a Data Scientist at Apex Systems is pivotal to transforming raw data into actionable insights that drive strategic decision-making. As a Data Scientist, you will harness advanced analytical techniques and machine learning models to solve complex business problems, optimize processes, and enhance product offerings. This position is crucial not only for improving operational efficiency but also for shaping the future of products that impact a wide array of users across different sectors.

Ask early whether the loop includes an asynchronous take-home or a timed live case, because the two are graded on different things. A take-home is read as an artifact: the question you decided to answer, what you did about missing or malformed records, and a conclusion stated plainly enough for someone to act on. A reviewer who cannot rerun your notebook discounts the result whatever score is printed in it. Hold to the stated time box and write down what you would have done with more of it, since the follow-up round is usually a live defence of the same work.

Focus areas

Measure churn only on renewal-eligible accounts
Analyse at the account grain, cluster errors
Read NRR on a fixed account cohort

Overview

In your capacity as a Data Scientist, you will collaborate closely with cross-functional teams, including engineering, product management, and business stakeholders, to identify high-value data opportunities. This role involves working on diverse projects, such as predictive analytics for user behavior, developing recommendation systems, and performing A/B testing to refine product features. The complexity of the data and the scale at which you’ll operate make this position both challenging and rewarding, offering the chance to influence significant outcomes in the organization.

The Apex Systems interview process

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

Initial Screening

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

What to demonstrate

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

How to prepare

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

Technical Assessment

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 Interview

Most of the weight in this round sits on the disagreement questions. Data work routinely produces an answer someone senior did not want, and the interviewer is trying to learn what you do in that hour. Both failure modes are common: folding as soon as a director pushes back, and treating the pushback as ignorance to be corrected with a better chart. A strong answer usually contains a specific thing the other person knew that you did not, and describes how you found out whether it changed the conclusion.

What to demonstrate

Whether you can state the other side's argument accurately before you explain why you disagreed
What you treated as evidence during the disagreement, such as a rerun under their assumption or a holdout check, rather than persuasion technique
Whether you distinguish being overruled from being wrong, and can give an example of each

How to prepare

Write out one disagreement where you turned out to be wrong, and say what in the data misled you. Candidates prepare the story where they were right, and the follow-up asks for the other one.
For your main disagreement story, be ready to say what result would have made you drop your position. If no such result exists, you were not arguing from the data.
Practise stating the opposing position out loud in one sentence the stakeholder would accept, then continue the story.

Final Interviews

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

Computing monthly churn against the entire customer base when contracts are annual

An annual contract has no opportunity to churn except at its renewal date, so an account that is eleven months from renewal is in the denominator while being incapable of appearing in the numerator. The resulting rate is smaller than the real one by roughly the ratio of the base to the renewal-eligible base, and it oscillates with the seasonality of when deals were originally signed rather than with anything about the customers. The corresponding trap on the other side is counting a churn on the date the record was updated rather than on term_end_date, which shifts losses into whichever month the operations team did its paperwork.

Treating raw request or usage volume as engagement

Most traffic in this domain is emitted by machines. Continuous-integration pipelines, scheduled batch jobs, synthetic monitors, backfills and client retries can all grow by an order of magnitude from one configuration change made by one engineer, and none of it represents a new decision to use the product. The inversion is what makes it dangerous: when the platform degrades, clients retry, so error-driven retry volume rises at the exact moment the customer is most likely to leave, and an engagement dashboard built on raw counts shows growth immediately before a churn. Filter on traffic_class and on successful status before anything else, and keep failed-request volume as its own separate series.

Accepting a metric definition without asking about the denominator

Pin down the denominator, the eligibility filter and the time window before computing anything: conversion rate per session, per user, per eligible user and per new user are four different numbers with different behaviour. Restate the definition in one sentence and get agreement before you analyse.

Averaging per-user rates to produce a population rate

Decide which quantity you want: the mean of per-user ratios and the ratio of summed numerator to summed denominator are different estimands, and heavy users dominate one but not the other. For a ratio metric, aggregate numerator and denominator separately and use the delta method for its variance.

Questions & practice

Describe a machine learning project you worked on. What were the challenges?

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

Can you explain a time when you used a regression model? What was your approach?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
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.

Follow-up

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

Bootstrap a confidence interval for net revenue retention

Medium · bootstrap · retention · resampling

You have one row per account with arr_start_cents (ARR twelve months ago) and arr_end_cents (ARR today, zero if churned), covering the fixed cohort of accounts that had ARR twelve months ago. Net revenue retention is sum(arr_end_cents) / sum(arr_start_cents). Write a nonparametric bootstrap from scratch, without scipy.stats.bootstrap: resample accounts with replacement, recompute the ratio of sums on each resample, and return the point estimate with a 95% percentile interval from 10,000 resamples. Also report the interval you would get from the mean of per-account ratios, and explain the difference.

Approach

Resample the account, because the account is the unit the estimand is defined over. One bootstrap draw is a vector of account indices and both numerator and denominator are recomputed from that same draw; resampling the two sides independently destroys the within-account correlation that makes a ratio estimator stable.
Vectorise the draws: idx = rng.integers(0, n, size=(B, n)), then end[idx].sum(axis=1) / start[idx].sum(axis=1). A 10,000 by n index matrix is usually far cheaper than a Python loop; if the matrix is too large for memory, chunk over B rather than reverting to a loop.
Take the interval from np.quantile(ratios, [0.025, 0.975]). The percentile interval differs from estimate +/- 1.96 * bootstrap SE whenever the resample distribution is skewed, which it will be here, and the skew is the thing you want represented.
Compute the mean-of-ratios version on the same resamples, and state the exact relationship rather than guessing which of the two is larger. With r_i = arr_end_i / arr_start_i, the ratio of sums is the arr_start-weighted mean of exactly those r_i, so sum(end)/sum(start) - mean(r) = Cov(arr_start, r) / mean(arr_start) using the population covariance. The gap is positive when larger accounts retain and expand better than smaller ones, and negative when they do not; a cohort whose small accounts churn at a higher rate has positive covariance, which puts the mean of per-account ratios BELOW the ratio of sums. Requires arr_start_i > 0 for every account, which the fixed-cohort definition guarantees; r_i is floored at 0 and unbounded above, so a handful of 4x expansions among small accounts can flip the sign. Compute the covariance and report it instead of asserting a direction.
Report the interval width beside the concentration of the cohort. If the largest account is 12% of starting ARR, a narrow interval is evidence that the resampling unit is wrong rather than evidence that the estimate is precise.

Worked solution (30 min)

start = df.arr_start_cents.to_numpy(float); end = df.arr_end_cents.to_numpy(float); n = len(start); point = end.sum() / start.sum()
rng = np.random.default_rng(7); idx = rng.integers(0, n, size=(10_000, n)); ratios = end[idx].sum(1) / start[idx].sum(1)
lo, hi = np.quantile(ratios, [0.025, 0.975]); return point, lo, hi
per_acct = end / start; mean_point = per_acct.mean(); mean_boot = per_acct[idx].mean(1); compare np.quantile(mean_boot, [0.025, 0.975]) against (lo, hi), and report np.cov(start, per_acct, ddof=0)[0,1] / start.mean() as the quantity that accounts for the gap between the two centres.

Expected result: A point estimate equal to sum(end)/sum(start) on the unresampled data — the bootstrap mean is close but must not be substituted for it — and a percentile interval that is asymmetric around the point when expansion is skewed. The mean-of-ratios interval is centred on a different estimand, and which side it falls on is decided by Cov(arr_start, r): it sits below the ratio of sums in the common case where small accounts churn at a higher rate than large ones, and above it when the large accounts are the ones shrinking. Its width is governed by the spread of per-account growth, while the ratio-of-sums width is governed by how concentrated starting ARR is, so neither interval is reliably the wider one.

Verify it yourself

point equals sum(end)/sum(start) computed directly on the original arrays, and point - per_acct.mean() equals np.cov(start, per_acct, ddof=0)[0,1] / start.mean() to floating-point tolerance. That is an algebraic identity, not an empirical claim: a mismatch means the per-account ratios or the weights are wrong, and the sign of it tells you which of the two numbers is larger before you look.
np.median(ratios) sits within a small fraction of a point of the point estimate; a large gap indicates a wrong resampling unit or a transposed index matrix.
Raising B from 10,000 to 20,000 moves the endpoints by far less than the interval width.
Setting every arr_end equal to its arr_start gives point == 1.0, per_acct.mean() == 1.0, a zero covariance term and a zero-width interval.

Follow-up

The cohort has 800 accounts and the largest is 12% of starting ARR. How much do you trust a percentile interval here?
How would you extend this to an interval on the year-over-year change in NRR?
Two accounts merged mid-window and one contract was co-termed into the other. How do you keep the cohort fixed?

Monthly account margin joined as-of the live contract version

Medium · as-of join · fan-out · scd2 · unit economics

fct_usage_daily carries account_id, usage_date, net_amount_cents and cogs_cents. fct_subscription_period carries account_id, plan_tier, term_start_date, term_end_date, booked_at and is_current, with one row per contract version, so several superseded versions can bracket the same usage_date. dim_account is a type 2 dimension keyed on account_id with employee_band, is_internal, effective_from, effective_to and is_current. Return monthly net revenue, allocated COGS and gross margin per account, labelled with the plan_tier in force during that month and the employee_band in force at month end. The reported figure excludes internal accounts, so it cannot equal the raw sum of net_amount_cents; reconcile additively instead, quantifying every bucket you drop so the reported and dropped amounts add back to that raw sum.

Approach

Aggregate fct_usage_daily to one row per (account_id, month) before touching any dimension. Aggregating after the join is what multiplies revenue, and no amount of DISTINCT afterwards recovers the right number.
Resolve the contract as-of the month rather than as-of now. Take fct_subscription_period rows whose term brackets the month, then rank with ROW_NUMBER() OVER (PARTITION BY account_id, month ORDER BY booked_at DESC, subscription_period_id DESC) and keep rank 1. Filtering on is_current instead backdates today's plan over last year's usage and quietly rewrites history.
Resolve dim_account with the half-open interval effective_from <= month_end AND (effective_to > month_end OR effective_to IS NULL). The NULL on the live version has to be spelled out or the current row drops out of every recent month.
Compute margin as (sum(net_amount_cents) - sum(cogs_cents)) / NULLIF(sum(net_amount_cents), 0) at the account-month grain, and report the distribution rather than a blended rate so margin-negative accounts stay visible.
State the month-straddling rule explicitly: either split the month at the amendment date or take the version in force at month end. Both are defensible; an unstated choice is not.
Reconcile additively, not by equality to the unfiltered total. The output deliberately drops two populations: account-months whose as-of dim_account version carries is_internal = true, and account-months with no fct_subscription_period version bracketing the month, which an inner join on the contract removes without saying so. The statement that ties out is reported_net + internal_net + unmatched_net = sum(net_amount_cents) over the same usage_date range. Plain equality to the raw sum would only hold if both dropped buckets were empty, and the internal one never is.
Compute the internal bucket with the identical as-of rule used for the output, evaluating is_internal on the dim_account version in force at that month end. Reading is_internal from the current version instead moves accounts between the two sides of the identity and it stops closing.

Follow-up

An account amends mid-month from team to enterprise. Show what your query reports and argue for one attribution rule over the other.
Your monthly total disagrees with the finance figure by a small amount. Where would you look first, and which number do you defend?

Sessionise interactive API traffic with a thirty-minute inactivity gap

Medium · window functions · sessionisation · gaps and islands

fct_api_request carries account_id, user_id (null for service accounts), request_at, traffic_class and http_status. Using only traffic_class = 'interactive' rows with a non-null user_id, group each seat's requests into sessions on a 30-minute inactivity threshold: a request more than 30 minutes after the previous request from the same (account_id, user_id) opens a new session. For one ISO week return, per account, the session count, the median session duration in minutes and the median requests per session. A single-request session has a duration of zero.

Approach

Filter first: traffic_class = 'interactive' and user_id IS NOT NULL. Machine traffic has no sessions in any useful sense, and leaving CI or batch rows in produces sessions that are really cron schedules.
Get the previous timestamp with LAG(request_at) OVER (PARTITION BY account_id, user_id ORDER BY request_at). Partitioning by user_id alone stitches one person's work across two different accounts into one fabricated session, because a human holds memberships in several accounts.
Flag a boundary where the lag is NULL or request_at - lag > interval '30 minutes'. Decide and state whether exactly 30 minutes continues the session; strictly greater is the conventional choice and needs to be written down either way.
Assign session ids with SUM(boundary::int) OVER (PARTITION BY account_id, user_id ORDER BY request_at ROWS UNBOUNDED PRECEDING), the standard running-count construction for islands.
Roll up to sessions with min(request_at), max(request_at) and count(*), then to accounts with percentile_cont(0.5) WITHIN GROUP (ORDER BY ...). Use medians, not means: session length is strongly right-skewed and one long-running client dominates the average.

Worked solution (30 min)

Build the filtered week: interactive rows with user_id IS NOT NULL inside [week_start, week_start + 7 days).
Add prev_at via LAG(request_at) OVER (PARTITION BY account_id, user_id ORDER BY request_at) and derive is_new_session = (prev_at IS NULL OR request_at - prev_at > interval '30 minutes').
Add session_seq = SUM(is_new_session::int) OVER (PARTITION BY account_id, user_id ORDER BY request_at ROWS UNBOUNDED PRECEDING).
Aggregate to sessions by (account_id, user_id, session_seq) taking min, max and count, with duration_minutes = EXTRACT(EPOCH FROM max - min) / 60.
Aggregate to accounts with count(*) AS sessions, percentile_cont(0.5) WITHIN GROUP (ORDER BY duration_minutes) and percentile_cont(0.5) WITHIN GROUP (ORDER BY request_count).

Expected result: One row per account with session_count, median_session_minutes and median_requests_per_session, computed over interactive human traffic only, where a lone request yields a session of duration zero and request count one.

Verify it yourself

Summing request_count across all sessions equals the filtered row count for the week exactly.
Every session duration is non-negative, and no two consecutive requests inside one session are more than 30 minutes apart when you re-derive the LAG within sessions.
Recomputing with the partition on user_id alone yields strictly fewer sessions and longer medians for any account sharing users with another account, which confirms the partition key matters.

Follow-up

Sessions belonging to colleagues in one account are correlated. What does that do to a t-test on session length across an experiment arm?
A long-poll or streaming endpoint keeps a connection open for hours. How do you stop it reading as one twelve-hour session?

Given a dataset, how would you identify outliers and assess their impact on your analysis?

Medium · product sense and metrics

Approach

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

Follow-up

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

Walk me through your thought process when solving a complex business problem.

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

What would you do if the primary metric and the guardrail moved in opposite directions?
Which segment would you cut first, and what would that rule out?

How do you prioritize tasks when working on multiple projects?

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
Name one primary metric, then the guardrail that stops it being gamed.
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?
Which segment would you cut first, and what would that rule out?

How would you approach predicting customer churn for a subscription service?

Medium · product sense and metrics

Approach

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

How would you design an experiment to test a new feature in a product?

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.
State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

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

What is the difference between supervised and unsupervised learning?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
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?

Cut variance with pre-period usage before the test starts

Medium · cuped · variance-reduction · stratification

You are planning an account-randomised test on 2,800 paying accounts. The outcome is a 28-day sum of billable_quantity for sku_code = 'compute_hours' from fct_usage_daily, and the same account's 28-day pre-period sum correlates 0.80 with it. 420 accounts were created inside the pre-period and have partial or no history. Specify the variance-reduction plan: the adjusted estimator and where its coefficient comes from, how assignment is stratified, how the 420 incomplete accounts are handled, and what must change in the pre-period window given that fct_usage_daily rows are restated after first write.

Approach

Write the estimator explicitly: Y_adj = Y - theta * (X - mean(X)), with theta = Cov(X, Y) / Var(X). Estimate theta from pre-experiment history or pooled across arms, never separately by arm and never from post-treatment outcomes. Fitting theta on treatment-arm outcomes folds the effect being measured into the adjustment and biases the result toward whatever the treatment did.
Quantify the gain and convert it into the currency the team cares about. The residual variance multiplier is 1 - 0.80^2 = 0.36, so the standard error falls to 0.60 of its unadjusted value and the MDE falls with it. That is the same precision as running with 1 / 0.36, about 2.8 times as many accounts, which matters because the account population is fixed and cannot be bought with a longer run.
Stratify assignment on pre-period usage decile crossed with the three paid plan tiers, giving 30 cells at roughly 93 accounts each, and collapse any cell below about 20. Use the same strata in the analysis through strata fixed effects or post-stratification: stratified assignment analysed pooled discards much of the gain, and stratified analysis without stratified assignment risks empty cells in the top decile, which is precisely where the revenue sits.
Handle the 420 incomplete accounts by imputing X at the stratum mean and adding a binary indicator for missing pre-period, rather than dropping them. Dropping silently redefines the population to established accounts, which is usually the opposite of the segment a new feature targets, and it makes the result non-generalisable in a way the readout will not disclose.
Fix the window against restatement. Measure the empirical settling time by comparing a usage_date's total at first_written_at against its value after restated_at has stopped moving, then end the pre-period that many days before assignment. A pre-period whose last days are still settling carries recency-correlated measurement error in the covariate, which both weakens rho and can correlate with assignment date.
Pre-register the whole plan before assignment: the estimation set for theta, the strata definition and collapsing rule, the imputation rule, the winsorisation cap and the trailing exclusion. Every one of these can be tuned after the fact to move a p-value, which is why they are worth nothing if decided afterwards.

Worked solution (30 min)

Estimate rho on a historical pair of adjacent 28-day windows, applying exactly the traffic-class and is_internal filters the live experiment will use.
Compute the variance multiplier 1 - 0.80^2 = 0.36 and translate it to a standard-error multiplier of 0.60 and an effective-sample multiplier of about 2.8.
Build 30 strata from ten pre-period usage deciles crossed with three paid plan tiers, inspect the minimum cell count and collapse cells below about 20 accounts.
Measure the metering settling time from first_written_at against restated_at and shift the pre-period window back by that many days.
Write the pre-registration: theta source, strata, imputation for the 420 incomplete accounts, winsorisation cap, trailing exclusion.

Expected result: Standard-error multiplier 0.60 and effective-sample multiplier about 2.8 at rho = 0.80, with 30 strata collapsed where thin, the 420 incomplete accounts retained through stratum-mean imputation plus a missingness indicator, and the pre-period ending one measured settling window before assignment.

Verify it yourself

Adjusted and unadjusted point estimates must be close under valid randomisation, because this reduces variance rather than moving the estimate. A large shift means theta was fitted on post-treatment data or that randomisation itself failed.
The multiplier 1 - rho^2 is monotone in the absolute correlation and equals 1 at rho = 0, so at rho = 0.30 the standard error can only fall by about 5%. Any larger claimed reduction at that correlation is arithmetically wrong.
Computing the pre-period sum twice, a week apart, should return the same number once the settling window is excluded. If it does not, the exclusion window is too short.

Follow-up

Once continuous-integration and synthetic traffic are excluded, rho turns out to be 0.45 rather than 0.80. What is the revised variance reduction, and is the added complexity still worth it?
How would you extend this to more than one covariate, and what stops you from adding twenty?
Does this adjustment repair an imbalance you discover after assignment, or only reduce variance? Be precise about the difference.

Weekly active organisations fell nine percent over one week

Easy · seasonality · denominators · segmentation

A dashboard reports the weekly active organisation ratio on a trailing seven-day window ending each Wednesday. This week it reads nine percent below last week. You have fct_api_request (account_id, environment, traffic_class, http_status, request_at) and dim_account (account_id, billing_country, account_status, is_internal, is_current). Nothing was released. Decide whether usage actually fell, and hand back a corrected series plus a one-paragraph explanation that a non-analyst can repeat without you in the room.

Approach

Count the holiday-free business days inside each window before comparing them. A trailing seven-day window spans exactly five weekdays wherever it ends, so its business-day count can only fall to four or fewer when a public holiday lands inside it and can never reach six, while usage in this domain follows a hard five-to-two weekday cycle. Two windows holding different numbers of business days are not comparable whatever the product did.
Count distinct active accounts per calendar day for the last ten weeks and overlay the two windows. A calendar problem shows as a small number of weekdays sitting at weekend level, not as every day being uniformly lower.
Cut the daily series by billing_country and index each country-day to that country's trailing same-weekday median, which isolates a regional public holiday from a product change.
Check the denominator on its own: the metric divides by accounts whose account_status was in trial, free or active_paid for the whole week, so a batch suspension or status backfill moves the ratio with no change in the numerator at all.
Report the series with each window's business-day count and the holiday dates annotated beside it, and state the residual week-over-week change that survives once the calendar effect is removed. Compare against earlier windows holding the same number of business days rather than dividing by business days, since distinct-account counts are sublinear in window length and dividing would over-correct.

Follow-up

Distinct account counts are sublinear in the number of days in the window. Why does losing one of five business days reduce the count by noticeably less than twenty percent?
How would you make this metric comparable across countries with different holiday calendars without hand-maintaining a holiday table forever?

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.

Being wrong in public

A number you shipped turned out to be wrong, and someone had already acted on it. That is one of the most useful stories a data person can carry. What is being scored is how fast you noticed, who you told first, and what you changed in the process so the same class of error could not repeat quietly.

Describe a situation where you had to work with a difficult stakeholder. How did you handle it?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Disagree with a product manager about an adoption claim

Medium · conflict · telemetry hygiene · adoption metrics

A product manager is about to present that a new SDK release drove a 40 percent rise in requests among adopting accounts, computed from fct_api_request counts grouped by sdk_version. You find the rise is concentrated in traffic_class equal to ci, that rows with is_retry true grew alongside it, and that restricting to interactive non-retry traffic leaves a 3 percent lift. The launch review is in two days. Decide how you raise this, with whom and in what order, and what you propose the claim becomes.

Approach

The interviewer is probing whether you can correct a colleague without ambushing them, and whether your own counter-analysis carries the caveats theirs lacked. Go to the product manager privately before the review. A correction delivered in the room is a status move and loses the argument you are actually trying to win.
Bring a decomposition rather than a verdict: the same accounts and window, requests split by traffic_class with retries held out as their own column, so their 40 percent and your 3 percent reconcile line by line and neither has to be taken on trust.
Reproduce their figure exactly first. If you cannot land on 40 percent with their method, you do not yet know what you are disagreeing with.
Ask whether the continuous-integration lift is itself valuable. An account wiring the SDK into its pipeline has increased integration depth, which is the dominant switching cost in this domain, so the honest claim may be that integration depth rose while interactive usage moved 3 percent. Improving the claim beats deleting it.
Name the mechanism that makes the raw count dangerous: clients retry when the platform degrades, so retry volume climbs exactly when the customer is most at risk. Pull the 5xx rate for the same accounts and window before anyone concludes anything, and note that billable_units is zero on 5xx rows, so request counts and billable quantities diverging is itself the signal.
Close with a standing definition for launch metrics so the next release does not repeat the exercise.

Follow-up

The product manager argues that continuous-integration traffic is real usage and declines to split it out. Is that position defensible, and under what metric definition?
Suppose the 5xx rate for those same accounts also rose 40 percent. What is the claim now?
The review happens and the raw number is presented regardless. What do you do next, and what do you not do?

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?