Technocolabs Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Technocolabs, you will play a pivotal role in transforming data into actionable insights that drive business decisions and enhance user experiences. This position is critical in leveraging advanced analytics, machine learning, and statistical modeling to inform product development and strategy. You will work closely with cross-functional teams, including engineering, product management, and marketing, to understand the data needs of the organization and deliver solutions that impact products and services directly.

Most of the loop measures decision-making under uncertainty rather than recall. You are scored on whether you state your assumptions, commit to an estimate you can defend, and say explicitly what evidence would change it.

Focus areas

• Measure catalogue breadth beyond head consumption
• Correct discovery slates for position bias
• Diagnose rebuffering by device, network and POP

Overview

In this role, you will engage with complex datasets, employing various tools and methodologies to uncover patterns and trends. Your contributions will not only influence the direction of projects but also shape the strategic decisions made by leadership. Expect to work on challenging problems that require innovative thinking and a deep understanding of both the technical and business aspects of data analysis. The work you do will have significant implications on the effectiveness of products in the market and the overall growth trajectory of Technocolabs.

The Technocolabs interview process

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

Initial Screening Call

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

• Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
• Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
• The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

• Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
• Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
• Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

In-Depth Technical Interview

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

What to demonstrate

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

How to prepare

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

Behavioral Questions

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

What to demonstrate

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

How to prepare

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

Where candidates lose points

Collapsing cancellation and payment failure into one churn number.

Involuntary churn from expired or declined payment instruments is a large and volatile share of gross churn, and it responds to retry schedules, card-updater coverage and billing provider, not to anything in the product. It also resolves late, so a period that looks involuntary today can be a successful retry next week, and reading the split before the dunning horizon closes overstates it. Compounding this, cancel-at-period-end means the cancellation request and the entitlement end are different timestamps on different rows, so a churn curve keyed on cancel_requested_ts and one keyed on churn_ts disagree by a full billing period.

Treating the account as the person, or the profile as the person.

A household account carries several people, profiles frequently are not switched, and shared-screen, car and speaker playback often lands on a default profile with no user behind it. Personalisation trained on a profile therefore learns a mixture, retention regressions attribute one member's behaviour to another, and a per-account taste statistic describes a household composite. The practical consequence is that apparent personalisation wins can be device or context effects, so any identity-level claim needs a stated unit and an acknowledgement of what that unit actually aggregates.

Stopping an experiment the moment it crosses significance

Fix the sample size or duration before launch, or use a method built for continuous monitoring such as a sequential test, always-valid confidence intervals, or group-sequential boundaries. Repeatedly checking a fixed-horizon p-value against 0.05 pushes the real false-positive rate well above 5 percent.

Comparing periods without accounting for seasonality or day-of-week

Compare whole weeks against whole weeks and check whether the same swing appeared in prior cycles or prior years before attributing it to anything you changed. Weekday and weekend populations often differ enough that a Tuesday-to-Saturday comparison is meaningless.

Questions & practice

Write a function to calculate the mean and standard deviation of a list of numbers.

Medium · statistics and probability

Approach

1. Write down the assumption the method needs before you use the method.
2. Say what the estimate is of, and over what population it generalises.
3. Quantify uncertainty explicitly rather than reporting a point estimate alone.

Follow-up

• What sample size would you need to detect an effect half this size?
• Which assumption here is most likely to be violated in practice?

How do you evaluate the performance of a machine learning model?

Medium · machine learning and modelling

Approach

1. Pick an evaluation metric that matches the cost of each error type, not a default.
2. Say how the offline result would be validated online before it is trusted.
3. Check what information would not exist at prediction time, and exclude it.

Follow-up

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

Describe how you would optimize a machine learning algorithm for better performance.

Medium · machine learning and modelling

Approach

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

Follow-up

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

How would you implement a decision tree from scratch?

Medium · machine learning and modelling

Approach

1. Say how the offline result would be validated online before it is trusted.
2. Set a baseline first, so any model has something honest to beat.
3. Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

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

Measure catalogue concentration with a Gini written from scratch

Medium · concentration · catalogue · pandas

One calendar month of fct_stream (content_version_id, played_seconds, is_qualified) joins to dim_content_version (content_version_id, content_id, content_type). Roll versions up to content_id, then report, overall and per content_type: the number of content_ids, the share of qualified hours held by the top one percent, and the Gini coefficient over content_ids by hours. Write the Gini from its definition rather than importing one. State which content_ids are in the population you measure over, and defend that choice.

