How to count the number of registrations for each day of the month?

Question

MySQL

Hello everyone) I’ll make a reservation right away that the task is educational, but I just can’t figure out how to implement its solution.
There is a registers table that has fields user_id and register_date( date format)
It looks like this:
user_id register_date
001 2021-02-01
002 2021-02-15
003 2021-02-15
004 2021-04-12
005 2021-06 -01
006 2021-06-02
007 2021-06-15

The essence of the task is as follows - to display the number of users who have already been registered by the time of June 2021. The output should be like this:
report_date cnt_all_couriers
2021-06-01 5
2021-06-02 6
2021-06-03 6
2021-06-04 6
---
2021-06-15 7

That is, in addition to the days in which there are records in registers, you need to display all the days of the month and the number of registered clients by this date.

wrote the following code:
CREATE TABLE registers (
user_id int,
register_date date
);

INSERT INTO registers VALUES
(001, '2021-02-01'), (002, '2021-02-15'), (003, '2021-02-15'), (004, '2021-06-01' ), (005, '2021-06-02'), (006, '2021-06-03'), (007, '2021-06-15');

SELECT register_date, sum(user_id) AS cnt_all_users
FROM registers
WHERE register_date LIKE "_%06%_"
GROUP BY register_date

But it doesn't output days that have no entries, how can I fix this? Thank you !

Answer 1

user_id corresponds to the number of registered customers and wrote the following code

Bad decision. There is no guarantee that the id's will be strictly consecutive.
Solution for MySQL 8.0 and higher

WITH RECURSIVE `dates` (`date`) AS (
  SELECT '2021-06-01'
  UNION
  SELECT `date` + INTERVAL 1 DAY
    FROM `dates`
    WHERE `date` < '2021-06-30'
)
SELECT `d`.`date`, MAX(`c`.`count`) OVER `win` AS `registered`
  FROM `dates` AS `d`
  LEFT JOIN (
    SELECT DISTINCT `date`, COUNT(*) OVER `win` AS `count`
      FROM `registers`
      WINDOW `win` AS (ORDER BY `date`)
  ) AS `c` ON `c`.`date` = `d`.`date`
  WINDOW `win` AS (ORDER BY `d`.`date`)