0Pricing
SQL Interview Prep · レッスン

N日目リテンションとローリングリテンション

従来型、ローリング型、期間限定型のリテンション定義の違いを学びます。

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

リテンションに複数の定義がある理由

面接官が単に「リテンションを計算してください」と言うことは、ほとんどありません。鋭い追加質問は、どのリテンションですかというものです。同じデータでも、定義によって数値は大きく異なります。

必ず知っておくべき3つは、Day-N（クラシック）リテンション、ローリング（無制限）リテンション、範囲指定ウィンドウリテンションです。違いを理解し、ビジネスがどれを求めているかを確認すること自体が、ここで試されているスキルです。

Day-N（クラシック）リテンション

Day-Nリテンションとは、初回アクションからちょうどN日後にユーザーがアクティブだったかを問う指標です。Day-1、Day-7、Day-30は、モバイルアプリで一般的に使われる指標です。

重要な言葉はちょうどです。6日目と8日目にはアクティブだったものの、7日目にはアクティブでなかったユーザーは、クラシックな定義ではDay-7リテンション対象ではありません。この厳密さによって、件数は厳しくなり、曲線はぎざぎざになります。

日数差の計算

Day-Nリテンションの基礎は、コホート開始日と各アクティビティ日の間の日数です。Postgresでは、2つの日付を減算すると整数の日数が直接得られます。

その他の方言では、SQL ServerはDATEDIFF(day, start, d)、MySQLはDATEDIFF(d, start)を使います。方言を明示してください。日数オフセットという考え方は同じです。

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
),
act AS (
  SELECT DISTINCT user_id, event_at::date AS day
  FROM events
)
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

Day-7リテンションクエリ

Day-7リテンション率を得るには、日数オフセットが7に等しいユーザーの重複しない数をコホートサイズで割ります。条件付き集計を使えば、分子と分母を1回のスキャンで取得できます。

= 7という等価条件（>= 7ではありません）が、クラシックリテンションの特徴です。不等号に置き換えると、定義が気づかないうちに変わります。

WITH dn AS (
  SELECT c.user_id, (a.day - c.day0) AS day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
)
SELECT
  COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END) AS d7_retained,
  COUNT(DISTINCT user_id)                               AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END)
        / NULLIF(COUNT(DISTINCT user_id), 0), 1)         AS d7_pct
FROM dn;

ローリング（無制限）リテンション

Day-N時点のローリングリテンションは、もう少し緩やかに、ユーザーがN日目またはそれ以降のいずれかの日にアクティブだったかを問います。7日目、20日目、またはそれ以降のどこかで戻ってきたユーザーは、Day-7時点でリテンション対象として扱います。

この定義では、より滑らかで高い曲線が得られ、長期的な定着度の測定に適していることがよくあります。特徴的な変更点は、アクティビティ最終日の条件を= Nから>= Nに変えることです。

最大日数を使ったローリングリテンション

ローリングリテンションを計算する明快な方法は、各ユーザーの最終アクティブ日の日数オフセットをMAXで求め、その最大値が>= Nであれば、N日目のローリングリテンション対象とすることです。

MAXの後は1ユーザーにつき1行になるため、件数の計算は単純です。また、ローリングリテンションが単調になることも明確になります。30日目にリテンション対象であれば、それより小さいすべてのNでもリテンション対象です。

WITH last_day AS (
  SELECT c.user_id, MAX(a.day - c.day0) AS max_day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
  GROUP BY c.user_id
)
SELECT
  COUNT(*)                                          AS cohort_size,
  COUNT(*) FILTER (WHERE max_day_n >= 7)            AS rolling_d7,
  ROUND(100.0 * COUNT(*) FILTER (WHERE max_day_n >= 7)
        / NULLIF(COUNT(*), 0), 1)                    AS rolling_d7_pct
FROM last_day;

範囲指定ウィンドウリテンション

中間的な定義がウィンドウ型リテンションです。これは、Day-Nの前後に設定した期間内、たとえば「1週目」の指標なら5日目から9日目までに1回以上アクティブだったかを見ます。正確な日にはアクティブでなかったユーザーも含められますが、完全なローリングほど大きく平滑化されません。

