SQL Interview Prep · レッスン

リテンションマトリクスの作成

Q: 「リテンションマトリクスの作成」で何を学びますか？

コホートと経過期間ごとにアクティブユーザーを数え、リテンションテーブルを作成します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

コホートと経過期間ごとにアクティブユーザーを数え、リテンションテーブルを作成します。

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

リテンションマトリクスとは

コホートを定義した次に登場する有名なリテンションマトリクスは、行にコホート、列に期間オフセット（0か月目、1か月目、2か月目など）を並べ、各セルにそのオフセット時点でもアクティブだったコホートユーザーの人数を示します。

面接官がこの問題を好むのは、コホートの割り当て、アクティビティへの再結合、期間差の計算、ピボットを組み合わせる必要があるからです。これはプロダクト分析を最も代表するクエリです。

2つの入力

必要なのは2つです。前のレッスンで求めた各ユーザーのコホート期間と、ユーザーごとのすべてのアクティブ期間の記録です。アクティビティは同じイベントテーブルから取得し、期間単位に集約します。

したがって、クエリはコホート CTE を作り、次に各ユーザーがどの月にアクティブだったかを列挙するアクティビティ CTE を作り、それらを結合する構成にします。

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

アクティブな期間を列挙する

アクティビティ CTE は、「各ユーザーがどの月にアクティブだったか」という問いに答えます。すべてのイベントを月単位に切り捨て、DISTINCT または GROUP BY で重複を除きます。これにより、3月に40回アクティブだったユーザーも3月の1行になります。

このユーザーごと・月ごとの一覧をコホートに結合して、オフセットごとの継続状況を測定します。

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

期間オフセットを計算する

マトリクスの中心となるのは期間番号です。これは、コホートの開始から何か月後に特定のアクティビティが発生したかを表します。アクティブな月からコホートの月を差し引きます。

Postgres では、2つの日付の間にある完全な月数を数える方法が分かりやすいでしょう。移植性を重視するなら、年の差を12倍して月の差を加える式を使えます。多くのデータベースには、この計算を補助する関数も用意されています。オフセット0は、コホート自身の開始月を意味します。

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

コホートとアクティビティの結合

コホートCTEとアクティビティCTEをuser_idで結合します。各出力行は、コホートXに属するこのユーザーがオフセットNでアクティブだったことを示します。(コホート、オフセット)ごとに重複しないユーザー数を数えると、マトリクスをロング形式で表せます。

すべてのコホートメンバーは自分の開始月にアクティブになるため、オフセット0はコホートサイズと一致するはずです。これは組み込みの sanity check になります。

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

ロング形式のリテンションテーブル

オフセットの計算を追加して集計します。これで、コホートとオフセットごとにリテンション対象ユーザー数を1行で表す、整然としたロング形式の結果が得られます。ピボットは見た目を整えるだけなので、多くの面接官はこの形式をそのまま受け入れます。

オフセットの式は保存された列ではなく計算される値なので、SELECTとGROUP BYの両方に現れることに注目してください。

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

ワイド形式の列へのピボット

従来のグリッド形式にするには、条件付き集計を使ってオフセットを列に変換します。各オフセットについてCASEを使い、SUMで集計します。この移植性の高いパターンなら、特殊なPIVOT構文がなくてもすべての方言で動作します。

各CASEは、その行のperiod_numberが該当する列と一致するときに1を返すため、SUMによってそのオフセットのリテンション対象ユーザー数を数えられます。

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

件数からリテンション率へ

面接官が通常求めるのは、生の件数ではなくパーセンテージです。各オフセットのリテンション対象ユーザー数を、コホートサイズ（オフセット0）で割ります。整数除算を避けるために、浮動小数点数へキャストするか1.0を掛けてください。整数除算は、ここで最もよくある気づきにくいバグです。

結果はリテンション曲線になります。月0では100%で、次第に低下して一定水準へ近づきます。その一定水準こそ、ステークホルダーが実際に重視する指標です。

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

整数除算の落とし穴

面接で必ず出てくるひっかけです。ほとんどのエンジンでは、両方のオペランドが整数であるため、120 / 500は0.24ではなく0になります。その結果、リテンション率が気づかないうちにすべて0になります。

片方を数値に変換して修正します。100.0を掛ける、片方のオペランドをNUMERICにCASTする、またはNULLIF(size, 0)で割ります。最後の方法なら空のコホートにも対応できます。「NULLIFによってゼロ除算も防げます」と言えると加点になります。

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

欠落したオフセットをゼロで埋める

あるコホートでオフセット2のリテンション対象ユーザーが0人だった場合、JOINによって行自体が生成されず、マトリクスに穴が残ります。明示的な0を表示するには、(コホート、オフセット)の組み合わせをすべて含むグリッドを生成し、件数をLEFT JOINしてください。

コホートと数値またはオフセットのリストをCROSS JOINしてグリッドを作り、欠落した件数をcoalesceして0にします。この欠落に気づいたことは、面接官から評価されます。

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

三角形の構造と直近性バイアス

もう1つ説明しておきたい点は、マトリクスが三角形になることです。先月開始したコホートには、まだ月3の値が存在しないため、後ろのオフセットほど集計に寄与するコホートが少なくなります。

そのため、コホート全体で列の平均を比較すると、古いコホートに偏ります。三角形をそのまま正直に表示するか、すべてのコホートが到達しているオフセットだけに比較対象を限定すると説明してください。この点を理解していることが、単なるクエリ作成者とアナリストを分けます。

クイックチェック

リテンションクエリではリテンション対象ユーザー数をコホートサイズで割っていますが、月0以外のパーセンテージがすべて0と表示されます。最も可能性の高い原因は何でしょうか。

まとめ：リテンションマトリクス

面接でリテンションマトリクスを作成する手順は次のとおりです。

各ユーザーにコホート期間を割り当て、各ユーザーのアクティブ期間を重複除去して列挙します。
両者を結合し、コホートとアクティビティの間の期間オフセットを計算します。
COUNT(DISTINCT user_id)でロング形式に集計し、グリッドが必要ならCASEでピボットします。
100.0とNULLIFを使い、整数除算とゼロ除算を避けながら件数を率に変換します。
生成したグリッドをLEFT JOINしてゼロのセルを埋め、マトリクスが三角形になることを忘れないでください。

よくある質問

「リテンションマトリクスの作成」レッスンは無料ですか？

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

「リテンションマトリクスの作成」で何を学びますか？

コホートと経過期間ごとにアクティブユーザーを数え、リテンションテーブルを作成します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「リテンションマトリクスの作成」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Interview Prepに戻る