対象日から過去1週間のユニークユーザー数を出したい

何かしらのイベントのデータ分析をする際に、特定イベントのデイリーのユニークユーザー数を出すだけでなく、その日から過去１週間のユニークユーザー数を出したい場合があるかと思います。 例えば、2023-04-16のユニークユーザー数と2023-04-16から過去1週間のユニークユーザー数（2023-04-09~2023-04-16）を出したいという場合などです。

このようなケースで、どのようにクエリを書けば求めたい数値が出せるか悩んだので、備忘録的に残しておきます。

環境

• ツール：Redash
• データソース：Snowflake

データ構造

シンプルに以下のようなデータ構造であった場合を想定します。

イベント日 (event_date)	ユーザーID (user_id)	イベント名 (event_name)
yyyy-mm-dd	xxxx-xxxxx-xxxx	test_event1
yyyy-mm-dd	yyyy-yyyyy-yyyy	test_event1
yyyy-mm-dd	zzzz-zzzzz-zzzz	test_event2

解決策

対象日から過去1週間のデータを引っ張るために、SnowflakeのDATEADD関数を使います。

docs.snowflake.com

-- with句を使ってサブクエリを書く

WITH target_events AS (
  SELECT
    user_id,
    event_date
  FROM
    events
  WHERE
    event_name = 'test_event1'
),
target_dates AS (
  SELECT
    event_date
  FROM
    events
  GROUP BY
    event_date
)

-- target_datesのevent_dateカラムの値を使ってUUを算出
SELECT
  event_date,
  (SELECT COUNT(DISTINCT user_id) FROM target_events WHERE event_date = t.event_date) AS daily_uu, -- デイリーのユニークユーザー数
  (SELECT COUNT(DISTINCT user_id) FROM target_events WHERE event_date between DATEADD(day, -(7), t.event_date) and t.event_date) AS weekly_uu -- ウィークリーのユニークユーザー数（過去1週間なので-7）
FROM
  target_dates t
WHERE
  t.event_date between '{{ start_date }}'
  and '{{ end_date }}' -- redashのパラメータを埋め込む

もっとシンプルなクエリが書けそうではありますが、これで取得したいデータを得ることができます。

イベント日	daily_uu	weekly_uu
yyyy-mm-dd	100	300
yyyy-mm-dd	200	400
yyyy-mm-dd	300	500