多段階ファネルの作成
順序どおりに各ステップへ到達したユーザーを数え、ステップごとのコンバージョン率を計算します。
「多段階ファネルの作成」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。
ファネルに関する質問で本当に問われていること
面接官が「サインアップファネルを作ってください」と言うとき、順序付けられた各ステップに到達した重複しないユーザー数を数え、ステップ間の離脱を表現できるかどうかを確認しています。
ファネルには、visit -> signup -> activate -> purchaseのようなステージがあります。通常、成果物はステップごとに1行で、ユーザー数とコンバージョン率を含みます。
- イベントではなくユーザーを数えます（1人のユーザーが同じイベントを2回発生させても、1ユーザーとして数えます）。
- ステップには順序があります。ステップ3に到達したということは、ステップ1と2を通過したことを意味します。
与えられるイベントテーブル
ほぼすべてのファネルに関する質問では、ロング形式のeventsテーブルが1つ与えられます。次のような形を想定してください。
user_idアクションを実行したユーザーevent_name'visit'、'signup'、'purchase'などのイベント名event_timeタイムスタンプ
1行が1つのアクションを表します。これをステップごとの件数に変換するのが仕事です。SQLを書く前に、正確なイベント名を面接官に必ず確認してください。
CREATE TABLE events (
user_id INT,
event_name VARCHAR(50),
event_time TIMESTAMP
);1つのステップのユーザー数を数える
まずは単純なところから始めます。1つのステップに到達した重複しないユーザー数を求めるには、イベント名で絞り込み、COUNT(DISTINCT user_id)を使います。
これはすべてのファネルの基礎となる処理です。1つのステップをきれいに数えられれば、すべてのステップを数えられます。
SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';すべてのステップを条件付き集計する
面接での洗練された回答では、条件付き集計を使ってすべてのステップを1回の走査で数えます。COUNT(DISTINCT ...)の中にCASEを記述します。
各ステップについて、そのステップに一致するイベントを発生させた重複しないユーザーを数えます。1回の走査で、ステップごとの合計を1行にまとめられます。
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'visit' THEN user_id END) AS step1_visit,
COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END) AS step2_signup,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;隠れたバグ：ステップに順序がない
先ほどのクエリには、面接官が好んで出す落とし穴があります。データ上でvisitやsignupを一度も行っていなくても、'purchase'を発生させたユーザーを数えてしまいます。
実際のファネルでは、後の各ステップは前のステップの部分集合でなければなりません。イベントを独立して数えると、ステップ3がステップ2より大きくなることがあります。これはファネルとして論理的にあり得ません。
修正方法は、各ユーザーのステップを関連付けることです。通常は、まずユーザーごとに1行へ集約します。
フラグを使ってユーザーごとに1行にする
堅牢なパターンは、イベントログをユーザーごとに1行へ集約し、各ステップを実行したことがあるかどうかを表すブール型のフラグ（0または1）を持たせる方法です。MAX(CASE ...)によって、ロング形式のログをユーザーごとのワイド形式のサマリーに変換できます。
WITH user_steps AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS did_visit,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS did_signup,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id
)
SELECT * FROM user_steps;ステップの順序を適用する
ここでファネルのルールを適用します。ユーザーがステップNとして数えられるのは、それより前のすべてのステップも実行している場合だけです。'purchase'に到達していても、'visit'と'signup'も実行していなければ意味がありません。
前提となる条件をANDでつなぎ、フラグを合計します。これにより各ステップが直前のステップの正しい部分集合になります。
WITH user_steps AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS did_visit,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS did_signup,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id
)
SELECT
SUM(did_visit) AS step1_visit,
SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END) AS step2_signup,
SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END) AS step3_purchase
FROM user_steps;件数を整然としたロング形式の結果に変換する
面接官は、ワイド形式で1行にまとめるよりも、ステップごとに1行ある形式を好むことがよくあります。小さなUNION ALLを使ってワイド形式の合計をロング形式に変換し、並び順のためのステップ番号を付けます。
こうしておくと、次のステップでコンバージョン率を計算したり、グラフ化したりするのがはるかに簡単になります。
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;ステップ間のコンバージョン率
重要な率は2つあり、面接官はどちらを意味しているのかを確認します。
- ステップコンバージョン率：このステップのユーザー数を、直前のステップのユーザー数で割った値です。
- 全体コンバージョン率：このステップのユーザー数を、ファネルの最初のステップのユーザー数で割った値です。
ステップ間の率にはLAGを使って直前のステップの件数を取得します。整数除算にならないよう、10進数にキャストしてください。
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT
step_name,
users,
ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;最初のステップからの全体コンバージョン率
全体コンバージョン率では、各ステップの件数を最初のステップの件数で割ります。順序付けたファネルに対してFIRST_VALUEを使うと、最初の件数をすべての行に保持できます。
100.0を掛けて整数除算を防いでいることを、必ず面接官に伝えてください。
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT
step_name,
users,
ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;ファネル全体を組み立てる
これが面接官の求める一連の回答です。ユーザーごとのフラグに集約し、順序を適用し、ロング形式に変換してから、2種類の率を計算します。各CTEの目的を説明しながら、処理の流れを声に出して説明してください。
この構成は拡張性にも優れています。ステップを追加するには、フラグを1つとUNION ALLの行を1つ追加するだけです。
WITH user_steps AS (
SELECT user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS s1,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS s2,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
FROM events GROUP BY user_id
),
totals AS (
SELECT 1 AS step_no, 'visit' AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
SELECT 2, 'signup', SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;確認問題
面接官が、あなたのファネルでは'purchase'が95ユーザーなのに、'signup'は80ユーザーしかいないことに気付きました。最も可能性の高い原因は何でしょうか。
まとめ：複数ステップのファネル
これで、面接で期待される形のファネルを作れるようになりました。
- 順序付けられた各ステップで重複しないユーザーを数え、決して生のイベント数を数えません。
MAX(CASE ...)のフラグを使い、イベントログをユーザーごとに1行へ集約します。- 順序を適用し、各ステップが直前のステップの部分集合になるようにします。
- 整数除算を防ぎながら、ステップ間（LAG）と全体（FIRST_VALUE）のコンバージョン率を計算します。
次は、これらのステップが実際に正しい順序で、かつ時間枠内に発生したことを確認します。
よくある質問
「多段階ファネルの作成」レッスンは無料ですか？
はい。「多段階ファネルの作成」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。
「多段階ファネルの作成」で何を学びますか？
順序どおりに各ステップへ到達したユーザーを数え、ステップごとのコンバージョン率を計算します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Coding Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。
「多段階ファネルの作成」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このCoding Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。