SQL Quiz Book

SQL イベントモデリング — セッション・DAU/MAUの基礎

基礎イベントモデリングウィンドウ関数セッション / 行動パスDAU / MAUPostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 1

イベントピボット集計 — FILTER(WHERE ...) + GROUP BY でイベント種別を横展開する

FILTER WHEREGROUP BYピボット変換イベント集計
前提知識 ▼

イベントモデリングでは、アクション（クリック・購入・閲覧など）をすべて 1テーブルの縦持ち（tall format）で記録します。分析時はこれを横持ち（wide format）に変換することが頻出です。PostgreSQL の FILTER (WHERE ...) 句を使うと、各イベント種別を1行の中で集計できます。

-- 縦持ち（イベントテーブルの標準形）
user_id | event_type | event_time
      1 | view       | 2024-01-10 10:00
      1 | click      | 2024-01-10 10:05
      1 | purchase   | 2024-01-10 10:30

-- 横持ちに変換したい結果イメージ
user_id | view_cnt | click_cnt | purchase_cnt
      1 |        1 |         1 |            1
FILTER が COUNT の前処理フィルタとして機能する：COUNT(*) FILTER (WHERE event_type = 'view') は、その GROUP のうち条件を満たす行だけを COUNT します。イベントが存在しないユーザーのカウントは 0 になり（NULLではない）、これが後続の割り算でゼロ除算を防ぐ利点になります。
問題 ▼

user_events テーブルから、ユーザーごとに view・ click・ purchase の各イベント発生回数を横持ちで集計してください。出力列は user_id, view_cnt, click_cnt, purchase_cnt、user_id 昇順で返してください。

使用テーブル
► user_events（10行）
user_idevent_typeevent_time
1view2024-01-10 10:00
1click2024-01-10 10:05
1purchase2024-01-10 10:30
2view2024-01-10 11:00
2click2024-01-10 11:10
3view2024-01-11 09:00
3view2024-01-11 09:15
4view2024-01-11 14:00
4click2024-01-11 14:20
4purchase2024-01-11 14:50
期待出力 ▼
user_idview_cntclick_cntpurchase_cnt
1111
2110
3200
4111
模範解答コード ▼ 
SELECT
  user_id,
  COUNT(*) FILTER (WHERE event_type = 'view')      AS view_cnt,  -- 種別ごとに条件カウント
  COUNT(*) FILTER (WHERE event_type = 'click')     AS click_cnt,
  COUNT(*) FILTER (WHERE event_type = 'purchase')  AS purchase_cnt
FROM   user_events
GROUP BY user_id
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM user_events       → 行を読込
  2. GROUP BY user_id       → グループ化
  3. COUNT(*) FILTER (...)  → イベント種別ごとに条件カウント
  4. ORDER BY user_id       → 並び替えて出力
  */
