SQL interview question: A/B test conversion rate by platform
Problem
Shared data rulesCompare order conversion between new_cart control and treatment, for each platform and for all participants together. Converted = placed an order within 7 days after assignment.
Formula
diff_pp = (conv_treatment − conv_control) × 100
Example
users| user_id | platform |
|---|---|
| 1 | ios |
| 2 | ios |
| 3 | android |
| 4 | android |
| 5 | android |
experiments| user_id | experiment_name | variant | assigned_at | Row note |
|---|---|---|---|---|
| 1 | new_cart | control | 2026-09-10 12:00 | |
| 2 | new_cart | treatment | 2026-09-10 15:00 | |
| 3 | new_cart | control | 2026-09-11 11:00 | |
| 4 | new_cart | treatment | 2026-09-12 13:00 | |
| 5 | new_cart | control | 2026-09-13 10:00 | |
| 5 | new_cart | treatment | 2026-09-16 10:00 | ← repeat assignment — ignored |
- Row 6: repeat assignment — ignored
orders| order_id | user_id | created_at | status | Row note |
|---|---|---|---|---|
| 101 | 1 | 2026-09-09 18:00 | delivered | ← before assignment |
| 102 | 1 | 2026-09-17 12:30 | delivered | ← 7 d 30 min later — late |
| 103 | 2 | 2026-09-13 13:00 | delivered | |
| 104 | 3 | 2026-09-14 10:00 | delivered | |
| 105 | 5 | 2026-09-14 15:00 | delivered | ← within the first assignment’s window |
- Order 101: before assignment
- Order 102: 7 d 30 min later — late
- Order 105: within the first assignment’s window
| segment | control_users | treatment_users | conv_control | conv_treatment | diff_pp |
|---|---|---|---|---|---|
| ios | 1 | 1 | 0 | 1 | 100 |
| android | 2 | 1 | 1 | 0 | -100 |
| all | 3 | 2 | 0.6667 | 0.5 | -16.67 |
all: 2 of 3 converted in control and 1 of 2 in treatment: (0.5 − 0.6667) × 100 = −16.67.
Output columns
segment- the participant’s platform (users.platform), or all for everyone
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.