SQL A/B Test Data Interview Questions: Assignment, Exposure, and Sample Contamination

In SQL A/B test data interview questions, the most consequential decision is often which rows survive. Starting from exposure logs can drop assigned users who never reached the feature. Joining every exposure to every purchase can multiply outcomes. Taking the first observed variant can hide broken assignment rather than repair it.

A strong answer builds one row per experiment version and randomization unit, preserves assignment, and adds exposure, outcome, and data-quality fields separately. Only after validating that dataset should you calculate arm-level metrics. This is a data-construction exercise for analyst and data scientist interviews, not a complete statistical testing procedure.

Here, the central question is whether the SQL produces a defensible analysis population in the first place.

Assignment, exposure, and outcome are different records

Assignment records the experimental arm allocated to a randomization unit. Exposure records a qualifying delivery or use of the experimental experience. An outcome records the behavior being measured. Their identities and timestamps must connect, but their populations and row counts need not be identical.

For example, a user can be assigned treatment without opening the application. Another can receive control after being assigned treatment because of an implementation error. A third can produce five purchase events while remaining one randomized user. None of these cases is resolved by renaming every event's variant field to “assignment.”

Microsoft's experimentation guidance identifies stable treatment assignment and accurate linking of randomization units as data-quality requirements. That supports checking these records separately; it does not prescribe the exact schema of our original fixture. Microsoft Research on experiment data quality

Define the analysis contract before the joins

Use a key such as (experiment_id, version, unit_id). The unit might be a user, household, account, or device, depending on the randomization design. Our fixture contains one already-selected experiment version with user-level assignment, so SQL can use unit_id alone without accidentally mixing experiments.

Eligibility is determined before assignment. The outcome is at least one purchase in the half-open seven-day interval beginning at assignment. All assignment windows are complete at the reporting cutoff, and event collection is assumed complete for the exercise. In real data, maturity and collection completeness require separate checks.

The intended assignment-based analysis keeps units in their assigned arm even if they never receive the experience. However, a unit whose true assignment cannot be resolved is a data-integrity problem. Our example quarantines that unit for inspection and blocks causal interpretation; it does not present the remaining subset as a repaired intention-to-treat estimate.

Predefining the population and success metrics keeps later convenience filters from silently changing the question. Microsoft discusses those design choices in its pre-experiment trustworthiness guidance.

Load logs that contain the actual traps

The following complete fixture runs in DuckDB. Numeric variants are avoided: A and B make it easier to spot a conflicting assignment. seen_at on repeated assignment telemetry is not a new randomization; the first timestamp is usable only after confirming that all records agree on the arm.

CREATE TABLE assignment_log (
 unit_id INTEGER, variant VARCHAR, seen_at TIMESTAMP
);
INSERT INTO assignment_log VALUES
 (1,'A','2026-08-01'),(1,'A','2026-08-01'),
 (2,'A','2026-08-01'),(3,'B','2026-08-01'),
 (4,'B','2026-08-01'),(5,'A','2026-08-01'),
 (6,'B','2026-08-01'),(7,'A','2026-08-01'),
 (7,'B','2026-08-02');
CREATE TABLE exposure_log (
 unit_id INTEGER, variant VARCHAR, exposed_at TIMESTAMP
);
INSERT INTO exposure_log VALUES
 (1,'A','2026-08-02'),(1,'A','2026-08-02'),
 (3,'B','2026-08-02'),(4,'A','2026-08-02'),
 (5,'A','2026-08-02'),(7,'A','2026-08-02'),
 (99,'B','2026-08-02');
CREATE TABLE purchases (
 event_id INTEGER, unit_id INTEGER, happened_at TIMESTAMP
);
INSERT INTO purchases VALUES
 (11,1,'2026-08-03'),(11,1,'2026-08-03'),
 (12,2,'2026-08-03'),(13,3,'2026-08-03'),
 (14,4,'2026-08-03'),(15,5,'2026-08-08');

There are seven assigned units, not nine. Unit 7 appears in two arms. Unit 99 has exposure without assignment. Unit 2 purchases without a logged exposure. Unit 4 receives A despite assignment to B. Unit 5 purchases exactly at the excluded seven-day endpoint.

Audit assignment before selecting a canonical row

Produce an audit relation rather than immediately using ROW_NUMBER() = 1. Repeated same-arm rows and conflicting-arm rows require different treatment.

CREATE TEMP VIEW assignment_audit AS
SELECT unit_id,
 COUNT(*) AS log_rows,
 COUNT(DISTINCT variant) AS variant_count,
 SUM(CASE WHEN variant IS NULL OR variant NOT IN ('A','B')
          THEN 1 ELSE 0 END) AS invalid_rows,
 MIN(variant) AS variant,
 MIN(seen_at) AS assigned_at
FROM assignment_log
GROUP BY unit_id;

SELECT unit_id, log_rows, variant_count, invalid_rows
FROM assignment_audit ORDER BY unit_id;

Unit 1 has two records but one valid variant. Unit 7 has two distinct variants and must not become “A” merely because MIN(variant) happens to return A. That field is consumed only after the audit filters establish a unique valid value.

The explicit invalid-row check matters because COUNT(DISTINCT variant) ignores NULL. Without it, an A/NULL pair could look clean. PostgreSQL documents the distinction between counting rows and counting non-NULL expressions. Also validate missing unit IDs and timestamps before this transformation; our fixture deliberately provides both. PostgreSQL aggregate functions

Build one row per stable assigned unit

Reduce exposure separately and use EXISTS for the binary purchase outcome. The exposure rollup records whether anything was delivered and whether a different arm was observed. It does not move a user into the delivered arm.

CREATE TEMP VIEW analysis_units AS
WITH assigned AS (
 SELECT unit_id, variant, assigned_at
 FROM assignment_audit
 WHERE variant_count = 1 AND invalid_rows = 0
), exposure AS (
 SELECT a.unit_id,
  MAX(CASE WHEN e.unit_id IS NOT NULL THEN 1 ELSE 0 END)
    AS has_exposure,
  MAX(CASE WHEN e.variant <> a.variant THEN 1 ELSE 0 END)
    AS cross_arm
 FROM assigned a
 LEFT JOIN exposure_log e ON e.unit_id = a.unit_id
  AND e.exposed_at >= a.assigned_at
  AND e.exposed_at < a.assigned_at + INTERVAL '7 days'
 GROUP BY a.unit_id
)
SELECT a.unit_id, a.variant, x.has_exposure, x.cross_arm,
 CASE WHEN EXISTS (
  SELECT 1 FROM purchases p
  WHERE p.unit_id = a.unit_id
   AND p.happened_at >= a.assigned_at
   AND p.happened_at < a.assigned_at + INTERVAL '7 days'
 ) THEN 1 ELSE 0 END AS converted
FROM assigned a JOIN exposure x ON x.unit_id = a.unit_id;

SELECT * FROM analysis_units ORDER BY unit_id;

Expected rows, shown as (unit, arm, exposed, cross_arm, converted), are (1,A,1,0,1), (2,A,0,0,1), (3,B,1,0,1), (4,B,1,1,1), (5,A,1,0,0), and (6,B,0,0,0).

Both arms have three stable assigned units and two converters. These are descriptive fixture counts, not evidence of no treatment effect. The conflict on unit 7 and crossover on unit 4 still require investigation, and a six-user toy dataset cannot support a launch decision.

Why the convenient joins give different answers

An inner join to exposures removes units 2 and 6. Arm A then has one converter among two exposed units instead of two among three assigned units. The removed A unit converted; the removed B unit did not. This changes the comparison through selection, not through a different treatment implementation.

Joining the raw tables creates another trap. Unit 1 has two assignment rows, two exposure rows, and two purchase rows. A direct three-way join can produce eight records for that single person. COUNT(*) then measures joined combinations rather than users, and summing purchase amounts would overstate revenue.

EXISTS makes repeated purchase rows harmless for this binary outcome only. It is not a general revenue deduplication strategy. For revenue, resolve duplicate event_id records deterministically, quarantine conflicting amounts, sum once per unit, and then join that result to assignment. A final DISTINCT cannot reliably repair a sum already multiplied upstream.

Diagnose contamination without rewriting assignment

Three problems deserve separate labels. Assignment conflict means the allocated arm is ambiguous. Cross-arm exposure means delivery differs from the recorded assignment. Interference means one unit's treatment can affect another unit's outcome, even when both logs are internally consistent.

SQL can surface the first two directly when identifiers and versions are available. It cannot establish the absence of network interference merely because every user has one variant. Marketplace, social, household, and shared-device experiments may need design changes or additional exposure models.

For this fixture, report one conflicting assigned unit, one cross-arm exposed stable unit, and one orphan exposure unit. Preserve the raw evidence and explain whether the trusted assignment service can recover unit 7's original allocation. If it can, rebuild from that authoritative record; if it cannot, describe the limitation rather than silently dropping the unit and claiming randomized validity.

An exposure-only analysis can be appropriate under a carefully designed triggering rule with valid counterfactual logging and assumptions. “Only users who clicked the treatment button” is not automatically such a rule. Post-treatment selection can change the population and the estimand, meaning the effect being estimated.

Run integrity checks before testing significance

Check sample-ratio mismatch on the correct assignment population against the configured allocation. Also compare missingness, join success, invalid timestamps, delivery failures, and observation-window maturity by arm. A balanced final table cannot prove healthy randomization if an earlier filter removed different kinds of users.

Microsoft's post-experiment guidance warns that treatment can alter telemetry reliability and that metric observation units can be imbalanced even when a scorecard-level check looks normal. Use that as a reason to inspect the path from allocation to each metric, not as a reason to “balance” arms by deleting records. Microsoft post-experiment checks

We executed the visible SQL in DuckDB 1.5.2 and asserted all six analysis rows. Removing duplicate telemetry leaves the result unchanged. Adding another valid purchase does not change a user's binary conversion flag. Moving unit 5's purchase one second before the endpoint changes that flag from zero to one.

In production, freeze a data-as-of timestamp alongside the experiment and metric versions. A late purchase can change an old outcome without changing assignment. An identity-map correction can affect several units, so treat it as a versioned reconstruction rather than an invisible dashboard adjustment.

Practice the handoff from data to decision

Question	Practice focus
Analyze A/B Test Results to Inform Stakeholder Decisions	Audit before inference.
Identify Bugs in Python Script for User Assignment	Stable groups.
Debug and harden trial-assignment Python code	Idempotent delivery.
Analyze an A/B test over last 7 days	Window and allocation.
Design and analyze A/B test with interference	Cross-unit effects.

The experimentation-platform design article explains the upstream architecture. For your next SQL A/B test data answer, rehearse the downstream contract: one assignment unit, independent rollups, preserved non-exposure, explicit contamination flags, and a clear stop when data integrity prevents causal interpretation.

Then attempt Analyze A/B Test Results to Inform Stakeholder Decisions, carrying the dataset checks into the recommendation instead of presenting a rate difference alone.

Sources and Further Reading

• Microsoft Research: experiment data quality
• Microsoft Research: pre-experiment patterns
• Microsoft Research: post-experiment patterns
• PostgreSQL aggregate functions

Sources checked September 21, 2026. Fixture, expected rows, and mutation checks are original; no live experiment or proprietary assignment system was tested.