# Parallel Traces > Same experiment. Different variants. Who overlaps? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## 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. ## 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 **Self-join with an inequality for unique pairs** ```sql 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 ``` > **Trick to solving** > > The inequality is doing two jobs at once. `e1.user_id` < `e2.user_id` removes the row-against-itself match AND picks a single canonical ordering for every pair, so (100, 876) survives but (876, 100) never appears. Swap it for != and you keep both self-matches out but still emit each pair twice. > **Interviewers watch for** > > Whether the candidate reaches for `user_id` < `user_id` rather than `user_id` != `user_id`. Both exclude self-matches, but only the strict inequality deduplicates the mirror-image orderings. Watching which one they type is the fastest read on whether they have internalized the self-join pair pattern. > **Common pitfall** > > Dropping the inequality entirely returns every pair in both orders plus every user paired with itself. Using != instead of < leaves the doubling in place. And forgetting DISTINCT lets a user who ran the same experiment twice inflate a single pair into several identical rows. > **Cost analysis** > > Self-join on 3M rows. The equality predicates on (`exp_name`, platform) drive the match, and a composite index on (`exp_name`, platform, variant, `user_id`) lets the engine probe partners without a full scan per row. Popular experiments still fan out heavily, so the intermediate result for a hot `exp_name` is the real cost driver. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/parallel_traces) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.