回答編集履歴

10 追記

sazi

sazi score 22895

2018/11/22 09:32  投稿

試せてませんが、[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  
 
```
9 削除

sazi

sazi score 22895

2018/11/22 09:25  投稿

試せてませんが、[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
```
もっとシンプルに
```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
)
, base_date as(
   select event_date from T group by event_date
)
select
   base_date.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 base_date inner join T as dau
     on base_date.event_date = dau.event_date
     inner join T as wau
     on base_date.event_date between wau.event_date and wau.wau_range_date
     inner join T as mau
     on base_date.event_date between mau.event_date and mau.wau_range_date
group by base_date.event_date
order by base_date.event_date asc
```
8 修正

sazi

sazi score 22895

2018/11/22 09:23  投稿

試せてませんが、[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
```
もっとシンプルに
```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
)
, base_date as(
   select event_date from T group by event_date
)
select
   base_date.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 base_date inner join T as dau
      on base_date.event_date = dau.event_date
   left join T as wau
      on base_date.event_date between wau.event_date and wau.wau_range_date
   left join T as mau
      on base_date.event_date between mau.event_date and mau.wau_range_date
     on base_date.event_date = dau.event_date
    inner join T as wau
     on base_date.event_date between wau.event_date and wau.wau_range_date
    inner join T as mau
     on base_date.event_date between mau.event_date and mau.wau_range_date
group by base_date.event_date
order by base_date.event_date asc
```
7 追記

sazi

sazi score 22895

2018/11/22 09:22  投稿

試せてませんが、[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
```  
もっとシンプルに  
```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  
)  
, base_date as(  
   select event_date from T group by event_date  
)  
select  
   base_date.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 base_date inner join T as dau  
       on base_date.event_date = dau.event_date  
   left join T as wau  
       on base_date.event_date between wau.event_date and wau.wau_range_date  
   left join T as mau  
       on base_date.event_date between mau.event_date and mau.wau_range_date  
group by base_date.event_date  
order by base_date.event_date asc  
```
6 修正

sazi

sazi score 22895

2018/11/21 18:36  投稿

試せてませんが、[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 T.event_date between base_date.event_date and wau_range_date
      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 T.event_date between base_date.event_date and mau_range_date
      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
```
5 追記

sazi

sazi score 22895

2018/11/21 18:33  投稿

試せてませんが、[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)
[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 T.event_date between base_date.event_date and 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 T.event_date between base_date.event_date and 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
```
4 条件の日付の書式を合わせるように修正

sazi

sazi score 22895

2018/11/21 18:22  投稿

試せてませんが、[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_add(date base_date.event_date,interval 7 day)
      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_add(date base_date.event_date,interval 1 month)
      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)
3 追記

sazi

sazi score 22895

2018/11/21 18:11  投稿

試せてませんが、[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_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_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)
2 修正

sazi

sazi score 22895

2018/11/21 18:09  投稿

試せてませんが、[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
     parse_date("%Y%m%d", event_date) event_date
      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
    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_add(base_date.event_date,interval 7 day)
      on T.event_date between base_date.event_date and 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_add(base_date.event_date,interval 1 month)
      on T.event_date between base_date.event_date and 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
```
1 追記

sazi

sazi score 22895

2018/11/21 18:05  投稿

試せてませんが、[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  
     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  
)  
, 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_add(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_add(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  
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る