Interview guides · Global Insight Solutions

Global Insight Solutions Data Scientist Interview Questions & Guide 2026

Global Insight Solutions data scientist prep on data pitfalls: selection bias, target leakage, and checking an outlier's provenance before you drop it.

The 60-second brief

The Data Scientist role at Global Insight Solutions serves as a strategic engine for the organization, bridging the gap between raw data and actionable business intelligence. You will be responsible for translating complex business problems into rigorous analytical frameworks, driving product innovation through experimentation, and ensuring that the company's data-driven decision-making remains accurate and scalable.

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

Correct late-entered timesheets before trending recent weeks
Cluster inference at the account, not the engagement
Reconstruct pipeline stage history from mutable rows

Overview

This position is critical because your work directly influences product roadmaps and operational efficiency. Whether you are diagnosing unexpected metric drops or designing A/B tests to validate new features, you will function as a key partner to product managers and engineers. You can expect a fast-paced environment where technical depth in statistics and SQL is balanced with the ability to communicate findings to non-technical stakeholders.

The Global Insight Solutions interview process

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

Initial 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 Assessment

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

Behavioral Assessment

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.

Where candidates lose points

Computing days-to-pay or proposal cycle time over completed records only

At any snapshot date, invoices that have already been paid are disproportionately the fast ones, and proposals that already have a decision are disproportionately the quick ones. Averaging over the completed set alone biases both numbers downward, and the bias grows exactly when the business is deteriorating, because the slow cases are the ones still open. Unpaid and undecided records are right-censored; use Kaplan-Meier or a restricted mean up to a fixed horizon, and never fill paid_at with a placeholder.

Treating accounts as independent observations

Revenue is concentrated: a small number of client_ids typically carries a large share of fees, and engagements within one account share a partner, a rate card and a delivery team. Ordinary standard errors computed over engagements therefore understate uncertainty badly. Cluster at client_id, and with fewer than roughly 40 clusters use a wild cluster bootstrap or a CR2 correction, because cluster-robust standard errors are downward-biased in that regime and will manufacture significance that a replication will not reproduce.

Analysing at a different unit than the one randomised

Say out loud what was randomised (user, device, account, cluster) and make the analysis unit match, or account for the clustering with cluster-robust standard errors, the delta method, or aggregation up to the randomised unit. Randomising users and then running a test over sessions understates variance and inflates the false-positive rate.

Explaining an aggregate move without decomposing the mix shift

Split the change in the aggregate into within-segment movement and movement in segment weights before you explain it. Every segment's rate can fall while the overall rate rises, purely because volume shifted toward segments that already had higher rates.

Questions & practice

Implement billable utilisation against a defended availability denominator

Medium · metric implementation · denominators · proration

Implement billable utilisation for one calendar month at consultant grain, then roll it up to practice_area. Inputs: time_entries (consultant_id, work_date, hours, is_billable, charge_code, status), consultants (consultant_id, practice_area, fte_fraction, home_region, is_billable_role, hire_date, termination_date) and holidays (region, holiday_date). Numerator is approved hours with is_billable true. Denominator is scheduled workdays in the month, clipped to [hire_date, termination_date] and net of that region's holidays, times 8 times fte_fraction, minus approved leave hours. Roll up as sum(numerator) / sum(denominator).

Approach

Build the workday set per region first: business days in the month minus that region's holiday_dates. State the Monday-to-Friday assumption out loud, because a region with a different working week makes np.busday_count wrong rather than approximate.
Prorate by clipping each consultant's interval to [max(month_start, hire_date), min(month_end, termination_date or month_end)] and counting workdays inside the clipped interval, so a mid-month start produces a smaller denominator instead of a fake dip in the ratio.
Subtract approved leave hours (charge_code in leave_paid, leave_unpaid) only for leave falling on days already counted as workdays; leave logged on a public holiday would otherwise be subtracted twice and can push a denominator negative.
Filter to is_billable_role, then drop consultant-months whose denominator is zero or negative (full-month leave, a hire on the last day) rather than emitting inf or NaN, and return the dropped count as part of the result.
Roll up by summing numerators and denominators separately. Averaging per-person ratios gives a 0.2 FTE consultant the same weight as a full-time one and produces a practice number that no individual reconciles to.

