BigQueryで日毎のユニークユーザ数と通算のユニークユーザ数を集計したい

Question

実現したいこと

下記のtable_Aから、

user_id	date
1	2023-01-01
2	2023-01-01
1	2023-01-02
3	2023-01-02
1	2023-01-03
3	2023-01-03

下記のように集計したいです。

date	日毎のUU	通算のUU
2023-01-01	2	2
2023-01-02	2	3
2023-01-03	2	3

クエリを書いてみたのですが下記ですと毎日のUUの積み重ねになってしまいます。

select
  date,
  count(distinct user_id) as 毎日のUU,
  sum(count(distinct user_id)) over(order by date rows unbounded preceding) as 通算のUU
from
  table_A
group by
  date

table_Aに初めて登場したuser_idに1が入っているカラム「first_flag」を追加してそのカラムを集計するようにしてみました。

table_A_added_first_flag

user_id	date	first_flag
1	2023-01-01	1
2	2023-01-01	1
1	2023-01-02	0
3	2023-01-02	1
1	2023-01-03	0
3	2023-01-03	0

select
  date,
  count(distinct user_id),
  sum(first_flag) over(order by date rows unbounded preceding)
from
  table_A_added_first_flag
group by
  date

しかしfirst_flagでgroup byしなさいというエラーが出ます。
group byにfirst_flagを追加すると、集計がばらけてしまいます。

毎日のUUと通算のUUを別々に集計して、dateでinner joinして一つのテーブルにするしか方法はないでしょうか。

Answer 1

select
date,
count(distinct user_id),
sum(sum(first_flag)) over(order by date rows unbounded preceding)
from
table_A_added_first_flag
group by
date

Answer 2

累計する為にはuser_idの重複を省かないと駄目なので、一度に取得する為にはwindow関数ではなくサブクエリーの方が簡潔だと思います。

select  date
      , count(distinct user_id)
      , (select count(distinct user_id) from table_a where date<=ta.date)
from  table_A ta
group by date

Comments

[sysder]

ご回答ありがとうございます。

クエリを試してみたところ、小さいデータでは動くのですが、大きいデータになると下記のエラーになりました。
Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

そこで相関サブクエリを使わないように、table_Aに初めて登場したuser_idに1が入っているカラム「first_flag」を追加して下記のように書き換えたところ、first_flagでgroup byしなさいというエラーが出ます。
これはなぜなんでしょうか？countするために必要ということでしょうか？
当たり前なのですがgroup byにfirst_flagを追加すると、集計がばらけてしまいます。
select
date,
count(distinct user_id),
sum(first_flag) over(order by date rows unbounded preceding)
from
table_A_added_first_flag
group by
date

[sysder]

下記のクエリで解決いたしました。

select
date,
count(distinct user_id),
sum(sum(first_flag)) over(order by date rows unbounded preceding)
from
table_A_added_first_flag
group by
date

改めましてご回答ありがとうございました。