前提
login_log_table → 大量のログイン履歴が入ってるTBL
login_date → ログイン日時
user_id → 会員ごとにユニークなID
やりたいこと
日毎にログイン人数がどう増えていってるかを見たい。
例えば、2019/10/1 に 100 人がログインして、2019/10/2 にはまた 100 人(その中50人が1日にもログインしていた人)がログインしたとして、
loginYmd | count |
---|---|
2019-10-01 | 100 |
2019-10-02 | 150 |
という結果になってほしい。
これでなんとかなった、という備忘録
SELECT loginYmd,
cumulative_count from
(SELECT *, count(*) OVER (ORDER BY loginYmd) cumulative_count
FROM
(SELECT user_id, min(FORMAT_TIMESTAMP('%Y-%m-%d', login_date,'Asia/Tokyo')) as loginYmd
FROM my_dataset.login_log_table
WHERE FORMAT_TIMESTAMP('%Y-%m-%d', offer_date,'Asia/Tokyo') BETWEEN '2019-10-01' AND '2019-10-02'
GROUP BY user_id) t) AS TEMP
GROUP BY loginYmd, cumulative_count