実際の利用は断続的に発生するため、これはビジネスの実態に最も合った定義です。クエリでは日数オフセットに対してBETWEENを使います。

SELECT
  COUNT(DISTINCT CASE WHEN day_n BETWEEN 5 AND 9
                      THEN user_id END) AS week1_retained,
  COUNT(DISTINCT user_id)               AS cohort_size
FROM dn;

同じユーザーに対する3つの定義

違いを具体的に見てみましょう。あるユーザーが0日目に利用を開始し、その後は9日目にだけアクティブだったとします。

  • クラシックDay-7：リテンション対象ではありません（7日目ちょうどのアクティビティがないため）。
  • ローリングDay-7：リテンション対象です（最大日数9 >= 7のため）。
  • 範囲指定5–9の1週目：リテンション対象です（9日目がウィンドウ内にあるため）。

同じデータから3つの答えが得られます。面接では、このような例を1つ説明して、構文だけでなく意味を理解していることを示してください。

期間の粒度：日・週・月

「Day-N」は期間-Nへ一般化できます。月単位で利用するB2B製品では、日次リテンションはノイズになるため、月単位に区切ってMonth-Nを問うことになります。仕組みは同じで、変わるのは切り捨てる粒度だけです。

製品本来の利用頻度に合う粒度を選び、その理由も説明してください。一般消費者向けモバイルアプリなら日次、利用頻度が低いB2Bツールなら週次または月次が適しています。

-- weekly grain: offset in whole weeks
SELECT
  c.user_id,
  FLOOR((a.day - c.day0) / 7) AS week_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

生存者バイアスと成熟度の落とし穴

シニア向けの鋭い確認事項です。開始から10日しか経っていないコホートについて、Day-30リテンションを報告してはいけません。そのコホートにはDay-30にアクティブになる機会がまだないため、値が本当にリテンションが低いことを示しているのではなく、人為的に0になってしまいます。

Day-Nを報告するときは、年齢が>= Nのコホートだけを含めて対処します。CURRENT_DATE - day0 >= Nでフィルタリングしてください。これを忘れると、最近のコホートが壊滅的に悪く見えてしまいます。

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
)
SELECT *
FROM cohort
WHERE (CURRENT_DATE - day0) >= 30;  -- mature enough for Day-30

定義を声に出して選ぶ

面接での最良の回答はクエリではなく、質問を返すことです。「クラシックDay-N、ローリング、範囲指定ウィンドウのどれを求めていますか。また、自然な期間は何ですか」と確認します。

続けてトレードオフを説明します。クラシックは厳密で、特定日のプロダクト施策の効果測定に適しています。ローリングは短期の定着を過大評価しますが、生涯にわたる定着度を捉えられます。範囲指定は現実的な妥協案です。目的を持って指標を選んでいることを示すのが、このレッスンの核心です。

クイックチェック

あるユーザーの初回アクションは0日目で、それ以外のアクティビティは12日目だけでした。各定義では、Day-7時点でリテンション対象になるでしょうか。

まとめ：リテンションの定義

Day-Nとローリングリテンションの要点：

  • クラシックDay-N：ちょうどN日目にアクティブ（オフセット= N）— 厳密で、ぎざぎざした曲線になります。
  • ローリング：N日目またはそれ以降にアクティブ（MAX offset >= N）— より滑らかで単調になり、定着度を測定します。
  • 範囲指定：ウィンドウ内にアクティブ（BETWEEN）— 現実的な妥協案です。
  • 製品の利用頻度に合う期間の粒度へ、日を一般化します。
  • 生存者バイアスを避けるため、Day-Nは成熟したコホート（年齢>= N）についてのみ報告し、ビジネスがどの定義を指しているかを必ず確認します。

よくある質問

「N日目リテンションとローリングリテンション」レッスンは無料ですか？

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

「N日目リテンションとローリングリテンション」で何を学びますか？

従来型、ローリング型、期間限定型のリテンション定義の違いを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「N日目リテンションとローリングリテンション」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. 最初のアクションによるコホート定義
  2. リテンションマトリクスの作成
  3. N日目リテンションとローリングリテンション
  4. チャーンと復帰のクエリ
← SQL Interview Prepに戻る