解説（テーブル変化・ポイント） ▼
SELECT user_id, COUNT(*) FILTER (WHERE event_type = 'view') AS view_cnt, COUNT(*) FILTER (WHERE event_type = 'click') AS click_cnt, COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_cnt FROM user_events GROUP BY user_id ORDER BY user_id;
LEGEND ▼
データ取得・読込対象
① FROM user_events（10行） ▼
FROM user_eventsuser_events テーブルの10行を読み込みます。event_type が view / click / purchase の3種類あり、これを横展開するのが今回の目的です。
1 / 5
user_idevent_typeevent_time
1view2024-01-10 10:00
1click2024-01-10 10:05
1purchase2024-01-10 10:30
2view2024-01-10 11:00
2click2024-01-10 11:10
3view2024-01-11 09:00
3view2024-01-11 09:15
4view2024-01-11 14:00
4click2024-01-11 14:20
4purchase2024-01-11 14:50
10行読込（4ユーザー × 複数イベント）
学習ポイント
FILTER vs SUM(CASE WHEN ...) の違い：COUNT(*) FILTER (WHERE event_type = 'view') はマッチしない行を 0 としてカウントし NULL を返さないため、後続の割り算で NULLIF ガードが不要です。SUM(CASE WHEN ... THEN 1 ELSE 0 END) も同等ですが FILTER の方が可読性が高く、PostgreSQL のオプティマイザがより最適化しやすい形式です。
COUNT(*) vs COUNT(DISTINCT user_id) の使い分け：今回はイベントの「発生回数」を集計したいので COUNT(*) を使います。「そのイベントを1回でも発生させたユーザー数」を集計したい場合は COUNT(DISTINCT user_id) FILTER (...) になります。分析の目的（回数 vs ユニーク数）を明確に意識することが重要です。
縦持ちを横持ちに変換するメリット：横持ち変換後は各ユーザーの全行動が1行に収まるため、ユーザー行動プロファイルのクラスタリング・機械学習の特徴量エンジニアリングに直接使えます。「view はしたが purchase しなかったユーザー」のセグメントも WHERE view_cnt > 0 AND purchase_cnt = 0 と簡潔に書けます。
アンチパターン
GROUP BY なしで FILTER を使う：GROUP BY を省略すると全テーブルが1グループとして扱われ、全ユーザーの合計カウントが返ります。「ユーザーごとの集計」には必ず GROUP BY user_id が必要です。
event_type が増えるとメンテナンスが大変：新しいイベント種別が追加されるたびに SELECT 句を手動で更新する必要があります。event_type が動的に変わる場合は jsonb_object_agg(event_type, cnt) や crosstab() を検討してください。
実務コラム：イベントテーブルの縦持ち設計が標準な理由
「なぜ最初から横持ちで記録しないのか？」という疑問が湧くかもしれません。縦持ち設計の利点は3つあります。①スキーマを変えずに新しいイベント種別を追加できる（購入ログに「返品」を追加するだけ）、②各イベントにプロパティ（金额・商品IDなど）を柔軟に付与できる、③ストリーミング処理や Kafka 等のメッセージキューと相性が良い。横持ち変換は分析クエリの段階で行うのが正しい役割分担です。
1 / 5
問題を選択
QUESTION 2

初回イベント抜出 — ROW_NUMBER() OVER (PARTITION BY ORDER BY) で最初のアクションを特定する

ROW_NUMBEROVER PARTITION BY初回イベントウィンドウ関数
前提知識 ▼

ROW_NUMBER() はウィンドウ関数の基本で、指定した PARTITION BY と ORDER BY に従って各行に連番を振ります。これにより「ユーザーごとの最初のイベント」を CTE + WHERE rn = 1 のパターンで抜出できます。

ROW_NUMBER() OVER (
  PARTITION BY user_id          -- ユーザーごとにリセット
  ORDER BY     event_time ASC  -- 最も古い順 → rn=1 が最初のイベント
)
-- ASC → rn=1 が最初のイベント（first touch）
-- DESC → rn=1 が最後のイベント（last touch）
ウィンドウ関数は GROUP BY せずに行ごとの結果を返す：GROUP BY と違い、ROW_NUMBER() は元の行数を保ったまま各行に計算結果（連番）を付与します。その後 CTE に包んで WHERE rn = 1 でフィルタするのが定番パターンです。WHERE 句でウィンドウ関数の結果を直接参照することはできないため、必ず CTE かサブクエリを挟みます。
問題 ▼

user_events テーブルから、ユーザーごとに最初に発生したイベント（event_type と event_time）を1行だけ抜出してください。出力列は user_id, first_event_type, first_event_time、user_id 昇順で返してください。

使用テーブル
► user_events（8行）
user_idevent_typeevent_time
1view2024-01-10 10:00
1signup2024-01-10 10:30
1purchase2024-01-10 11:00
2signup2024-01-11 09:00
2purchase2024-01-11 09:45
3view2024-01-12 14:00
3purchase2024-01-12 15:00
4view2024-01-13 10:00
期待出力 ▼
user_idfirst_event_typefirst_event_time
1view2024-01-10 10:00
2signup2024-01-11 09:00
3view2024-01-12 14:00
4view2024-01-13 10:00
模範解答コード ▼ 
WITH ranked AS (
  SELECT
    user_id,
    event_type,
    event_time,
    ROW_NUMBER() OVER (          -- ウィンドウ関数：GROUP BY しない
      PARTITION BY user_id        -- ユーザーごとに番号をリセット
      ORDER BY     event_time ASC -- 古い順に番号付け → rn=1 が最初
    ) AS rn
  FROM   user_events
)
SELECT
  user_id,
  event_type  AS first_event_type,
  event_time  AS first_event_time
FROM   ranked
WHERE  rn = 1           -- 各ユーザーの最初の1行だけを取り出す
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE ranked
  2. 外側クエリ
  3. SELECT event_type AS first_event_type  → 列名をリネーム
  4. ORDER BY user_id                       → user_id 昇順
  */