Approach

1. Roll versions to content_id before anything else. A remaster or a dubbed rendition is a separate content_version_id sharing a content_id, so leaving it split spreads one work's hours across rows and reports the catalogue as less concentrated than it is.
2. Decide and state the population: content_ids with at least one qualified stream this month measures concentration among what was played, while the full territory-eligible catalogue adds the zero-hours tail and pushes both statistics up. Either is defensible; the two are not comparable to each other.
3. Implement Gini on the ascending-sorted hours vector as 2 times sum(i times x_i) over (n times sum(x)) minus (n + 1) over n, with i one-based. Verify it on two hand-made vectors before pointing it at real data.
4. For the top one percent, take ceil(0.01 times n) content_ids by hours descending over the total. Say what you did at the boundary, because with a few thousand titles the rounding rule moves the answer visibly.
5. Compute each content_type inside its own population, and note that the per-type Ginis do not aggregate to the overall one: concentration is not additive across strata, and the overall figure carries between-type inequality the per-type figures exclude.

Worked solution (25 min)

1. Filter to is_qualified, join content_id and content_type, then group by content_id summing played_seconds and dividing by 3600.
2. Write gini(x): drop negatives, sort ascending, n = len(x), return 2 times (arange(1, n+1) times x).sum() over (n times x.sum()) minus (n + 1) over n. Assert gini([1,1,1]) is 0 and gini([0,0,1]) is 2/3.
3. Top one percent: k = ceil(0.01 times n); sort hours descending and divide the k-largest sum by the total.
4. Repeat both statistics inside each content_type group, keeping each type's own n.
5. Assemble a frame of scope, n_content_ids, top1pct_share and gini, with one overall row plus one row per content_type.

Expected result: A frame with one overall row and one row per content_type, where every Gini lies in [0, 1), every top-one-percent share is at least 0.01, and the overall Gini is not the mean of the per-type Ginis.

Verify it yourself

• Gini of a constant vector is exactly 0, and Gini of a vector with one non-zero entry out of n is (n - 1) over n.
• A separately computed Lorenz curve reproduces the same number through G = 1 minus twice the trapezoid area under it.
• Appending the zero-hours eligible catalogue raises both the Gini and the top-one-percent share. If either falls, the append landed in the wrong column.
• Hours summed across the per-type rows reproduce total qualified hours, confirming the type join dropped no content_id.

Follow-up

• This month's Gini rose by 0.03. Name three things other than a ranking change that could produce that.
• How would you measure breadth so a ranking team could act on it, rather than reporting one summary number?
• A rights window expired mid-month and removed 400 titles. How do you keep the month-over-month comparison honest?

Sessionise playback into listening sessions with an idle gap

Medium · window functions · gaps and islands · sessionisation

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

Approach

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

Worked solution (30 min)

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

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

Verify it yourself

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

Follow-up

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

Weekly engaged accounts from qualified streams on distinct days

Easy · aggregation · active accounts · qualification threshold

fct_stream has one row per playback with account_id, started_at (UTC), played_seconds, is_qualified (true when played_seconds >= 30) and event_date, a date partition key derived from started_at. dim_account has account_id and is_test_account. Write one query returning, for the seven days ending 2026-03-31 inclusive, the count of weekly engaged accounts: distinct non-test accounts with at least one qualified stream on two or more distinct UTC dates inside that window. Return a single row. Count at the account level, not the profile level.

Approach

1. Filter fct_stream on event_date BETWEEN '2026-03-25' AND '2026-03-31' and is_qualified = true. Filtering on started_at alone leaves the engine no partition to prune, so it reads every day the table holds; event_date is the partition key and is what belongs in the predicate.
2. Drop test accounts before the date count, with a join or semi-join to dim_account on is_test_account = false. Excluding them afterwards is too late, because a test account that streamed on two days has already been counted as engaged.
3. GROUP BY account_id and take COUNT(DISTINCT event_date) AS active_days. The distinct is the entire metric: an account with forty qualified streams inside one calendar day has one active day.
4. Filter HAVING COUNT(DISTINCT event_date) >= 2, then wrap in an outer COUNT(*). The group-by has already made account_id unique, so an outer COUNT(DISTINCT account_id) adds a sort for nothing.
5. Sanity-bound the answer before reporting it: it must be no larger than the count of distinct non-test accounts with any qualified stream in the window.

