【データ抽出/MYSQL】新規ユーザー、リピーター、カムバックユーザーを分けてカウントする

## 今回の課題
**新規ユーザー、リピーター、カムバックユーザーを分けてカウントする**ためのクエリを作成する段階で、
下記の問題が発生したためメモ

LAGウィンドウ関数を使用して、
一か月前の日付を`last_month`カラムに格納しようとすると、
1カ月前ではなく、1行前の日付が格納されてしまうなどの不具合が発生した。

`date_sub`関数を使用して、一か月前の日付を表現しようとしたが、
WITH句で`date_format`で日付を変換していることにより、
文字列型で日付データが生成されてしまっているため、その方法は使えなかった。

## NG例
#### 使用テーブル
**users**テーブル
|user_id|register_date|
|:-:|:-:|
|001|2022-12-01|
|002|2022-12-02|
|003|2022-12-10|
|004|2022-12-11|
|005|2022-12-25|

**action_log**テーブル
|user_id|stamp|
|:-:|:-:|
|001|2022-12-03 00:00:00|
|002|2022-12-03 00:00:00|
|004|2022-12-13 00:00:00|
|003|2022-12-15 00:00:00|
|004|2023-01-16 00:00:00|
|003|2023-01-17 00:00:00|
|005|2023-01-25 00:00:00|
|003|2023-02-26 00:00:00|
|002|2023-03-27 00:00:00|
|001|2023-03-31 00:00:00|


#### 使用クエリ
```SQL
/* ログテーブルとユーザーマスタを結合。日付を月ベースに変換する。 */
with monthly_action_users as (
    select
        al.user_id
        , date_format(a.stamp, "%Y-%m") as action_month
        , date_format(u.register_date, "%Y-%m") as register_month
    from
        action_log as l
        left join users as u
            on al.user_id = ms.user_id
)
/* LAGウィンドウ関数で、1行前のaction_monthを格納するlast_monthカラムを生成 */
, monthly_last_action_users as (
    select
        action_month
        , lag(action_month) over(partition by user_id order by action_month) as last_month
        , register_month
        , user_id
	from
        monthly_action_users
)
select
    *
from
    monthly_last_action_users
;
```

#### 出力テーブル
|action_month|last_month|register_month|user_id|
|:-:|:-:|:-:|:-:|
|2022-12|NULL|2022-12|001|
|2022-12|NULL|2022-12|002|
|2022-12|NULL|2022-12|004|
|2022-12|NULL|2022-12|003|
|2023-01|2022-12|2022-12|004|
|2023-01|2022-12|2022-12|003|
|2023-01|NULL|2022-12|005|
|2023-02|2023-01|2022-12|003|
|2023-03|2022-12|2022-12|002|
|2023-03|2022-12|2022-12|001|

この出力結果のように、LAGウィンドウ関数を使用するだけでは、
`last_month`カラムで、前月の日付を取得することができなかった。

## 成功例
結論から言うと、日付データがtimestamp型の段階で、
一ヵ月前の日付を取得して、`date_format`関数で月ベースの日付を取得する方法を選べばよかった。
```SQL
/* 日付データを月ベースに変換する。last_monthカラムはtimestamp型の段階で一ヵ月前の日付を取得しておく */
with monthly_user_action as (
    select
        distinct u.user_id
        , date_format(u.register_date, "%Y-%m") as register_month
        , date_format(l.stamp, "%Y-%m") as action_month
        , date_format(date_sub(date(timestamp(l.stamp)), interval 1 month), "%Y-%m") as last_month
    from
        users as u
        left join action_log as l
            on u.user_id = l.user_id
)
select
    *
from
     monthly_user_action
```
このクエリを使えば、`last_month`カラムに、`action_month`の一ヵ月前の日付を格納することができた。

## 新規ユーザー、リピーター、カムバックユーザーを分けてカウントする。
下記のクエリを使えば、実現できる。

**■補足**
・リピーター = 前月も利用しているユーザー
・カムバックユーザー = 前月は利用していないが、前々月以前に利用しているユーザー
```SQL
/* 日付データを月ベースに変換する。last_monthカラムはtimestamp型の段階で一ヵ月前の日付を取得しておく */
with monthly_user_action as (
    select
        distinct u.user_id
        , date_format(u.register_date, "%Y-%m") as register_month
        , date_format(l.stamp, "%Y-%m") as action_month
        , date_format(date_sub(date(timestamp(l.stamp)), interval 1 month), "%Y-%m") as last_month
    from
        users as u
        left join action_log as l
            on u.user_id = l.user_id
),
/* case式でaction_monthをlast_monthやregister_monthを比較して場合分けをする */
monthly_user_with_type as (
    select
        action_month
        , user_id
        , case
            when register_month = action_month then 'new_user'
            when last_month = lag(action_month) over(partition by user_id order by action_month) then 'repeat_user'
            else 'come_back_user'
		end as user
        , last_month
	from
        monthly_user_action
)
select
    action_month
    , count(user_id) as mau
    , count(case when user = 'new_user' then 1 end) as new_users
    , count(case when user = 'repeat_user' then 1 end) as repeat_users
    , count(case when user = 'come_back_user' then 1 end) as come_back_users
from
    monthly_user_with_type
group by
    action_month
```

## 出力テーブル
|action_month|mau|new_users|repeat_users|come_back_users|
|:-:|:-:|:-:|:-:|:-:|
|2022-12|4|4|0|0|
|2023-03|2|0|0|2|
|2023-01|3|0|2|1|
|2023-02|1|0|1|0|

以上です。