解説（テーブル変化・ポイント） ▼
WITH ranked AS ( SELECT user_id, event_type, event_time, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY event_time ASC ) AS rn FROM user_events ) SELECT user_id, event_type AS first_event_type, event_time AS first_event_time FROM ranked WHERE rn = 1 ORDER BY user_id;
LEGEND ▼
データ取得・読込対象
① FROM user_events（8行） ▼
FROM user_eventsuser_events テーブルの8行を読み込みます。user_id ごとに複数のイベントがあり、最初の1行だけを抜出するのが今回の目的です。
1 / 5
user_idevent_typeevent_time
1view2024-01-10 10:00
1signup2024-01-10 10:30
1purchase2024-01-10 11:00
2signup2024-01-11 09:00
2purchase2024-01-11 09:45
3view2024-01-12 14:00
3purchase2024-01-12 15:00
4view2024-01-13 10:00
8行読込（4ユーザー × 複数イベント）
学習ポイント
ROW_NUMBER / RANK / DENSE_RANK の使い分け：同一 event_time のイベントが複数ある場合、ROW_NUMBER は任意の1行に rn=1 を割り当て（決定論的でない）、RANK と DENSE_RANK は同順位に同じ番号を付けるため複数行が rn=1 になります。「1行だけ欲しい」なら ROW_NUMBER、「同着を全部欲しい」なら RANK を使います。
WHERE でウィンドウ関数を直接参照できない理由：SQL の論理実行順序では WHERE はウィンドウ関数より先に評価されます。そのため WHERE ROW_NUMBER() OVER (...) = 1 と書くとエラーになります。必ず CTE かサブクエリで一度包んでから WHERE rn = 1 と参照してください。
最後のイベントを取り出す場合：ORDER BY event_time DESC にするだけで rn=1 が最後のイベントになります。「ユーザーが最後に行ったアクション（ラストタッチ）」の取得もまったく同じパターンです。
アンチパターン
PARTITION BY を省略すると全テーブル横断の連番になる：ROW_NUMBER() OVER (ORDER BY event_time) と書くと、全ユーザーを通じた1本の連番になります。rn=1 はテーブル全体の最古イベント行1件だけになり、「各ユーザーの最初のイベント」にはなりません。
同一 event_time に複数イベントがある場合の ORDER BY：event_time だけで ORDER BY すると、同時刻のイベント間の順序が不定になります。決定論的な結果を保証するには ORDER BY event_time, event_id のようにユニークなキーを追加してください。
実務コラム：ファーストタッチ属性分析への応用
「最初のイベント（first touch）」の抜出は、マーケティング効果測定のファーストタッチ属性モデルに直結します。例えば first_event_type = 'signup' のユーザー（view をスキップして直接登録）は、紹介リンクや SNS 広告経由の流入が多い傾向があります。ROW_NUMBER() で取り出した最初のイベントにキャンペーンIDやリファラー情報をJOINすることで、どの獲得チャネルがLTVの高いユーザーを連れてくるかを分析できます。
2 / 5
問題を選択
QUESTION 3

セッション分析 — LAG() でイベント間隔を計算してセッション境界を検出する

LAGEXTRACT EPOCHセッション分析時系列処理
前提知識 ▼

LAG() はウィンドウ関数で、現在行の1つ前の行の値を参照します。イベントモデリングでは「直前イベントとの時間差」を計算し、一定時間（例: 30分）以上空いた場合を新しいセッション開始と定義するために使います。

LAG(event_time) OVER (
  PARTITION BY user_id      -- ユーザーをまたいで前の行を参照しない
  ORDER BY     event_time    -- 時系列順に並べた上で前の行を取る
)
-- タイムスタンプ差を分に変換する計算
EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60
-- EPOCH = 秒数。÷60 で分に変換
最初のイベントは prev_event_time が NULL になる：各ユーザーの最初のイベントには「前の行」が存在しないため、LAG() は NULL を返します。CASE WHEN prev_event_time IS NULL THEN true とすることで、最初のイベントを常に「新セッション開始」として扱うことができます。
問題 ▼

user_events テーブルから、各イベントの直前イベントとの時間差（分）を計算し、間隔が 30 分以上（または最初のイベント）なら true のセッション開始フラグを付与してください。出力列は user_id, event_type, event_time, prev_event_time, gap_min, is_new_session、user_id / event_time 昇順で返してください。