Worked solution (35 min)

Per region, workdays = np.busday_count(month_start, month_end + 1 day, holidays=region_holidays); per consultant, recompute on the clipped [start, end] interval so proration and holidays compose.
leave = time_entries[status=='approved' & charge_code.isin(['leave_paid','leave_unpaid']) & work_date.isin(region workdays)].groupby('consultant_id').hours.sum().
denominator = workdays_clipped * 8 * fte_fraction - leave, reindexed to the billable-role consultant list and filled with 0 leave where absent.
numerator = time_entries[status=='approved' & is_billable].groupby('consultant_id').hours.sum(), reindexed and zero-filled.
Drop rows with denominator <= 0 (counting them), compute utilisation_pct = 100 * numerator / denominator, then groupby practice_area and divide summed numerator by summed denominator.

Expected result: A consultant-month frame carrying numerator, denominator and utilisation_pct, a practice-area frame whose ratio equals sum(numerator)/sum(denominator), and an explicit count of consultant-months excluded for a non-positive denominator.

Verify it yourself

A full-time consultant with no leave in a 21-workday month has denominator exactly 168.0.
Every consultant's denominator is at most workdays * 8 * fte_fraction, with equality only when leave is zero.
The practice-area ratio equals the denominator-weighted mean of the per-consultant ratios, and sits between their min and max.
Summed numerators across practice areas equal total approved billable hours for in-scope consultants.

Follow-up

Utilisation rose two points this month while realisation fell. Reconcile those two movements with one mechanism.
A regional lead says their team looks four points below another region. What do you check before you answer, and in what order?

Build a dense consultant-week hours spine with zero weeks

Easy · calendar spine · groupby · missing rows

You are given time_entries (time_entry_id, consultant_id, engagement_id, charge_code, work_date as datetime64, hours, is_billable, status) and a list of consultant_ids in scope plus a start and end date. Produce a DataFrame with exactly one row per consultant per Monday-anchored week in that range, with columns consultant_id, week_start, billable_hours, total_hours. Weeks in which a consultant logged nothing must appear as zeros rather than be absent. Count only rows with status == 'approved'. Do not loop over consultants and do not use resample.

Approach

Anchor weeks arithmetically: week_start = work_date - pd.to_timedelta(work_date.dt.weekday, unit='D'). Avoid dt.isocalendar().week, whose numbers restart each January and do not sort across a year boundary.
Construct the spine from the inputs, not from the data: pd.date_range(start, end, freq='W-MON') crossed with the in-scope consultant_ids via pd.MultiIndex.from_product, so its size is fixed before any aggregation runs.
Aggregate approved entries once with a single groupby on [consultant_id, week_start], producing total hours and billable hours (hours.where(is_billable, 0).sum()) in the same pass.
Left-merge the aggregate onto the spine, fillna(0.0) on both hour columns, and keep float dtypes so later ratios do not integer-divide.
Assert the shape before returning: len(out) == n_consultants * n_weeks, and out.total_hours.sum() equals the sum of hours over the filtered input inside the window.

Follow-up

Utilisation gets computed on this spine. What breaks for a consultant hired mid-window or terminated mid-window, and where would you fix it?
How would you extend this to consultant x engagement x week without the spine exploding to a row count nobody can hold in memory?

Kaplan-Meier days to payment with unpaid invoices censored

Hard · survival analysis · censoring · kaplan-meier

invoice_lines has invoice_line_id, engagement_id, line_type, issued_at, due_date, paid_at (null when unpaid), amount_usd and status in draft, issued, partially_paid, paid, disputed, written_off. At a given snapshot_date, estimate the median days from issue to full payment. Implement Kaplan-Meier yourself; no lifelines or equivalent. Treat issued, partially_paid and disputed as right-censored at snapshot_date, and decide and justify what to do with written_off. Report the naive mean over paid lines alongside your estimate and state the sign of its bias.

