# Experiment Impact

> Which experiments moved the needle? Rank them within each group.

Canonical URL: <https://datadriven.io/problems/experiment_impact>

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

Data science wants to rank A/B test performance inside each variant. For each (exp_name, variant) pair in experiments, compute the average outcome and participant count, skipping rows where outcome is NULL. Within each variant, rank experiments by average outcome from best to worst with ties sharing a position. Return the exp_name, variant, average outcome, participant count, and position.

## Worked solution and explanation

### Why this problem exists in real interviews

Ranking experiment outcomes within each variant tests window functions with PARTITION BY. The interviewer checks whether you can apply per-group ranking.

---

### Break down the requirements

#### Step 1: Rank by outcome within each experiment-variant pair

Use `DENSE_RANK() OVER (PARTITION BY exp_name, variant ORDER BY outcome DESC)` to rank users by outcome.

#### Step 2: Return the ranked results

Include experiment name, variant, user, outcome, and rank.

---

### The solution

**Window function ranking within variant groups**

```sql
SELECT exp_name, variant, user_id, outcome,
       DENSE_RANK() OVER (PARTITION BY exp_name, variant ORDER BY outcome DESC) AS rnk
FROM experiments
ORDER BY exp_name, variant, rnk
```

> **Cost Analysis**
>
> Window function sort within each (exp_name, variant) partition. An index on `(exp_name, variant, outcome)` helps.

> **Interviewers Watch For**
>
> The interviewer checks that PARTITION BY includes both exp_name and variant.

> **Common Pitfall**
>
> Forgetting `variant` in PARTITION BY would rank across all variants, not within each.

---

## Common follow-up questions

- How would you return only the top 3 per variant? _(Tests adding WHERE rnk <= 3 in an outer query.)_
- How would you handle ties? _(Tests DENSE_RANK vs. RANK vs. ROW_NUMBER.)_
- How would you compare top performers across variants? _(Tests cross-variant join.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/experiment_impact)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.