Pricing Page A/B Test Conversion by Variant — SQL Problem
The growth team ran the experiment pricing_page_v2, splitting users between a control and a treatment pricing page.
- Difficulty: Medium
- Topics: Conditional Logic, Joins, Aggregation
- Dialect: MySQL
- Problem: #407
Problem statement
The growth team ran the experiment pricing_page_v2, splitting users between a control and a treatment pricing page. A user converted in the test when they have at least one conversion at or after the moment they were assigned; a purchase before assignment was not caused by the page, and a second purchase still counts the user once. Assignments to other experiments are irrelevant.
Return one row per variant of pricing_page_v2 with variant, users (users assigned), converters and conversion_rate_pct = 100 × converters ÷ users, rounded to 2 decimals. Order by variant.
Tables
Table: experiment_assignments
| Column | Type |
|---|---|
| user_id | int |
| experiment | varchar |
| variant | enum(control, treatment) |
| assigned_at | datetime |
Primary key: user_id, experiment.
A user is assigned at most once per experiment.
Table: conversions
| Column | Type |
|---|---|
| conversion_id | int |
| user_id | int |
| plan | varchar |
| converted_at | datetime |
Primary key: conversion_id.
One row per purchase of a paid plan.
Examples
Example 1
experiment_assignments
| user_id | experiment | variant | assigned_at |
|---|---|---|---|
| 1 | pricing_page_v2 | control | 2024-05-01 10:00:00 |
| 2 | pricing_page_v2 | treatment | 2024-05-01 10:05:00 |
| 3 | pricing_page_v2 | control | 2024-05-01 11:00:00 |
| 4 | pricing_page_v2 | treatment | 2024-05-02 09:00:00 |
| 5 | pricing_page_v2 | treatment | 2024-05-02 12:00:00 |
| 6 | pricing_page_v2 | control | 2024-05-03 08:00:00 |
| 8 | pricing_page_v2 | treatment | 2024-05-03 09:30:00 |
| 1 | onboarding_checklist | treatment | 2024-04-20 10:00:00 |
conversions
| conversion_id | user_id | plan | converted_at |
|---|---|---|---|
| 1 | 2 | growth | 2024-05-03 14:00:00 |
| 2 | 5 | starter | 2024-04-28 09:00:00 |
| 3 | 3 | starter | 2024-05-04 10:00:00 |
| 4 | 2 | growth | 2024-05-20 10:00:00 |
| 5 | 7 | growth | 2024-05-05 16:00:00 |
| 6 | 8 | starter | 2024-05-03 09:30:00 |
Output
| variant | users | converters | conversion_rate_pct |
|---|---|---|---|
| control | 3 | 1 | 33.33 |
| treatment | 4 | 2 | 50 |
How to solve Pricing Page A/B Test Conversion by Variant
The denominator is everyone assigned to a variant of pricing_page_v2, so the query is driven by experiment_assignments filtered to that experiment. Each user is LEFT JOINed to the conversions that happened at or after their own assignment — the time condition sits in the ON clause, so a user whose only purchase came before the test keeps one row with NULLs and still counts as a non-converter.
COUNT(DISTINCT a.user_id) is the variant's size and COUNT(DISTINCT c.user_id) its converters: DISTINCT folds a user's repeat purchases into one, and the NULLs of non-converters are ignored by COUNT. The rate is computed from those counts and rounded to two decimals, as MySQL's division would otherwise return four.
Reducing to one row per user with a 0/1 flag — through EXISTS or MAX(CASE …) over a plain LEFT JOIN — then summing the flags is the same arithmetic, and is the shape you would want before running a significance test. Purchases by users never assigned (user 7) never join, because the drive is from assignments. The cost is one pass over the assignments with an index lookup into conversions per user.
Reference solution (MySQL)
SELECT a.variant,
COUNT(DISTINCT a.user_id) AS users,
COUNT(DISTINCT c.user_id) AS converters,
ROUND(100 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT a.user_id), 2) AS conversion_rate_pct
FROM experiment_assignments a
LEFT JOIN conversions c
ON c.user_id = a.user_id AND c.converted_at >= a.assigned_at
WHERE a.experiment = 'pricing_page_v2'
GROUP BY a.variant
ORDER BY a.variantAnother way
SELECT variant, COUNT(*) AS users, SUM(converted) AS converters, ROUND(100 * SUM(converted) / COUNT(*), 2) AS conversion_rate_pct
FROM (
SELECT a.variant, CASE WHEN EXISTS (SELECT 1 FROM conversions c WHERE c.user_id = a.user_id AND c.converted_at >= a.assigned_at) THEN 1 ELSE 0 END AS converted
FROM experiment_assignments a WHERE a.experiment = 'pricing_page_v2'
) t
GROUP BY variant ORDER BY variantAnother way
WITH per_user AS (
SELECT a.user_id, a.variant, MAX(CASE WHEN c.converted_at >= a.assigned_at THEN 1 ELSE 0 END) AS converted
FROM experiment_assignments a LEFT JOIN conversions c ON c.user_id = a.user_id
WHERE a.experiment = 'pricing_page_v2'
GROUP BY a.user_id, a.variant
)
SELECT variant, COUNT(*) AS users, SUM(converted) AS converters, ROUND(100 * SUM(converted) / COUNT(*), 2) AS conversion_rate_pct
FROM per_user GROUP BY variant ORDER BY variant← Days From Signup to First Captured Payment · Monthly Paid Revenue and Its Change From Last Month →