代表的な面接トピック

SQL面接対策：30分の非アクティブ間隔によるユーザーイベントのセッション化

データ難しい
Offer.cc 編集チーム公開日 更新日

質問

event_id、user_id、occurred_atを含むeventsテーブルがあります。各ユーザーのイベントを時間順に並べ、最初のイベントまたは直前のイベントとの間隔が30分以上ある場合に新しいセッションを開始してください。各セッションのシーケンス番号、開始時刻、終了時刻、イベント数、継続時間を返し、同一タイムスタンプ、範囲境界、遅延イベント、計算量、および本番環境での検証について説明してください。

問題と適用されるコンテキスト

次のPostgreSQLイベントテーブルを想定します。

sql
CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL
);

各ユーザーのイベントをoccurred_atおよびevent_id順に並べ替えます。最初のイベントがセッションを開始します。それ以降の各イベントは、直前のイベントとの間隔が30分以上ある場合に新しいセッションを開始します。 user_id、1から始まるsession_seq、session_start、session_end、event_count、およびsession_durationを返します。正確に30分の間隔は新しいセッションを開始します。この境界値の処理は仕様の一部です。

このパターンは、クリックストリーム処理、プロダクト分析、行動ファネルなどに頻繁に現れます。これは、連続ログイン日数を求める問題とは異なります。毎日のストリーク計算は隣接するカレンダー日付を比較しますが、セッション化は1人のユーザーにおける隣接するイベント間の経過時間を比較します。中心となる解法は、論理的に重複排除された完全なイベントセットに対して正確な結果を計算します。範囲が制限されたクエリや増分マテリアライゼーションには、追加の境界ルールが必要になります。

面接官が評価しているポイント

最初の評価基準は、候補者が文章による要件を実行可能な仕様に落とし込めるかどうかです。> 30 minutesと>= 30 minutesでは、境界となるイベントの割り当て先が異なります。また、直前のイベントと比較するか、セッション内の最初のイベントと比較するかによっても定義が異なります。この問題では隣接するイベント間の間隔を使用するため、継続的にアクティブなセッションは30分を大幅に超えて続く可能性があります。

2番目の評価基準は、3つのウィンドウステージへの分解です。LAG()を使用して直前のイベントを取得し、セッション境界をマークし、それらのマークに対して累積SUM()を実行します。必要なウィンドウ計算は、単一のPostgreSQL式内で単純に任意にネストすることはできません。個別のCTEに分けることで、検査用の中間リレーションも明確になります。

3番目の評価基準は、決定論的な順序付け（deterministic ordering）です。2つのイベントが同じoccurred_atを持つ場合があります。時間のみで順序付けすると、それらの相対的な順序が不定になります。ユニークなevent_idを追加することで、LAG()と累積合計に同一の全順序が与えられます。同一タイムスタンプ間の経過時間はゼロであるため、同一セッション内に維持されます。

4番目の評価基準は時間セマンティクスです。timestamptzは、経過時間の比較に適した絶対的な瞬間を表します。減算する前にローカルの壁時計時間に変換すると、夏時間（DST）による時間の飛びが発生する可能性があります。名前付きタイムゾーンへの変換は表示時に行うべきであり、このセッション境界の計算内で行うべきではありません。

最後に、優れた回答は、遅延データが過去の履歴を書き換える可能性があることを認識しています。タイムラインの途中にイベントが挿入されると、以前は分かれていた2つのセッションが結合される可能性があります。したがって、増分システムはsession_seqを追加専用（append-only）として扱うことはできません。範囲を限定した再計算、バージョン管理された修正、または明示的な確定ウォーターマークが必要です。

