How to write a query to MYSQL?

Question

MySQL

Please tell me how to make a request. There is a table "user" in it 3 columns

ID | Message | date_added
1       |         10     |        2021-08-06
2       |        15      |       2021-08-06
3       |         20     |        2021-08-06
1       |         25     |        2021-08-07
2       |         30     |         2021-08-07
3       |         45     |        2021-08-07
1       |         40     |        2021-08-08
2       |         55     |        2021-08-08
3       |         60     |        2021-08-08
1       |         40     |        2021-08-09
2       |         60     |        2021-08-09
3       |         80     |        2021-08-09
4       |         40     |        2021-08-09

I need to get the number of messages for the last day and these numbers should come out. ID 4 is not displayed, because it has only one record.

ID | Message
1    |     0             
2    |    5            
3    |    20

Answer 1

SELECT UserID, t1.CountMessage-t2.CountMessage
FROM daily_exp_snapshots t1
JOIN daily_exp_snapshots t2 USING (UserID)
WHERE t1.date_added >= CURRENT_DATE
  AND t1.date_added < CURRENT_DATE + INTERVAL 1 DAY
  AND t2.date_added >= CURRENT_DATE - INTERVAL 1 DAY
  AND t2.date_added < CURRENT_DATE