問題
あるECサイトの顧客が、購入にいたるまでの購入プロセスを収集したプロセスログテーブル(PROCESS_LOG)がある。
プロセスは以下の５STEPに分類されている。
- STEP1 = サイトを表示
- STEP2 = 商品ページを閲覧
- STEP3 = 商品をカートに追加
- STEP4 = 支払情報の入力
- STEP5 = 購入完了
このサイトの離脱ポイントを特定したいので、このプロセスログテーブルから各プロセスID(PROCESS_ID)を実行したセッション数を集計しなさい。
セッション数に関しては、セッションID(SESSION_ID)をカウントすること。
[データ制約について]
- 1つのセッションIDで、同一のプロセスIDは複数存在していないものとする
- 異なるプロセスIDで、実行日時が同じデータは存在しないものとする
[集計対象となる条件]
- STEP1~5が、実行日時(EX_TIMESTAMP)が早い順に実行されているSTEPまでが対象
- STEP1よりも先に他のSTEPが実行されていないこと
- STEP1以外のSTEPは、1つ前のSTEPが実行されていること
下記の例の場合、STEP1 → STEP2 → STEP3 → STEP4 → STEP5 の順番に実行されたのはSTEP1とSTEP2までなので、
この2つのレコードだけを集計対象とする。
【例】
| SESSION_ID |
PROCESS_ID |
USER_ID |
EX_TIMESTAMP |
| aaa |
STEP1 |
110 |
2022-04-12 00:00:00 |
| aaa |
STEP2 |
110 |
2022-04-12 00:01:00 |
| aaa |
STEP4 |
110 |
2022-04-12 00:02:00 |
| aaa |
STEP3 |
110 |
2022-04-12 00:03:00 |
| aaa |
STEP5 |
110 |
2022-04-12 00:04:00 |
表示項目は以下とする。(エイリアスを使用し→の項目名とする)
- PROCESS_ID → PROCESS
- 集計した件数 → CNT
表示順
- PROCESS_IDの昇順
表示フォーマット
※あくまでフォーマットを示すもので、正解例ではありません。行数も正解とは異なります。
ER図 (半角:物理名、全角:論理名)
テーブル定義
テーブル名: PROCESS_LOG
サンプルデータ
テーブル名: PROCESS_LOG
