# The A/B Verdict

> Variant A or Variant B. The conversion numbers pick the winner.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each experiment on the A/B testing platform, show the count of positive outcomes (where the outcome value is greater than zero), the count of non-positive outcomes, and the average outcome value.

## Worked solution and explanation

### Why this problem exists in real interviews

Pivoting experiment outcomes by variant tests conditional aggregation with `CASE WHEN`. This is the standard pattern for A/B test result comparison in SQL.

> **Trick to Solving**
>
> Use `SUM(CASE WHEN variant = 'X' THEN ...)` for each variant column. This avoids self-joins and produces the pivot in one pass.
> 
> 1. Identify the variants
> 2. Use CASE WHEN inside aggregate functions per variant
> 3. GROUP BY experiment

---

### Break down the requirements

#### Step 1: Identify the pivot structure

Each experiment should be a row, each variant a column, with positive outcomes as the cell value.

#### Step 2: Apply conditional aggregation

Use `SUM(CASE WHEN variant = 'control' AND outcome > 0 THEN 1 ELSE 0 END)` per variant.

#### Step 3: Group by experiment

`GROUP BY exp_name` collapses variant rows into one row per experiment.

---

### The solution

**Conditional aggregation pivot for experiment variants**

```sql
SELECT exp_name,
       SUM(CASE WHEN variant = 'control' AND outcome > 0 THEN 1 ELSE 0 END) AS control_conversions,
       SUM(CASE WHEN variant = 'treatment' AND outcome > 0 THEN 1 ELSE 0 END) AS treatment_conversions
FROM experiments
GROUP BY exp_name
ORDER BY exp_name
```

> **Cost Analysis**
>
> Single pass with conditional counters. Experiment tables are typically small.

> **Interviewers Watch For**
>
> The interviewer checks whether you compute per-variant metrics in a single query rather than separate queries.

> **Common Pitfall**
>
> Dividing treatment by control without NULLIF causes division-by-zero if control has zero conversions.

---

## Common follow-up questions

- How would you add a lift column? _(Tests (treatment - control) / NULLIF(control, 0).)_
- What if there are more than two variants? _(Tests extending the CASE pattern.)_
- How would you add statistical significance? _(Tests awareness of chi-squared tests.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/experiment_conversion_pivot)
- [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.