A/B Test Assignment and Metrics
Joining experiment assignment to outcomes and computing per-variant metrics.
A/B Test Assignment and Metrics is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What an A/B Test Question Tests
A/B test questions check whether you can correctly join experiment assignment to outcomes and compute a clean per-variant metric.
The trap is almost always in the join: counting outcomes for users who were never enrolled, or double-counting users assigned twice. Get the assignment join right and the metrics are easy arithmetic.
The Two Tables You Get
Expect an assignment table and an outcome table:
assignments(user_id, variant, assigned_at)where variant is 'control' or 'treatment'.orders(user_id, order_id, amount, created_at)or a generic events table.
Assignment is the source of truth for who is in the experiment. Outcomes only count if the user appears in assignment.
CREATE TABLE assignments (
user_id INT,
variant VARCHAR(20),
assigned_at TIMESTAMP
);
CREATE TABLE orders (
user_id INT,
order_id INT,
amount NUMERIC,
created_at TIMESTAMP
);Start From Assignment, LEFT JOIN Outcomes
The cardinal rule: drive from the assignment table and LEFT JOIN outcomes. This keeps users who were enrolled but never converted, which you need for an honest denominator.
An INNER JOIN would silently drop non-converters and inflate your conversion rate.
SELECT
a.user_id,
a.variant,
o.order_id
FROM assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id;Counting Conversion Per Variant
Conversion rate = converted users / assigned users, per variant. Count distinct converters in the numerator and all assigned users in the denominator.
Use COUNT(DISTINCT ...) on the order user so a user with three orders still counts as one converter.
SELECT
a.variant,
COUNT(DISTINCT a.user_id) AS assigned,
COUNT(DISTINCT o.user_id) AS converters,
ROUND(100.0 * COUNT(DISTINCT o.user_id)
/ COUNT(DISTINCT a.user_id), 2) AS conv_rate_pct
FROM assignments a
LEFT JOIN orders o ON o.user_id = a.user_id
GROUP BY a.variant;The Double-Assignment Trap
What if a user appears twice in assignments, once in each variant? Your join now counts them on both sides and the experiment is contaminated.
Interviewers plant this. Defend against it: deduplicate assignment to one variant per user, typically the first assignment, before joining.
WITH dedup AS (
SELECT user_id, variant,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY assigned_at) AS rn
FROM assignments
)
SELECT user_id, variant
FROM dedup
WHERE rn = 1;Only Count Outcomes After Assignment
An order placed before the user was assigned cannot be caused by the experiment. Add a time guard: the outcome must occur at or after assigned_at.
Put this condition in the ON clause of the LEFT JOIN so non-converters are still preserved.
SELECT
a.variant,
COUNT(DISTINCT a.user_id) AS assigned,
COUNT(DISTINCT o.user_id) AS converters
FROM assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id
AND o.created_at >= a.assigned_at
GROUP BY a.variant;ON vs WHERE in the Outcome Join
This is a guaranteed follow-up. If you move o.created_at >= a.assigned_at into WHERE, you turn the LEFT JOIN into an inner join: rows where the user never ordered have o.created_at = NULL, the predicate is UNKNOWN, and they vanish.
Keep outcome-filtering conditions in ON to preserve non-converters in the denominator.
Per-Variant Revenue Metrics
Beyond conversion, interviewers ask for revenue per user (ARPU) and revenue per converter. Sum the amount, then divide by the right denominator.
ARPU divides by all assigned users; revenue per converter divides only by users who ordered. Be explicit about which the business wants.
SELECT
a.variant,
COUNT(DISTINCT a.user_id) AS assigned,
COALESCE(SUM(o.amount), 0) AS revenue,
ROUND(COALESCE(SUM(o.amount), 0)
/ COUNT(DISTINCT a.user_id), 2) AS arpu
FROM assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id
AND o.created_at >= a.assigned_at
GROUP BY a.variant;The Two-Level Aggregation Pattern
When a metric is "average orders per user", do not compute it in one pass, you would mix user-level and order-level grains. Aggregate to the user level first, then average across users.
This per-user-then-per-variant pattern is the correct grain and a common interview discriminator.
WITH per_user AS (
SELECT a.variant, a.user_id,
COUNT(o.order_id) AS orders_cnt
FROM assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id
AND o.created_at >= a.assigned_at
GROUP BY a.variant, a.user_id
)
SELECT variant, ROUND(AVG(orders_cnt), 3) AS avg_orders_per_user
FROM per_user
GROUP BY variant;A Complete, Defensible Query
Combine everything: dedupe to first assignment, drive from assignment, time-guard outcomes in ON, and report conversion plus ARPU per variant. Narrate each guard as you write it.
WITH enrolled AS (
SELECT user_id, variant, assigned_at
FROM (
SELECT user_id, variant, assigned_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY assigned_at) AS rn
FROM assignments
) x WHERE rn = 1
)
SELECT
e.variant,
COUNT(DISTINCT e.user_id) AS assigned,
COUNT(DISTINCT o.user_id) AS converters,
ROUND(100.0 * COUNT(DISTINCT o.user_id)
/ COUNT(DISTINCT e.user_id), 2) AS conv_pct,
ROUND(COALESCE(SUM(o.amount),0)
/ COUNT(DISTINCT e.user_id), 2) AS arpu
FROM enrolled e
LEFT JOIN orders o
ON o.user_id = e.user_id
AND o.created_at >= e.assigned_at
GROUP BY e.variant;Sanity Checks Interviewers Expect
Before quoting results, validate the experiment setup:
- Are the variant sizes roughly balanced? A 90/10 split when 50/50 was intended signals a bug.
- Did any user land in both variants? Count users with more than one distinct variant.
- Are there assignments with no possible outcome window (assigned after the data cutoff)?
Offering these checks unprompted shows analytical maturity.
SELECT user_id, COUNT(DISTINCT variant) AS variant_count
FROM assignments
GROUP BY user_id
HAVING COUNT(DISTINCT variant) > 1;Quick Check
You compute conversion per variant by LEFT JOINing orders to assignments, but you put o.created_at >= a.assigned_at in the WHERE clause. What happens?
Recap: A/B Test Assignment and Metrics
You now have a defensible experiment-analysis playbook:
- Treat assignment as the source of truth; LEFT JOIN outcomes.
- Deduplicate to one variant per user (first assignment).
- Time-guard outcomes in the ON clause, never WHERE, to keep non-converters.
- Pick the right denominator for conversion vs ARPU vs revenue-per-converter.
- Aggregate to user grain first for per-user averages.
- Run sanity checks on split balance and cross-assignment.
Next: turning these per-variant metrics into lift, significance, and guardrails.
Frequently asked questions
Is the “A/B Test Assignment and Metrics” lesson free?
Yes — the full text of “A/B Test Assignment and Metrics” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “A/B Test Assignment and Metrics”?
Joining experiment assignment to outcomes and computing per-variant metrics. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “A/B Test Assignment and Metrics” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Building a Multi-Step Funnel
- Ordered Events and Time Windows
- A/B Test Assignment and Metrics
- Lift, Significance and Guardrails in SQL