Follow-up

• If you counted distinct dates per profile and then rolled up to the account, would the number rise or fall, and which unit does the metric actually want?
• An account plays one 40-second track at 23:58 and another at 00:03. That is two distinct UTC dates and therefore engaged. Is that the intent, and what would you change if not?
• Offline plays upload in bulk several days late with started_at backdated. Which part of this query silently changes when they land?

How would you handle an imbalanced dataset?

Medium · product sense and metrics

Approach

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

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?

Given a dataset, how would you approach exploratory data analysis?

Medium · product sense and metrics

Approach

1. Fix the population and the time window before naming any metric.
2. Name one primary metric, then the guardrail that stops it being gamed.
3. Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

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

How would you design a data pipeline for a real-time analytics system?

Medium

Approach

1. Work from the decision backwards to the evidence you would need.
2. Clarify what is being asked and what a complete answer would contain.
3. State your assumptions explicitly before working the problem.

Follow-up

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

Publish a per-person metric when no table holds a person

Medium · metric design · unit of analysis · identity · dashboards

Leadership asks for streams per user per week on a weekly dashboard. You have dim_account (account_id, plan_tier, max_profiles), dim_profile (profile_id, account_id, profile_type, personalisation_opt_out, last_active_ts) and fct_stream (profile_id, account_id, device_type, started_at, played_seconds, is_qualified). Neither table holds a person. Deliverable: state the unit you will publish, define the metric fully, name what that unit actually aggregates, and give one diagnostic that tells the dashboard's readers how much of the data has no identifiable person behind it.

Approach

1. Establish that both available units are wrong in different directions: an account is a household so it aggregates several people, and a profile is not reliably one person either because profiles are frequently not switched and shared-screen, car and speaker playback lands on whatever profile was last active or on a default.
2. Pick the account as the published unit and justify it by what the number will be used for: billing, churn and revenue denominators are all per account, so an account-grained engagement metric joins to every other number on the dashboard without an impedance mismatch, while a profile-grained one silently changes the denominator between panels.
3. Define it fully: qualified hours per active account-week, numerator sum(played_seconds)/3600 over is_qualified rows, denominator distinct (account_id, ISO week) pairs with at least one qualified stream, is_test_account excluded, window the four most recent complete ISO weeks, with the account-level two-distinct-date rule used for the active-account count.
4. Label what the unit aggregates directly on the dashboard, not in documentation: the figure is hours per subscribing household per week, and family and duo plan_tier accounts read higher for composition reasons and are not more engaged people.
5. Give the diagnostic that makes the ambiguity visible: share of qualified hours on profile_type = 'default_unnamed' plus the share on device_type in ('smart_speaker','car','smart_tv'), published beside the headline, since that is the fraction of consumption whose person is genuinely unknown and it tells a reader how much interpretive weight the number bears.
6. Rule out the false fix explicitly: never divide account hours by max_profiles, because max_profiles is a plan entitlement that does not vary with household size and dividing by it manufactures a per-person figure that is a deterministic function of the plan.

Worked solution (25 min)

1. Write one sentence each for what dim_account and dim_profile actually identify, and state the two mechanisms that break the profile-equals-person assumption.
2. Write the chosen metric in full, including numerator, denominator, window, exclusions and the two-distinct-date active rule, and state the reason the account grain matches the other dashboard panels.
3. Write the unidentified-hours diagnostic as a query shape: share of sum(played_seconds) where profile_type = 'default_unnamed' or device_type in ('smart_speaker','car','smart_tv'), over total qualified played_seconds, by week.
4. Write the label that sits under the headline number naming the household interpretation, in the words a non-analyst reader would need.
5. State the prohibition on max_profiles division and the one-line reason, so the next person to ask for per-person numbers gets an answer rather than a repeat.

Expected result: Published unit is the billing account. Metric: qualified hours per active account-week with numerator, denominator, window and exclusions specified and the account-level two-distinct-date active rule. The unit is labelled as a subscribing household, not a person. Diagnostic: weekly share of qualified hours on default_unnamed profiles plus shared-device types, published beside the headline. Dividing by max_profiles is explicitly refused.

