PARTITION BYを使ってユーザーの最後のイベントから対象者を抽出するクエリを書く

最後に購入してから10日以内のユーザーにメッセージを送りたい等、ユーザーが行った一番最後のイベントを抽出したい場合があります。

そうした時はWindow関数を使うと、以下のように書くことができます。

WITH
  user_events_desc AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC ) AS row_num
  FROM
    user_events )
SELECT
  *
FROM
  user_events_desc
WHERE
  row_num = 1 AND name = "purchase" AND DATETIME_DIFF(CURRENT_DATETIME("Asia/Tokyo"), created_at, DAY) <= 10

「OVER」はWindow関数を使うためのお決まりのキーワードです。OVERの後にどのようにウィンドウを作るかを定義します。

「PARTITION BY」で分けたいグループを指定し、「ORDER BY」で並び順を指定し、「ROW_NUMBER」で連番を振ります。

これによって最後に購入してから10日以内のユーザーを取得することができます。