使用テーブル
► user_events（7行）
user_idevent_typeevent_time
1view2024-01-10 10:00
1click2024-01-10 10:03
1view2024-01-10 10:35
1purchase2024-01-10 10:40
2view2024-01-10 11:00
2click2024-01-10 11:05
2view2024-01-10 12:10
期待出力 ▼
user_idevent_typeevent_timeprev_event_timegap_minis_new_session
1view10:00NULLNULLtrue
1click10:0310:003false
1view10:3510:0332true
1purchase10:4010:355false
2view11:00NULLNULLtrue
2click11:0511:005false
2view12:1011:0565true
模範解答コード ▼ 
WITH with_lag AS (
  SELECT
    user_id, event_type, event_time,
    LAG(event_time) OVER (       -- 直前の event_time を参照
      PARTITION BY user_id         -- ユーザーをまたがらない
      ORDER BY     event_time       -- 時系列順
    ) AS prev_event_time
  FROM   user_events
)
SELECT
  user_id,
  event_type,
  TO_CHAR(event_time, 'HH24:MI') AS event_time,
  TO_CHAR(prev_event_time, 'HH24:MI') AS prev_event_time,
  ROUND(
    EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60  -- 秒→分に変換
  )::int  AS gap_min,
  CASE
    WHEN prev_event_time IS NULL                                        -- 最初のイベント
      OR EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 >= 30  -- 30分以上の空白
    THEN true
    ELSE false
  END AS is_new_session
FROM   with_lag
ORDER BY user_id, event_time;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE with_lag
  2. 外側クエリ
  3. ORDER BY user_id, event_time  → 時系列順
  */
解説（テーブル変化・ポイント） ▼
WITH with_lag AS ( SELECT user_id, event_type, event_time, LAG(event_time) OVER ( PARTITION BY user_id ORDER BY event_time ) AS prev_event_time FROM user_events ) SELECT user_id, event_type, event_time, prev_event_time, ROUND( EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 )::int AS gap_min, CASE WHEN prev_event_time IS NULL OR EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 >= 30 THEN true ELSE false END AS is_new_session FROM with_lag ORDER BY user_id, event_time;
LEGEND ▼
データ取得・読込対象
① FROM user_events（7行） ▼
FROM user_eventsuser_events テーブルの7行を読み込みます。user1 が4行、user2 が3行のイベントを持ちます。これをユーザーごとの時系列として処理します。
1 / 6
user_idevent_typeevent_time
1view10:00
1click10:03
1view10:35
1purchase10:40
2view11:00
2click11:05
2view12:10
7行読込（user1=4行, user2=3行）
学習ポイント
PARTITION BY なしの LAG は全テーブルをまたいで参照する：LAG(event_time) OVER (ORDER BY event_time) と PARTITION BY を省略すると、user1 の最初の行がuser2の最後のイベントを「前の行」として参照するバグが発生します。イベントログの分析では必ず PARTITION BY user_id を付けるのが鉄則です。
EPOCH でタイムスタンプ差を数値化する理由：event_time - prev_event_time の結果は PostgreSQL の interval 型です。interval 型は >= 30 などの数値比較ができないため、EXTRACT(EPOCH FROM interval) で「秒数（float）」に変換してから比較します。÷60 で分、÷3600 で時間、÷86400 で日数に変換できます。
セッションIDの付与への拡張：is_new_session が true の行に連番を振ることでセッションIDを生成できます。SUM(is_new_session::int) OVER (PARTITION BY user_id ORDER BY event_time) で「ユーザー内の累積セッション数」が得られ、これがそのままセッションIDとして機能します。
アンチパターン
タイムゾーンを考慮しないセッション分割：event_time が UTC で記録されているが分析はJST（+9時間）で行うケースで、タイムゾーン変換なしに処理すると日またぎセッションが誤分割されます。event_time AT TIME ZONE 'Asia/Tokyo' で変換してから LAG を計算する習慣をつけてください。
セッション閾値を固定値にする問題：30分という閾値は Google Analytics の慣習ですが、プロダクトによって適切な値は異なります。長時間の動画視聴サービスなら60〜90分、即時性の高いチャットサービスなら5〜10分が適切なことも多く、閾値は定数ではなくプロダクト特性から決定すべきです。
実務コラム：セッションIDの付与と分析への応用
セッション境界を検出したら、次のステップは各行にセッションIDを付与することです。SUM(is_new_session::int) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) の累積和でセッション番号が生成できます。セッションIDが揃えば「セッションあたりの平均イベント数」「セッション長の分布」「セッション内でのコンバージョン率」など、ページビュー単位ではなくセッション単位の指標が算出できるようになります。
3 / 5
問題を選択
QUESTION 4