Verify it yourself

• Every panel on the dashboard that has a denominator uses the same one, so engagement, revenue and churn are normalised by the same population.
• The household label appears on the chart, not only in the metric documentation, because the misreading happens at the chart.
• The unidentified-hours diagnostic is a share of hours rather than a share of streams, so short shared-device plays do not dominate it.
• A cut by plan_tier is available, since pooling family and individual accounts is what makes the household composition invisible.

Follow-up

• Personalisation claims a win measured at the profile level. What do you need to know before you believe it is a person-level effect and not a device or context effect?
• A cut of the dashboard by plan_tier shows family accounts at three times individual accounts on hours. Write the sentence you would put under that chart.
• If you could add one column to dim_profile to make this less ambiguous, what would it be and what would it still fail to tell you?

Consumption fell the week a new ranker shipped

Hard · confounding · catalogue-supply · causal-inference

Qualified hours fell 4% in the seven days after a home-row ranker shipped to 100%, and a rollback is being demanded for tomorrow. You have fct_stream (played_seconds, is_qualified, content_version_id, start_source, started_at, play_territory), fct_impression (ranker_version, content_version_id, rendered_at), dim_content_version (content_id, parent_id, rights_expires_at, available_territories, catalogue_added_ts, is_active_version), and no holdout — the rollout went from 5% straight to 100%. Determine whether the ranker caused the fall. Deliverable: a defensible causal read, or an explicit statement of what cannot be concluded and what you would run instead.

Approach

1. Check supply before demand. Sum the prior period's qualified hours on content_version_ids whose rights_expires_at falls inside the window, whose is_active_version flipped, or whose available_territories lost a territory. If titles carrying several points of hours left the catalogue that Monday, the fall is partly accounted for before the ranker is even discussed.
2. Separate the mechanically unavailable from the merely deranked. Hours lost on items still available but no longer surfaced belong to the ranker; hours lost on items that cannot legally be served do not, and no ranking change recovers them. Only the first category is evidence for a rollback.
3. Recover a comparison the rollout destroyed. With no holdout, use the 5% phase as a pre-period and territories unaffected by the expiry as a control, running difference-in-differences with the expiry as the treatment. State the parallel-trends assumption out loud and test it on the four pre-window weeks; abandon the estimator if it fails rather than reporting it with a caveat.
4. Measure displacement instead of gross hours. For accounts whose usual items left, did total hours fall or did they substitute within the catalogue? Fully substituted removal costs nothing in hours and shows up only in the affected rights holder's payout, which is a different problem with a different owner.
5. Ask separately what the ranker did to exposure: impression share by content_id under each ranker_version, and top-1% exposure concentration. A ranker that concentrates exposure can depress hours through a mechanism nobody has named yet, and this is what distinguishes that story from the rights story.
6. Refuse the causal claim the rollout cannot support. Say plainly that a 5%-to-100% ramp across a confounded week yields no unbiased effect estimate, and propose the switchback or re-randomised holdout that would, with its cost stated.

Follow-up

• Design the switchback: slice length, burn-in long enough to clear carryover on a personalised surface, and how the standard error handles correlation between adjacent slices.
• If the rollback is ordered regardless of your analysis, what do you measure during the rollback to get a second, independent read?

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.

Explaining a method to someone who will not follow the maths

Half of this section is about translation. Be ready to describe how you explained a result to someone who did not want the method, only the implication, and what you did when the simplified version started being repeated in a way that overstated it. Correcting your own simplification is a strong beat.

Describe a challenging project you worked on. What was your role?

Medium · behavioural and stakeholder questions

Approach

1. Pick a story where you drove the decision, not one where you observed it.
2. Quantify the outcome, including what you would not claim credit for.
3. Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

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

Can you provide an example of how you communicated complex data findings to a non-technical audience?

Medium · behavioural and stakeholder questions

Approach

1. State the situation in two sentences and spend the rest on your reasoning.
2. Close with what you would do differently, concretely.
3. Pick a story where you drove the decision, not one where you observed it.

Follow-up

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

Walk through an analysis you shipped that was wrong

Medium · error ownership · data quality · postmortem

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

Approach

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

Follow-up

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