SQL and Experiment Case Study

Describe how you would combine basic SQL joins and window functions with A/B testing to answer a case study question.

Use the supplied assignment, user, and order data. Calculate variant-level conversion and order metrics, compare each variant with control group A, and rank variants by conversion rate.

Output

1. One row per experiment variant.
2. Include variant, assigned_users, converted_users, conversion_rate, total_orders, total_revenue, avg_order_value, lift_vs_control, and variant_rank.
3. Include variants with no orders, treat an assigned user with at least one order as converted, and sort by variant_rank, then variant.

Output details

• Count each assigned user only once within each variant.
• Rank conversion rates in descending order, giving tied rates the same rank and leaving gaps afterward.
• Sort tied ranks by variant in ascending lexicographic order.
• Preserve full numeric precision without rounding conversion_rate, avg_order_value, or lift_vs_control.
• Set total_revenue to 0 when a variant has no orders and avg_order_value to NULL.
• Calculate lift_vs_control as the relative conversion-rate change from control A, yielding NULL when the control rate is zero.