Approach

Build the duration and event table explicitly. Drop draft lines, which have no clock. For status paid, duration = (paid_at - issued_at).days with event = 1. For issued, partially_paid and disputed, duration = (snapshot_date - issued_at).days with event = 0.
Handle written_off as a competing event rather than a censor. Censoring it makes the estimator answer 'time to payment if written-off invoices could still pay', which overstates collection. Either report a cumulative-incidence version alongside, or censor them and say plainly that the result is conditional on eventual collection.
Implement the estimator directly: sort unique event times, at each t take n_i as the count with duration >= t and d_i as the payments at exactly t, and accumulate S(t) = product of (1 - d_i / n_i). Censored rows leave the risk set without causing a drop, which is the whole mechanism and the reason the answer differs from any completed-case average.
Read the median as min{t : S(t) <= 0.5}. If S never reaches 0.5 within observed follow-up, report 'not reached'; interpolating past the last observation invents data that the snapshot does not contain.
Add Greenwood's formula for Var(S(t)) to put a band on the curve, then invert the band at 0.5 for an interval on the median rather than quoting a point estimate alone.
Compare against the mean over paid lines only and name the direction: at any snapshot the paid set over-represents fast payers, so the naive mean is biased low, and the bias widens exactly when collections deteriorate.

Follow-up

A large account moved to a monthly payment run. Is administrative censoring still independent of payment time, and what would you check?
Finance wants one DSO number against a target. What do you give them, and what do you refuse to give them?
Stratify by line_type. Do milestone lines behave like fees lines, and what would it mean for the firm if they do not?

How would you use SQL window functions to calculate a rolling average of user activity over the last 30 days?

Medium · sql and data manipulation

Approach

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

Follow-up

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

Explain how you would handle missing data in a large-scale database query.

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Say which table is the grain you start from, and join outward from it.

Follow-up

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

Build a client cohort revenue table from invoice lines

Easy · cohorts · date truncation · revenue recognition

dim_client holds client_id and first_engagement_date. fct_engagement maps engagement_id to client_id. fct_invoice_line holds engagement_id, line_type, period_start_date, amount_usd and status; recognised fees are lines with line_type IN ('fees','milestone','credit_note') and status <> 'draft', and credit notes carry negative amount_usd. Return one row per (cohort_month, months_since_first) giving distinct clients billed and net recognised fees. cohort_month is the month of first_engagement_date; months_since_first is the whole-month gap between period_start_date and first_engagement_date. Long format, no pivot. Decide explicitly what to do with clients whose first_engagement_date is NULL.

Approach

Filter fct_invoice_line to recognised lines first (line_type, status <> 'draft'), and key recognition on period_start_date rather than issued_at or paid_at. Billing period, issue date and payment date are three different clocks, and mixing them produces a cohort table nobody can reconcile against finance.
Join invoice lines to fct_engagement to reach client_id, since invoice lines carry engagement_id only, then to dim_client for first_engagement_date.
Truncate both dates to month, then compute months_since_first as 12 * (year(bill_month) - year(cohort_month)) + (month(bill_month) - month(cohort_month)). Doing it on truncated months is what keeps a client billed on day 2 of the third month in bucket 2 instead of bucket 1.
Aggregate to (cohort_month, months_since_first) with SUM(amount_usd) and COUNT(DISTINCT client_id), keeping credit_note lines inside the sum so the figure is net rather than gross.
Handle NULL first_engagement_date as an explicit exclusion and report how many clients and how much revenue that removes, rather than letting the join drop them silently. That exclusion is why the output's total is smaller than the filtered line set's total, so carry the excluded figure as a named number rather than discovering the gap later.

Worked solution (25 min)

Write the recognised-lines CTE with the line_type and status predicates, and check its row count against the unfiltered table so you know how much you excluded.
Join through fct_engagement to dim_client and confirm the join does not multiply rows: a line has exactly one engagement, an engagement exactly one client.
Add the two date_trunc expressions and the integer month arithmetic, then eyeball ten rows against their source dates.
Group and aggregate, then sort by cohort_month and months_since_first.
Run a separate count of clients with NULL first_engagement_date, and a separate sum of their recognised fees, and note both beside the output.

Expected result: A long-format table of (cohort_month, months_since_first, active_clients, net_fees_usd), plus two scalars reported beside it: the count of clients with a NULL first_engagement_date and the recognised fees attached to them. months_since_first is a non-negative integer on every row; a negative value means an invoice period precedes the recorded first_engagement_date, which is a data-quality finding to report, not something to clamp to zero.

Verify it yourself

SUM(net_fees_usd) over the whole result equals SUM(amount_usd) over the filtered lines whose engagement resolves to a client with a non-NULL first_engagement_date. It does not equal SUM(amount_usd) over the full filtered line set, which is larger by exactly the excluded NULL-cohort revenue: add that separately reported figure back and the two agree. Any remainder after that is a line whose engagement_id has no matching row in fct_engagement, which is a referential finding to name rather than absorb.
COUNT(DISTINCT client_id) at months_since_first = 0 within a cohort_month is less than or equal to the number of clients whose first_engagement_date falls in that month.
Zero rows have a negative months_since_first; if any appear, name the clients rather than filtering them out.
Re-running with period_end_date in place of period_start_date moves only lines whose billing period spans a month boundary.

Follow-up

How would you turn this table into a 12-month net revenue retention ratio, and what exactly becomes the denominator?
One account contributes 40 percent of a cohort's month-13 fees. How do you present the cohort so a reader sees that before they act on the ratio?
If subsidiaries roll up to parent_client_id, which first_engagement_date defines the cohort, and does the rollup change the cohort a subsidiary lands in?

How would you design a set of success metrics for a new subscription-based feature?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.

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?

If an experiment shows a significant lift in a secondary metric but a decline in the primary metric, how do you proceed?

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

Follow-up

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

How do you balance long-term user retention against short-term revenue gains when choosing KPIs?

Medium · product sense and metrics

Approach

Fix the population and the time window before naming any metric.
Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.

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?

If you notice a sudden metric drop in daily active users, what is your diagnostic framework for identifying the root cause?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.
Fix the population and the time window before naming any metric.

Follow-up

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

What are the most common experimentation pitfalls that lead to false positives?

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.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

What would you conclude if the result is positive but the test is underpowered?
What would you do if you could not randomise at all?

Walk me through the steps you take to ensure statistical significance in an A/B test.

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
State the primary metric and the minimum effect worth shipping, then size the test.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

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

Refuse an early stop on a weekly-monitored pilot

Medium · peeking · sequential testing · alpha spending · novelty effect

A 26-week pilot is running and the sponsor reads a dashboard every Monday. At week 6 the primary metric is up with a nominal p of 0.03 and the sponsor wants to roll out. Quantify the type I error those weekly looks have already spent, give the boundary the week-6 look would have had to clear under a pre-registered four-look O'Brien-Fleming design, and explain why a week-6 estimate of a newly introduced delivery process is a poor estimate of its steady-state effect in either direction. Say what you would do instead.

Approach

Correct the naive arithmetic first. Looks at cumulative data are positively correlated, so the inflation is not 1 - 0.95^26 = 0.74. The classical repeated-significance result gives about 0.19 at ten equally spaced looks and about 0.25 at twenty, so 26 weekly looks at a nominal 0.05 sit near a quarter, roughly five times the advertised rate.
Give the pre-registered alternative concretely. An O'Brien-Fleming boundary with four equally spaced looks uses z_k = z_K * sqrt(K/k), which yields 4.05, 2.86, 2.34 and 2.02, corresponding to nominal two-sided p of roughly 0.00005, 0.004, 0.019 and 0.043. Week 6 is about a quarter of the information, so the boundary is 4.05 and the observed p of 0.03 is z = 2.17.
Offer the option that actually fits continuous monitoring: an always-valid confidence sequence or a mixture sequential probability ratio test, valid at every stopping time at the cost of wider intervals, rather than a fixed-horizon test read 26 times.
Separate the statistical objection from the measurement objection. Six weeks in, treated teams are six weeks into a changed way of working. Novelty inflates early estimates through attention to anything new, primacy depresses them through resistance to a process change, and in delivery-process pilots primacy usually dominates, so the early estimate is as likely too low as too high.
Diagnose rather than assert. Plot the effect by weeks since exposure rather than by calendar week and compare cohorts that adopted at different calendar dates. If the curve is still moving at the pre-specified end of the window, the window was too short and the estimand should be restated.

