SQLを使って、数値間の比較をしたい

Question

トランザクションデータの分析をしています。
下記のようなデータがあるとします。

|transaction_id|user_id|item_id|price|create_dt|
|:--|:--:|--:|--:|
|1|1|2|100|2021-11-10|
|2|2|2|200|2021-11-12|
|3|1|1|300|2021-11-14|
|4|2|3|100|2021-11-15|
|5|1|1|100|2021-11-17|

ユーザーの購入日の間隔を知りたいので、下記のようにユーザーごとに購入の順番に番号をふり(row_number)、1つ前の購入との差分を算出(date_diff)したいです。
どのようなクエリを書いたらよいでしょうか？

|user_id|transaction_id|item_id|price|create_dt|row_number|date_diff|
|:--|:--:|--:|--:|
|1|1|2|100|2021-11-10|1|0|
|1|3|1|300|2021-11-14|2|4|
|1|5|1|100|2021-11-17|3|3|
|2|2|2|200|2021-11-12|1|0|
|2|4|3|100|2021-11-15|2|3|

よろしくお願いします。

Answer 1

当方BigQueryを使える環境にないですが、BigQueryはウインドウ関数を使用出来るようなので

select
  user_id
, transaction_id
, item_id
, price
, create_dt
, row_number() over(partition by user_id order by create_dt)
, create_dt -  lag(create_dt) over(partition by user_id order by create_dt) as date_diff
from tbl

構文エラーにならなければ、以下でもok

select
  user_id
, transaction_id
, item_id
, price
, create_dt
, row_number() over w
, create_dt -  lag(create_dt) over w as date_diff
from tbl
window w as (partition by user_id order by create_dt)

Comments

[kanineco]

回答ありがとうございます。
create_dt はtimestampだったので、このようにして解決することができました。

DATE_DIFF(TIMESTAMP_ADD(create_dt),lag(dta.create_dt),1) OVER(PARTITION BY user_id ORDER BY create_dt),day) AS date_diff