DL日毎のN週目継続率の出し方がわからない

Question

以下のように、コーホートのような形で、DL日毎の、1週目（1週間のどこかで一度でもアクセスすればtrue）、2週目(1週目にアクセスしたユーザのうち、2週目にアクセス)・・・N週目継続率を集計したい
|DL日|DL数|1週目継続率|2週目継続率|3週目継続率|・・・|N週目継続率
|:--|:--:|--:|
|2018−07−07|10000|70％|50%|30%|・・・|10%
|2018-07-08|8000|65%|50%|20%|0%|0%
|・・・|
|2019-02-10|7000|50％|30%|

N週目という形で、現在まで続くようにクエリを書くにはどうすればいいのでしょう…
ご助言いただければ幸いです。

Answer 1

カラム毎に集計の範囲に意味を持たせるものはクロス集計と呼ばれ、そのカラムはcase文やサブクエリーで条件を判定して求める事が一般的です。

また、質問にあるような、N週目継続率のようにカラムが可変になるようなものを一回のSQLで求めるような場合は、DBMS固有のクロス集計用の機能が無い限りは、動的に求めるしかありません。

BigQueryではそのような機能は無いので、動的SQLにするか、Arry_aggで配列化したものを取り出しの際に分解するか、だと思います。