イベント遷移分析 — LEAD() で次のイベントを参照してユーザー行動パスを集計する

LEADCTE遷移パス分析行動シーケンス
前提知識 ▼

LEAD() は LAG() と逆方向のウィンドウ関数で、現在行の1つ後ろの行の値を参照します。「このイベントの次にどのイベントが発生したか」を取得することで、ユーザー行動パス（遷移パターン）を集計できます。

LEAD(event_type) OVER (
  PARTITION BY user_id      -- ユーザーをまたいで次の行を参照しない
  ORDER BY     event_time    -- 時系列順に並べた上で次の行を取る
)
-- 最後のイベントは「次の行」が存在しないため NULL を返す
遷移ペアの集計が行動分析の核心：(from_event → to_event) のペアを COUNT することで「どのイベントの後に何が起きるか」の頻度マトリクスが作れます。WHERE to_event IS NOT NULL で末尾行を除外するのが必須です。除外しないと「purchase→NULL」「click→NULL」など意味のないペアが混入します。
問題 ▼

user_events テーブルから、各イベントの直後に発生したイベントの遷移パターン（from_event → to_event）を集計してください。末尾イベント（to_event = NULL）は除外し、出力列は from_event, to_event, transition_count、transition_count 降順 / from_event 昇順で返してください。

使用テーブル
► user_events（10行）
user_idevent_typeevent_time
1view2024-01-10 10:00
1click2024-01-10 10:05
1view2024-01-10 10:10
1purchase2024-01-10 10:30
2view2024-01-11 09:00
2click2024-01-11 09:10
2view2024-01-11 09:20
2click2024-01-11 09:35
3view2024-01-12 14:00
3purchase2024-01-12 14:30
期待出力 ▼
from_eventto_eventtransition_count
viewclick3
clickview2
viewpurchase2
模範解答コード ▼ 
WITH transitions AS (
  SELECT
    user_id,
    event_type        AS from_event,
    LEAD(event_type)  OVER (   -- 直後のイベント種別を参照
      PARTITION BY user_id       -- ユーザーをまたがらない
      ORDER BY     event_time     -- 時系列順
    ) AS to_event
  FROM   user_events
)
SELECT
  from_event,
  to_event,
  COUNT(*) AS transition_count
FROM   transitions
WHERE  to_event IS NOT NULL    -- 末尾イベント（次のイベントなし）を除外
GROUP BY from_event, to_event
ORDER BY transition_count DESC, from_event;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE transitions
  2. 外側クエリ
  3. ORDER BY transition_count DESC, from_event  → 多い順・アルファベット順
  */
解説（テーブル変化・ポイント） ▼
WITH transitions AS ( SELECT user_id, event_type AS from_event, LEAD(event_type) OVER ( PARTITION BY user_id ORDER BY event_time ) AS to_event FROM user_events ) SELECT from_event, to_event, COUNT(*) AS transition_count FROM transitions WHERE to_event IS NOT NULL GROUP BY from_event, to_event ORDER BY transition_count DESC, from_event;
LEGEND ▼
データ取得・読込対象
① FROM user_events（10行） ▼
FROM user_eventsuser_events テーブルの10行を読み込みます。user1が4行、user2が4行、user3が2行のイベント系列を持ちます。各ユーザー内の時系列を LEAD() で処理します。
1 / 6
user_idevent_typeevent_time
1view10:00
1click10:05
1view10:10
1purchase10:30
2view09:00
2click09:10
2view09:20
2click09:35
3view14:00
3purchase14:30
10行読込（user1=4行, user2=4行, user3=2行）
学習ポイント
LEAD(expr, offset, default) の3引数形式：LEAD(event_type, 2) とすれば「2つ後のイベント」が取得できます。第3引数はデフォルト値で LEAD(event_type, 1, 'end') とすれば末尾行の NULL を 'end' に置換できます。遷移分析では通常 IS NOT NULL で除外する方が意味が明確ですが、末尾状態を明示したいケースでは便利です。
遷移確率への変換（マルコフ連鎖）：遷移頻度を遷移確率に変換するには、各 from_event の総件数で割ります。transition_count * 1.0 / SUM(transition_count) OVER (PARTITION BY from_event) とすることで、「view の後に click が来る確率」などの条件付き確率が算出できます。
複数ステップの遷移を連鎖させる：LEAD(event_type, 1) と LEAD(event_type, 2) を同時に使えば「A → B → C」という3ステップのシーケンスが取得できます。これを GROUP BY することで「どの3連続パターンが多いか」がわかり、プロダクトのコアユーザー行動ループを特定できます。
アンチパターン
PARTITION BY を省略するとユーザーをまたいだ遷移が混入する：LEAD(event_type) OVER (ORDER BY event_time) と書くと、user1 の最後のイベントの「次」が user2 の最初のイベントとして参照されます。存在しない遷移がデータに混入し、分析が歪みます。
WHERE to_event IS NOT NULL を忘れると末尾ノイズが入る：除外を忘れると「purchase→NULL」「click→NULL」などが出現し、NULL という「イベント名」が遷移先として集計されてしまいます。分析結果が誤った方向に向かいます。
実務コラム：マルコフ連鎖モデルでの行動予測
遷移頻度から遷移確率を計算したものを一次マルコフ連鎖モデルと呼びます。「現在のイベントだけから次のイベントを予測する」という単純な仮定ですが、実務のパス分析では十分な精度が出ることが多いです。SQL でできる遷移分析を理解しておくことが、機械学習パイプラインの設計に直結します。
4 / 5
問題を選択
QUESTION 5

