A/Bテストの割り当てと指標
実験の割り当て結果を成果データに結合し、バリアントごとの指標を計算します。
「A/Bテストの割り当てと指標」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
A/Bテストの質問で問われること
A/Bテストに関する質問では、実験の割り当てを結果に正しく結合し、バリアントごとの指標を適切に計算できるかが確認されます。
落とし穴はほとんどの場合、結合にあります。実験に登録されていないユーザーの結果を数えたり、2回割り当てられたユーザーを二重に数えたりする可能性があります。割り当ての結合を正しく行えば、指標の計算は簡単な算術になります。
与えられる2つのテーブル
割り当てテーブルと結果テーブルが与えられると考えてください。
assignments(user_id, variant, assigned_at)。variant は 'control' または 'treatment' です。orders(user_id, order_id, amount, created_at)、または汎用的なイベントテーブルです。
実験の対象者を決める正しい情報源は割り当てです。結果は、ユーザーが割り当てテーブルに存在する場合にのみカウントします。
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
);割り当てから始めて、結果を LEFT JOIN
基本原則は、割り当てテーブルを起点にすることと、結果を LEFT JOIN することです。これにより、登録されたもののコンバージョンしなかったユーザーも保持でき、正確な分母を作れます。
INNER JOIN を使うと、コンバージョンしなかったユーザーが暗黙に除外され、コンバージョン率が過大になります。
SELECT
a.user_id,
a.variant,
o.order_id
FROM assignments a
LEFT JOIN orders o
ON o.user_id = a.user_id;バリアントごとのコンバージョン数を数える
バリアントごとのコンバージョン率は、コンバージョンしたユーザー数を割り当てられたユーザー数で割ったものです。分子ではコンバージョンしたユーザーを重複なく数え、分母では割り当てられたすべてのユーザーを数えます。
注文側のユーザーに対して COUNT(DISTINCT ...) を使うと、1人のユーザーが3件注文していても、コンバージョンしたユーザー1人として数えられます。
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;二重割り当ての罠
1人のユーザーが割り当てテーブルに2回、それぞれ異なるバリアントで登場したらどうなるでしょうか。結合すると、そのユーザーが両方の側で数えられ、実験が汚染されます。
これは面接でよく仕込まれる論点です。結合前に、ユーザーごとに1つのバリアントだけが残るよう割り当てを重複排除してください。通常は最初の割り当てを使います。
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;割り当て後の結果だけをカウント
ユーザーが割り当てられる前に行った注文は、実験によって生じたものではありません。結果は assigned_at 以降に発生していなければならないという時間条件を追加します。
この条件は LEFT JOIN の ON 句に記述し、コンバージョンしなかったユーザーも保持されるようにします。
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 と WHERE
これは必ず聞かれる追加質問です。o.created_at >= a.assigned_at を WHERE に移すと、LEFT JOIN が内部結合に変わります。ユーザーが注文していない行では o.created_at = NULL となり、述語の評価結果が UNKNOWN になるため、その行が消えてしまいます。
コンバージョンしなかったユーザーを分母に残すため、結果を絞り込む条件は ON に記述してください。
バリアントごとの売上指標
コンバージョン以外にも、面接ではユーザーあたりの売上（ARPU）やコンバージョンユーザーあたりの売上が求められます。金額を合計してから、適切な分母で割ります。
ARPU は割り当てられたすべてのユーザー数で割り、コンバージョンユーザーあたりの売上は注文したユーザー数だけで割ります。ビジネスがどちらを求めているのかを明確にしてください。
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;2段階集計のパターン
指標が「ユーザーあたりの平均注文数」の場合、1回の集計で計算しないでください。ユーザーレベルと注文レベルの粒度が混在してしまいます。まずユーザーレベルに集計し、その後ユーザー間で平均を取ります。
この「ユーザーごとに集計してからバリアントごとに集計する」パターンが正しい粒度であり、面接でよく使われる見極めポイントです。
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;完全で説明可能なクエリ
すべてを組み合わせます。最初の割り当てだけが残るよう重複排除し、割り当てを起点にして、ON で結果に時間条件を付け、バリアントごとにコンバージョン率とARPUを出力します。各条件を記述する際に、その目的も説明してください。
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;面接官が期待する健全性チェック
結果を提示する前に、実験の設定を検証します。
- バリアントの規模はおおむね均衡していますか。50/50を意図していたのに90/10になっている場合は、バグの兆候です。
- 両方のバリアントに入ったユーザーはいませんか。異なるバリアントが複数割り当てられたユーザー数を数えます。
- データの締め切り後に割り当てられるなど、結果を観測できる期間が存在しない割り当てはありませんか。
このようなチェックを求められる前に提案できると、分析力の高さを示せます。
SELECT user_id, COUNT(DISTINCT variant) AS variant_count
FROM assignments
GROUP BY user_id
HAVING COUNT(DISTINCT variant) > 1;クイックチェック
バリアントごとのコンバージョン率を計算するため、orders を assignments に LEFT JOIN し、o.created_at >= a.assigned_at を WHERE 句に入れました。何が起こるでしょうか？
振り返り：A/Bテストの割り当てと指標
これで、根拠を説明できる実験分析の手順が整いました。
- 割り当てを正しい情報源として扱い、結果を LEFT JOIN します。
- ユーザーごとに1つのバリアント（最初の割り当て）だけが残るよう重複排除します。
- コンバージョンしなかったユーザーを残すため、結果の時間条件は必ず ON 句に記述し、WHERE には記述しません。
- コンバージョン率、ARPU、コンバージョンユーザーあたりの売上に適した分母を選びます。
- ユーザーあたりの平均値を求める場合は、まずユーザー粒度に集計します。
- 配分の均衡や重複した割り当てについて健全性チェックを実行します。
次は、これらのバリアントごとの指標をリフト、有意性、ガードレールへ発展させます。
よくある質問
「A/Bテストの割り当てと指標」レッスンは無料ですか？
はい。「A/Bテストの割り当てと指標」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「A/Bテストの割り当てと指標」で何を学びますか？
実験の割り当て結果を成果データに結合し、バリアントごとの指標を計算します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「A/Bテストの割り当てと指標」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 多段階ファネルの作成
- 順序付きイベントと時間枠
- A/Bテストの割り当てと指標
- SQLにおけるリフト、統計的有意性、ガードレール