回答前に明確にすべき質問

  • 30分の境界値はどちらのセッションに属するか？ この問題では>= 30 minutesで新しいセッションを開始します。「厳密に大なり（>）」というプロダクト要件の場合、演算子と境界の期待値がすべて変わります。
  • 隣接するイベントと比較するのか、それともセッションの最初のイベントと比較するのか？ ここでは隣接するイベントです。最大セッション長を設ける場合は、セッション開始時に固定された別の状態を保持する必要があります。
  • 重複はどのように処理されるか？ event_idが論理イベントキーであるため、再送されたイベントはこのクエリの前に重複排除する必要があります。同じユーザーとタイムスタンプを持つ異なるイベントは、有効な行として残ります。
  • 間隔を定義するタイムゾーンは何か？ 絶対的な瞬間同士の経過時間です。名前付きタイムゾーンは表示を変更するだけであり、経過秒数は変わりません。
  • クエリの期間は制限されているか？ 全履歴を対象とする場合はシンプルです。集計期間が指定されている場合、期間前に開始されたセッションが完全に維持されるかどうかを指定し、直前のコンテキストを読み込む必要があります。
  • イベントはどれくらい遅れて到達する可能性があるか？ アドホックなクエリであれば再計算できます。マテリアライズされた結果の場合、修正可能期間（補正ホライズン）、ウォーターマーク、および下流への更新または取り消しプロトコルが必要です。
  • 空のテーブルやイベントが1つしかないユーザーに対しては何を返すべきか？ 空のテーブルには0行、イベントが1つのユーザーには継続時間0のセッションが1つ返されます。

30秒の回答フレームワーク

「ユーザーごとにoccurred_at, event_idによって決定論的な順序を作成し、LAG(occurred_at)を使用して直前のイベントを取得します。最初の行および30分以上のすべての間隔を1とマークし、それ以外のすべての行を0とマークします。明示的なROWSフレームでの累積合計により、1から始まるセッションシーケンスを生成します。その後、ユーザーとシーケンスでグループ化して、開始、終了、件数、および継続時間を計算します。 テストケースには、29分59秒、正確に30分、同一タイムスタンプ、イベントが1つのユーザー、重複ID、遅延データ、および集計範囲の開始時点を含めます。増分マテリアライゼーションの場合、セッションが常に追加のみであると仮定せず、許容される遅延ウィンドウ内の影響を受けるユーザーを再計算します。」

ステップごとの詳細解説

まず、共通の順序付けルールを1つ使用して直前のイベントを取得します。event_idは経過時間に影響を与えず、同一タイムスタンプの順序を安定させるためだけに機能します。

sql
WITH ordered AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    LAG(occurred_at) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
    ) AS previous_at
  FROM events
),
marked AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    CASE
      WHEN previous_at IS NULL THEN 1
      WHEN occurred_at - previous_at >= INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    SUM(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM marked
)
SELECT
  user_id,
  session_seq,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count,
  MAX(occurred_at) - MIN(occurred_at) AS session_duration
FROM sessionized
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

明示的なROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWの指定は重要です。累積合計は、デフォルトのウィンドウフレームのピア（同順位）セマンティクスを継承するのではなく、物理的な行を1行ずつ取り込む必要があります。このクエリではユニークなevent_idによってピアが存在しなくなりますが、フレームを明記することで意図が固定され、将来タイブレーカーが削除されても動作が無意識に変化するのを防ぎます。

境界を確認するために次のイベントを使用します。

userideventidoccurred_at直前のイベントとの間隔期待されるセッション
1109:00最初のイベント1
1209:055分1
1309:3530分2
1409:5015分2
1510:1020分2
2609:00最初のイベント1
2709:2929分1
2809:5829分1

ユーザー1には2つのセッションがあります。5分間にわたる2つのイベントと、その後の35分間にわたる3つのイベントです。ユーザー2の合計スパンは58分ですが、隣接するすべての間隔が30分未満であるため、3つのイベントすべてが1つのセッションに留まります。これにより、隣接間隔によるセッション化と最大セッション期間によるセッション化の違いが明確になります。

正当性は単純な不変条件に従います。ユーザーの最初の行によって、累積合計が0から1に増加します。それ以降は、境界条件を満たす行のみが合計を増加させ、非境界行は値をそのまま維持します。マークされた境界によって隔てられていない場合に限り、2つの行はまったく同じ累積値を持ちます。したがって、ユーザーとその累積値でグループ化することで、ユーザーが混ざったり境界をまたいだりすることなく、最大の連続したセグメントがすべて得られます。

N件のイベントの場合、ウィンドウの実行計画は通常、ユーザーと時間でソートされるため、O(N log N)の時間がかかります。ウィンドウスキャンと集約はO(N)です。中間状態はO(N)であり、ディスクにスピル（一時書き出し）する可能性があります。インデックスは論理的な順序と一致させることができます。

sql
CREATE INDEX events_session_order_idx
  ON events (user_id, occurred_at, event_id);

