SQL文：一か月内の利用日数別の人数（月内出現日数分布）を調べたい

Question

前提・実現したいこと

SQL文で、一か月の利用日数別の人数（月内出現日数）を調べたい

アプリゲームのような一日に何回もログインできるサービスの利用ログをベースに
ユーザーの利用日数別の人数を出したいです。
元のデータは利用ログがTIMESTAMPの秒刻みで記録されているのですが、下記のクエリで
各ユーザーの日別利用状況をユニーク化するところまではたどりつきました。
この後、月単位の出現回数の算出方法がわからず困っております。

Excelならピポットの個数カウントで可能だとは思うのですが
データ量が多く処理ができないため、SQLでの処理が必要となりました。
どなたか教えていただけますと幸いです。
利用ツールはBigqueryです。

■Excelでの実現イメージ
http://tigertaizo.hatenablog.com/entry/2016/09/13/202109

■こちらを参照しましたが、少し状況が異なりわかりませんでした
https://teratail.com/questions/224861

現在の状況

現在のテーブル一部抜粋（数か月分のデータを処理するので実際複数月となります）

|行|month|date|status|user_id
|:--|:--:|--:|
1|10|10/01|gold|aaaaaaa
2|10|10/02|gold|aaaaaaa
3|10|10/03|gold|aaaaaaa
4|10|10/01|silver|bbbbbbb
5|10|10/02|silver|bbbbbbb
6|10|10/03|silver|bbbbbbb
7|10|10/01|gold|cccccccc
8|10|10/01|gold|eeeeeee
9|10|10/02|gold|eeeeeee
10|10|10/03|gold|eeeeeee

この状態に持ってくるまでに利用したSQL

SELECT FORMAT_DATE("%m",date)month,date,status,user_id
FROM  テーブル名
WHERE _TABLE_SUFFIX between "開始日" and "終了日"
GROUP BY date,status,user_id
ORDER BY user_id DESC;

やりたきこと

month|status|一か月の利用日数|人数
|:--|:--:|--:|
10|gold|1日|2341人
10|gold|2日|1242人
10|gold|3日|241人
10|gold|4日|124人
10|gold|5日|8人
10|gold|・・・最大30日まで続く|2人
11|gold|1日|2341人
11|gold|2日|1242人
11|gold|3日|241人
11|gold|4日|124人
11|gold|5日|8人
11|gold|・・・最大30日まで続く|2人

Answer 1

SELECT FORMAT_DATE("%m",date) month, date, status
     , EXACT_COUNT_DISTINCT(user_id)
FROM  テーブル名
WHERE _TABLE_SUFFIX between "開始日" and "終了日"
GROUP BY date, status
ORDER BY date, status

※EXACT_COUNT_DISTINCT()は重複をカウントしません。
同一日の同じuser_idをそれぞれカウントする場合は、count()を使用して下さい。

Comments

[mihcisuji]

全くうまくできずに悶々としておりましたので、助かりました！ありがとうございます。