Worked solution (20 min)

Convert the reported p of 0.03 to a test statistic: two-sided, so z = 2.17.
Construct the four-look O'Brien-Fleming boundary: choose z_4 = 2.024 so the overall two-sided level is 0.05, then z_k = 2.024 * sqrt(4/k), giving 4.05, 2.86, 2.34, 2.02.
Place week 6 on the information fraction: 6/26 = 0.23, which is the first look, boundary 4.05.
Compare 2.17 against 4.05: the look does not cross, so continue to the next pre-specified look rather than stopping.
Plot the weekly effect estimate against weeks since exposure with its confidence band and check whether the curve has flattened before the pre-specified end.

Expected result: Roughly 25% type I error already spent by unstructured weekly looks, a required week-6 boundary of z at least 4.05 against an observed z of 2.17, and a recommendation to continue to the next pre-specified look, with the separate warning that a week-6 estimate of a process change is not its steady-state effect.

Verify it yourself

O'Brien-Fleming boundaries must fall across looks and the final one must sit just above the fixed-horizon 1.96; a final boundary below 1.96 means the spending function is wrong.
Simulating the null with 26 sequential looks at a fixed nominal 0.05 should reproduce a rejection rate near 0.25, not near 0.74.
Information fraction at each look must be built from accumulated observations or events, not elapsed calendar time, whenever enrolment is uneven.

Follow-up

The sponsor argues the cost of a false positive here is low. Does that change the boundary, and how would you set alpha from a cost ratio instead of convention?
You have four pre-registered looks. What does adding a futility boundary buy, and what does it cost?
How would you distinguish a novelty effect from a real effect that genuinely decays?

Realisation rate steps up overnight after a warehouse release

Medium · definition change · realisation · restatement

Realisation rate for one practice area steps from 0.93 to 1.02 on a single reporting date. Bill rates, hours worked and the client set are unchanged. The metric is SUM(amount_usd) from fct_invoice_line over SUM(hours * bill_rate_usd) from approved billable fct_time_entry rows for the same engagement and period. A warehouse release shipped that week. Determine what in the definition can produce a level shift with no operational change, and deliver a back-restated series plus the definition you would keep.

Approach

Treat the shape as evidence: a definition change is a discontinuity at one processing boundary, while a behaviour change ramps over weeks. Plot daily, not monthly, to see which one this is before forming any hypothesis.
Diff the metric's lineage across the release and enumerate the candidate predicates: which line_type values enter the numerator (dropping credit_note and write_off inflates it), whether the period join moved from period_start_date to issued_at, and whether the denominator counts status = 'approved' only or now includes 'submitted'.
Run both definitions over the same fixed pre-release month. If old and new differ by approximately the size of the step, the step is definitional; if they agree, look for a behaviour change coinciding with the release date.
Sanity-check the new level against the metric's physics: values above 1.0 are legitimate only on fixed_fee work delivered under its hour budget, so a practice dominated by time_and_materials sitting at 1.02 is itself a red flag.
Restate the full history under the chosen definition, publish old and new series together with the changeover marked, and add a reconciliation test that recomputes one known engagement-month from raw rows.

Follow-up

Which definition is correct, and what does excluding write-offs from the numerator do to the guardrail relationship with utilisation?
How do you prevent the next release from silently restating a headline metric?
If the release also changed the denominator's status filter, which direction does that push realisation and by roughly how much?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

