SQL for A/B Test Results | Dataford Interview Questions

Write SQL queries to analyze the results of a completed A/B test. Asked in the Round 1 (SQL + product) stage. The expected analysis uses the completed experiment and its assigned users and orders.

Write a SQL query for experiment experiment_id = 100.

Output

One row per variant.
Columns: variant, assigned_users, purchasing_users, conversion_rate, total_orders, total_revenue, and average_order_value.
Include all variants, including those with no orders, and count only orders during the experiment dates.
Sort by variant ascending.

Schema

experiments

Column	Type	Description
experiment_idPK	INT	Unique experiment identifier
experiment_name	VARCHAR(100)	Experiment name
status	VARCHAR(20)	Experiment lifecycle status
start_date	DATE	First date included in the experiment
end_date	DATE	Last date included in the experiment

experiment_assignments

Column	Type	Description
assignment_idPK	INT	Unique assignment identifier
experiment_id	INT	Experiment receiving the assignment
user_id	INT	Assigned user
variant	VARCHAR(20)	Experiment variant

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_amount	DECIMAL(10,2)	Order revenue amount

Your question is SQL for A/B Test Results. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem