SQL Interview Prep · レッスン

最初のアクションによるコホート定義

Q: 「最初のアクションによるコホート定義」で何を学びますか？

各ユーザーを、最初のイベントが発生した日付に基づいてコホートに割り当てます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

各ユーザーを、最初のイベントが発生した日付に基づいてコホートに割り当てます。

「最初のアクションによるコホート定義」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

コホートが面接で登場する理由

プロダクト分析の面接官が「コホートを作成してください」と言うとき、各ユーザーが初めて何かを行った時期に基づいて全ユーザーをグループ化し、そのグループを時間の経過に沿って追跡できるかを確認しています。

コホートとは、同じ期間に同じ開始イベントを経験したユーザーの集合です。通常は、初回購入、サインアップ、ログインなどが開始イベントになります。コホートの強みは、ユーザーを同じ条件で比較できることです。1月コホートの全員を、それぞれの1月の開始時点から測定できます。

最初に身につけるべきスキルであり、このレッスンで練習するのは、各ユーザーの初回アクション日を確実に計算することです。

ソーステーブル

ほぼすべてのコホート問題は、イベントテーブルから始まります。これは、タイムスタンプ付きのユーザーアクションを1行ずつ記録したテーブルです。たとえば、events テーブルには次のようなカラムがあります。

user_id — アクションを行ったユーザー
event_type — 行ったこと
event_at — アクションを行った時刻を表すタイムスタンプ

面接では、データの粒度を声に出して確認しましょう。「これはイベントごとに1行で、1人のユーザーが複数回登場する可能性がありますか？」答えはほぼ必ず「はい」です。だからこそ、ユーザーごとの初回アクションに集約する必要があります。

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

初回アクション = タイムスタンプの MIN

基本的な操作は単純です。user_id でグループ化し、MIN(event_at) を取得します。この最小値がユーザーの初回アクション、つまりコホートに割り当てる時点です。

これは、ウィンドウ関数を使った凝った解法に進む前に、面接官がまず聞きたい答えです。単純な GROUP BY は正しく、読みやすく、高速です。

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

基準となるイベントへの絞り込み

コホートは、すべてのイベントではなく、特定のアクションによって定義されることがよくあります。「初回購入によってユーザーをコホート化する」とは、最小値を取得する前に購入行だけに絞り込む必要があるということです。

フィルターは WHERE に記述し、MIN が条件を満たす行だけを見るようにします。よくある面接の落とし穴は、すべてのイベントに対して MIN を取得してから絞り込むことです。これでは、購入前に閲覧していたユーザーに誤った開始日が割り当てられます。

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

コホート期間へのバケット化

コホートは通常、正確なタイムスタンプではなく期間です。たとえば「2024-03 コホート」や「2024-03-04 の週」です。初回アクション日を、対象期間の単位まで切り捨てます。

Postgres では DATE_TRUNC('month', ...) を使います。MySQL では DATE_FORMAT(d, '%Y-%m-01')、SQL Server では DATETRUNC(month, d) または計算によって月初日を求める方法を使えます。面接では使用する SQL 方言を明示すると、構文の選択に意図があることが伝わります。

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

CTE にまとめる

ユーザーごとのコホート割り当ては、リテンションのクエリで再利用する構成要素です。そのため、分かりやすい名前を付けた CTE にまとめましょう。これにより後続の処理が読みやすくなり、再利用可能な部品として考えていることも面接官に示せます。

ここから先のクエリでは、user_cohort に結合することで、各ユーザーがどのグループに属するかを確認できます。

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

コホートサイズ: メンバー数を数える

面接官が最初に期待する妥当性確認は、コホートサイズ、つまり各コホートに何人のユーザーが属するかです。割り当てを行う CTE を cohort_month でグループ化し、ユーザー数を重複なく数えます。

CTE がすでにユーザーごとに1行になっていても、念のため COUNT(DISTINCT user_id) を使いましょう。データの粒度を意識していることが伝わります。このカウントは、後でリテンション率を計算するときの分母になります。

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

ウィンドウ関数を使う方法

面接では、集約したテーブルではなく、すべてのイベント行にコホートラベルを付けるよう求められることがあります。この場合はウィンドウ関数が役立ちます。MIN(event_at) OVER (PARTITION BY user_id) を使えば、行を削除せずに初回アクションを計算できます。

イベントの詳細とコホートタグの両方を1回の処理で取得したい場合に便利で、リテンションを集計する準備にもなります。

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

同値と重複による落とし穴

ユーザーに、最も早い時刻がまったく同じイベントが2件ある場合はどうなるでしょうか。MIN なら問題ありません。何行が同じ最小値になっていても、最小値を1つだけ返すため、コホートの割り当てはユーザーごとに1つに保たれます。

これに対して、ROW_NUMBER() ... ORDER BY event_at を使う方法では、同値の行が任意に決められます。安定した結果を得るには、event_id のような決定的なタイブレーカーを追加する必要があります。このトレードオフを促される前に説明できると、シニアレベルの視点が伝わります。

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

タイムゾーンと日付の境界

微妙な面接問題として、ニューヨークで午後11時30分に行われた購入が、UTC では翌日になるケースがあります。コホートを暦日で分類する場合、ユーザーがどのコホートに入るかはタイムゾーンによって決まります。

堅実な回答は、タイムスタンプを UTC で保存し、日単位に切り捨てる前にビジネス上のタイムゾーンへ変換することです。メトリクスにおける「1日」をどのタイムゾーンで定義するのかを明確に述べてください。その1つの決定によって、何千人ものユーザーが別のコホートに移る可能性があります。

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

対象期間より前のユーザーを除外する

実際の分析では、コホートの期間を「第1四半期に開始したコホート」のように日付範囲で限定します。絞り込む対象は集約後の初回アクション日です。つまり、HAVING 句、または CTE に対する外側のフィルターを使い、元のイベントに対する WHERE では絞り込みません。

元のイベントを日付で絞り込むと、12月に初めて購入したものの第1四半期にもアクションを行ったユーザーが、第1四半期のコホートに誤って入ってしまいます。必ず計算済みの初回アクションを基準に対象範囲を制限してください。

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

クイックチェック

面接官から次のように質問されました。「ユーザーを初回購入月でコホート化してください。購入前に閲覧するユーザーもいます。」どの方法が正しいでしょうか。

まとめ: コホートを定義する

コホート定義に関する面接問題の重要なポイントは次のとおりです。

コホートは、ユーザーを最初の条件に合うアクションによってグループ化します。
基準となるイベントを WHERE で絞り込んだ後、MIN(event_at) で計算します。
DATE_TRUNC（または SQL 方言に応じた同等の関数）で期間単位に分類します。
割り当てを CTE にまとめて再利用し、COUNT(DISTINCT user_id) でコホートサイズを求めます。
タイムゾーンによる日付の境界に注意し、日付範囲は元のイベントではなく、計算済みの初回アクションを基準に制限します。

ここを確実に身につければ、次のレッスンのリテンションマトリクスは結合だけで作れるようになります。

よくある質問

「最初のアクションによるコホート定義」レッスンは無料ですか？

はい。「最初のアクションによるコホート定義」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「最初のアクションによるコホート定義」で何を学びますか？

各ユーザーを、最初のイベントが発生した日付に基づいてコホートに割り当てます。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン1/4です。

「最初のアクションによるコホート定義」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Interview Prepに戻る