DAU / MAU の算出 — DATE_TRUNC で日・月単位のアクティブユーザー指標を集計する

DATE_TRUNCCOUNT DISTINCTDAU / MAUアクティブ指標
前提知識 ▼

イベントモデリングの最重要指標が DAU（Daily Active Users）と MAU（Monthly Active Users）です。DATE_TRUNC で event_time の粒度を「日」や「月」に丸め、COUNT(DISTINCT user_id) でユニークユーザー数を算出します。

指標DATE_TRUNC の粒度意味
DAU'day'その日に1回以上イベントを発生させたユニークユーザー数
WAU'week'その週に1回以上イベントを発生させたユニークユーザー数
MAU'month'その月に1回以上イベントを発生させたユニークユーザー数
SELECT DATE_TRUNC('day', ts_col) AS bucket,   -- 粒度は 'day' / 'week' / 'month'
       COUNT(DISTINCT id_col)    AS uniq_cnt  -- 重複を除いたユニーク数
FROM   table_name
GROUP BY DATE_TRUNC('day', ts_col)
ORDER BY bucket;
DAU を合計しても MAU にはならない：同じユーザーが月内の複数日にアクティブだった場合、DAU の SUM はそのユーザーを複数回カウントします。MAU は必ず event_time を月単位に丸めてから COUNT(DISTINCT user_id) で算出してください。この「重複排除」こそが COUNT DISTINCT の核心です。
問題 ▼

user_events テーブルから、① 日付ごとの DAU（日次アクティブユーザー数） と ② 月ごとの MAU（月次アクティブユーザー数） をそれぞれ算出してください。①は activity_date, dau、②は activity_month, mau を activity_date / activity_month 昇順で返してください。

使用テーブル
► user_events（10行）
user_idevent_time
12024-01-10 10:00
12024-01-10 14:00
22024-01-10 11:00
32024-01-11 09:00
12024-01-11 15:00
22024-01-12 10:00
42024-01-12 16:00
52024-02-01 09:00
12024-02-01 11:00
32024-02-02 10:00
期待出力 ▼

期待する出力①：

activity_datedau
2024-01-102
2024-01-112
2024-01-122
2024-02-012
2024-02-021

期待する出力②：

activity_monthmau
2024-01-014
2024-02-013
模範解答コード ▼ 
-- ① DAU: event_time を日単位に丸めてユニークユーザーを集計
SELECT
  DATE_TRUNC('day', event_time)::date  AS activity_date,
  COUNT(DISTINCT user_id)             AS dau
FROM   user_events
GROUP BY activity_date
ORDER BY activity_date;

-- ② MAU: event_time を月単位に丸めてユニークユーザーを集計
SELECT
  DATE_TRUNC('month', event_time)::date  AS activity_month,
  COUNT(DISTINCT user_id)               AS mau
FROM   user_events
GROUP BY activity_month
ORDER BY activity_month;