Your part, not the team's

Most data work is done by groups, so an interviewer has to work out which piece was yours. An answer that runs on 'we' for several minutes gets interrupted with a question about what you personally did, and by then the answer sounds defensive even when it is true. Mark your own contribution as you go, and name the parts that belonged to someone else instead of leaving them ambiguous. Keep a few specifics back as well, like the name of the metric or who actually objected, so a probe can be answered with something you had not already said.

How do you handle conflict when a stakeholder disagrees with your data-driven recommendation?

Medium · behavioural and stakeholder questions

Approach

State the situation in two sentences and spend the rest on your reasoning.
Quantify the outcome, including what you would not claim credit for.
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?

Explaining a censored days-to-pay number on a board slide

Easy · censoring · kaplan-meier · executive communication

A finance lead wants average days to get paid for the last four quarters, for a board slide. In fct_invoice_line, rows with status in ('issued','partially_paid','disputed') have paid_at NULL. The mean of (paid_at - issued_at) over paid invoices is 38 days. A Kaplan-Meier median, right-censoring the open invoices at snapshot_date minus issued_at, is 51 days. You get two sentences and one chart. Explain the number you put on the slide, the gap between the two figures, and what will make that number move next quarter.

Approach

Name the probe: whether you can give a non-technical decision-maker one number, the direction of the error in the alternative, and the reason, without teaching survival analysis.
Explain the mechanism in business language. Invoices that have been paid are disproportionately the ones that pay fast; the slow ones are still open and therefore missing from the 38-day average. The error is one-directional and it grows as collections get worse, which is exactly when the number matters.
Commit to one headline. Either the Kaplan-Meier median, or a restricted mean days-to-pay capped at a fixed horizon such as 90 days, which is easier for a finance audience to audit. Say which you used and why, and do not put both headline numbers on the slide.
Make the chart the survival curve or a simple share-paid-by-day-k curve rather than a bar of averages, because the audience question is really when cash arrives, not a single moment.
State the forward behaviour before you are asked: the figure for a recent quarter will rise or fall as open invoices resolve, so the slide carries the snapshot date and the share of invoices still open.

Follow-up

The exec asks why the number printed on last quarter's slide no longer reproduces. What is your answer, and what would have prevented the question?
How would you report this by account_tier without putting six survival curves on one slide?

Your utilisation dashboard caused a staffing decision on incomplete data

Medium · late-arriving data · postmortem · reporting windows

Six weeks ago you shipped a weekly billable-utilisation dashboard keyed on fct_time_entry.work_date. It showed a six-point drop across the three most recent weeks. A practice lead pulled two consultants off an engagement in response. The drop reversed on the next refresh, because time entries are created days to weeks after the work happens and entered_at trails work_date. Describe what you do now: the diagnosis, the change to the artifact, and the conversation with the person who acted on your number.

Approach

Name the probe: whether you own a reporting-design error rather than reclassifying it as someone else's timesheet compliance problem, and whether you fix the class of bug instead of the single week.
Quantify before explaining. Measure the backfill curve directly from entered_at: for work_date D, the share of final hours that existed as of D plus k, for k from 1 to 45. That gives an observed tail length instead of a guessed cutoff.
Change the artifact so the incomplete region cannot be read as a trend. Either end the trended series at snapshot minus the measured tail, or publish an as-of-entered_at series that is internally consistent, and label which one is on screen.
Tell the person who acted, first and directly, with the corrected series and the specific decision to revisit. A correction that arrives after they notice costs more than the original error.
Add a standing completeness tile: timesheet submission lag, the share of entries where entered_at date minus work_date exceeds seven days, so the dashboard shows its own reliability rather than depending on you remembering.

Follow-up

Leadership still wants to see the current week. What do you show, and how do you label it?
One practice runs a three-day lag and another twenty days. Do you set one firm-wide cutoff or one per practice, and what does that cost in comparability?