インデックスが存在してもソートなしの実行計画が保証されるわけではありません。フルスキャンのコスト、可視性、並列処理、述語のすべてがオプティマイザに影響を与えます。インデックスが存在するだけで満足せず、代表的なデータに対してEXPLAIN (ANALYZE, BUFFERS)を実行し、スキャン、ソート、一時I/O、推定行数、実際の行数を確認してください。

時間範囲のフィルタリングは、最も陥りやすい正当性の罠です。あるユーザーが09:50と10:10にイベントを持っており、クエリが10:00から開始される場合、最初にフィルタリングを行うと10:10が誤って新しいセッションとしてマークされてしまいます。出力に範囲内のメンバーシップのみが必要な場合は、各ユーザーの開始時刻より前の直近の先行イベントを少なくとも1つ読み取った上で、出力からコンテキスト行を除外します。完全なセッションの開始時刻を出力に含める必要がある場合は、真の30分境界に到達するまで過去に遡って読み取りを続けます。

また、遅延データによって履歴がマージされることもあります。09:00と09:50のイベントは、最初は別々のセッションを形成します。遅れて09:25のイベントが到着すると、隣接する両方の間隔が25分に変化し、セッションがマージされます。バッチ処理では影響を受けるパーティションを再計算できます。増分システムでは、許容される遅延範囲にわたってユーザー単位で再計算し、バージョンまたは取り消しレコードを発行する必要があります。ウォーターマークを超えたデータを隔離（quarantine）するのか、破棄するのか、それともより広範囲の修正をトリガーすることを許可するのかを仕様で定めておく必要があります。

各段階を検証します。ordered内の先頭以外の各previous_atは、共通順序における直前のタイムスタンプと等しくなければなりません。markedは先頭行としきい値境界にのみ1を含みます。各ユーザーについて、session_seqは1から始まり、決して減少せず、最大でも1ずつ増加します。最終的なリレーションにはsession_start <= session_endが含まれ、そのイベント数の合計は論理入力イベント数と等しくなり、隣接するセッション間の間隔は30分以上でなければなりません。

高品質な模範解答

「まず、境界条件が30分以上であること、および比較が隣接するイベント間で行われることを確認します。一意のevent_idによって論理イベントの重複が排除される一方、同一時刻の異なるイベントは保持されます。最初のCTEでは各ユーザーを時間とイベントIDで順序付けし、LAGを使用して直前のタイムスタンプを取得します。2つ目のCTEでは先頭行としきい値間隔のすべてをマークします。3つ目のCTEでは明示的なROWSフレームに対して累積合計を取り、安定したセッションシーケンスを作成します。その後、ユーザーとシーケンスで集約し、開始、終了、件数、および継続時間を取得します。

テストとしては、29分59秒とちょうど30分、同一タイムスタンプ、イベントが1つのユーザー、および空のテーブルを検証します。また、LAGの前にフィルタリングを行うと誤ったセッション境界が作成されてしまうため、集計範囲より前の先行イベントのテストも実施します。ソート処理がおよそO(N log N)で大半の計算量を占めます。(user_id, occurred_at, event_id)に対するインデックスによって必要な順序が提供される可能性がありますが、最終的な判断はバッファを考慮した実行計画を確認して行います。

継続的にマテリアライズされる結果の場合、セッション番号を不変のものとして扱いません。09:00と09:50のイベントは別々のセッションですが、09:25の遅延イベントによってそれらが結合される可能性があります。システムには遅延ウィンドウ内でのユーザー単位の再計算と、バージョンを認識できるコンシューマが必要です。ウォーターマークを超えたイベントは、明示的な隔離ポリシーまたは広範囲の修正ポリシーに従う必要があります。」

