チャーンと復帰のクエリ
離脱したユーザーと、空白期間の後に戻ってきたユーザーを特定します。
「チャーンと復帰のクエリ」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
リテンションの裏側
リテンションが残ったユーザーを測るのに対し、チャーンは離脱したユーザーを、復活は戻ってきたユーザーを測ります。これらがリテンションとセットで面接に登場するのは、アクティビティの不在について推論できるかを確認できるためです。これは存在を数えるより難しい問題です。
繰り返し登場するポイントは、存在しない行ではフィルタリングできないことです。チャーンクエリでは本質的に、ユーザーの最後のアクティビティと現在時点（または次のアクティビティ）の間にある空白期間を見つけます。
チャーンを正確に定義する
ウィンドウを定めなければ、「チャーンした」という言葉には意味がありません。よくある定義では、ユーザーが過去30日間にまったくアクティビティがない場合にチャーンしたとします。この30日間という非アクティブ期間のしきい値こそ、明確に決めるべきビジネス上の選択です。
サブスクリプション製品では、キャンセルまたは期限切れになったサブスクリプション、つまりアクティビティの空白ではなくステータス変更をチャーンとする場合もあります。SQLを書く前に、どのモデルを適用するのか確認してください。
ユーザーごとの最終アクティビティ
アクティビティの空白期間に基づくチャーン分析の基礎は、各ユーザーの最新イベントです。ユーザーごとにグループ化し、イベント日付のMAXを取得します。
この1つの値を今日の日付と比較すれば、ユーザーがどれだけ長く利用していないかが分かります。それ以降の処理はすべて、この最終確認日との比較です。
SELECT
user_id,
MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;チャーンユーザーのクエリ
最終アクティビティが30日より前であれば、ユーザーはチャーンしたと判定します。last_activeをCURRENT_DATE - 30と比較してください。最新のイベントがこの基準日より前にあるユーザーは、利用を停止しています。
処理が集計の後に行われている点に注目してください。まずユーザーごとに1行へ集約し、その後で空白期間を判定します。生のイベントを日付でフィルタリングするだけでは、期間内に非アクティブだったユーザーは分かっても、全体としてチャーンしたユーザーは分かりません。
WITH last_seen AS (
SELECT user_id, MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';チャーン率の集計
チャーン率は、該当する母数に占めるチャーンユーザーの割合です。母数には、その期間の開始時点でアクティブだったユーザーを使うことがよくあります。条件付き集計でチャーンユーザー数と合計数を1回の処理で数え、100.0とNULLIFを使って慎重に割り算を行います。
面接では分母を明確にしてください。全期間のユーザーに対するチャーンと、過去にアクティブだったユーザーに対するチャーンは、異なる指標です。
WITH last_seen AS (
SELECT user_id, MAX(event_at::date) AS last_active
FROM events GROUP BY user_id
)
SELECT
COUNT(*) FILTER (
WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
) AS churned,
COUNT(*) AS total_users,
ROUND(100.0 * COUNT(*) FILTER (
WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
/ NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;集合演算による期間比較チャーン
別の捉え方として、先月はアクティブだったが今月はアクティブでないユーザーは誰かを考えます。これは集合差です。先月アクティブだったユーザーの集合と、今月アクティブだったユーザーの集合を作り、前者に含まれていて後者には含まれないメンバーを見つけます。
EXCEPT、LEFT JOINとIS NULLによるアンチ結合、またはNOT EXISTSで表現できます。アンチ結合が最も移植性が高く、面接官が最もよく見たがる方法です。
WITH last_month AS (
SELECT DISTINCT user_id FROM events
WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
SELECT DISTINCT user_id FROM events
WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;アンチジョイン形式
同じ「今期のチャーン」を求めるクエリを、アンチジョインで表したものです。今月のアクティブユーザーを先月のユーザーにLEFT JOINし、結合先がNULLの行だけを残します。これは先月は存在したものの今月は存在しないユーザー、つまりチャーンしたユーザーです。
NOT EXISTSでも同じように正しく求められ、NULLも安全に扱えます。一方、内側の集合にNULLが含まれる可能性がある場合は、NOT INは危険になるという、典型的な落とし穴にも触れておきましょう。
SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;リザレクションの定義
リザレクション（別名リアクティベーション）とは、チャーンしたユーザーが再びアクティブになった状態です。その特徴は、タイムライン上の空白期間です。アクティブだった後、チャーンのしきい値を超える期間活動がなくなり、その後再びアクティブになります。
つまり、今月リザレクションしたユーザーとは、現在はアクティブで、前の期間は非アクティブでしたが、それより前のいずれかの期間には活動していたユーザーです。チャーンとは正反対の概念です。
LAGでギャップを検出する
リザレクションを見つける洗練された方法は、LAGウィンドウ関数を使うことです。ユーザーごとに各活動期間について、直前のアクティブ期間を確認します。2つの期間の間隔がしきい値を超えていれば、その期間はリアクティベーションです。
LAGを使うと自己結合が不要になり、クエリも読みやすくなります。ユーザーごとにパーティションを分け、アクティブ期間の順に並べて、各期間を直前の期間と比較します。
WITH monthly AS (
SELECT DISTINCT user_id,
DATE_TRUNC('month', event_at) AS active_month
FROM events
),
gaps AS (
SELECT user_id, active_month,
LAG(active_month) OVER (
PARTITION BY user_id ORDER BY active_month
) AS prev_month
FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
AND active_month > prev_month + INTERVAL '1 month';新規・リザレクション・継続
完全なアクティビティ分類クエリでは、今期アクティブなユーザーを次のいずれかに分類します。新規（過去に活動がない）、継続（前の期間もアクティブ）、またはリザレクション（過去に活動はあるが、空白期間がある）です。LAGで得られるprev_monthが、この3つすべての判定に使われます。
prev_month IS NULL→ 新規prev_month = active_month - 1→ 継続- それ以外（空白期間あり） → リザレクション
この内訳まで示せれば、強く、完全な回答になります。
SELECT user_id, active_month,
CASE
WHEN prev_month IS NULL THEN 'new'
WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
ELSE 'resurrected'
END AS user_state
FROM gaps;NULLがある場合のNOT INの罠
最後に、非常に危険な落とし穴があります。チャーンをWHERE user_id NOT IN (SELECT user_id FROM this_month)のように記述し、そのサブクエリがNULLを1つでも返すと、結果全体が空になります。これは、NOT INがNULLに対してUNKNOWNと評価されるためです。
NULLを正しく扱えるNOT EXISTS、またはLEFT JOIN / IS NULLによるアンチジョインを使うのが安全です。こちらから言わなくてもこの違いを指摘できると、リテンションに関する面接では確かなシニアらしさを示せます。
-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
SELECT 1 FROM this_month tm
WHERE tm.user_id = lm.user_id
);確認問題
先月はアクティブでしたが今月はアクティブでないユーザーを求めたいとします。チームメイトがWHERE user_id NOT IN (SELECT user_id FROM this_month)と記述したところ、明らかにチャーンしたユーザーがいるにもかかわらず0行が返されました。最も安全な修正方法は何でしょうか。
まとめ：チャーンとリザレクション
チャーンとリザレクションの要点：
- 非アクティブ期間のしきい値（例：30日間活動がない）またはサブスクリプションのステータス変更でチャーンを定義します。どちらを使うのか明確にしてください。
- 各ユーザーの最終アクティビティのMAXを求め、
CURRENT_DATE - thresholdと比較します。 - 期間ごとのチャーンは集合差です。EXCEPT、NOT EXISTS、またはLEFT JOIN / IS NULLによるアンチジョインを使います。
- リザレクションはタイムライン上の空白期間です。
LAGで検出し、ユーザーを新規・継続・リザレクションに分類します。 - NULLが入り得る場合は
NOT INを避けてください。結果が黙って空になる可能性があります。
よくある質問
「チャーンと復帰のクエリ」レッスンは無料ですか？
はい。「チャーンと復帰のクエリ」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「チャーンと復帰のクエリ」で何を学びますか？
離脱したユーザーと、空白期間の後に戻ってきたユーザーを特定します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「チャーンと復帰のクエリ」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。