179 questions
No questions match those filters.
How do you analyze an A/B test in SQL, and what has to...
This is one of the questions in the full AI/ML interview bank. Pro unlocks all 1789 questions; Premium includes the same bank plus the highest daily Practice limit.
See plansThe join itself has a subtlety that’s easy to get wrong: outcomes have to be attributed within a bounded window after assignment, not just “did this user ever convert,” because an unbounded lookback lets pre-existing behavior or a much-later, unrelated conversion leak into the experiment’s numbers. LEFT JOIN orders o ON a.user_id = o.user_id AND o.order_date BETWEEN a.assignment_date AND a.assignment_date + INTERVAL '14 days' fixes the attribution window explicitly, and the LEFT JOIN (not INNER JOIN) matters too — users who never converted still need to appear in the denominator with zero orders, not disappear from the analysis entirely.
The sample ratio mismatch check is the single highest-value five minutes spent on any experiment analysis: SELECT variant, COUNT(*), COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () FROM ab_assignments GROUP BY variant should return roughly the intended split (50/50, 90/10, whatever was configured). If it doesn’t — say control has 60% of users instead of the intended 50% — something upstream broke the randomization (a caching layer serving stale assignments, a bug in the bucketing hash, a filter applied asymmetrically), and the conversion-rate comparison downstream is confounded by whatever caused the imbalance, not a valid read on the treatment effect.
Guardrail metrics get the same per-variant aggregation as the primary metric and are checked in the same query, because a launch decision needs both together: a treatment that lifts conversion by 5% but doubles P95 latency or triples hallucination rate is very likely not a launch, even though the primary metric technically won. Computing the actual significance test (a two-proportion z-test or a Bayesian equivalent) is normally handed off to Python once the clean per-variant aggregates are pulled from SQL, since that’s a statistics problem rather than a data-wrangling one — but the aggregates themselves, and the SRM check that validates they’re trustworthy, are squarely SQL’s job.