前回購入日からの日数を計算したい

Question

SQL初心者です。
ヒントを頂ければと思い投稿しました。

施策の効果を振り返るために、前回の購入日からのどれくらい経過したカスタマーが戻ってきているのか知りたいです。
前回購入からの日数を計算する方法がわからず四苦八苦しております。
具体的にはLag関数などを用いて計算したのですが、カスタマーごとに経過日を出すことができないので困っております。

・前回購入という1個前の購入を参照する方法
・UserIDごとに集計したい
└具体的にはlag関数を用いるとbbbbの12−10購入がaaaaの１２−１７購入を参照しないようにしたいです。

環境はBig Queryを使っています。
もしヒントを頂けたら嬉しいです。よろしくお願い致します。

参考にデータは下記のような構造になっております。

UserID	購入日時
aaaa	2021-12-10
aaaa	2021-12-17
bbbb	2021-12-10
bbbb	2021-12-15

Comments

[退会済みユーザー]

SQL Server, PostgreSQL とどういう関係があるのでしょう？

[yambejp]

タグはSQL Server, PostgreSQLを消してBigQueryをつけたほうがよいでしょうね

[aiueo122]

消します。
ありがとうございます。

Answer 1

useridごとに前回購入日時を求める場合、以下のようなクエリになるかと思います。

SELECT 
  userid,
  購入日時,
  LAG(購入日時) OVER (PARTITION BY userid ORDER BY 購入日時) AS 前回購入日時
FROM TBL

実行結果は以下のようになります。

userid	購入日時	前回購入日時
aaaa	2021/12/10	NULL
aaaa	2021/12/17	2021/12/10
bbbb	2021/12/10	NULL
bbbb	2021/12/15	2021/12/10

Comments

[aiueo122]

ご丁寧にありがとうございます。
これなら購入日時-前回購入日で経過日が算出できそうですね。
やってみます。