あるアプリケーションへのアクセスログを保存している，以下のようなテーブルがあったとする．
| event_date | user_id |
|---|---|
| 2020-01-01 | A |
| 2020-01-02 | A |
| 2020-01-02 | B |
| 2020-01-02 | C |
| 2020-01-03 | A |
| 2020-01-03 | C |
単純な DAU を調べたければ簡単なのだが，「ユーザーの継続利用」に関して調べたいこともある．
例えば，ある時点までで「過去 N 日間連続してログインしたユーザー数」とかである．上のサンプルデータの時に，「過去 2 日の連続利用」を調べたかった，この例では以下のようなテーブルを作成できれば良い．
| event_date | count |
|---|---|
| 2020-01-01 | 0 |
| 2020-01-02 | 1 |
| 2020-01-03 | 2 |
なぜなら，2020-01-01 は初日なのでもちろん継続ユーザーはいない．2020-01-02 になると，ユーザー A が前日もログインしているので count は 1 になる．同様に 2020-01-03 は ユーザー A と ユーザー C で 2 になる．
このデータを得るための SQL はいくつかあるだろうが，window 関数の一つである LAG 関数 を使うのがシンプルで良い．LAG 関数を使うと，対象行の指定のグループにおける N 個前のデータサンプルを参照することができる．文だと説明がわかりにくいので上のテーブルを使っての具体例を挙げる．
select
event_date,
count,
LAG(event_date, 1) OVER(PARTITION BY user_id ORDER BY event_date) as lag_value
from
sample_table
これを実行すると，
| event_date | user_id | lag_value |
|---|---|---|
| 2020-01-01 | A | null |
| 2020-01-02 | A | 2020-01-01 |
| 2020-01-02 | B | null |
| 2020-01-02 | C | null |
| 2020-01-03 | A | 2020-01-02 |
| 2020-01-03 | C | 2020-01-02 |
が得られる．
LAG(event_date, 1) は，新しいカラムには event_date を入れたい，1 個前の情報を抽出したい，という意味．
その後の OVER の部分では，user_id でグループングして前の情報をとってくる，さらに event_date でソートしている．
つまり，PARTITION BY user_id を設定することで，ユーザーごとに直前のアクセス日の抽出を実現している．
このテーブルを得ることができたらあとは簡単．またいくつか方法はあるが，現時点の N 日前の日付と N 回目のアクセス日が一致しているか調べれば良い．
DATA_SUB(event_date, interval 1 day) = lag_value のような事をすれば以下のテーブルが得られる．
| event_date | user_id | lag_value | seq_access |
|---|---|---|---|
| 2020-01-01 | A | null | false |
| 2020-01-02 | A | 2020-01-01 | true |
| 2020-01-02 | B | null | false |
| 2020-01-02 | C | null | false |
| 2020-01-03 | A | 2020-01-02 | true |
| 2020-01-03 | C | 2020-01-02 | true |
最後に，event_date で group by して，seq_access の sum を取れば，各日に連続 N 回ログインしているユーザー総数を集計できる．
参考