プロンプトと適用されるコンテキスト
アナリティクステーブルには events(event_id, user_id, event_at, event_name, is_internal_user) が含まれています。 event_at は PostgreSQL の timestamptz です。ユーザーは、少なくとも1つの app_open、 view_dashboard、または run_report イベントを発生させた場合にアクティブとみなされます。社内ユーザーはカウントされません。
2026年6月1日から6月30日までの America/New_York カレンダー日付ごとに1行を返してください。各 レポート日について、rolling_7d_active_users はその日付または 過去6カレンダー日付のいずれかでアクティブだった対象となる個別ユーザーの数です。したがって、6月1日には5月26日から6月1日までの アクティビティ（両端を含む）が必要となります。3日間にわたって20回のイベントを発生させたユーザーであっても、そのウィンドウ内では1回のみカウントされます。
クエリはユーザーが0人の日付を保持し、明示的な時間境界を使用し、遅延して到着した イベントが以前に公開された結果にどのように影響するかを明記する必要があります。本問の核心的な課題は、ローリング形式での distinct union（個別ユーザー集合の和集合）の算出です。すでに集計された 日次カウントの移動合計ではありません。
面接官が評価するポイント
第一の評価基準は、構文の前にメトリクス定義が明確であるかです。優秀な候補者は、適格なイベント、 除外対象、レポート対象のタイムゾーン、出力の粒度（grain）、7日間の両端を含むウィンドウ、およびデータの 完全性境界（completeness boundary）を明示します。これらが定義されていないと、構文的に正しい2つのクエリであっても異なる質問に回答してしまう可能性があります。
第二の評価基準は、粒度のコントロールです。生イベントはまずユニークな (user_id, activity_date) のペアにする必要があります。これにより同一日の重複が排除されますが、複数日にまたがるユーザーは意図的に保持されます。最終的な ウィンドウでは、それらのユーザーセットの distinct union をカウントします。
第三の評価基準は、安易な近道を退けられるかどうかです。7日分のDAU値を合計すると、ユーザーが アクティブだった日数ごとに1回ずつカウントされてしまいます。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW は7行を表すものであり、必ずしも7つの カレンダー日付を表すわけではありません。また、日次カウントに対してこれを適用しても、日をまたいだ distinct union を再構築することはできません。
最後の評価基準は、本番環境を見据えた判断力です。要求された期間の前の6日間のウォームアップ期間をスキャンする、カレンダースパインによって 空の日付を保持する、宣言されたタイムゾーンを使用してタイムスタンプを変換する、遅延イベントの 更新セマンティクスを定義する、そして正確または近似のスケーリング戦略を意図的に選択できるかどうかが評価されます。
回答前に確認すべき明確化のための質問
- 何をもってアクティブとみなしますか？ ログインのみの定義と、意味のある
プロダクトイベントによる定義では結果が異なります。イベント名やボット・社内ユーザーの除外はメトリクスの契約に含まれます。
- どの日付を基準とするタイムゾーンですか？ この回答では
America/New_Yorkを使用します。UTCを使用すると、
現地の深夜付近のイベントが別のレポート日に移動してしまいます。
- ウィンドウは7カレンダー日付ですか、それとも168経過時間ですか？ プロンプトは現地カレンダー日付を求めています。
夏時間の移行により、これら7日間の合計が167時間または169時間になることがあります。
- 両方の境界は含まれますか（inclusive）？ ユーザーセットは
report_date - 6からreport_dateまでをカバーします。
ソースのタイムスタンプフィルターでは、翌日の午前0時が二重にカウントされるのを防ぐために半開区間を使用します。
- 欠損している日付も出力に含める必要がありますか？ はい。既存のイベントから日付を導出するのではなく、
30日分すべてのレポート日を生成してください。
- イベントテーブルの完全性はどの程度ですか？ イベントが3日遅れて到着する可能性がある場合、直近の結果は
暫定的なものになるか、明示的なウォーターマークが必要になります。SQL単体では不完全な入力を確定値にすることはできません。
- 厳密な個別カウント（exact distinct）が必要ですか？ 面接のクエリでは厳密な値が求められます。超大規模な環境では、
エラー許容契約が承認された場合に限り、マージ可能なセット表現（近似）が許容される場合があります。
- どのデータベースおよびスケールが適用されますか？ この回答ではPostgreSQLを使用します。データウェアハウスでは、
同じ集合セマンティクスを保持したまま、別の date-spine 関数やビットマッププリミティブを使用する場合があります。
30秒回答フレームワーク
「まず、アクティブイベント、除外条件、日の境界としての America/New_York、およびレポート日ごとに1行という 出力粒度を定義します。6月1日にはそれ以前の6日分の日付が必要なため、5月26日からスキャンを行い、 タイムスタンプを現地日付に変換し、ユーザー×日付ごとに1行に重複排除します。次に generate_series で 6月1日から6月30日までを生成し、各日付を date - 6 からその日付までのアクティビティに left join して、 個別ユーザー数をカウントします。
複数日にわたってアクティブなユーザーが重複してカウントされるため、日間アクティブユーザー数を単純に合計することはしません。 また、日付が欠損している場合、6行ウィンドウでは正しく機能せず、distinct union も作成できません。 深夜の境界、重複、空の日付、およびウォームアップ期間のアクティビティをテストし、遅延イベントが到着した際には as-of ウォーターマークを公開するか直近の日付を再計算します。」
ステップ別の詳細解説
ステップ 1: メトリクス契約と入力範囲の確定
要求される出力は6月1日からですが、ソースのスキャンは5月26日から開始します。6月の行のみを読み取ると、 最初の6つのレポート日で過小カウントが発生します。ソースの上限境界は現地時間の7月1日午前0時です。6月30日を 末尾とするローリングウィンドウにとって、それ以降のアクティビティは無関係です。
これらの現地時間の午前0時を、フィルター内で timestamptz 定数に変換します。これにより、インデックスが作成された event_at 列の述語が維持されます。WHERE 句の内部で各ソーストランスファーを日付型にキャストすると、 通常の範囲インデックスによるプルーニングが機能しなくなる可能性があります。
ステップ 2: イベントを個別のユーザー日（user-days）に正規化
範囲を絞り込んだタイムスタンプフィルターを適用した後にのみ、各適格なインスタントをニューヨークのカレンダー日付に変換します。 その後、user_id と現地日付でグループ化します。異なる行IDを持つイベントのリトライや、同一ユーザーによる1日の20件の イベントは、すべて1つのユーザー日になります。
この重複排除だけで最終的な問題が解決するわけではありません。同じユーザーが6月1日と 6月2日にアクティブである場合、どちらの日付のローリングウィンドウにもそのユーザーを含められるよう、両方のユーザー日の行が利用可能である必要があります。
ステップ 3: 完全な日付スパイン（Date Spine）の生成
generate_series は、イベントの有無に関係なくすべてのレポート日を生成します。イベントテーブルから 開始すると、アクティビティのない日付が省略され、ROWS フレーム内の行数が変化し、ダッシュボード上に 値がゼロのポイントが表示されなくなります。日付スパインが出力の確定的な粒度となります。
ステップ 4: 各ウィンドウの distinct union をカウント
直接的な厳密解クエリは以下の通りです：
WITH params AS (
SELECT
DATE '2026-06-01' AS report_start,
DATE '2026-06-30' AS report_end
),
activity_days AS (
SELECT
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
FROM events AS e
WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
AND e.event_at < TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
AND e.is_internal_user = false
GROUP BY
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
SELECT gs::date AS report_date
FROM params AS p
CROSS JOIN generate_series(
p.report_start,
p.report_end,
INTERVAL '1 day'
) AS gs
)
SELECT
d.report_date,
COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;left join により、アクティビティのないレポート日も保持されます。COUNT(DISTINCT a.user_id) は、マッチしなかった left join によって生成された NULL を無視します。このインターバルには、正確に7つの date 値（当日と先行する6日） が含まれます。
ステップ 5: 一般的なウィンドウ関数のショートカットが失敗する理由の証明
ユーザーAが月曜日と火曜日にアクティブで、ユーザーBが火曜日のみにアクティブであると仮定します。DAUはそれぞれ1と2ですが、 2日間の distinct union は3ではなく2です。日次カウントによってユーザー識別子が置き換えられてしまうと、SQLは Aが両日に存在することを検出できなくなります。
行フレーム（row frame）を使用すると別のエラーが発生します。水曜日にイベントがなく、入力データに存在しない場合、「先行する6行」は カレンダー上で8日以上過去にさかのぼってしまう可能性があります。日付スパインを使用すればカレンダーの欠損は解消されますが、 DAUに対する移動合計は依然として識別子を二重にカウントします。正しい処理順序は、まず和集合（union）を取り、次に濃度（cardinality）を計算することです。
ステップ 6: セマンティクスを変えずにスケールさせる
中規模のデータ量の場合、生データに event_at のインデックスを作成し、7倍のインターバル結合を行う前に ユーザー日に集約します。(activity_date, user_id) をキーとする日次アクティビティのマテリアライズドテーブルを使用することで、 生イベントの再スキャンを回避できます。パーティションプルーニングにはウォームアップ期間の日付を含める必要があります。
より長いレポート期間の場合、各ユーザー日を最大7つの対象レポート日に展開し、それらの日付を 要求された範囲に限定してから、個別ユーザーを集計します。これにより結合の形状は変わりますが、ワーストケースの7倍の 展開自体は変わりません。厳密なビットマップセットをサポートするエンジンでは、日次ユーザービットマップを結合できます。近似スケッチは セットの結合をサポートし、測定されたエラー率を開示する必要があります。日次の HyperLogLog 推定値を単純加算することは無効です。 近似カーディナリティを加算しても和集合を求めることはできないためです。
ステップ 7: 遅延データと検証の定義
結果とともに as_of ウォーターマークを公開します。パイプラインが最大3日の遅延イベントを受け入れる場合、 7日間の入力ウィンドウが変更対象データと交差するすべてのレポート日を少なくとも再計算します。安定した イベントIDは取り込み時の重複排除に役立ち、ユーザー日のグループ化はこのメトリクスを複数の適格イベントから保護します。ただし、 いずれも完全性モニタリングの代わりにはなりません。
重複イベント、複数日にわたる同一ユーザー、社内ユーザー、不適格イベント、5月26日および5月31日のウォームアップイベント、 空の日付、両側の現地深夜のインスタント、夏時間の境界を含む手動テストデータ（テストオラクル）を用意します。各出力日を、 単純なアプリケーションレベルのセットユニオンと比較します。本番相当のデータ量で EXPLAIN (ANALYZE, BUFFERS) を実行し、ソースのプルーニング、 ユーザー日のカーディナリティ、結合の展開、実行時間、およびディスクへのスピル挙動を検証します。
優れた回答例
「SQLを書く前にまず集合を定義します。適格なユーザーとは承認されたプロダクトイベントを少なくとも1つ発生させたユーザーであり、 社内ユーザーは除外され、1日は America/New_York を意味します。レポート日 D に対する集合は、D-6からD（両端を含む）までの間に 現地アクティビティ日を持つすべての適格ユーザーです。出力には30日分すべての日付が含まれている必要があります。
生タイムスタンプを現地時間の5月26日午前0時から7月1日午前0時までフィルタリングし、上限境界は排他（exclusive）とします。 フィルタリング後、現地日付に変換してユーザーと日付でグループ化します。generate_series スパインが 6月1日から6月30日を提供します。各スパイン日付はその先行インターバル内のユーザー日と left join し、COUNT(DISTINCT user_id) が 和集合のカーディナリティを返します。
複数日にわたってアクティブな識別子が重複カウントされるため SUM(DAU) は使用せず、行はカレンダー日付ではなく日次集計によって識別子が失われるため ROWS 6
PRECEDING も除外します。スケール対策としては (activity_date, user_id) をマテリアライズし、ウォームアップ範囲をプルーニングします。厳密な出力が求められない場合に限り、 厳密なビットマップユニオンまたは測定済みの近似セットユニオンを検討します。結果には as_of ウォーターマークが付与され、 遅延データが発生した場合は範囲を限定した再計算がトリガーされます。」
よくある間違い
- 7つのDAU値を合計する → リピートユーザーがアクティブな日ごとにカウントされる → ウィンドウ全体でユーザー識別子の和集合（union）を取り、その後にカウントする。
- 疎な日付データに対して
ROWS 6 PRECEDINGを使用する → 6行が過去6日を超える期間にまたがる可能性がある → 完全なカレンダースパインを生成し、カレンダー境界を明示する。 - 6月のみをスキャンする → 6月上旬のウィンドウで5月のアクティビティが失われる → 6日間のウォームアップを含める。
- ソースフィルター内で
event_atのキャストを行う → 通常のタイムスタンプインデックスが効率的にプルーニングされない可能性がある → 現地日付を導出する前に、半開区間のtimestamptz境界でフィルタリングする。 - 生のイベントをカウントする → リトライや頻繁な利用によりユーザー数が水増しされる → ユーザー日単位で重複排除し、かつ最終ウィンドウ全体で distinct カウントを行う。
- イベントから出力日付を導出する → アクティビティのない日付が消失する → 日付スパインを出力粒度とする。
- 直近の出力を確定値とみなす → 遅延イベントによって集合が変化する可能性がある → ウォーターマークを公開し、影響を受けるウィンドウを再更新する。
- 近似日次カーディナリティを加算する → カーディナリティの加算では重複を排除できない → ユニオンを推定する前に、セット操作に対応したスケッチまたはビットマップをマージする。
フォローアップ質問と回答
フォローアップ 1: メトリクスが「過去168時間」を意味する場合、何が変わりますか？
現地の date 値の代わりにインスタントを比較します。各レポートインスタントについて、製品定義上の正確な境界を明示した上で、 (report_at - interval '168 hours', report_at] などの半開タイムスタンプウィンドウを使用します。 夏時間の移行期には、これがニューヨークの7カレンダー日付と異なる場合があります。一方の定義をもう一方の定義と混同してはいけません。
フォローアップ 2: ウィンドウ関数単体で厳密な移動個別カウント（rolling distinct）を解決できますか？
ウィンドウ集約は、移動合計のように集約が行の値から構成される場合に有効です。今回のケースで 必要な状態は、古い日付がウィンドウから外れた際に要素を削除できるセット（集合）です。PostgreSQLでの直接的な解法は、 識別子を保持したままレポート日に結合することです。専用エンジンでは厳密なビットマップユニオンのウィンドウ処理が提供されている場合がありますが、 それはエンジンの機能であり、日次カウントを合計してよい理由にはなりません。
フォローアップ 3: イベントが3日遅れて到着した場合、どのように再更新しますか？
イベントの現地アクティビティ日 A を特定します。これは、公開範囲と交差する A から A+6 までのレポート日に影響を与える可能性があります。 それらのパーティションのみを再計算または置換し、整合性確認後にウォーターマークを進め、処理の冪等性（idempotency）を 維持します。日付 A のみを更新すると、下流にある6つのウィンドウを見落とすことになります。
フォローアップ 4: 国別にセグメント化するにはどうすればよいですか？
まず、国情報がイベントに属するのか、ユーザーの現在のプロファイルに属するのか、それともアクティビティ時点の SCD（Slowly Changing Dimension：徐々に変化するディメンション）プロファイルに属するのかを定義します。この選択によって過去の事実が変わります。選択した国を ユーザー日の粒度、日付スパイン、グループ化、および検証用オラクルに追加します。現在のプロファイルに結合すると、ユーザーの転居時に過去の履歴が書き換わる可能性があります。
フォローアップ 5: 厳密な個別カウントのコストが高すぎる場合はどうしますか？
まず厳密なクエリを計測します。承認されたエラーバジェットで近似が許容される場合は、アクティビティ日およびセグメントごとに マージ可能なセットスケッチを1つ保存し、7つのスケッチをユニオンして1回で推定します。低・通常・高カーディナリティの各スライスについて、 厳密なセットと比較してバイアスと相対誤差を検証します。請求、利用資格判定、その他推定誤差が許容されない決定には、引き続き厳密な処理を使用してください。