日付毎にその日から一週間の間のアクティブユーザー数を集計したい

Question

完成イメージ↓
|日付|WAU|MAU|
|:--|:--:|--:|
|2018/09/09|200|500|
|2018/09/10|180|480|

WAU・・・日付−7日間の間に一度でもログインしたユニークユーザー数
MAU・・・日付−30日間の間に一度でもログインしたユニークユーザー数

何を使ってどう集計すればいいのでしょうか？
ちなみにDAUは以下のように出しています。
```standardsql
with T as (
    select
      parse_date("%Y%m%d", event_date) event_date
      , user_id
      , event_name
    from
      `sample*`
    where(
      _TABLE_SUFFIX BETWEEN
       "20180707" AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    and
        app_info.install_source = 'iTunes'
    and
        date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
    and
        user_id is not null
)

,dau as (
  select 
  event_date
  ,count(distinct(user_id)) as dau_count 
  from 
  T
  group by 
  event_date
)
```
user_idはイベント毎に計測されるため、元データでは重複があります。


＜↓saziさんの回答を自分なりに解釈して書いたコード（コピペだとエラーで、saziさんの書き方が高度でどう変えればいいかわかりませんでした。。。）＞
エラーはないのですが、dau、wau、mauの数字がほぼ同じになってしまいました。

```standardsql
with T as (
    select
      parse_date("%Y%m%d", event_date) event_date
      , user_id
      , event_name
    from
      `sample*`
    where(
      _TABLE_SUFFIX BETWEEN
       "20180707" AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    and
        app_info.install_source = 'iTunes'
    and
        date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
    and
        user_id is not null
    limit 10000000
)

, dau as (
  select
    event_date
    ,count(distinct(user_id)) as dau_count
  from
    T
  group by
    event_date
)

, wau as (
  select
     event_date
    ,count(distinct(user_id)) as wau_count
  from
    T
  where
    event_date between t.event_date and date_add(t.event_date,interval 7 day)
  group by event_date
)

, mau as (
  select
    event_date
    ,count(distinct(user_id)) as mau_count
  from
    T
  where
    event_date between t.event_date and date_add(t.event_date,interval 1 month)
  group by event_date
)

, before_formating as (
  select
    dau.event_date
    ,dau_count
    ,wau_count
    ,mau_count
  from
    dau
  left join
    wau
  on
    dau.event_date = wau.event_date
  left join
    mau
  on
    dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc
```

Accepted Answer

試せてませんが、[DATE_ADD](https://cloud.google.com/bigquery/sql-reference/functions-and-operators?hl=ja#date_add)を使用すると以下のようになるかと。
```SQL
  select event_date
       , (select count(distinct(user_id)) from T 
          where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -7 DAY)) 
         ) as wau_count 
       , (select count(distinct(user_id)) from T 
          where event_date between t1.event_date and DATE_ADD(t1.event_date, INTERVAL -1 MONTH)) 
         ) as mau_count 
from (
  select event_date
  from  T
  group by event_date
) t1
```
追記
--
追加された記述より展開
```SQL
with T as (
    select
        date(event_date) event_date
      , user_id
      , event_name
    from
      `sample*`
    where(
      _TABLE_SUFFIX BETWEEN
       "20180707" AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
    and
        app_info.install_source = 'iTunes'
    and
        date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
    and
        user_id is not null
    limit 10000000
)
, dau as (
  select
     event_date
    ,count(distinct(user_id)) as dau_count
  from
    T
  group by
    event_date
)
, base_date as(
  select event_date from T group by event_date
 )

, wau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 7 day))
  group by base_date.event_date
)
, mau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on T.event_date between base_date.event_date and date(date_add(date base_date.event_date,interval 1 month))
  group by base_date.event_date
)
, before_formating as (
  select
    dau.event_date
    ,dau_count
    ,wau_count
    ,mau_count
  from
    dau
  left join
    wau
  on
    dau.event_date = wau.event_date
  left join
    mau
  on
    dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc
```
BigQueryでは相関問い合わせは使用しない方が良いみたいですね。
[BigQueryで相関サブクエリがうまく動かなかった話](http://silva-tech.hatenablog.com/entry/2018/04/14/233112)

追記
--
予め、範囲となる日付を求めておくように修正
```SQL
with T as (
    select
        date(event_date) event_date
      , date(date_add(event_date,interval 7 day)) wau_range_date
      , date(date_add(event_date,interval 1 month)) mau_range_date
      , user_id
      , event_name
    from `sample*`
    where	(_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
      and	app_info.install_source = 'iTunes'
      and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
      and user_id is not null
    limit 10000000
)
, dau as (
  select
     event_date
    ,count(distinct(user_id)) as dau_count
  from
    T
  group by
    event_date
)
, base_date as(
  select event_date from T group by event_date
 )

, wau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on base_date.event_date between T.event_date and T.wau_range_date
  group by base_date.event_date
)
, mau as (
  select
      base_date.event_date
    , count(distinct(T.user_id)) as wau_count
  from base_date inner join T
       on base_date.event_date between T.event_date and T.mau_range_date
  group by base_date.event_date
)
, before_formating as (
  select
    dau.event_date
    ,dau_count
    ,wau_count
    ,mau_count
  from
    dau
  left join
    wau
  on
    dau.event_date = wau.event_date
  left join
    mau
  on
    dau.event_date = mau.event_date
)
select *
from before_formating
order by event_date asc
```
単にユーザーのIDをカウントするだけなので、シンプルになりそう。
```SQL
with T as (
    select
        date(event_date) event_date
      , date(date_add(event_date,interval 7 day)) wau_range_date
      , date(date_add(event_date,interval 1 month)) mau_range_date
      , user_id
      , event_name
    from `sample*`
    where    (_TABLE_SUFFIX BETWEEN "20180707" AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)))
      and    app_info.install_source = 'iTunes'
      and date(TIMESTAMP_ADD(timestamp_micros(user_first_touch_timestamp), interval device.time_zone_offset_seconds SECOND)) > "2018-07-06"
      and user_id is not null
    limit 10000000
)
select
    dau.event_date
  , count(distinct(dau.user_id)) as dau_count
  , count(distinct(wau.user_id)) as wau_count
  , count(distinct(mau.user_id)) as mau_count
from  T as dau
      inner join T as wau
      on  dau.event_date between wau.event_date and wau.wau_range_date
      inner join T as mau
      on  dau.event_date between mau.event_date and mau.wau_range_date
group by dau.event_date
order by dau.event_date asc

```

日付	WAU	MAU
2018/09/09	200	500
2018/09/10	180	480

追記

追記

関連した質問