๐ฅ 5 SQL Interview Questions on A/B Testing & Experiment Analysis (Real Data Science Scenarios)
About this lesson
A/B testing is one of the most common data science workflows in companies building products. SQL is usually the first tool used to analyze experiment results before deeper statistical modeling. 1) Measuring conversion rate with SQL Conversion rate is typically calculated as conversions divided by users exposed to each variant. Example table experiment_events with columns: user_id, variant, event. SELECT variant, COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS conversion_rate FROM experiment_events GROUP BY variant; This query tells you whether variant A or B leads to more purchases. 2) Estimating statistical significance with SQL While full statistical tests are often done in Python or R, SQL can calculate the inputs needed. Example aggregated metrics: SELECT variant, COUNT(DISTINCT user_id) AS users, SUM(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS conversions FROM experiment_events GROUP BY variant; These results can be exported to Python to run tests like: Chi-square test t-test Bayesian experiment analysis Tools commonly used with SQL outputs: Python, SciPy, Statsmodels, Jupyter notebooks. 3) Detecting Sample Ratio Mismatch (SRM) SRM happens when experiment traffic is not evenly distributed. Expected example: 50% users โ variant A 50% users โ variant B SQL check: SELECT variant, COUNT(DISTINCT user_id) AS users FROM experiment_events GROUP BY variant; If distribution looks like: A = 70% B = 30% then experiment results may be invalid. 4) Computing experiment metrics Example CTR (click-through rate): SELECT variant, SUM(CASE WHEN event = 'click' THEN 1 ELSE 0 END) * 1.0 / SUM(CASE WHEN event = 'impression' THEN 1 ELSE 0 END) AS ctr FROM experiment_events GROUP BY variant; Example revenue per user: SELECT variant, SUM(revenue) / COUNT(DISTINCT user_id) AS revenue_per_user FROM experiment_revenue GROUP BY variant; These metrics help
DeepCamp AI