Parallel Traces

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Airbnb

Problem

The experimentation team is looking for cross-variant contamination. Pair users who participated in the same experiment under different variants but on the same platform. Show both user IDs for each qualifying pair, running the list up from the lowest-numbered user, and within each user up from their lowest-numbered partner.

Same experiment. Different variants. Who overlaps?

Tables

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

Expected output

Worked solution and explanation

What this problem really is

This is a self-join with an inequality dressed up as experiment-contamination detection. The skill being probed: can you join a table to itself so each qualifying pair of users surfaces exactly once? Anyone can match rows on the same exp_name and platform with differing variants. The trap is the pair math: without e1.user_id < e2.user_id, every pair comes back twice (once in each order) and every row also matches itself. Get that wrong and your contamination report double-counts every collision and flags users as colliding with themselves.

Break down the requirements

Step 1: Line up the same experiment on the same platform

Join experiments to a second copy of itself, keeping only rows that share the same exp_name and the same platform. This is the surface where two participations could collide.

Step 2: Force the variants to differ

Add variant != variant so the two sides are genuinely different treatments. Same variant is not contamination, it is just the same arm of the test.

Step 3: Emit each pair once

Add user_id < user_id. This is the quiet workhorse: it drops the self-match (a row against itself) and collapses the two mirror-image orderings of a pair down to one. DISTINCT then absorbs any repeat participation by the same users.

The solution

SELECT DISTINCT e1.user_id AS user_id1, e2.user_id AS user_id2
FROM experiments e1
JOIN experiments e2
  ON e1.exp_name = e2.exp_name
  AND e1.platform = e2.platform
  AND e1.variant != e2.variant
  AND e1.user_id < e2.user_id
ORDER BY user_id1, user_id2

Common follow-up questions

• How would you also show which experiment and platform each pair shares? (Add `exp_name` and platform to the SELECT so each contaminated pair carries its shared context.)
• What if you needed to count the number of contaminated pairs per experiment? (Wrap the pair query and GROUP BY `exp_name` with COUNT(*).)
• How would you handle users who appear in more than two variants of one experiment? (The self-join already handles this: a user in three variants produces one pair per distinct pairing of those variants.)