/*
  実行順序（DAU クエリ）:
  1. FROM user_events          → 行を読込
  2. DATE_TRUNC('day', ...)    → event_time を日に丸める
  3. GROUP BY activity_date    → 日付でグループ化
  4. COUNT(DISTINCT user_id)   → 日内のユニークユーザー数
  5. ORDER BY activity_date    → 日付昇順

  実行順序（MAU クエリ）:
  1. FROM user_events          → 行を読込
  2. DATE_TRUNC('month', ...)  → event_time を月に丸める
  3. GROUP BY activity_month   → 月でグループ化
  4. COUNT(DISTINCT user_id)   → 月内のユニークユーザー数
  5. ORDER BY activity_month   → 月昇順
  */
解説（テーブル変化・ポイント） ▼
SELECT DATE_TRUNC('day', event_time)::date AS activity_date, COUNT(DISTINCT user_id) AS dau FROM user_events GROUP BY activity_date ORDER BY activity_date;
LEGEND ▼
データ取得・読込対象
① FROM user_events（10行） ▼
FROM user_eventsuser_events テーブルの10行を読み込みます。user1 が同日（2024-01-10）に2回アクセスしていることに注目。COUNT DISTINCT がこの重複を除去します。
1 / 5
user_idevent_time
12024-01-10 10:00
12024-01-10 14:00
22024-01-10 11:00
32024-01-11 09:00
12024-01-11 15:00
22024-01-12 10:00
42024-01-12 16:00
52024-02-01 09:00
12024-02-01 11:00
32024-02-02 10:00
10行読込
SELECT DATE_TRUNC('month', event_time)::date AS activity_month, COUNT(DISTINCT user_id) AS mau FROM user_events GROUP BY activity_month ORDER BY activity_month;
LEGEND ▼
データ取得・読込対象
① FROM user_events（10行） ▼
FROM user_events同じ user_events テーブルを使いますが、今度は月単位に丸めて月間アクティブユーザー数（MAU）を計算します。1月に複数日アクティブなユーザーも月内では1人として数えます。
1 / 5
user_idevent_time
12024-01-10 10:00
12024-01-10 14:00
22024-01-10 11:00
32024-01-11 09:00
12024-01-11 15:00
22024-01-12 10:00
42024-01-12 16:00
52024-02-01 09:00
12024-02-01 11:00
32024-02-02 10:00
10行読込
学習ポイント
DATE_TRUNC の粒度を切り替えるだけでDAU/WAU/MAUが切り替わる：'day' → DAU、'week' → WAU、'month' → MAU と、第1引数の粒度文字列だけ変えれば同じクエリ構造ですべての期間粒度に対応できます。'quarter' で四半期ごとの集計も同じ構造で書けます。
DAU の合計 ≠ MAU の数学的意味：1月のDAUが2+2+2=6ですがMAU=4になるのは、user1とuser2が複数日にアクティブであり重複カウントされたためです。DAU/MAUの大小関係で、ユーザーが1ヶ月に平均何日アクティブかがわかります（スティッキネス）。
::date キャストの必要性：DATE_TRUNC() の返り値は timestamp 型です。::date で日付型にキャストすることで、出力が '2024-01-01 00:00:00' ではなく '2024-01-01' と表示されるなど、後続広告ツールや BI ツールとの相性が良くなります。
アンチパターン
COUNT(*) でイベント数をアクティブユーザー数と間違える：COUNT(*) はイベントの「発生件数」です。1人のユーザーが10回イベントを発火した場合、COUNT(*)=10 になります。DAU/MAU は必ず COUNT(DISTINCT user_id) を使ってください。
DAU を SUM して MAU を得ようとする：1月にDAU(2+2+2)=6 ですがMAU=4です。複数日にアクティブなユーザーが重複カウントされているためです。MAU は決して DAU の合計にはなりません。必ず別クエリまたは CTE で独立に算出してください。
実務コラム：DAU/MAU からスティッキネスへ
DAU と MAU の両方が揃うと、その比率からスティッキネス（DAU/MAU × 100）が算出できます。「月間の平均日次アクティブユーザー数 ÷ 月間アクティブユーザー数」で、ユーザーが1ヶ月に平均何日サービスを利用しているかを示します。SNS 系プロダクトは50％以上、SaaS ツールは20〜40％が目安です。この指標をコホート別・機能別に分析することで、「どのユーザーセグメントが最も頻繁に利用しているか」が明確になります。
5 / 5
問題を選択
次のセット →