よくある間違い

  • 現在のイベントからセッションの最初のイベントを減算する → 合計継続時間が30分を超えた時点で、継続してアクティブなユーザーが分割されてしまう → 指定どおりに隣接するイベント同士を比較する。
  • 正確に30分の間隔を古いセッションに残す → >= 30 minutesの契約に違反する → 専用のしきい値テストを作成する。
  • occurred_atのみで順序付けする → 同一のタイムスタンプ間で安定した全順序が得られない → 一意のevent_idを追加し、両方のウィンドウで同じ順序を再利用する。
  • 明示的なROWSフレームを省略する → デフォルトのピア動作が行ごとの累積と異なる可能性がある → 先頭行から現在の行までのフレームを明記する。
  • ローカルの壁時計時間を減算する → 夏時間（DST）の切り替わりにより1時間のズレが生じたり隠れたりする → timestamptzの絶対瞬間を比較し、表示時のみローカライズする。
  • LAGの前にレポート開始位置でフィルタリングする → 範囲内の最初のイベントがその先行イベントを見失い、誤った境界になってしまう → 出力をトリミングする前に境界前のコンテキストを読み込む。
  • 再送されたレコードをイベントとしてカウントする → event_countが水増しされる → 論理イベントキーによって重複排除する。
  • 過去のセッションは追加のみであると仮定する → 遅延データによって境界が移動したりセッションがマージされたりする → 制限された範囲を再計算し、修正可能な結果を発行する。
  • 一致するインデックスがあればすべてのソートが排除されると仮定する → スキャンコストと述語に基づいて、オプティマイザが別の計画を選択する場合がある → 代表的な実行計画と一時I/Oを検証する。

フォローアップの質問と回答

フォローアップ1：正確に30分の間隔を古いセッションに残す場合、何が変わりますか？

境界の述語を>= INTERVAL '30 minutes'から> INTERVAL '30 minutes'に変更します。ウィンドウパイプラインの残りの部分は変わりませんが、メトリクスの定義とすべてのテストフィクスチャをそれに合わせて変更する必要があります。将来ポリシーがブレないように、29:59、30:00、30:01のケースを保持しておきます。

フォローアップ2：セッションの最大継続時間を2時間とする場合、累積合計による解法で十分ですか？

不十分です。短い間隔が連続するとセッションが無制限に延長される可能性があるため、次の境界は動的なセッション開始時刻にも依存します。再帰CTE、順序付きステートマシン、またはストリームプロセッサにおけるユーザーごとの状態管理を使用する方が通常は明快です。まず、固定の2時間ウィンドウと「セッションの最初のイベントから2時間後」を区別してください。これらは異なる仕様です。

フォローアップ3：24時間遅れて到着したイベントに対して、マテリアライズされた結果をどのように修正しますか？

user_idでイベントを特定し、遅延タイムスタンプの前後の確定した境界を少なくとも1つ含む範囲を読み取り、そのセグメントを再計算して以前のバージョンとの差分を取ります。コンシューマが更新、マージ、取り消しをべき等に適用できるように、出力には安定したビジネスキーとバージョンが必要です。ウォーターマークによって24時間前の出力の変更が禁止されている場合は、イベントを隔離し、黙って無視するのではなくデータ品質シグナルを出力します。

フォローアップ4：数十億件のイベントに対してこれをどのように最適化しますか？

プルーニング可能な時間パーティションを読み取り、(user_id, occurred_at, event_id)の順序を利用してソートを削減します。ファイルや日付の境界がセッションの境界になってしまわないように、定期的なジョブによって各ユーザーの最後のイベントとオープンセッションの状態をパーティション境界を越えて引き継ぎます。実際のユーザースキュー、ソートスピル、スキャンバイト数、エンドツーエンドのレイテンシを用いて設計を検証します。単一のスーパーユーザーによるホットスポットには、専用の順序付きパスが必要になる場合があります。

フォローアップ5：イベントが省略されたり2回カウントされたりしていないことをどのように証明しますか？

保存則のチェックを使用します。最終的なevent_countの値の合計は、重複排除された入力行数と等しくなければなりません。すべてのevent_idは正確に1つの(user_id, session_seq)にマッピングされます。セッションシーケンスは1から始まり、ユーザーごとに連続して増加します。セッション内の隣接する間隔は30分未満であり、セッション間の境界は30分以上です。その後、シャッフルされた入力、再送、同一タイムスタンプ、パーティションの境界、遅延イベントのマージなどを用いたプロパティテストを実行します。

公開情報ソース

Sessionization Using Window Functions

DataVidhya

PostgreSQL 18 Documentation: Window Functions

PostgreSQL Global Development Group

PostgreSQL 18 Documentation: Date/Time Types

PostgreSQL Global Development Group

関連する質問

データサイエンス面接：確率のキャリブレーション（Calibration）をどのように評価するか？質問と回答を開くデータサイエンス面接：情報漏洩（リーケージ）なしで時系列予測モデルを検証するには？質問と回答を開くSQL面接対策：過去7日間の移動アクティブユーザー数（Rolling 7-Day Active Users）の算出質問と回答を開く
無料で登録