プロンプトと適用コンテキスト
次のPostgreSQLイベントテーブルが与えられます：
CREATE TABLE user_logins (
user_id bigint NOT NULL,
login_at timestamptz NOT NULL
);すべてのユーザーについて、そのユーザーが少なくとも1回ログインした連続するAmerica/New_Yorkカレンダー日の最長期間をすべて返してください。出力列はuser_id、streak_start、streak_end、およびstreak_daysです。同じローカル日における複数のイベントは、1アクティブ日としてカウントされます。ローカル日が1日でも欠落すると連続記録（ストリーク）は途切れます。ユーザーに同じ長さの最長ストリークが2つある場合は、両方を返してください。イベントのないユーザーは結果に含めません。
これはデータおよびSQLの面接問題であり、経過した24時間間隔をカウントする要求ではありません。夏時間の移行期におけるローカル日は23時間または25時間になる場合がありますが、それでも1カレンダー日です。したがって、この問題ではタイムスタンプを日付に変換する前にレポート用タイムゾーンを確定します。主な解答はPostgreSQLを対象としています。他のダイアレクトでは異なる日付演算が必要です。
中核となるタスクはGaps and Islands（ギャップとアイランド）問題です。連続する1つの期間に含まれるすべての日付が安定した同一キーを共有するように順序付けられた日付を変換し、各キーをアイランドに集約してから、ユーザーごとの最大長と同率のアイランドをすべて保持します。
面接官が評価するポイント
1つ目のシグナルは、候補者がウィンドウ関数を記述する前にデータの粒度（Grain）を定義しているかどうかです。ソースの粒度は1ログインイベントですが、ビジネス上の粒度はユーザーおよびローカルカレンダー日あたり1行です。その変換をスキップすると、同日の重複イベントによってROW_NUMBER()、カウント、およびストリークの境界が不当に増加してしまいます。
2つ目のシグナルは、候補者がアイランドキーを導出できるかどうかです。一意な日付をソートした後、連続する期間内では日付とROW_NUMBER()の両方が1ずつ進みます。したがって、各日付から行番号のオフセットを減算すると、その期間全体で常に同一の値が生成されます。ギャップが生じると、行番号は正確に1進むのに対して日付は2日以上飛ぶため、キーが変化します。
3つ目のシグナルは、仕様（契約）に対する厳密さです。「最長ストリーク」という表現は、同じ長さの期間が2つある場合に曖昧になります。1つの結果を選択するためにROW_NUMBER()を使用するクエリは、有効な同率結果を暗黙的に破棄してしまいます。このプロンプトでは同率の最大値をすべて返すことが求められているため、解答では各アイランドの長さとそのユーザーの最大アイランド長を比較します。
4つ目のシグナルは、タイムゾーンの正確性です。login_atを直接dateにキャストすると、環境によって異なる可能性があるデータベースセッションのタイムゾーンが使用されます。解答では、各timestamptzをまず指定されたビジネスタイムゾーンに変換し、その後に日付を取得します。夏時間のルールによってオフセットが変化するタイムゾーンに対しては、固定のUTCオフセットでは不十分です。
最後のシグナルは、検証とスケーラビリティの判断力です。正しいクエリは、重複イベント、1日のみの期間、ギャップ、同率、ローカル深夜（24:00/0:00）のケース、および夏時間の境界ケースを用いて、各CTEごとにテストされる必要があります。繰り返しクエリされる大規模なイベントテーブルでは、生のイベントをユーザー日あたり1行の保存データに削減する方が、最終的なウィンドウクエリを微細に最適化するよりも価値が高い場合があることを候補者が認識しているかどうかが問われます。
回答前に確認すべき質問
- 1日はどのように定義されますか？ 指定されたビジネスタイムゾーン、UTC、または各ユーザー自身のタイムゾーンのいずれを使用するかによって、日付変換が変化し、場合によっては結果も変わります。このプロンプトでは、すべてのユーザーに対して
America/New_Yorkを使用します。 - 1日に複数回ログインした場合、複数回カウントされますか？ ここでは複数回カウントされないため、番号付けの前に重複排除を行う必要があります。代わりに連続したイベントがメトリクスである場合、粒度とグループ化のルールが変わります。
- 日付が1日でも抜けると必ずストリークは途切れますか？ はい。30分のしきい値を持つセッショナライゼーションの問題では、厳密なカレンダー日の隣接性ではなく、前の行との比較が必要になります。
- 同率の結果はどのように返すべきですか？ 今回の仕様では、同率最長のアイランドをすべて返します。最新のストリークのみを選択する場合は、別の明示的なタイブレーク条件が必要になります。
- 範囲は限定されていますか？ 日付フィルターを使用すると処理量を削減できますが、その範囲より前から始まっているストリークが切り捨てられます。呼び出し元は、結果が「範囲内のもの」であるべきか、境界を越える「完全なストリーク」であるべきかを明示する必要があります。
user_idやlogin_atはNULLになり得ますか？ スキーマ上は不可です。NULLが許可されている場合は、ソートやグループ化の前にその処理方法を指定する必要があります。- これはアドホックな単発クエリですか、それとも定期的なプロダクトメトリクスですか？ 単発の回答であれば日次行をスキャンしてソートすれば十分です。頻繁に更新されるダッシュボードであれば、増分更新されるユーザー日テーブルを保持することが正当化されます。
30秒の回答フレームワーク
「まず、各timestamptzを合意されたビジネスタイムゾーンに変換し、ユーザーおよびローカル日ごとに1行となるよう重複を排除します。各ユーザー内でそれらの日付をソートし、ROW_NUMBER()を割り当てます。厳密に連続する日付の場合、login_day - row_number × one dayはストリーク内では一定であり、ギャップがあると変化するため、その導出キーでグループ化して各ストリークの境界と長さを取得します。その後、各長さをユーザーの最大値と比較することで、同率の記録を保持します。同一日の重複イベント、1日のみのストリーク、ギャップ、同率の最大値、ローカル深夜や夏時間のケースをテストし、代表的なデータで実行計画を確認します。」
ステップごとの詳細解説
イベントストリームをビジネスの粒度に正規化することから始めます。timestamptzに対して指定されたタイムゾーンでAT TIME ZONEを使用すると、そのゾーンにおける現地時計のタイムスタンプが生成されます。その結果をdateにキャストすると、ビジネスカレンダー日が得られます。その後、SELECT DISTINCTによってユーザー日ごとに正確に1行であることが保証されます。
完全なクエリは次のとおりです：
WITH login_days AS (
SELECT DISTINCT
user_id,
(login_at AT TIME ZONE 'America/New_York')::date AS login_day
FROM user_logins
),
numbered AS (
SELECT
user_id,
login_day,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_day
) AS rn
FROM login_days
),
grouped AS (
SELECT
user_id,
login_day,
login_day - (rn * INTERVAL '1 day') AS island_key
FROM numbered
),
streaks AS (
SELECT
user_id,
MIN(login_day) AS streak_start,
MAX(login_day) AS streak_end,
COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, island_key
),
scored AS (
SELECT
user_id,
streak_start,
streak_end,
streak_days,
MAX(streak_days) OVER (PARTITION BY user_id) AS max_streak_days
FROM streaks
)
SELECT
user_id,
streak_start,
streak_end,
streak_days
FROM scored
WHERE streak_days = max_streak_days
ORDER BY user_id, streak_start;証明は、ソートされた日次行に基づきます。ある1人のユーザーについて、一意な日付をd1, d2, ...、行番号を1, 2, ...とします。d(i+1) = d(i) + 1 dayである場合、次の行番号オフセットを引くと同じ追加日数が差し引かれるため、導出されるキーは等しくなります。1日以上の日付が欠落している場合、d(i+1)は2日以上進むのに対し、行番号は1しか進みません。そのため、導出キーが増加して新しいグループが開始されます。重複排除によりCOUNT(*)はカレンダー日数と等しくなり、MINとMAXは正確なアイランドの境界となります。
最終的な最大値には、別のROW_NUMBER()ではなく、意図的にウィンドウ関数のMAXを使用しています。長さがユーザーの最大値と等しいすべてのアイランドが残ります。プロダクト側で後から1つのストリークのみが要求された場合は、「最新の終了日を優先する」などの明示的なルールを追加し、決定論的なソートを使用します。クエリ内で勝手にルールを作ってはいけません。
2人のユーザーの正規化された日付を考えてみます：
user 1: Mar 07, Mar 08, Mar 09, Mar 11, Mar 12
user 2: Nov 01, Nov 02, Nov 04, Nov 05
result:
user 1 | Mar 07 | Mar 09 | 3
user 2 | Nov 01 | Nov 02 | 2
user 2 | Nov 04 | Nov 05 | 2ユーザー1の最大値は3日です。ユーザー2には2日間の最大値が2回あるため、両方の行が必要です。表示されている日付内で未加工イベントが複数あっても結果は変わりません。夏時間の変更前後において重要な問題は、タイムスタンプが正確に24時間離れているかではなく、ローカル日付が隣接しているかどうかです。
N件の未加工イベントとD件の一意なユーザー日行がある場合、重複排除ステップではN行を読み込み、ハッシュ化またはソートを行う可能性があります。ウィンドウステップでは、最大D行をユーザーと日付でソートします。面接で有用な計算量の上限目安は、ソートベースの計画で時間計算量がO(N log N + D log D)、中間領域の空間計算量がO(D)ですが、オプティマイザがハッシュ、既存の順序、並列処理、またはディスクスピルを使用する場合があることにも留意してください。本番クエリとして許容可能かどうかを決定するのは、Big-Oだけでなく実行計画です。
数十億件のイベントに対する定期的なメトリクスでは、(user_id, login_day)に一意キーを持つ増分更新テーブルを作成します。これにより、タイムゾーン変換と同日重複排除が取り込み（インジェスチョン）またはバッチの境界に移行するため、ストリーククエリはN件のイベントではなくD件の日次行を読み込むだけで済みます。アドホッククエリに期間指定がある場合は、ローカル日への変換前に検索引数化可能（sargable）なUTCタイムスタンプの境界を適用しますが、夏時間の変更を尊重するために、それらのUTC境界は指定ゾーンのローカル深夜から導出してください。
最終テーブルをそのまま信用せず、中間結果を検証します：
-- These checks are run against the corresponding CTE or materialized test result.
SELECT user_id, login_day, COUNT(*)
FROM login_days
GROUP BY user_id, login_day
HAVING COUNT(*) > 1;
SELECT *
FROM numbered
ORDER BY user_id, login_day;
SELECT *
FROM streaks
WHERE streak_days <> (streak_end - streak_start + 1);1番目と3番目のチェックは0行を返すべきです。番号付けされた出力を確認することで、粒度やソート順の誤りを可視化できます。クエリ全体の先頭にEXPLAIN (ANALYZE, BUFFERS)を付けて実行し、スキャン、ソート、推定行数、一時I/O、および未加工イベントを早い段階で削減することが重要かどうかを確認します。本番環境のステートメント自体の実行コストが高い場合は、安全で代表的なデータのコピーを使用してください。
日付シフトの手法は汎用的なものではありません。ギャップが30分を超えたときに新しいセッションが開始される場合や、ステータス値が変更されない間アイランドが継続する場合は、LAG()を使用して前の行を検査し、各境界にフラグを立て、それらのフラグの累積SUM()を取得します。判断基準は単純です。厳密な単位ごとの連続シーケンスにはシフトキーを使用し、継続性がカスタム比較に依存する場合は境界フラグを使用します。
高品質な回答例
「SQLを記述する前に、粒度と同率時の仕様を確定させます。ソースにはユーザーごとに多数のイベントが存在しますが、メトリクスはユーザーごとに1つのAmerica/New_Yorkカレンダー日をカウントします。したがって、ウィンドウ関数を適用する前に、timestamptzを指定されたタイムゾーンに変換し、dateにキャストして重複排除を行います。これにより、セッションのタイムゾーンによって結果が勝手に変わることも防げます。
Gaps and Islandsのステップでは、各ユーザー内でローカル日付順にソートされたROW_NUMBER()を割り当てます。連続する期間中、日付と行番号は両方とも1ずつ進むため、行番号の日付オフセットを引くと一定のキーが生成されます。日付が抜けると、日付は行番号よりも大きく進むためキーが変化します。ユーザーとそのキーでグループ化することで、各ストリークの開始日、終了日、アクティブ日数が得られます。
次に、ストリーク長に対してウィンドウ関数の最大値を使用し、その最大値と等しいレコードを保持します。これにより、1つを勝手に選択することなく、要件どおり同率最長のストリークをすべて返すことができます。ソートベースの上限計算量は概ねO(N log N + D log D)です（ここでNは未加工イベント数、Dは一意のユーザー日数）。ただし、実際の実行計画とスピルの有無を確認します。
テストデータには、同日の複数イベント、1日のみのユーザー、日付の抜け、2つの等しい最大値、ローカル深夜をまたぐイベント、および夏時間の移行を含めます。大規模な定常メトリクスに対しては、一意なユーザー日テーブルを保持し、その小さな粒度に対してウィンドウロジックを実行します。継続性の条件がカレンダーの隣接性からしきい値ギャップに変更された場合は、LAG()と境界フラグ、および累積和のアプローチに切り替えます。」
よくある間違い
- 未加工のログインイベントに番号を振る → 重複イベントによって行番号が進み、カウントが水増しされる → ウィンドウ関数を適用する前に1ユーザー日1行に重複排除する。
timestamptzを直接dateにキャストする → 結果がセッションタイムゾーンに依存してしまう → まず指定されたビジネスゾーンに変換する。- 固定のUTCオフセットを使用する → 指定ゾーンのオフセットが切り替わったときにローカル日が不正確になる → カレンダールールを持つIANAタイムゾーンを使用する。
- 24時間間隔でタイムスタンプを比較する → 23時間または25時間のローカル日によって有効なカレンダーストリークが途切れる → 仕様はカレンダー日の隣接性であるため、ローカル日を比較する。
- シフトされた日付のみでグループ化する → 同じ導出キーを持つ別ユーザー同士が混ざってしまう →
user_idとisland_keyの両方でグループ化する。 ROW_NUMBER()で1行のみを取得する → 同率最長のストリークが破棄される → 各アイランドをユーザーごとの最大値と比較する。- 境界ルールを決めずに集計対象期間をフィルターする → 開始日をまたぐストリークが切り捨てられ、誤ったラベルが付く可能性がある → 結果を範囲内限定とするか、完全なアイランドとするかを定義する。
- 最初の行を処理せずに
LAG()を使用する → 最初のアイランドに境界がなくなる → NULLの前の行をグループの開始として処理する。 - Big-Oのみを提示する → ソートのディスクスピルやカーディナリティの不適切な見積もりが考慮されない → 中間カウントと
EXPLAIN (ANALYZE, BUFFERS)を確認する。 - ダッシュボードの更新ごとに未加工の履歴を全スキャンする → 繰り返される変換と重複排除がコストの大半を占める → ワークロードが正当化される場合は、一意なユーザー日粒度のデータを保持する。
フォローアップ質問と回答
フォローアップ1：最新の最長ストリークのみを返すにはどうすればよいですか？
アイランドの構築方法はそのまま維持します。ストリークを計算した後、ユーザーごとにstreak_days DESC、次いでstreak_end DESC、最後に決定論的な最終タイブレークとしてstreak_start DESCでランク付けします。ランク1を返します。この場合、出力の仕様が変更され、同じ長さの記録がすべて残るわけではなくなることを明示します。
フォローアップ2：30分間の非アクティブ状態の後にセッションが終了する場合は何が変わりますか？
カレンダーの引き算では継続性をモデル化できなくなります。イベントをタイムスタンプ順にソートし、ユーザーごとにLAG(login_at)を使用し、最初の行または30分を超えるギャップを新しいセッションとしてマークし、明示的なROWS UNBOUNDED PRECEDINGフレームでそのフラグの累積SUMを計算します。ユーザーと生成されたセッションIDで集約します。
フォローアップ3：各ユーザー自身のタイムゾーンにはどのように対応しますか？
イベントをその発生時点で有効なバージョン管理されたユーザータイムゾーンの値と結合し、日付を取得する前に変換します。単一の現在のプロファイル設定を使用すると、ユーザーが引っ越した後に過去の日付が書き換わってしまう可能性があります。プロダクト側が、過去のアクティビティを当時のタイムゾーンで固定したいのか、現在のタイムゾーンで再計算したいのかを明確にします。これらは異なるメトリクスです。
フォローアップ4：直近90ローカル日のみをクエリするにはどうしますか？
ストリークが対象期間の前に始まってよいかどうかを定義します。期間内限定の結果の場合は、指定ゾーンにおける開始時と終了時のローカル深夜に対応する2つのUTC時点を導出し、それらの境界でlogin_atをフィルタリングしてから正規化します。完全なアイランドを取得する場合は、最初の実際のギャップを見つけるために十分な先行日次行を含めます。機械的に90日でカットすると真の開始日を特定できません。
フォローアップ5：日次ダッシュボード向けにこれを効率化するにはどうしますか？
一意キーと冪等なUPSERTを備えたuser_login_days(user_id, login_day)を保持します。合意されたタイムゾーンルールを使用して、イベントパイプラインからテーブルを更新します。日次行が変更されたユーザーのみを再計算するか、遅延到着イベントを取り込むために重複期間（オーバーラップウィンドウ）から定期的に再構築します。公開前に、日次行のカウントを生のソースと照合します。
フォローアップ6：リリース前にどのようなテストを要求しますか？
重複、1行のみのユーザー、内部のギャップ、同率の最大値、ローカル深夜のイベント、夏時間の開始と終了、遅延到着イベント、およびレポート境界のまたがりを対象としたテーブル駆動テスト（table-driven fixtures）を使用します。日次行が一意であること、すべてのアイランドがstreak_days = streak_end - streak_start + 1を満たしていること、および返されたすべてのストリークがそのユーザーの最大値と等しいことをアサートします。 サンプリングしたユーザーに対して増分日次テーブルと未加工イベントの再計算結果を比較し、本番相当のカーディナリティでクエリプランと一時I/Oを確認します。