mysqlを使って翌日のユーザー数を取得したい

Question

実現したいこと

やりたいことはタイトルの通りです

前提

tableを以下として
id|user_id|date
1 1 2023-04-01
2 1 2023-04-02
3 1 2023-04-03
4 1 2023-04-04
5 2 2023-04-02
6 2 2023-04-03
7 2 2023-04-03

date|active_user|tomorrow_active_user
2023-04-01 1 2
2023-04-02 2 2
2023-04-03 2 1
2023-04-04 1 0
の様に結果が出力されたいです

user_idが一緒で同日のものはひとくくりです。
また、自己結合を使用します。
2重にselect分を使ってもいけないです。

どなたかご教授いただきたいです。

発生している問題・エラーメッセージ

エラーメッセージ

該当のソースコード

SELECT 
    active_users.date, 
    COUNT(DISTINCT active_users.user_id) AS active_users, 
    COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_users
FROM 
    tmp_active_users AS active_users 
LEFT JOIN 
    tmp_active_users AS tomorrow_active_users 
    ON active_users.id = tomorrow_active_users.id
GROUP BY 
    active_users.date;

試したこと

WHERE
active_users.date + INTERVAL 1 DAY = tomorrow_active_users.date
を追加してみたが上手くいきませんでした。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Answer 1

SELECT active_users.date,
       COUNT(DISTINCT active_users.user_id) AS active_user,
       COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_user
FROM active_users
LEFT JOIN active_users AS tomorrow_active_users
ON active_users.date = DATE_SUB(tomorrow_active_users.date, INTERVAL 1 DAY)
GROUP BY active_users.date;

Comments

[nagano-yuhi]

返信ありがとうございます。
これだと、tomorrow_active_userの欄が
1
2
1
0
となってしまい答えとあいません、、

[nagano-yuhi]

2
2
1
0
となるのが理想です。
翌日の人数を出力してます。

[nagano-yuhi]

言葉足らずですみません

[退会済みユーザー]

修正しました

[nagano-yuhi]

できました！
ありがとうございました！！！

Answer 2

@positive words コードを更新していただけますか? 私にはうまくいきません。 ありがとう。

Answer 3

ありがとう baldi's basics