0Pricing
Coding Interview Prep · レッスン

順序付きイベントと時間枠

ウィンドウ関数を使い、各ステップが順番どおり、かつ制限時間内に発生することを確認します。

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

順序と時間が重要な理由

前のレッスンで扱った基本的なファネルは、ユーザーが各ステップを実行したかどうかだけを確認します。より鋭い面接官は、ステップが正しい順序で、かつ妥当な時間内に発生したかを尋ねます。

月曜日に購入し、金曜日にマーケティングページを訪れたユーザーは、ファネルを通じてコンバージョンしたわけではありません。順序と時間を考慮することで、単純なフラグベースのファネルを信頼できるものにできます。

ユーザーごとに最初のタイムスタンプを取得する考え方

順序を考えるには、各ステップにおけるユーザーの最初の時刻を取得します。最初のvisit、最初のsignup、最初のpurchaseです。

そのうえで、正しいコンバージョンとは、first_signup_time >= first_visit_timeなど、チェーンに沿って条件を満たすことだと考えます。ステップごとにグループ化したMIN(event_time)で、これらの基準時刻を取得できます。

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

ステップを順番どおりに要求する

ステップごとの最初のタイムスタンプがあれば、順序の適用は比較だけで行えます。ユーザーが本当にステップ3までコンバージョンしたとみなせるのは、各タイムスタンプがNULLではなく、単調に増加している場合だけです。

NULLのタイムスタンプ（そのステップが一度も発生していない場合）は自然に比較条件を満たしません。これはまさに意図した動作です。

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

時間枠を追加する

ほとんどのファネルには期限があります。たとえば「最初のvisitから7日以内にコンバージョンする」です。最初のステップと最後のステップの間に、時間間隔の上限を追加します。

日付の計算方法は方言によって異なります。Postgresではvisit_t + INTERVAL '7 days'、MySQLではDATE_ADD(visit_t, INTERVAL 7 DAY)と記述できます。使用する方言は必ず明示してください。

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

任意のタイムスタンプではなく最初のタイムスタンプを使う理由

面接で問われやすい微妙なポイントがあります。時間枠はユーザーの最初のvisitから始めるべきでしょうか。それともsignup前の直近のvisitから始めるべきでしょうか。これはプロダクト上の問いによって異なります。

  • ファーストタッチの時間枠は、最初の興味からコンバージョンまでにかかった時間を測定します。
  • ラストタッチの時間枠は、最後のvisit後にコンバージョンするまでの短期的な動きを測定します。

どちらを意味するのか面接官に確認してください。意図を持って選択できると、シニアらしさを示せます。

LEADでイベントの順序を扱う

複雑な複数ステップの経路では、ウィンドウ関数が力を発揮します。ユーザーごとにイベントを時刻順に並べ、LEADで次のイベントを確認し、それが想定した次のステップかどうかを検証します。

この方法なら、ステップの間に関係のないイベントが発生する経路にも対応できます。

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

次に期待されるステップと一致させる

LEADを発展させて、'visit'の直後に'signup'が続く行だけを残します。これにより、単なる共起ではなく、実際に連続して発生した遷移を見つけられます。

この遷移チェックを連鎖させれば、順序付けられた経路全体をステップごとに検証できます。

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

連続するステップ間の時間

面接官は「各ステップにどのくらい時間がかかりますか」とよく尋ねます。タイムスタンプに対してLEADを使い、差を計算します。連続するイベント間の差が、そのステージでの滞在時間です。

遷移ごとに中央値または平均値を集計すると、ファネルの中で最も時間がかかるステージを見つけられます。

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

同一タイムスタンプのエッジケース

2つのイベントがまったく同じevent_timeを持っていたらどうなるでしょうか。その場合、同時に発生していてもsignup_t >= visit_tは真になり、時刻だけで並び順を決めることはできません。

  • >=と>のどちらを使うかを意図的に選び、その理由を説明します。
  • ウィンドウ関数の結果を決定的にするため、イベントのシーケンスIDなどのタイブレーカーをORDER BYに追加します。

こちらから言わなくてもこの点に触れられると、面接官に好印象を与えられます。

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

順序と時間枠を1つのクエリにまとめる

これが、順序と時間枠の両方を適用した完全なファネルです。最初のvisitを起点とし、後続の各ステップの最初の発生が直前のステップより後であることを要求し、経路全体を7日以内に制限します。

これは、ファネルを単にフラグの数え上げとしてではなく、仕組みとして理解している候補者と、そうでない候補者を分ける回答です。

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

SQL方言ごとの注意点

ライブコーディングで覚えておきたい移植性に関する注意点は2つあります。

  • 集約関数で使うFILTER (WHERE ...)は標準SQLで、Postgresでは動作します。MySQLや古いエンジンでは、代わりにSUM(CASE WHEN ... THEN 1 ELSE 0 END)を使います。
  • インターバルの構文は異なります。Postgresは+ INTERVAL '7 days'、MySQLはDATE_ADD(d, INTERVAL 7 DAY)、SQL ServerはDATEADD(day, 7, d)です。

前提とする方言を明示すれば、どの方言を選んだかは面接官もほとんど気にしません。重要なのは、それぞれに違いがあることを理解していることです。

クイックチェック

最初の訪問から7日以内に、ユーザーが visit -> signup -> purchase を順番どおりに完了したことを条件に、そのユーザー数を数える必要があります。どのアプローチが正しいでしょうか？

振り返り：順序付きイベントと時間ウィンドウ

重要なポイント：

  • 各ユーザーについて、各ステップの最初のタイムスタンプを MIN(CASE ...) で取得します。
  • 各ステップの時刻が直前のステップの時刻以降であることを要求し、順序を保証します。
  • intervalを指定してパスの範囲を制限し、使用する方言の構文を明示します。
  • LEAD/LAG を使って、遷移の確認やステップ間の滞在時間を調べます。
  • ORDER BY にタイブレーカーを追加して、同一タイムスタンプでの同順位を処理します。

次は、ファネルから実験へ移り、バリアントごとの指標を計算します。

よくある質問

「順序付きイベントと時間枠」レッスンは無料ですか？

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

「順序付きイベントと時間枠」で何を学びますか？

ウィンドウ関数を使い、各ステップが順番どおり、かつ制限時間内に発生することを確認します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「順序付きイベントと時間枠」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. 多段階ファネルの作成
  2. 順序付きイベントと時間枠
  3. A/Bテストの割り当てと指標
  4. SQLにおけるリフト、統計的有意性、ガードレール
← Coding Interview Prepに戻る